Welcome to the Beyond Blog

John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.



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}')
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!

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

Comments

No comments made yet. Be the first to submit a comment
Already Registered? Login Here
Guest
Friday, 22 June 2018

Demonstration

Request a demo of our products here

REQUEST DEMO

Contact

Beyond Systems Limited

Barnett House, 53 Fountain Street,

Manchester M2 2AN United Kingdom

 

 

Tel:    +44(0)8450 940 998

Email:  contact@wegobeyond.co.uk