Introduction
When Oracle Multitenant was released with Oracle Database 12.1.0.1.0 in June 2013, Oracle Application Express was installed by default common in the CDB in CDB$ROOT, PDB$SEED, and all PDBs. In fact, that was the architecture that we recommended at that time. As we have gained experience with multitenant in our internal deployments, we realized that our initial recommendation was wrong. Having APEX installed common locks you in to one version for your entire CDB, and when you need to upgrade APEX, you have to upgrade every PDB, even if that PDB is not using APEX. This would cause unnecessary added time to APEX upgrades.Applying the lessons we have learned in our internal deployments, we have been recommending that customers remove Oracle Application Express from their CDBs and install APEX locally in PDBs since version 5.0.
What do you do if you have been using the common APEX from 12.1.0.1.0 or 12.1.0.2.0, and now you want to follow our new recommendation of having APEX installed locally in a PDB? We have been asked that question many times by both internal and external customers. Unfortunately there is no simple answer, no magic SQL script you can run to do the conversion, so we have never really had a good answer.
Then my manager, Joel Kallman, had a novel idea. He reminded me of the work I did for the Oracle Database Cloud Schema Service, to effectively export almost every single artifact of a workspace, using the command line utility APEXExport, so that a schema service tenant could be moved from one APEX instance to another. This involved greatly expanding our workspace export code, modifying APEXExport to allow for more export options, and then scripting the calls to APEXExport, including creating an installer script to invoke all the exports, and finally zipping the scripts produced. Joel's idea was to reuse this method in a process to convert a CDB where APEX is installed common, to a CDB where APEX is installed locally in PDBs.
The Big Idea
The high level steps to convert common APEX in a CDB to local APEX in PDBs are:- Identify all PDBs in your CDB that have APEX instances
- In each PDB, identify all workspaces
- Run a script to export all artifacts of all workspaces identified in step 1 into zip files with installers
- Completely back up your CDB
- Run the APEX removal script to remove APEX from the CDB and all PDBs
- Install APEX locally in each PDB identified in step 1. (This may be a good opportunity to upgrade to the latest version!)
- Unzip each of your files from setp 3, and run the installer script from each, connected to the appropriate PDB
The Setup
I have a 12.1.0.2.0 CDB with two PDBs running APEX. One PDB has multiple workspaces, the other PDB has only one workspace. The CDB is the default APEX 4.2.5.0.00.08 that shipped with 12.1.0.2.0. The workspaces may have SQL scripts uploaded to SQL Workshop, SQL History and Saved SQL that we want to preserve. There are sample and packaged applications that we want to preserve. There are also custom built applications on objects in the schema associated with the workspaces. There are workspace images, and static files that we also want to preserve. I have already completely backed up my CDB.HR Workspace in APEX1 figures
 |
| Applications in HR |
 |
| SQL Script in SQL Workshop |
 |
| SQL History in HR |
 |
| Workspace image in HR |
 |
| Static file in HR |
FINANCE Workspace in APEX1 figures
 |
| Applications in FINANCE workspace |
 |
| Saved SQL in SQL Workshop |
MARKETING Workspace in APEX2 figures
 |
| Applications in MARKETING workspace |
 |
| Workspace Images in MARKETING workspace |
 |
| Saved SQL in SQL Workshop |
The Hook
Identify all PDBs that have APEX instances
SQL> select p.pdb_name, r.version, r.status from sys.dba_pdbs p, sys.cdb_registry r where p.pdb_id = r.con_id and r.comp_id = 'APEX' order by 1;PDB_NAME VERSION STATUS
------------------------------ ------------------------------ -----------
APEX1 4.2.5.00.08 VALID
APEX2 4.2.5.00.08 VALID
Identify all workspaces
SQL> set num 40SQL> alter session set container=APEX1;
Session altered.
SQL> select workspace_id from apex_workspaces where workspace_id > 100;
WORKSPACE_ID
----------------------------------------
2222525459993115
2336806112004961
SQL> alter session set container=APEX2;
Session altered.
SQL> select workspace_id from apex_workspaces where workspace_id > 100;
WORKSPACE_ID
----------------------------------------
2224405822867543
Run a script to export all artifacts
The original script that I wrote was in C shell and did the job, but my colleague Vlad Uvarov rewrote it in bash (apparently anyone under 40 no longer uses C shell) and vastly improved it. A slightly revised version of the script is listed below.#!/bin/bash -e
THIS_SCRIPT=$(basename "$0" )
THIS_DIR=$(cd "$(dirname "$0" )" && pwd )
CURRENT_DIR=$(pwd )
# ===================================================================================
# Function that displays script usage instructions and terminates the script
#
f_usage () {
cat <<EOF
Usage: $THIS_SCRIPT <JDBC connection string> <user> <password> <workspace ID>
Parameters:
JDBC connection string Database connection URL in JDBC format
user Database username
password Password of the database user
workspace ID ID of the workspace to be exported
Example:
./$THIS_SCRIPT //apex.example.com:1521/orcl apex_040200 secret 1234567890
EOF
exit 1
}
# ===================================================================================
# First, check environment variables and input parameters
#
[ -z "$ORACLE_HOME" ] && echo "Error: ORACLE_HOME environment variable is not set." && exit 1
JAVA_EXE=$ORACLE_HOME/jdk/bin/java
[ ! -x "$JAVA_EXE" ] && echo "Error: Java executable not found ($JAVA_EXE)." && exit 1
OJDBC_JAR=$ORACLE_HOME/jdbc/lib/ojdbc6.jar
[ ! -f "$OJDBC_JAR" ] && echo "Error: Oracle JDBC library not found ($OJDBC_JAR)." && exit 1
APEXEXPORT=$ORACLE_HOME/apex/utilities/oracle/apex/APEXExport.class
[ ! -f "$THIS_DIR/$APEXEXPORT" -a ! -f "$APEXEXPORT" ] && echo "Error: Missing APEXExport utility ($APEXEXPORT)." && exit 1
(( $# < 4 )) && echo "Error: This script requires 4 parameters." && echo && f_usage
# ===================================================================================
# Step 1: Export APEX workspace, including applications and files
#
EXP_DIR=$(mktemp -d ${TMPDIR:-/tmp}/$(basename "$THIS_SCRIPT" .sh ).XXXXXX )
cd "$EXP_DIR"
cat <<EOF
Oracle Application Express workspace export
Using ORACLE_HOME=$ORACLE_HOME
EOF
$JAVA_EXE -version
echo
CP=$OJDBC_JAR:$THIS_DIR:$ORACLE_HOME/apex/utilities
if [ "$THIS_DIR" != "$CURRENT_DIR" ]; then
CP=$CP:$CURRENT_DIR
fi
$JAVA_EXE -cp $CP oracle.apex.APEXExport -db "$1" -user "$2" -password "$3" -workspaceid "$4" -expWorkspace -expTeamdevdata
$JAVA_EXE -cp $CP oracle.apex.APEXExport -db "$1" -user "$2" -password "$3" -workspaceid "$4" -expFiles
$JAVA_EXE -cp $CP oracle.apex.APEXExport -db "$1" -user "$2" -password "$3" -workspaceid "$4" -expLocked -expSavedReports
# ===================================================================================
# Step 2: Create ZIP archive with a generated installation script and all export files
#
INSTALL_SCRIPT=install_${4}.sql
cat > "$INSTALL_SCRIPT" <<EOF
prompt
spool install_${4}.log
prompt Oracle Application Express workspace export
prompt Workspace ID $4 exported on $(date )
alter session set current_schema = APEX_050100;
@@w${4}.sql
EOF
for f in *.sql; do
if [ "$f" != "w${4}.sql" -a "$f" != "$INSTALL_SCRIPT" ]; then
echo "@@$f" >> "$INSTALL_SCRIPT"
fi
done
cat >> "$INSTALL_SCRIPT" <<EOF
spool off
prompt
exit
EOF
echo
ZIP_FILE="$CURRENT_DIR/install_${4}_$(date +%Y%m%d ).zip"
rm -f "$ZIP_FILE"
zip -9 "$ZIP_FILE" *.sql
echo
echo "Generated $ZIP_FILE"
echo
# ===================================================================================
# Step 3: Clean up
#
cd "$CURRENT_DIR"
rm -rf "$EXP_DIR"
workspace_export.shOne thing to note in the script above is that the install script being generated assumes that APEX 5.1 is installed in your target APEX instance. If that is not your plan, change the line "alter session set current_schema = APEX_050100" to match the APEX schema of the version you plan to install locally in your PDBs.
Also, for convenience, I am going to temporarily unlock APEX_040200 in my CDB and set a known password, and pass those necessary credentials to the workspace_export.sh script. You could really use any database user that has been granted the APEX_ADMINISTRATOR_ROLE.
SQL> alter user apex_040200 account unlock identified by oracle container=all;
User altered.
I saved the script above in a directory where I want all my zip files to end up. Now I will call the script passing each of the workspace IDs I identified in the previous step.
[jstraub@slc02otv cdbconvertapex]$ ./workspace_export.sh //localhost:1521/APEX1 APEX_040200 oracle 2222525459993115
Oracle Application Express workspace export
Using ORACLE_HOME=/scratch/jstraub/app/product/12.1.0/dbhome_1
java version "1.6.0_75"
Java(TM) SE Runtime Environment (build 1.6.0_75-b13)
Java HotSpot(TM) 64-Bit Server VM (build 20.75-b01, mixed mode)
Exporting Workspace 2222525459993115:'HR'
Completed at Fri Feb 03 08:29:07 PST 2017
Exporting Application 100:'Sample Database Application'
Completed at Fri Feb 03 08:29:16 PST 2017
Exporting Application 102:'Checklist Manager'
Completed at Fri Feb 03 08:29:17 PST 2017
Exporting Application 103:'EMPLOYEES'
Completed at Fri Feb 03 08:29:18 PST 2017
Exporting Application 104:'Sample Websheet Application - Big Cats'
Completed at Fri Feb 03 08:29:18 PST 2017
adding: f100.sql (deflated 88%)
adding: f102.sql (deflated 89%)
adding: f103.sql (deflated 93%)
adding: files_2222525459993115.sql (deflated 55%)
adding: install_2222525459993115.sql (deflated 39%)
adding: w2222525459993115.sql (deflated 87%)
adding: ws104.sql (deflated 64%)
Generated /scratch/jstraub/cdbconvertapex/install_2222525459993115_20170203.zip
[jstraub@slc02otv cdbconvertapex]$ ./workspace_export.sh //localhost:1521/APEX1 APEX_040200 oracle 2336806112004961
Oracle Application Express workspace export
Using ORACLE_HOME=/scratch/jstraub/app/product/12.1.0/dbhome_1
java version "1.6.0_75"
Java(TM) SE Runtime Environment (build 1.6.0_75-b13)
Java HotSpot(TM) 64-Bit Server VM (build 20.75-b01, mixed mode)
Exporting Workspace 2336806112004961:'FINANCE'
Completed at Fri Feb 03 10:17:59 PST 2017
Exporting Application 101:'Sample Database Application'
Completed at Fri Feb 03 10:18:13 PST 2017
Exporting Application 105:'Group Calendar'
Completed at Fri Feb 03 10:18:14 PST 2017
Exporting Application 106:'QUOTES'
Completed at Fri Feb 03 10:18:14 PST 2017
adding: f101.sql (deflated 88%)
adding: f105.sql (deflated 89%)
adding: f106.sql (deflated 93%)
adding: files_2336806112004961.sql (deflated 64%)
adding: install_2336806112004961.sql (deflated 39%)
adding: w2336806112004961.sql (deflated 82%)
Generated /scratch/jstraub/cdbconvertapex/install_2336806112004961_20170203.zip
And then for the other PDB, APEX2, we pass a different connect string:
[jstraub@slc02otv cdbconvertapex]$ ./workspace_export.sh //localhost:1521/APEX2 APEX_040200 oracle 2224405822867543
Oracle Application Express workspace export
Using ORACLE_HOME=/scratch/jstraub/app/product/12.1.0/dbhome_1
java version "1.6.0_75"
Java(TM) SE Runtime Environment (build 1.6.0_75-b13)
Java HotSpot(TM) 64-Bit Server VM (build 20.75-b01, mixed mode)
Exporting Workspace 2224405822867543:'MARKETING'
Completed at Fri Feb 03 10:20:04 PST 2017
Exporting Application 100:'Sample Database Application'
Completed at Fri Feb 03 10:20:13 PST 2017
Exporting Application 101:'Sample Websheet Application - AnyCo IT Department'
Completed at Fri Feb 03 10:20:13 PST 2017
Exporting Application 102:'PROJECT_TASKS'
Completed at Fri Feb 03 10:20:14 PST 2017
Exporting Application 103:'Decision Manager'
Completed at Fri Feb 03 10:20:14 PST 2017
Exporting Application 104:'P-Track'
Completed at Fri Feb 03 10:20:17 PST 2017
adding: f100.sql (deflated 88%)
adding: f102.sql (deflated 93%)
adding: f103.sql (deflated 89%)
adding: f104.sql (deflated 89%)
adding: files_2224405822867543.sql (deflated 54%)
adding: install_2224405822867543.sql (deflated 40%)
adding: w2224405822867543.sql (deflated 82%)
adding: ws101.sql (deflated 58%)
Generated /scratch/jstraub/cdbconvertapex/install_2224405822867543_20170203.zip
The long game
I have a complete backup of my database, and I have all the zip files that will reconstruct my workspaces. It is time to do the actual conversion.Remove APEX from the CDB and all PDBs
Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @apxremov_con.sql
PL/SQL procedure successfully completed.
Performing installation in multitenant container database in the background.
The installation progress is spooled into apxremov*_con*.log files.
Please wait...
catcon: ALL catcon-related output will be written to apxremov1_con_catcon_9311.lst
catcon: See apxremov1_con*.log files for output generated by scripts
catcon: See apxremov1_con_*.lst files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to apxremov2_con_catcon_9723.lst
catcon: See apxremov2_con*.log files for output generated by scripts
catcon: See apxremov2_con_*.lst files for spool files, if any
catcon.pl: completed successfully
Installation completed. Log files for each container can be found in:
apxremov*_con*.log
You can quickly scan for ORA errors or compilation errors by using a utility
like grep:
grep ORA- *.log
grep PLS- *.log
Install APEX 5.1 locally in the PDBs
SQL> alter session set container=APEX1;Session altered.
SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
SQL> @apex_rest_config.sql
SQL> @apxchpwd.sql
SQL> @apex_epg_config.sql /scratch/jstraub/apx51 -- only if you are using EPG, very doubtful
SQL> alter session set container=APEX2;
Session altered.
SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
SQL> @apex_rest_config.sql
SQL> @apxchpwd.sql
SQL> @apex_epg_config.sql /scratch/jstraub/apx51 -- only if you are using EPG, very doubtful
Recreate the workspaces using the generated zip files
First, I am again going to temporarily unlock and change the password for the APEX database user, in this case APEX_050100. Since now APEX_050100 is a local user to the PDB, I have to do it in both APEX1 and APEX2 PDBs.SQL> alter session set container=APEX1;
Session altered.
SQL> alter user APEX_050100 account unlock identified by oracle;
User altered.
SQL> alter session set container=APEX2;
Session altered.
SQL> alter user APEX_050100 account unlock identified by oracle;
User altered.
Then I unzip each complete workspace export and run the corresponding install script.
[jstraub@slc02otv cdbconvertapex]$ mkdir hr
[jstraub@slc02otv cdbconvertapex]$ unzip install_2222525459993115_20170203.zip -d hr
[jstraub@slc02otv cdbconvertapex]$ cd hr
[jstraub@slc02otv hr]$ sqlplus apex_050100/oracle@localhost/APEX1
SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 3 12:05:24 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @install_2222525459993115.sql
[jstraub@slc02otv cdbconvertapex]$ mkdir finance
[jstraub@slc02otv cdbconvertapex]$ unzip install_2336806112004961_20170203.zip -d finance
[jstraub@slc02otv cdbconvertapex]$ cd finance
[jstraub@slc02otv finance]$ sqlplus apex_050100/oracle@localhost/APEX1
SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 3 12:14:21 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Fri Feb 03 2017 12:05:24 -08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @install_2336806112004961.sql
[jstraub@slc02otv cdbconvertapex]$ mkdir marketing
[jstraub@slc02otv cdbconvertapex]$ unzip install_2224405822867543_20170203.zip -d marketing
[jstraub@slc02otv marketing]$ sqlplus apex_050100/oracle@localhost/APEX2
SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 3 12:16:11 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @install_2224405822867543.sql
Now we login to each workspace and check that all the artifacts from the workspace migrated over.
HR workspace in APEX1 figures - APEX 5.1
 |
| Applications in HR Workspace |
 |
| Static Files and Images in HR Workspace |
 |
| SQL Script in SQL Workshop |
 |
| SQL History in SQL Workshop |
FINANCE workspace in APEX1 figures - APEX 5.1
 |
| Applications in FINANCE Workspace |
 |
| Saved SQL in SQL Workshop |
MARKETING workspace in APEX2 figures - APEX 5.1
 |
| Applications in MARKETING Workspace |
 |
| Static Workspace Files in MARKETING |
 |
| Saved SQL in SQL Workshop |
One more thing
Actually, two more things. First, this process will not bring over "Template Applications" and "Public Themes." These features are not well known and rarely used, but you can easily determine if you have them by logging into internal administration, click on Manage Instance, and then click the Public Themes and Template Applications links. If there are applications in either area, simply export them, and them import them into internal administration after you recreate your workspaces.Second, you may want to create another PDB, call it APEX51SEED, and install APEX into it. Put the PDB in read only mode, and then when you create a new PDB, you can create it from APEX51SEED and it will already have APEX 5.1 installed locally. In our case, we could not use this method to install APEX only once, because we needed the schemas that were in our APEX1 and APEX2 PDBs.
