Automatically Backing Up Your Apex Application(s)

Normally you can backup a specific ApEx Application by taking a manual export via the standard Apex interface. You can rename the export file by putting a date/hour notion in the name and copy the renamed file on a drive that is included in your general corporate backup process.

This is a laborious work … And once in while you forget to do it…

So, it would be nice to automate this process without requiring a manual export from the Web interface.

Since ApEx 2.2 you get a java-based export utility for free that allows us to export ApEx applications from the command-line.

When you download Oracle Application Express (here) and unzip the file , you will notice that there is an extra subdirectory named …/Utilities. This contains 2 java programs and a small readme-file explaining how to use these command-line export utilities.

The first program is APEXExport. You can easily integrate this APEXExport-program in a script and schedule this script on a daily base.

Following is an example of backup-bat file that exports for a windows environment a given ApEx application (by application id), rename it by putting a date notion in the filename and move it to another (backup) directory. This bat-file can be scheduled in windows using the AT command.

You should adapt the values in blue to your local situation.

@echo off
@REM *****************************************************************
@REM * File: backup.bat
@REM * Purpose : Export a given oracle ApEx application
@REM *****************************************************************
@if ‘%theDrive%’ == ” set theDrive=d:
@if ‘%theOraclePath%’ == ” Set theOraclePath=C:\Oracle\10gR2\HTMLDB\jdbc\lib\classes12.jar
@if ‘%theUtilityPath%’ == ” set theUtilityPath=d:\iaf\utilities
@if ‘%theDbHost%’ == ” set theDbHost=wxpp-vhoofe:1521:orcl
@if ‘%theUser%’ == ” set theUser=iaf
@if ‘%thePwd%’ == ” set thePwd=iaf
@if ‘%theAppId%’ == ” set theAppId=131
@if ‘%theBckDir%’ == ” set theBckDir=D:\Iaf\backups\apex\
@REM *****************************************************************
@REM * DO NOT CHANGE CODE AFTER THIS LINE
@REM *****************************************************************
@REM => change current directory
@REM *****************************************************************
%theDrive%
cd %theUtilityPath%
@REM *****************************************************************
@REM => do the export
@REM *****************************************************************
set CLASSPATH=%CLASSPATH%;%theOraclePath%;%theUtilityPath%
java oracle.apex.APEXExport -db %theDbHost% -user %theUser% -password %thePwd% -applicationid %theAppId%

@REM *****************************************************************
@REM => Rename and move the file
@REM *****************************************************************
@for /F “tokens=2,3,4 delims=/ ” %%i in (‘date /t’) do set DateStamp=%%k%%j%%i
@for /F “tokens=1,2,3 delims=:,. ” %%i in (‘time /t’) do set TimeStamp=%%i%%j%%k
@SET BackupFile=%theBckDir%f_%theAppId%_%DateStamp%_%TimeStamp%.sql
move f%theAppId%.sql %BackupFile%
@REM***** End BAT File ************************************************

The yellow part contains the actual call to the java export program. For explanation of all the available parameters, you can verify the readme-file.

The second java program is called APEXExportSplitter and it can be used to split an export file into separate SQL scripts. This can be useful for example when you want to restore a discrete component/element of your ApEx application.

Give it a try…