Cloud

O SpecGold OracleBusIntApps7 clr

 Gcloud

 

   Call us now 

  Manchester Office

  +44 (0) 8450 940 998

 

  

 

John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.

The views expressed in this blog and completely my own and do not reflect those of Beyond Systems Ltd. All content is provided for informational purposes only, and you are solely responsible for how you use this on your own or others systems. We accept no liability for any losses or damages caused.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Team Blogs
    Team Blogs Find your favorite team blogs here.
  • Login
    Login Login form

Automating Oracle APEX Backups with Subversion

  • Font size: Larger Smaller
  • Subscribe to this entry
  • Print
Automating Oracle APEX Backups with Subversion

I know there are already a good number of blogs/guides out there already describing various methods of automating the backup of APEX applications, however I thought I'd share the method I recently implemented internally as it uses a remote subversion repository. This gives rise to a number of subsequent benefits such as holding a full version history, low storage overheads and resilience to local hardware failure. Why might you want to do this? Well, aside from the obvious catastrophes, acts of God, malicious deletion, accidental corruption etc, it's sometimes simply useful to be able to take your application as of a particular point in time, regardless of your database flashback etc.
Anyway, here is the process we take will follow.

  1. Export all our APEX applications from the workspace.
  2. Add any new applications that we've not seen before to the svn repository.
  3. Commit any changes to svn

First we need to create a working directory of our repository on the APEX database server. Note that I already added all applications to this repository previously - this is not necessary however. I chose to check out a specific directory only rather than the root. You of course need to install the svn client software on your server if you haven't already for this step. It's free and easy - and not worth explaining here.

[oracle@localhost tmp]$ svn checkout https://mysvnrepo/folder/subfolder/etc svn
A    svn/f101.sql
A    svn/f10100.sql
A    svn/f110.sql
A    svn/f10200.sql
A    svn/f20100.sql
A    svn/f10210.sql
A    svn/f20200.sql
A    svn/f20300.sql
Checked out revision 1079.

To export we can use the APEXExport Java utility. This is called in the following way.

java -cp $CLASSPATH oracle.apex.APEXExport -db <database connection> -user <database user> -password <database password> -workspaceid <workspace id>

This will generate a set of .sql files in the format f<application_id>.sql in the current directory, which we can then copy into our working directory. The issue here is that an APEX export file contains a line representing the date and time at which the export was done. This will then be considered a change by svn. To avoid that, I strip out that line using the sed utility.

sed -i '/--   Date and Time:/d' f*.sql

 

Then we need to detect whether there are any files which we haven't seen before that needed to be added to the repository... we can do this using the command svn status and look for any entries beginning with a ? - any we find, we add to the repository.

for i in $(svn status ./svn | egrep "^\?" | awk '{print $2}')
do
  if [[ "$i" != "" ]]
  then
    svn add "$i"
  fi
done

Finally we use the same command as above, but looking for Added or Modified entries - as we only want to commit when this has occurred (we don't ever delete from the backups).

if [[ $(svn status ./svn | egrep "^(M|A)") != "" ]]
then 
  svn ci -m "Automatic Backup $(date)" ./svn 
fi

All that needs to be done now is to schedule the script to execute periodically. For that we can use a crontab entry.

# Run at 8pm every night. You can run as often as you wish.
0 20 * * * ~/apex_backups/backup_apps.sh

Now whenever any changes have occurred at 8pm each night, they will automatically be transferred to the subversion repository:

[oracle@localhost apex_backups]$ svn log svn
------------------------------------------------------------------------
r1075 | myusername | 2017-11-13 20:05:49 +0000 (Mon, 13 Nov 2017) | 1 line

Automatic Backup Mon Nov 13 20:05:47 GMT 2017
------------------------------------------------------------------------
r1074 | myusername| 2017-11-13 20:01:16 +0000 (Mon, 13 Nov 2017) | 1 line

Automatic Backup Mon Nov 13 20:00:17 GMT 2017
------------------------------------------------------------------------
r1070 | myusername | 2017-11-13 14:19:34 +0000 (Mon, 13 Nov 2017) | 1 line

Automatic Backup Mon Nov 13 14:19:28 GMT 2017

You'll of course want to pull all the above into a script to add into the cron job - I used this, which you can adapt to your needs - fill out the svn directory and database credentials as appropriate for your environment.

#!/bin/bash

WORKSPACE="MY WORKSPACE"
DB_USER=user1
DB_PASS=mypassword
DB_CONN="localhost:1521/orcl"
BASE_FOLDER=~/apex_backups
SVN_FOLDER=$BASE_FOLDER/svn

# Set the environment
. ~/.bashrc
cd $BASE_FOLDER
APEX_HOME=~/apex
PATH=${PATH}:${ORACLE_HOME}/jdk/bin
CLASSPATH=${CLASSPATH}:${APEX_HOME}/utilities/:${ORACLE_HOME}/jdbc/lib/ojdbc8.jar

# Make sure we're at the latest version of the repo
svn up $SVN_FOLDER

# Get the ID of the workspace - this could be hard-coded, but I prefer to get it from the name.
WORKSPACE_ID=$(sqlplus -s $DB_USER/$DB_PASS <<EOF
set heading off
column workspace_id format 999999999999999999999
select workspace_id from apex_workspaces where workspace='$WORKSPACE';
exit;
EOF
)

# Download the app exports
java -cp $CLASSPATH oracle.apex.APEXExport -db $DB_CONN -user $DB_USER -password $DB_PASS -workspaceid $WORKSPACE_ID

# Strip out the modification dates and move to svn folder
sed -i '/--   Date and Time:/d' f*.sql
mv f*.sql $SVN_FOLDER/

# Add any new exports that haven't been already
for i in $(svn status $SVN_FOLDER | egrep "^\?" | awk '{print $2}')
do
  if [[ "$i" != "" ]]
  then
    svn add "$i"
  fi
done


# Commit the changes if required
if [[ $(svn status $SVN_FOLDER | egrep "^(M|A)") != "" ]]
then 
  svn ci -m "Automatic Backup $(date)" $SVN_FOLDER 
fi

exit 0

As per anything I post, if you enhance in any way then let me know in the comments so I can update the post. Thanks!

Last modified on
Tagged in: APEX
in Technical Hits: 714 0 Comments
0

My primary area of interest and expertise is Oracle E-Business Suite, particularly Foundation, Human Resources, Payroll, Time and Labor, Warehouse Management and Inventory. Whilst I prefer to focus on functional aspects I do have a keen interest in technical areas too such as SQL tuning/the optimizer, Application Express (APEX), Unix (particularly Linux) and general development.











  ACE Logo




Cert Logo  
Cert Logo 2

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest
Guest Saturday, 16 December 2017