For reference -
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
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)"
cd $adir
export CLASSPATH=.:${ORACLE_HOME}/jdbc/lib/ojdbc14.jar
echo APEX Backup Started $(date) >> $adir/backup_log
java oracle/apex/APEXExport -db localhost:1521:XE -user APEX_040100 -password HDlsdkjHs -workspaceid 1316703665945575 >> $adir/backup_log
#Check if mount does NOT exist
if ! grep -qs '/mnt/backup' /proc/mounts; then
mount /mnt/backup
fi
#Create backup directories
if [ ! -d "$ddir" ]; then
mkdir $ddir
fi
if [ ! -d "$tdir" ]; then
mkdir $tdir
fi
#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.
