Senthil Murugan's Blog

Home » Apex » Command Line Backups for APEX – Thanks to Martin

Command Line Backups for APEX – Thanks to Martin

http://www.talkapex.com/2012/04/command-line-backups-for-apex.html

One thing developers like to do is keep local backup copies of their development work. Sometimes they’ll copy files and suffix them with .bak or they’ll store them in a version control repository. With APEX you don’t copy a file to back it up. Either you have to copy the application (within the workspace) or export the application and save it locally. For one-off backups this can be ok, but as you develop with more and more applications in larger environments it can be tedious work and will slow down your development time.
Thankfully APEX has a tool, that comes as part of the install zip file, which allows for command line exports. It’s one of the lesser known tools that is extremely useful.
Below is a simplified copy of a batch file which will automatically backup all the APEX applications on a given database using the APEX backup tool. I usually recommend that organizations run such a script on their development environments to store hourly backups of their development environments. The example is a very simple example which should be modified to tag the backup with the date/time or zip and store the backups in version control. I’ve also appended an extra script which lists all the APEX applications, workspaces, and IDs and stores this information as part of the backup in a .html file.
To use the script, create the files listed below in the same directory and make the appropriate modifications inapex_backup.bat
apex_backup.bat
Backups all the applications in the database

REM ***** START MODIFICATIONS ******

REM ***** Parameters ******

REM Oracle information

REM To find SID name: select * from v$instance;

set oracleSID=XE

set oracleSystemPass=oracle

set oraclePort=1526

set oracleHost=localhost

REM Root directory where oracleapex*class files are stored

REM These is a sub directory from the extracted APEX install zip file

set apexExportJavaDir=C:tempapex_4.1.1_enapexutilities

REM File that contains names of applications and workspaces

set apexHtmlFileName=apex_info.html

REM Directory where backups will be stored

set backupLocation=C:tempapexbackup

REM ****** PATHS *********

REM Note: You may not need to explicitly define these as they may already be set in OS.

set ORACLE_HOME=C:oracleproduct11.2.0client_1

set CLASSPATH=%CLASSPATH%;.;%ORACLE_HOME%jdbclibojdbc5.jar;%apexExportJavaDir%

set PATH=%PATH%;.;C:Program Files (x86)Javajre6bin

REM ***** END MODIFICATIONS ******

REM ****** Other *********

set startRunLocation=%CD%

REM ****** Directory Setup ******

REM Create temp bacpkup location

mkdir %backupLocation%

REM ****** APEX BACKUP *******

REM Go to backup location to run backups in

cd %backupLocation%

REM Export all applications

java oracle.apex.APEXExport -db %oracleHost%:%oraclePort%:%oracleSID% -user system -password %oracleSystemPass% -instance

REM Export all Workspaces

java oracle.apex.APEXExport -db %oracleHost%:%oraclePort%:%oracleSID% -user system -password %oracleSystemPass% -expWorkspace

REM Generate listing of Workspaces and Applications

sqlplus system/%oracleSystemPass%@%oracleHost%:%oraclePort%/%oracleSID% @%startRunLocation%apex_backup_info.sql %apexHtmlFileName%

REM Back to start location

cd %startRunLocation%

apex_backup_info.sql
Stores all the application names, workspaces, and IDs

-- Parameters

-- 1 = File Name

-- http://forums.devshed.com/oracle-development-96/sql-plus-column-width-format-185085.html

-- http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1012748

SET NUMFORMAT 9999999999999999999999999999999999999

SET MARKUP HTML ON SPOOL ON HEAD "<title>APEX Export</title>"

SET ECHO OFF

SPOOL &1

-- Current Date

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') current_date

from dual;

-- Applications

SELECT application_id, workspace, application_name, owner

FROM apex_applications

WHERE workspace != 'INTERNAL'

ORDER BY application_id;

-- Workspaces

SELECT workspace_id, workspace

FROM apex_workspaces

WHERE workspace != 'INTERNAL'

ORDER BY workspace_id;

SPOOL OFF

SET MARKUP HTML OFF

SET ECHO ON

EXIT;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: