Welcome to the Beyond Blog

John's Blog

The views expressed in this blog and completely my own and do not reflect those of Beyond Systems Ltd. All content is provided for informational purposes only, and you are solely responsible for how you use this on your own or others systems. We accept no liability for any losses or damages caused.

Automating Oracle APEX Backups with Subversion

I know there are already a good number of blogs/guides out there already describing various methods of automating the backup of APEX applications, however I thought I'd share the method I recently implemented internally as it uses a remote subversion repository. This gives rise to a number of subsequent benefits such as holding a full version history, low storage overheads and resilience to local hardware failure. Why might you want to do this? Well, aside from the obvious catastrophes, acts of God, malicious deletion, accidental corruption etc, it's sometimes simply useful to be able to take your application as of a particular point in time, regardless of your database flashback etc.
Anyway, here is the process we take will follow.

  1. Export all our APEX applications from the workspace.
  2. Add any new applications that we've not seen before to the svn repository.
  3. Commit any changes to svn

First we need to create a working directory of our repository on the APEX database server. Note that I already added all applications to this repository previously - this is not necessary however. I chose to check out a specific directory only rather than the root. You of course need to install the svn client software on your server if you haven't already for this step. It's free and easy - and not worth explaining here.

[oracle@localhost tmp]$ svn checkout https://mysvnrepo/folder/subfolder/etc svn
A    svn/f101.sql
A    svn/f10100.sql
A    svn/f110.sql
A    svn/f10200.sql
A    svn/f20100.sql
A    svn/f10210.sql
A    svn/f20200.sql
A    svn/f20300.sql
Checked out revision 1079.

To export we can use the APEXExport Java utility. This is called in the following way.

java -cp $CLASSPATH oracle.apex.APEXExport -db <database connection> -user <database user> -password <database password> -workspaceid <workspace id>

This will generate a set of .sql files in the format f<application_id>.sql in the current directory, which we can then copy into our working directory. The issue here is that an APEX export file contains a line representing the date and time at which the export was done. This will then be considered a change by svn. To avoid that, I strip out that line using the sed utility.

sed -i '/--   Date and Time:/d' f*.sql


Then we need to detect whether there are any files which we haven't seen before that needed to be added to the repository... we can do this using the command svn status and look for any entries beginning with a ? - any we find, we add to the repository.

for i in $(svn status ./svn | egrep "^\?" | awk '{print $2}')
  if [[ "$i" != "" ]]
    svn add "$i"

Finally we use the same command as above, but looking for Added or Modified entries - as we only want to commit when this has occurred (we don't ever delete from the backups).

if [[ $(svn status ./svn | egrep "^(M|A)") != "" ]]
  svn ci -m "Automatic Backup $(date)" ./svn 

All that needs to be done now is to schedule the script to execute periodically. For that we can use a crontab entry.

# Run at 8pm every night. You can run as often as you wish.
0 20 * * * ~/apex_backups/backup_apps.sh

Now whenever any changes have occurred at 8pm each night, they will automatically be transferred to the subversion repository:

[oracle@localhost apex_backups]$ svn log svn
r1075 | myusername | 2017-11-13 20:05:49 +0000 (Mon, 13 Nov 2017) | 1 line

Automatic Backup Mon Nov 13 20:05:47 GMT 2017
r1074 | myusername| 2017-11-13 20:01:16 +0000 (Mon, 13 Nov 2017) | 1 line

Automatic Backup Mon Nov 13 20:00:17 GMT 2017
r1070 | myusername | 2017-11-13 14:19:34 +0000 (Mon, 13 Nov 2017) | 1 line

Automatic Backup Mon Nov 13 14:19:28 GMT 2017

You'll of course want to pull all the above into a script to add into the cron job - I used this, which you can adapt to your needs - fill out the svn directory and database credentials as appropriate for your environment.



# Set the environment
. ~/.bashrc

# Make sure we're at the latest version of the repo
svn up $SVN_FOLDER

# Get the ID of the workspace - this could be hard-coded, but I prefer to get it from the name.
set heading off
column workspace_id format 999999999999999999999
select workspace_id from apex_workspaces where workspace='$WORKSPACE';

# Download the app exports
java -cp $CLASSPATH oracle.apex.APEXExport -db $DB_CONN -user $DB_USER -password $DB_PASS -workspaceid $WORKSPACE_ID

# Strip out the modification dates and move to svn folder
sed -i '/--   Date and Time:/d' f*.sql
mv f*.sql $SVN_FOLDER/

# Add any new exports that haven't been already
for i in $(svn status $SVN_FOLDER | egrep "^\?" | awk '{print $2}')
  if [[ "$i" != "" ]]
    svn add "$i"

# Commit the changes if required
if [[ $(svn status $SVN_FOLDER | egrep "^(M|A)") != "" ]]
  svn ci -m "Automatic Backup $(date)" $SVN_FOLDER 

exit 0

As per anything I post, if you enhance in any way then let me know in the comments so I can update the post. Thanks!

Provisioning an Oracle Database Cloud Instance - F...
Oracle APEX Application Exploits - Part 3


No comments made yet. Be the first to submit a comment
Already Registered? Login Here
Tuesday, 25 February 2020


Request a demo of our products here



Beyond Systems Limited

Suite 1.01 Jactin House,

24 Hood Street, Manchester

M4 6WX United Kingdom



Tel:    +44(0)8450 940 998

Email:  contact@wegobeyond.co.uk