3 Utilizing the Multitenant Architecture in Oracle Database 12c

This section 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).

3.1 Understanding the Installation Choices

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.

When configuring multitenant architecture in Oracle Database 12c Release 1 (12.1), Oracle Application Express is installed in the root container database by default. In such an installation the root container, CDB$ROOT, includes the APEX_050000 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_050000 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_050000 schema with the Application Express tables, and creating metadata links back to the common database objects held in the APEX_050000 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_050000 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.

In Oracle Database 12c Release 2 (12.2) multitenant architecture, Oracle Application Express is not installed in the root container database or any pluggable database by default.

Tip:

Oracle recommends removing Oracle Application Express from the root container database for the majority of use cases, except for hosting companies or installations where all pluggable databases (PDBs) utilize Oracle Application Express and they all need to run the exact same release and patch set of Oracle Application Express. To learn more, see "Installing Application Express into Different PDBs."

3.2 Installing Application Express into Different PDBs

To have the flexibility of installing different versions of Oracle Application Express into different PDBs, you need to uninstall Application Express from the container database. Once you have removed Application Express from the container database, then you can install a local Application Express within each PDB as required. When Application Express is installed locally there are no Application Express metadata linked objects and all packages, views, and tables are created within the APEX_0500000 schema, within each PDB where Application Express is installed.

3.2.1 Uninstalling Application Express from a CDB

To uninstall Application Express from the CDB:

  1. Change the apex directory in the location where you unzipped the distribution.

  2. Start SQL*Plus and connect to the database where 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
      
      
  3. Run apxremov.sql.

    For example:

    @apxremov.sql
    

    Note:

    If you run apexremov.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.

3.2.2 Installing Application Express Locally in a PDB

Once you have removed 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 Application Express locally in a PDB:

  1. Change the apex directory in the location where you unzipped the distribution.

  2. Start SQL*Plus and connect to the database where 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
      
      
  3. Set the container to the PDB you want to install Application Express locally:

    ALTER SESSION SET CONTAINER = <PDB_name>;
    
  4. If you are using Oracle REST Data Services, complete the appropriate steps in "Installing Application Express and Configuring Oracle REST Data Services."

  5. If you are using Oracle HTTP Server, complete the appropriate steps in "Installing Application Express and Configuring Oracle HTTP Server."

  6. If you are using Embedded PL/SQL Gateway, complete the appropriate steps in "Installing Application Express and Configuring Embedded PL/SQL Gateway" and then run apex_epg_config.sql passing the path to the Oracle home.

See Also:

Oracle Database SQL Language Reference for more information about SQL*Plus

When Application Express installs, it creates three new database accounts:

  • APEX_050000 - The account that owns the Application Express schema and metadata.

  • FLOWS_FILES - The account that owns the Application Express uploaded files.

  • APEX_PUBLIC_USER - The minimally privileged account is used for 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 Application Express.

  • APEX_LISTENER - The account used to query RESTful Services definitions stored in Application Express.

3.2.3 Reinstalling Application Express into a CDB

If you previously uninstalled Oracle Application Express and want to reinstall Application Express into a CDB you need to install Oracle Application Express using the Oracle Database 12c distribution. Following the reinstallation, you can apply Application Express patches if required as outlined in "Patching or Upgrading Application Express in the CDB."

To reinstall Application Express into a CDB:

  1. Change your working directory to the apex directory in the location where you unzipped the distribution.

  2. Start SQL*Plus and connect to the database where 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
      
  3. If using Oracle REST Data Services, complete appropriate steps in Chapter 4, "Installing Application Express and Configuring Oracle REST Data Services".

  4. If using the Embedded PL/SQL Gateway, complete appropriate steps in Chapter 5, "Installing Application Express and Configuring Embedded PL/SQL Gateway".

  5. If using the Oracle HTTP Server, complete appropriate steps in Chapter 6, "Installing Application Express and Configuring Oracle HTTP Server".

See Also:

Oracle Database SQL Language Reference for more information about SQL*Plus

When Application Express installs, it creates three new database accounts:

  • APEX_050000 - The account that owns the Application Express schema and metadata.

  • FLOWS_FILES - The account that owns the Application Express uploaded files.

  • APEX_PUBLIC_USER - The minimally privileged account is used for 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 Application Express.

  • APEX_LISTENER - The account used to query RESTful Services definitions stored in Application Express.

3.3 Plugging in a PDB When Application Express is Installed in the Root Container

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.

3.3.1 Scenario 1: Plug-in Non-CDB with 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 Application Express.

After installing Application Express, you need to configure the Web listener for the PDB as described in "Installing Application Express and Configuring Oracle REST Data Services," "Installing Application Express and Configuring Embedded PL/SQL Gateway," or "Installing Application Express and Configuring Oracle HTTP Server."

If the version of Application Express installed in the originating database (which is now a PDB) is different from what is 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 "Working with Incompatible Application Express Versions."

3.3.2 Scenario 2: Plug-in PDB with a Common Application Express from Another CDB

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 "Installing Application Express and Configuring Oracle REST Data Services," "Installing Application Express and Configuring Embedded PL/SQL Gateway," or "Installing Application Express and Configuring Oracle HTTP Server."

Assuming Application Express release 5.0 is installed, the APEX_050000 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 different from what is 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 "Working with Incompatible Application Express Versions."

3.3.3 Scenario 3: Plug-in PDB with a Local Application Express from Another CDB

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 Application Express release 5.0 is installed, the APEX_050000 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:

  1. Change your working directory to $ORACLE_HOME/rdbms/admin.

  2. Start SQL*Plus and connect to the database where 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
      
      
  3. Set the container to the PDB to be configured:

    ALTER SESSION SET CONTAINER = <PDB_name>;
    
  4. Run apex_to_common.sql. For example:

    @apex_to_common.sql 
    
    

If the version of Application Express installed in the originating database is different from what is 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 "Working with Incompatible Application Express Versions."

3.3.4 Scenario 4: Plug-in Non-CDB or PDB with No Application Express

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_050000 for Application Express release 5.0, 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.

3.3.5 Working with Incompatible Application Express Versions

If the version of Application Express in the root container, CDB$ROOT, is not the same as the 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.

3.3.5.1 Patching or Upgrading Application Express in the CDB

If the version of Application Express in the PDB is a later minor release version than the version of Application Express in the root container (for example, the PDB contains Application Express release 4.2.6 and the CDB contains Application Express release 4.2.5) then you must patch the version of Application Express in the root container to be able to open the PDB without error. If the major version of Application Express in the PDB is higher than the version in the CDB (for example the PDB has Application Express release 5.0 and the CDB has Application Express release 4.2) then you must upgrade the version of Application Express in the CDB to be able to open the PDB without error.

To patch Application Express in the root container:

  1. Download the appropriate patch from My Oracle Support.

  2. Unzip and extract the installation files.

  3. Change your working directory to where the installation files were extracted

  4. Start SQL*Plus and connect to the database where 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
      
      
  5. Run apxpatch_con.sql for example:

    @apxpatch_con.sql
    
  6. 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
    

Tip:

To upgrade Application Express in the root container, see "Reinstalling Application Express into a CDB"

3.3.5.2 Patching or Upgrading Application Express in a PDB

If the minor version of Application Express in the PDB is lower than the version of Application Express in the root container (for example the PDB has Application Express release 4.2.0 and the CDB has Application Express release 4.2.6) then it will be necessary to patch the version of Application Express in the PDB. If the major version of Application Express in the PDB is lower than the version in the root container (for example, the PDB has Application Express release 4.2 and the CDB has Application Express release 5.0) then the version of Application Express in the PDB will need to be upgraded.

3.3.5.2.1 Patching Application Express in a PDB

To patch Application Express in the PDB:

  1. Download the appropriate patch from My Oracle Support.

  2. Unzip and extract the installation files.

  3. Change your working directory to where the installation files were extracted

  4. Start SQL*Plus and connect to the database where 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
      
      
  5. 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

  6. 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 apex_epg_config.sql for example:

    ALTER SESSION SET CONTAINER = <PDB_name>;
    @apex_epg_config.sql
    
3.3.5.2.2 Upgrading Application Express in a PDB

To upgrade Application Express in the PDB:

  1. Unzip and extract the installation files.

  2. Change your working directory to where the installation files were extracted

  3. Start SQL*Plus and connect to the database where 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
      
      
  4. Run apexins_nocdb.sql or apxrtins_nocdb.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 apexins -c '<PDB_name>'  apexins_nocdb.sql SYSAUX SYSAUX TEMP /i/ 
    

    Where:

    • &OH_HOME represents the full path to the Oracle home

    • <PDB_name> is the name of the PDB you are patching

  5. Follow the instructions outlined in the patch set notes for updating the images directory based on the Web Server you are using.

3.4 Plugging in a PDB When Application Express Is Not in the Root Container of the Target CDB

The scenarios in this section describe when 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 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.

3.4.1 Scenario 1: Plug-in a Non-CDB with 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 want 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 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 listener as outlined in "Installing Application Express and Configuring Oracle REST Data Services," "Installing Application Express and Configuring Embedded PL/SQL Gateway," or "Installing Application Express and Configuring Oracle HTTP Server" depending on the type of Web listener you are using.

3.4.2 Scenario 2: Plug-in PDB with Common Application Express from Another CDB

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 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 if it contains PDBs with locally installed Application Express.

3.4.3 Scenario 3: Plug-in PDB with a Local Application Express from Another CDB

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 listener in the PDB as described in "Installing Application Express and Configuring Oracle REST Data Services," "Installing Application Express and Configuring Embedded PL/SQL Gateway," or "Installing Application Express and Configuring Oracle HTTP Server" depending on the type of Web listener you are using.

Assuming Application Express release 5.0 is installed, the APEX_050000 schema within the PDB being plugged in already has all of the Application Express objects defined locally and no metadata links.

3.4.4 Scenario 4: Plug-in a Non-CDB or PDB with No Application Express

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_050000, within the PDB, and the PDB can be started without error.