APEX Application Migration

Schema Migration

To Migrate a Schema used by an APEX application from one Database Instance to another...

  • Create target tablespace(s) for the application tables

(it is slightly easier if the target tablespace name is the same as the source tablespace name)

  • Use DataPump to duplicate the Schema(s) supporting the application

APEXExport

To Migrate APEX Workspaces and Applications from one Database Instance to another...

Ensure JAVA Environment is appropriately set

Note: There appears to be a bug when using JDK 5 to run the export.Note: Use the correct version of apex utilities

Identify Workspaces on Source database

@/u01/dba/apx/apexwspace.sql

set linesize 200

set pagesize 40

column workspace_id format 999999999999999999

column workspace format a30


SELECT workspace_id, workspace

FROM apex_workspaces

WHERE workspace != 'INTERNAL'

ORDER BY workspace_id;

Identify Applications on Source database

@/u01/dba/apx/apexapps.sql

set linesize 200

set pagesize 40

column application_id format 999999999999999

column application_name format a60

column version format a12

column owner format a15

column workspace format a30


SELECT application_id, workspace, application_name, owner, version

FROM apex_applications

WHERE workspace != 'INTERNAL'

AND availability_status !='Unavailable'

ORDER BY application_id;

An alternate script covering all applications including internal and unavailable...

@/u01/dba/apx/apexallapps.sql

set linesize 200

set pagesize 40

column application_id format 999999999999999

column application_name format a60

column version format a12

column owner format a15

column availability_status format a30

column workspace format a30


SELECT application_id, workspace, application_name, owner, version, availability_status

FROM apex_applications

WHERE workspace != 'INTERNAL'

ORDER BY application_id;

Set Variables

Unless you are exporting from a different instance, all variables except APPLID and SYSPWD should be set in your .profile.

export APXHST=$(hostname -s) # Hostname of Source database

export APXPRT=9999 # Port of Source database

export APXSID=${ORACLE_SID} # ORACLE_SID of Source database

export APXSYS=syspwd # SYSTEM password of Source database

export APXAPP=999 # Application ID of the application to be exported (not required for export of All)

export APXDIR=/u01/img/apex/18.1 # Installation Directory of Current version of APEX

To see current settings use:

env | grep APX

NOTE: APEXExport cannot currently use an Oracle Wallet.

To export a specific application

cd ${APXDIR}

java oracle.apex.APEXExport -db ${APXHST}:${APXPRT}:${APXSID} -user system -password ${APXSYS} \

-applicationid ${APXAPP} -expPubReports -expSavedReports

To export all applications

cd ${APXDIR}

java oracle.apex.APEXExport -db ${APXHST}:${APXPRT}:${APXSID} -user system -password ${APXSYS} -instance

To export all Workspaces

cd ${APXDIR}

java oracle.apex.APEXExport -db ${APXHST}:${APXPRT}:${APXSID} -user system -password ${APXSYS} -expWorkspace

To see all arguments

cd ${APXDIR}

java oracle.apex.APEXExport

Import

There is no APEXImport Utility. You need to import through the APEX front-end as a Workspace Administrator.