Automatically backup Oracle APEX applications to Subversion

In continue of my previous posts about APEX and Subversion I made a short script using APEXExport utility to automate and backup APEX to Subversion repository.

I'm using Oracle Express Edition 11.2, which by default doesn't have the APEXExport utility. That's why you need first to create the directory holding it and then copy the utility there.

Create destination directory holding the utility and copy APEXExport.class there:

mkdir -p $ORACLE_HOME/utilities/oracle/apex

Create backup directory in the SVN project for holding the backup files:

[oracle@dbsrv ~]$ svn mkdir -m "Making a backup dir." http://192.168.8.34/svn/oracle/Corporate_Portal/trunk/Apex/Backup

Committed revision 33.

At this point you'll be asked for password which must be cached in order the automated backup to work correctly. The password itself is written (I assume you're logged in with oracle user) in file in this directory: /home/oracle/.subversion/auth/svn.simple/

Finally you need to decide where the backup directory will be checked out. This will be used to store all the APEX backups, which will be committed to the SVN repository:

cd /oracle
svn checkout http://192.168.8.34/svn/oracle/Corporate_Portal/trunk/Apex/Backup

This is the script for automating the backup of APEX applications, because the formatting is not correct here is download link for the script:

#!/bin/bash

### Automated APEX applications backup to Subversion (SVN), v1
### 2012-03-07
### Svetoslav Gyurov, http://sve.to

# Environment settings for Oracle database and Java
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export JAVA_HOME=/oracle/ora112ee/jdk

export PATH=$ORACLE_HOME/bin:$PATH
export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/utilities

# How many days/backups should be kept back
export RETENTION=7
export COMPRESS=0

# SVN directory for Oracle APEX
SVN_DIR=/oracle/Backup

# Get current date and oldest backup
DATE=`date +%Y%m%d`
OLDEST_BACKUP=`date --date="-${RETENTION} days" +%Y%m%d`

# Change to SVN directory and export data
cd $SVN_DIR
$JAVA_HOME/bin/java oracle.apex.APEXExport -db 192.168.8.34:1522:XE -user 		sve -password secret -applicationid 100

exitValue=$?

# If APEXExport is successfully continue, otherwise fail with message
if [ $exitValue -eq 0 ];
then
	svn update

for i in `ls -1 f*.sql`
do
	if [ $COMPRESS -eq 1 ];
then
	gzip $i
	mv $i.gz ${DATE}_$i.gz
	svn add ${DATE}_$i.gz

if [ -f ${OLDEST_BACKUP}_$i.gz ];
then
	rm ${OLDEST_BACKUP}_$i.gz
	svn remove ${OLDEST_BACKUP}_$i.gz
fi
else
	mv $i ${DATE}_$i
	svn add ${DATE}_$i

if [ -f ${OLDEST_BACKUP}_$i ];
then
	rm ${OLDEST_BACKUP}_$i
	svn remove ${OLDEST_BACKUP}_$i
	fi
fi
done

svn commit -m "Daily APEX backup completed and committed."

echo Daily APEX backup completed.

else
	echo "APEX applications could not be exported"
fi

Of course there are few parameters which need to be set. These are ORACLE_HOME, JAVA_HOME, RETENTION period defines how many backups to be kept back and COMPRESS defines whether the exported applications be compressed with gzip. Also don't forget to change the application id, you could also put the whole workspace to be backed up.

Finally put the script at the crontab:

31 1 * * * /home/oracle/apex_backup.sh > /dev/null 2>&1

To check whether the backup was successful run svn info or svn log within the backup directory or just browse the repository:

cd /oracle/Backup
[oracle@dbsvn Backup]$ svn info
Path: .
URL: http://192.168.8.34/svn/oracle/Corporate_Portal/trunk/Apex/Backup
Repository Root: http://192.168.8.34/svn/oracle
Repository UUID: 40fb9d41-50a5-4b10-b803-b133fb623816
Revision: 59
Node Kind: directory
Schedule: normal
Last Changed Author: oracle
Last Changed Rev: 59
Last Changed Date: 2012-03-07 01:31:07 +0200 (Wed, 07 Mar 2012)

[oracle@dbsrv Backup]$ svn log
------------------------------------------------------------------------
r59 | oracle | 2012-03-07 01:31:07 +0200 (Wed, 07 Mar 2012) | 1 line

Daily APEX backup completed and commited.
------------------------------------------------------------------------

Feel free to modify the script for your own needs.

Regards,
Sve