DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, July 4, 2018

Shell script to export all APEX Workspace and Applications as SQL scripts

Environment Details:

Oracle 11gR2 , Application Express 5.1.4 Oracle Linux 6.4 64-bit, Java JDK 8u131

Go to the apex software directory
cd /u10test/oracle/R12/db/tech_st/11.2.0.4/apex/utilities

1.Script for Workspace Export

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

CLASSPATH=$CLASSPATH:/u10test/oracle/R12/db/tech_st/11.2.0.4/oui/jlib/classes12.jar; export CLASSPATH
BACKUPDIR=/u01/apex_backup

cd /u10test/oracle/R12/db/tech_st/11.2.0.4/apex/utilities
usr/jdk/jdk1.7.0_80/bin/java oracle.apex.APEXExport -db localhost:1521:ora11g01 -user system -password <SYSTEM password> -expWorkspace

mv f*.sql $BACKUPDIR

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

2.Script for Application Export

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

CLASSPATH=$CLASSPATH:/u10test/oracle/R12/db/tech_st/11.2.0.4/oui/jlib/classes12.jar; export CLASSPATH
BACKUPDIR=/u01/apex_backup

cd /u10test/oracle/R12/db/tech_st/11.2.0.4/apex/utilities

usr/jdk/jdk1.7.0_80/bin/java oracle.apex.APEXExport -db localhost:1521:ora11g01 -user system -password <SYSTEM password> -instance

mv f*.sql $BACKUPDIR

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

------------------------------------------xxxxxxxxxxxxxxxxxxxx-------------------------------

IMPORT STEPS
1. On the target DB, create the DB schemas the APEX workspaces will be based upon and accomplish the standard DB import.
2. Accomplish the workspace import from sqlplus as the appropriate apex schema.  For example:
- connect sys as sysdba
alter user APEX_050100 identified by oracle123 account unlock;
- alter session set current_schema = APEX_050100;
- run the scripts to create the workspaces

3.cd APEX_APPLICATION/
[oracloud@testdb APEX_APPLICATION]$ ls -ltr
total 69436
-rwxr-xr-x. 1 root root 4857376 Jun 15 16:00 f100.sql
-rwxr-xr-x. 1 root root 1152903 Jun 15 16:00 f101.sql
-rwxr-xr-x. 1 root root 1591910 Jun 15 16:00 f102.sql
-rwxr-xr-x. 1 root root  221463 Jun 15 16:00 f103.sql
-rwxr-xr-x. 1 root root  384656 Jun 15 16:00 f104.sql
-rwxr-xr-x. 1 root root 3402983 Jun 15 16:00 f105.sql

ls -ltr|awk {'print $9'}>hi.txt
 cat hi.txt|while read line
> do
> sqlplus "/ as sysdba"<<!
> alter session set current_schema = APEX_050100;
> @$line
> exit;
> !
> done

No comments: