This chapter outlines the installation choices and different scenarios associated with copying and moving pluggable databases introduced by the Oracle Database 12c multitenant architecture with respect to Oracle Application Express (Application Express).
Plugging in a PDB When Application Express is Installed in the Root Container
Plugging in a PDB When Application Express Is Not in the Root Container of the Target CDB
Oracle Database 12c Release 1 (12.1) introduces the multitenant architecture. This database architecture has a multitenant container database (CDB) that includes a root container, CDB$ROOT, a seed database, PDB$SEED, and multiple pluggable databases (PDBs). Each pluggable database is equivalent to a separate database instance in Oracle Database release 11g. The root container, CDB$ROOT, holds common objects that are accessible to every PDB utilizing metadata links or object links. The seed database, PDB$SEED, is used when creating a new PDB to seed the new database. The key benefit of the Oracle Database 12c multitenant architecture is that the database resources, such as CPU and memory, can be shared across all of the PDBs. This architecture also enables many databases to be treated as one for tasks such as upgrades or patches, and backups.
Oracle Application Express Release 4.2 is the earliest release that can be configured with Oracle Database 12c. When choosing to install Oracle Database 12c using the Oracle Database 12c multitenant architecture, Oracle Application Express 4.2.4 is installed as a common database option by default. You may also choose to uninstall Application Express from the root container and then install a local Application Express Release 4.2.4 or later individually into one or more PDBs. Oracle does not support uninstalling Application Express from the root container, or reinstalling Application Express into the root container, once any PDBs have been configured. This may invalidate or completely remove the meta data associated with the existing Application Express installation.
When configuring multitenant architecture, Oracle Application Express is installed in the root container database by default. In such an installation the root container, CDB$ROOT, includes the APEX_040200 schema to store the common database objects for the Application Express engine such as packages, functions, procedures and views. The seed database, PDB$SEED, also includes the APEX_040200 schema to store the tables that are part of the Application Express engine.
You can create a new PDB by copying PDB$SEED, which includes the APEX_040200 schema with the Application Express tables, and creating metadata links back to the common database objects held in the APEX_040200 schema within the CDB$ROOT. As such there are multiple copies of the Application Express engine tables and only single copies of the Application Express engine packages, functions, procedures and views. Each PDB will have the APEX_040200 schema and have its own copy of the Application Express engine's tables so that it can hold the metadata for the Application Express applications defined within that PDB.
As a best practice, Oracle recommends having a common Oracle Application Express installation in the root container, which ensures every PDB is running the same version of Oracle Application Express. This offers ease of administration by enabling centralized management of Oracle Application Express infrastructure tasks, such as upgrading and patching. The other alternative is to uninstall Application Express from CDB$ROOT, PDB$SEED, and all other PDBs, and then install Application Express locally into each PDB. The advantage of a local Oracle Application Express is that you can run different versions of Application Express in each PDB. However, the disadvantage of choosing this option is that each local Oracle Application Express installation must be managed separately in respect of upgrades and patches for Application Express, which is a greater maintenance effort.
To have the flexibility of installing different versions of Oracle Application Express into different PDBs, you need to uninstall Oracle Application Express release 4.2.4 which was installed into the container database. Once you have removed Oracle Application Express from the container database, then you can install a local Application Express within each PDB as required. When Oracle Application Express is installed locally there are no Application Express metadata linked objects and all packages, views, and tables are created within the APEX_0402000 schema, within each PDB where Application Express is installed.
To uninstall Oracle Application Express from the CDB:
Change your working directory to $ORACLE_HOME/apex.
Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
Run apxremov_con.sql.
For example:
@apxremov_con.sql
Note:
If you runapexremov_con.sql after PDBs have been added to the CDB then Application Express uninstalls from all of the PDBs. Therefore, any applications defined in any of the PDBs will be removed. If you run apxremov.sql then Application Express is only removed from the root container. However, every PDB is left in an invalid state and will cause errors when trying to open the PDB.Once you have removed Oracle Application Express from the container database by following the instructions in "Uninstalling Application Express from a CDB", you can install Application Express locally in a PDB.
To install Oracle Application Express locally in a PDB:
Change your working directory to $ORACLE_HOME/apex.
Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
Set the container to the PDB you wish to install Application Express locally:
ALTER SESSION SET CONTAINER = <PDB_name>;
Select the appropriate installation option.
Full development environment provides complete access to the Application Builder environment to develop applications. A Runtime environment enables users to run applications that cannot be modified. To learn more, see "About the Oracle Application Express Runtime Environment".
Available installation options include:
Full development environment - Run apexins.sql passing the following four arguments in the order shown:
@apexins.sql tablespace_apex tablespace_files tablespace_temp images
Where:
tablespace_apex is the name of the tablespace for the Oracle Application Express application user.
tablespace_files is the name of the tablespace for the Oracle Application Express files user.
tablespace_temp is the name of the temporary tablespace or tablespace group.
images is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/.
For example:
@apexins.sql SYSAUX SYSAUX TEMP /i/
Runtime environment - Run apxrtins.sql passing the following four arguments in the order shown:
o @apxrtins.sql tablespace_apex tablespace_files tablespace_temp images
Where:
tablespace_apex is the name of the tablespace for the Oracle Application Express application user.
tablespace_files is the name of the tablespace for the Oracle Application Express files user.
tablespace_temp is the name of the temporary tablespace or tablespace group.
images is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/.
For example:
@apxrtins.sql SYSAUX SYSAUX TEMP /i/
Note:
If you did not apply the database patch prior to executingapxremov_con.sql in the previous step, then attempting to install Oracle Application Express into a PDB will fail as the installation script will exit immediately without installing Oracle Application Express. In this scenario please contact Oracle Support for further assistance.If you are using Oracle REST Data Services, complete the appropriate steps in "Download and Install Oracle REST Data Services" through "Create a Workspace and Add Oracle Application Express Users".
If you are using Oracle HTTP Server, complete the appropriate steps in "Configure Oracle HTTP Server Distributed with Oracle Database 12c" through "Create a Workspace and Add Oracle Application Express Users".
If you are using embedded PL/SQL gateway, run apex_epg_config.sql passing the path to the Oracle Home and then complete the appropriate steps in "Configure the Embedded PL/SQL Gateway".
See Also:
Oracle Database SQL Language Reference for more information about SQL*PlusWhen Oracle Application Express installs, it creates three new database accounts:
APEX_040200 - The account that owns the Oracle Application Express schema and metadata.
FLOWS_FILES - The account that owns the Oracle Application Express uploaded files.
APEX_PUBLIC_USER - The minimally privileged account is used for Oracle Application Express configuration with Oracle REST Data Services or Oracle HTTP Server and mod_plsql.
If you configured RESTful Web services, then these additional accounts are created:
APEX_REST_PUBLIC_USER - The account used when invoking RESTful Services definitions stored in Oracle Application Express.
APEX_LISTENER - The account used to query RESTful Services definitions stored in Oracle Application Express.
If you previously uninstalled Oracle Application Express 4.2.4 and want to reinstall Application Express into a CDB you need to install Oracle Application Express 4.2.4 using the Oracle Database 12c distribution. Following the reinstallation you can apply Application Express patches if required as outlined in "Patching Application Express in the CDB".
To reinstall Application Express into a CDB:
Change your working directory to $ORACLE_HOME/apex.
Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog SQL> CONNECT SYS as SYSDBA Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
Select the appropriate installation option.
Full development environment provides complete access to the Application Builder environment to develop applications. A Runtime environment enables users to run applications that cannot be modified. To learn more, see "About the Oracle Application Express Runtime Environment".
Available installation options include:
Full development environment. Run apexins_con.sql passing the following four arguments in the order shown:
@apexins_con.sql tablespace_apex tablespace_files tablespace_temp images
Where:
tablespace_apex is the name of the tablespace for the Oracle Application Express application user.
tablespace_files is the name of the tablespace for the Oracle Application Express files user.
tablespace_temp is the name of the temporary tablespace or tablespace group.
images is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/.
Example:
@apexins_con.sql SYSAUX SYSAUX TEMP /i/
Runtime environment. Run apxrtins_con.sql passing the following arguments in the order shown:
@apxrtins_con.sql tablespace_apex tablespace_files tablespace_temp images
Where:
tablespace_apex is the name of the tablespace for the Oracle Application Express application user.
tablespace_files is the name of the tablespace for the Oracle Application Express files user.
tablespace_temp is the name of the temporary tablespace or tablespace group.
images is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/.
Example:
@apxrtins_con.sql SYSAUX SYSAUX TEMP /i/
If using Oracle REST Data Services, complete appropriate steps in sections "Download and Install Oracle REST Data Services" through "Create a Workspace and Add Oracle Application Express Users".
If using the Oracle HTTP Server, complete appropriate steps in sections "Configure Oracle HTTP Server Distributed with Oracle Database 12c" through "Create a Workspace and Add Oracle Application Express Users".
If using the embedded PL/SQL gateway, run apex_epg_config_con.sql passing the path to the Oracle Home and then complete appropriate steps in "Configure the Embedded PL/SQL Gateway".
See Also:
Oracle Database SQL Language Reference for more information about SQL*PlusWhen Oracle Application Express installs, it creates three new database accounts:
APEX_040200 - The account that owns the Oracle Application Express schema and metadata.
FLOWS_FILES - The account that owns the Oracle Application Express uploaded files.
APEX_PUBLIC_USER - The minimally privileged account is used for Oracle Application Express configuration with Oracle REST Data Services or Oracle HTTP Server and mod_plsql.
If you configured RESTful Web services, then these additional accounts are created:
APEX_REST_PUBLIC_USER - The account used when invoking RESTful Services definitions stored in Oracle Application Express.
APEX_LISTENER - The account used to query RESTful Services definitions stored in Oracle Application Express.
This section describes scenarios in which the target database has Application Express installed into the root container, CDB$ROOT - the default installation option. Note there are multiple scenarios related to where the database being plugged in originated from and how Application Express was configured in the originating database.
Scenario 2: Plug-in PDB with a Common Application Express from Another CDB
Scenario 3: Plug-in PDB with a Local Application Express from Another CDB
Scenario 4: Plug-in Non-CDB or PDB with No Application Express
If you are upgrading from a previous Oracle Database release, then you first need to upgrade to a Oracle Database 12c non-CDB (or standalone database) and then plug the database into your CDB. Alternatively, if you have configured a non-CDB Oracle Database 12c, you may now want to plug this database into a CDB. In both cases, the originating database has Application Express installed and was not formerly a PDB.
As described in the Oracle Database Installation Guide for your operating system, when plugging in a standalone database you need to run the $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script. This script creates the necessary metadata linked objects, instead of local objects and recompiles the database objects for all common database options, including Oracle Application Express. After installing Oracle Application Express, you need to configure the Web listener for the PDB as described in "Configuration Tasks When Installing from the Database".
If the version of Application Express installed in the originating database (which is now a PDB) is not the same as that installed into the root container of the target, an error will be raised when trying to open the PDB. For information on how to make the versions of Application Express compatible, see "About Incompatible Application Express Versions".
If you are copying or moving a PDB from an existing Oracle Database 12c where the originating CDB had Application Express installed in the root container, you will not need to perform any additional steps, other than configuring the Web listener for the PDB as described in "Configuration Tasks When Installing from the Database".
Assuming Oracle Application Express release 4.2 is installed, the APEX_040200 schema within the PDB being plugged in already has the metadata linked objects defined and will compile without error against the metadata linked objects within the target CDB.
If the version of Application Express installed in the originating database is not the same as that installed in the root container of the target an error is raised when trying to open the PDB. For information on how to make the versions of Application Express compatible, see "About Incompatible Application Express Versions".
If you are copying or moving a PDB from an existing Oracle Database 12c where Application Express was not installed in the root container but is installed locally, then you need to perform additional steps before the PDB can be opened without errors. Assuming Oracle Application Express release 4.2 is installed, the APEX_040200 schema within the PDB being plugged in contains all of the Application Express database objects and has no metadata linked objects. Therefore, you need to run $ORACLE_HOME/rdbms/admin/apex_to_common.sql to remove the common objects and create the metadata links for the packages, views and so forth.
To replace local objects with metadata links in the PDB:
Change your working directory to $ORACLE_HOME/rdbms/admin.
Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
Set the container to the PDB to be configured:
ALTER SESSION SET CONTAINER = <PDB_name>;
Run apex_to_common.sql. For example:
@apex_to_common.sql
If the version of Application Express installed in the originating database is not the same as that installed in the root container of the target an error is raised when trying to open the PDB. For information on how to make the versions of Application Express compatible, see "About Incompatible Application Express Versions".
If you are plugging in a non-CDB, or copying or moving a PDB from another CDB, and Application Express is not installed in the originating database or PDB then a warning is raised in the alert log when opening the PDB in the target database.
In this scenario, the Application Express schema, such as APEX_040200 for Oracle Application Express release 4.2, will not be present in the originating database or PDB being plugged in. In order to open the PDB without issue and be able to run Application Express within the new PDB, you must install Application Express into the originating database or PDB before attempting to plug in to the target database. You should install the same version of Application Express into the originating database or PDB as the version installed into the target database.
If the version of Oracle Application Express in the root container, CDB$ROOT, is not the same as the Oracle Application Express version in the PDB then an error is raised every time the PDB is opened preventing normal database operations within the PDB. The PDB can only be opened in restricted mode by users with RESTRICTED SESSION privilege, until the versions are compatible.
If the version of Application Express in the PDB is higher than the version of Application Express in the root container, then you must patch the version of Application Express in the root container to be able to open the PDB without error.
To patch Application Express in the root container:
Download the appropriate patch from My Oracle Support.
Unzip and extract the installation files.
Change your working directory to where the installation files were extracted
Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
Run apxpatch_con.sql for example:
@apxpatch_con.sql
Follow the instructions outlined in the Patch Set Notes for updating the images directory based on the Web Server you are using. If you are using the embedded PL/SQL gateway, run apxldimg_con.sql for example:
@apxldimg_con.sql
If the version of Application Express in the PDB is lower than the version of Application Express in the root container, CDB$ROOT, then it will be necessary to patch the version of Application Express in the PDB.
To patch Application Express in the PDB:
Download the appropriate patch from My Oracle Support.
Unzip and extract the installation files.
Change your working directory to where the installation files were extracted
Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
Run apxpatch.sql using catcon.pl like the following example:
host &OH_HOME/perl/bin/perl -I &OH_HOME/rdbms/admin &OH_HOME/rdbms/admin/catcon.pl -b apxpatch -c '<PDB_name>' apxpatch.sql
Where:
&OH_HOME represents the full path to the Oracle home
<PDB_name> is the name of the PDB you are patching
Follow the instructions outlined in the patch set notes for updating the images directory based on the Web Server you are using. If you are using the embedded PL/SQL gateway, run apxldimg.sql for example:
ALTER SESSION SET CONTAINER = <PDB_name>;
@apxldimg.sql
The scenarios in this section describe when Oracle Application Express is not installed in the root container, CDB$ROOT, by explicitly removing it as described in "Uninstalling Application Express from a CDB". In such cases, you can optionally install a local Application Express into each PDB independently. If Oracle Application Express is installed into a PDB it is considered to be installed locally and has no metadata linked objects. There are multiple scenarios related to where the database being plugged in originated from and how Application Express was configured in the originating database.
Scenario 2: Plug-in PDB with Common Application Express from Another CDB
Scenario 3: Plug-in PDB with a Local Application Express from Another CDB
Scenario 4: Plug-in a Non-CDB or PDB with No Application Express
If you are upgrading from a previous Oracle Database release then you need to upgrade to Oracle Database 12c non-CDB (or standalone database) and then plug the database into your CDB. Alternatively you may have configured a non-CDB Oracle Database 12c that you now wish to plug into a CDB. In both cases, the originating database had Application Express installed and was not formerly a PDB.
As described in the Oracle Database Installation Guide for your operating system, when plugging in a standalone database you need to run the $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script. This script creates the necessary metadata linked objects (instead of local objects) and recompiles the database objects for all common database options. However, because Oracle Application Express has been removed from the root container, the script will not create any metadata links for any of the Application Express objects. The script does not change the Application Express installation from the originating database and no additional steps are needed other than configuring the Web Server as outlined in "Configuration Tasks When Installing from the Database".
If you are copying or moving a PDB from an existing Oracle Database 12c where the originating CDB had Application Express installed in the root container, then an error is raised whenever you try and open the PDB. The error is due to the fact that the originating PDB included metadata links to objects in the originating root container which cannot be recompiled because the target root container does not include Application Express. You will not be able to open the PDB unless you remove Application Express from the PDB or if Application Express is already installed in the target root container. Oracle does not support installing Application Express in the root container unless no PDBs are installed. For further assistance, contact Oracle Support.
If you are copying or moving a PDB from an existing Oracle Database 12c where the originating PDB had a local Application Express installed (not in the CDB) then you do not need to perform any additional steps, other than configuring the Web Server in the PDB as described in "Configuration Tasks When Installing from the Database". Assuming Oracle Application Express release 4.2 is installed, the APEX_040200 schema within the PDB being plugged in already has all of the Application Express objects defined locally and no metadata links.
If you are plugging in a non-CDB, or copying or moving a PDB from another CDB, where Application Express was not installed in the originating database or PDB then you do not need to perform any additional steps. There will be no Application Express engine schema, such as APEX_040200, within the PDB, and the PDB can be started without error.