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.
- Export all our APEX applications from the workspace.
- Add any new applications that we’ve not seen before to the svn repository.
- 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}') do if [[ "$i" != "" ]] then svn add "$i" fi done 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)") != "" ]] then svn ci -m "Automatic Backup $(date)" ./svn fi
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.
#!/bin/bash WORKSPACE="MY WORKSPACE" DB_USER=user1 DB_PASS=mypassword DB_CONN="localhost:1521/orcl" BASE_FOLDER=~/apex_backups SVN_FOLDER=$BASE_FOLDER/svn # Set the environment . ~/.bashrc cd $BASE_FOLDER APEX_HOME=~/apex PATH=${PATH}:${ORACLE_HOME}/jdk/bin CLASSPATH=${CLASSPATH}:${APEX_HOME}/utilities/:${ORACLE_HOME}/jdbc/lib/ojdbc8.jar # 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. WORKSPACE_ID=$(sqlplus -s $DB_USER/$DB_PASS <<EOF set heading off column workspace_id format 999999999999999999999 select workspace_id from apex_workspaces where workspace='$WORKSPACE'; exit; EOF ) # 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}') do if [[ "$i" != "" ]] then svn add "$i" fi done # Commit the changes if required if [[ $(svn status $SVN_FOLDER | egrep "^(M|A)") != "" ]] then svn ci -m "Automatic Backup $(date)" $SVN_FOLDER fi 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!