Shell script to export all APEX applications as SQL scripts

Environment: Oracle 11gR2 EE, Application Express 4.2.2, Oracle Linux 6.4 64-bit, Java JDK 7u21

The APEX software contains a Java class which can be used to script or schedule the export of one or more APEX applications. I will show you in this post how to do this.

First, I unzipped the APEX software to /u01/app/apex. The Java class file is called “APEXExport.class” and can be found in the folder /u01/app/apex/utilities/oracle/apex.

You also need a Java JDK. I installed Java 7 JDK (update 21) under /u01/app/java/java7.

You might need to unlock (and set a password) for the APEX schema owner (in my case, APEX_040200). I’m not sure if it’s possible to export the applications with another user.

This is how my script looks like:

#!/bin/sh
# script to export all ApEx applications as sql scripts

CLASSPATH=$CLASSPATH:/u01/app/oracle/product/11.2.0/db_1/oui/jlib/classes12.jar; export CLASSPATH
BACKUPDIR=/u05/apex_backup

cd /u01/app/apex/utilities

/u01/app/java/java7/bin/java oracle.apex.APEXExport -db localhost:1521:ora11g01 -user apex_040200 -password <password> -instance

mv f*.sql $BACKUPDIR

if [ -f $BACKUPDIR/apex_export.log ]
then
    cat $BACKUPDIR/apex_export.log|mail -s "APEX export - `hostname -s`" matthiash@mydomain.com
fi

You need to include the file classes12.jar in your CLASSPATH or the JDBC connection won’t work.

The -instance setting will export all your applications. To limit the number of applications, you can also use -workspaceid or -applicationid. Check the readme.txt file under the utilities folder for more information.

To schedule the script with cron, I added the following line to my crontab file:

00 22 * * * /home/oracle/scripts/apex_export.sh 1>/u05/apex_backup/apex_export.log 2>&1

This will execute the script each night at 22:00 and mail the log file to the mail address inside the script.

Happy scripting! :-)

Matthias

Script to monitor process on Linux

Environment: Oracle Linux 6.2

Sometimes you want to monitor a process on your Linux server and do some alerting when the process is not running anymore.

The following shell script can be used as an example. It will monitor the “dbconsole” process (database control) and send a e-mail message when the process is not running anymore.

$ vi check_db_control.sh

#!/bin/sh

PROCESSFILE='dbconsole'

if [ `ps ax|grep -v grep|grep -ic $PROCESSFILE` -gt 0 ]
then
        echo "$PROCESSFILE is running, everything is fine"
else
        echo "$PROCESSFILE is not running on `hostname -s`!" | mail -s "$PROCESSFILE down!" matthiash@mydomain.com
fi

exit

$ chmod u+x check_db_control.sh

To schedule the script, you can add it to cron. For example, this script will run every day at 10:00AM:

$ crontab -e

# Monitoring dbconsole process
00 10 * * * /home/oracle/scripts/check_db_control.sh 1>/dev/null 2>&1

 

HTH,
Matthias