JessWatts.com

For reference -

• APEX setup guide
• APEXExport Overview

This walk-through assumes you have APEX 4.1 Listener and Oracle 10G XE or greater installed. If not, reference the links above.

We will break this walk-through up into 4 sections:

• Mounting a network share for the backup directory and configuring fstab
• Configuring APEXExport to correctly talk to your database
• Write a script to make sure your backup directories exist and export all of your applications
• Configure a cron job to run the script twice a day 7 days a week

Mounting a network share for the backup directory and configuring fstab
Create local mount point
mkdir /mnt/backup

Create connection file
nano /etc/cifspw
username
password


Edit fstab
nano /etc/fstab
//server.domain.com/share /mnt/backup cifs exec,credentials=/etc/cifspw,uid=root 0 0


Mount the directory
mount /mnt/backup

Configuring APEXExport to correctly talk to your database

You must run APEXExport from the utilities folder where you installed APEX
cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/apex/utilities

Export the CLASSPATH
export CLASSPATH=.:${ORACLE_HOME}/jdbc/lib/ojdbc14.jar

APEXExport is run in the following syntax:
java oracle/apex/APEXExport -db localhost:port:SID -user schemaOwner -password yourPass -workspaceid workspaceID
port = 1521
SID = XE

schemaOwner can be found based on your version of APEX. If you are running 3.5 user = APEX_030500. If you are running 4.1 user = APEX_040100
Unlock user and set password through SQL*Plus as SYSTEM
SQL> ALTER USER APEX_040100 ACCOUNT UNLOCK;
SQL> ALTER USER APEX_040100 identified by yourPass;

workspace ID can be found from the SQL workshop utility
select workspace, schemas, workspace_id
from apex_workspaces;

Your APEXExport command should now run successfully by itself and look similar to
java oracle/apex/APEXExport -db localhost:1521:XE -user APEX_040100 -password yourPass -workspaceid 13541863513546

Write a script to make sure your backup directories exist and export all of your applications
Create a new script
touch apex_backup.sh
chmod a+x apex_backup.sh

The script I wrote creates a folder for the day of the week (Mon) and a folder in that folder for the time of day (AM/PM) since I want to backup 2 times a day 7 days a week. It then rolls over each week. The script performs the following steps.
1. Create variables for the backup directories
#/bin/bash

adir=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/apex/utilities
ddir=/mnt/backup/config/apex/backups/$(date +%A)
tdir="$ddir/$(date +%p)"

2. Make sure the working directory and path are correct
cd $adir
export CLASSPATH=.:${ORACLE_HOME}/jdbc/lib/ojdbc14.jar

3. Start the backup log and execute the APEXExport command
echo APEX Backup Started $(date) >> $adir/backup_log
java oracle/apex/APEXExport -db localhost:1521:XE -user APEX_040100 -password yourPass -workspaceid 13541863513546 >> $adir/backup_log

At this point the each application is exported in the form f101.sql f102.sql and so on (101 = application id).

4. Make sure backup directory is mounted
#Check if mount does NOT exist
if ! grep -qs '/mnt/backup' /proc/mounts; then
mount /mnt/backup
fi

5. Create backup directories
#Create backup directories
if [ ! -d "$ddir" ]; then
mkdir $ddir
fi
if [ ! -d "$tdir" ]; then
mkdir $tdir
fi

6. Move backups and copy logs to backup directory
#Copy backups and logs to backup directories
cp -f f*.sql $tdir
echo APEX Backup Completed $(date) >> $adir/backup_log
cp -f backup_log $tdir

So now that we know what each section of the code is doing here is the entire script:
#/bin/bash

#Copy backups and logs to backup directories
cp -f f*.sql $tdir
echo APEX Backup Completed $(date) >> $adir/backup_log
cp -f backup_log $tdir

Configure a cron job to run the script twice a day 7 days a week
Edit crontab as root
su
crontab -e

Add one job to run the script at 11:55am and another to run the script at 5:00pm
#APEXExport AM
55 11 * * * /root/apex_backup.sh >/dev/null 2>&1
#APEXExport PM
0 17 * * * /root/apex_backup.sh >/dev/null 2>&1

After a week you will have AM and PM backups for Mon – Sun.