In my previous article we learnt how to move an application container across CDBs. As we already know, Multitenant Application Containers eliminate the overhead of replicating application definitions by enabling many PDBs to share application objects such as code, metadata, and data. Multitenant Application Containers also allow application administrators to efficiently manage many application PDBs as one in a single Application Container while securely isolating their individual customer-specific data. These valuable features of Multitenant Application Containers can be leveraged for existing applications running in more than one PDB by migrating such applications to an application container.

In this article, we will learn how to migrate multiple PDBs running the same application to an application container. Assume that currently there are many PDBs that are running the same application and hence all these PDBs have the same objects, users, roles, and profiles required by the application. These PDBs can be migrated to an application container by following these steps:

  • Create an application root from one of the PDBs using one of the following methods:
    • Clone an existing PDB as an application root.
    • Relocate an existing PDB as an application root.
    • Unplug and plug in an existing PDB as an application root.

The newly created application root contains all of the database users / objects used by the application.

  • Install an application in the application root and associate the database objects, users, roles, and profiles with the application by running procedures in the DBMS_PDB package.
  • Create application PDBs in the new application container using the existing PDBs. 

Now, I will demonstrate the migration of multiple PDBs running the same application to an application container by means of a sample scenario.

Current scenario:

Here, we have an Oracle database 12.2.0.1 CDB called orclcdb, as shown below in Fig. 1. Within this CDB, besides CDB Seed PDB pdb$seed, we have four regular PDBs east_pdb, west_pdb, north_pdb and south_pdb representing databases for four regional offices of an organization. All these regular PDs supports the same sales application and hence contain the following application user/objects:

  • Application user sales_app_user
  • Application tables
    • sales_app_user.customers
    • sales_app_user.products
    • sales_app_user.zip_codes.

 

Fig. 1

In order to avoid replication of the application objects, we will create an application container sales_app_root  which stores various common application objects for the sales application (sales_app) as shown in Fig. 2. Subsequently, within the application container sales_app_root, we will create the application PDBs east_app_pdb, west_app_pdb, north_app_pdb and south_app_pdb from the existing regular PDBs east_pdb, west_pdb, north_pdb and south_pdb respectively so that they can share common application objects stored in the application root sales_app_root.  

Fig. 2

 

Overview of the steps:

  • Clone one of the PDBs; say, east_pdb, as application root sales_app_root.
  • Verify that the new application root sales_app_root contains all of the database objects used by the application.
  • With sales_app_root as the current container, start installation of the sales application sales_appby issuing an ALTER PLUGGABLE DATABASE ... BEGIN INSTALL statement.
    • Run the procedure DBMS_PDB.SET_USER_EXPLICIT to associate the user sales_app_user with the application sales_app as an application common user.
    • Run the DBMS_PDB.SET_DATA_LINKED procedure to associate table sales_app_user.products with the application sales_app as a data-linked application common table.
    • Run the DBMS_PDB.SET_METADATA_LINKED procedure to associate table sales_app_user.customers with the application sales_app as a metadata-linked application common table.
    • Run the DBMS_PDB.SET_EXT_DATA_LINKED procedure to associate table sales_app_user.zip_codes with the application sales_app as an extended data-linked application common table.
  • End the application installation operation by issuing an ALTER PLUGGABLE DATABASE ... END INSTALL statement.
  • Ensure that application sales_apphas been installed with various associated application objects and
    • The COMMON property of the user sales_app_user is correct
    • The SHARING and APPLICATION properties of the common application tables are correct
  • In the application root sales_app_root, clone existing regular PDBs east_pdb, west_pdb, north_pdb and south_pdb as application PDBs east_app_pdb, west_app_pdb, north_app_pdb and south_app_pdbb, respectively. Violations will be reported during the opening of application PDBs.
  • Switch to each of the application PDBs as a user with the required privileges and run the pdb_to_apppdb.sql script in the ORACLE_HOME/rdbms/admin directory. Optionally, drop source regular PDBs east_pdb, west_pdb, north_pdb and south_pdb.
  • Ensure that in all the application PDBs
    • The common property of the user sales_app_user is correct
    • The SHARING and APPLICATION properties of the common application tables are correct

Demonstration:

  • Check that at present there are four regular PDBs in CDB orclcdb.          

CDB$ROOT>sho parameter db_name

 

NAME                         TYPE   VALUE

------------------------------------ ----------- -----------------------------

db_name                      string orclcdb

 

CDB$ROOT>@get_app_containers

 

   CON_ID NAME         OPEN_MODE APP_ROOT APP_PDB APP_SEED

---------- -------------------- ---------- -------- -------- --------

      1 CDB$ROOT       READ WRITE NO        NO           NO

      2 PDB$SEED       READ ONLY  NO        NO           NO

      3 EAST_PDB       READ WRITE NO        NO           NO

      4 WEST_PDB       READ WRITE NO        NO           NO

      5 NORTH_PDB      READ WRITE NO        NO           NO

      6 SOUTH_PDB      READ WRITE NO        NO           NO

 

6 rows selected.

 

  • Clone one of the PDBs, say, east_pdb, as application root sales_app_root.

CDB$ROOT>ho mkdir -p /u02/app/oracle/oradata/orclcdb/sales_app_root

 

            ALTER session SET db_create_file_dest='/u02/app/oracle/oradata/orclcdb/sales_app_root/';

 

           CREATE PLUGGABLE DATABASE sales_app_root AS APPLICATION CONTAINER

            FROM east_pdb ;

 

Pluggable database created.

 

 

CDB$ROOT>@get_app_containers

 

   CON_ID NAME         OPEN_MODE APP_ROOT APP_PDB APP_SEED

---------- -------------------- ---------- -------- -------- --------

      1 CDB$ROOT       READ WRITE NO        NO           NO

      2 PDB$SEED       READ ONLY  NO        NO           NO

      3 EAST_PDB       READ WRITE NO        NO           NO

      4 WEST_PDB       READ WRITE NO        NO           NO

      5 NORTH_PDB      READ WRITE NO        NO           NO

      6 SOUTH_PDB      READ WRITE NO        NO           NO

      7 SALES_APP_ROOT READ WRITE YES       NO           NO

 

  • The new application root sales_app_root contains all of the database objects used by the application. When application root sales_app_rootis first opened,
    • The application user sales_app_user (a local user in source regular PDB east_pdb) has been marked as COMMON in sales_app_root. Since the user sales_app_user is not associated with any application yet, DDL operation can be performed on it even from outside an application action although it has been marked as COMMON.

EAST_PDB>select username, common from dba_users

         where username = 'SALES_APP_USER';

 

USERNAME            COMMON

-------------------- ----------

SALES_APP_USER         NO

 

 

SALES_APP_ROOT>select username, common, oracle_maintained from dba_users

               where common = 'YES' and oracle_maintained = 'N';

 

USERNAME       COMMON     ORACLE_MAINTAINED

--------------- ---------- --------------------

SALES_APP_USER    YES      N

 

SALES_APP_ROOT1>alter user sales_app_user identified by oracle;

 

User altered.

 

  • The SHARING attribute of application tables remains NONE as in source regular PDB east_pdb. Also, since application tables are not yet associated with any application, a DDL operation can be performed on them even from outside an application action.

EAST_PDB>select object_name, object_type, sharing, namespace from dba_objects

         where owner =    'SALES_APP_USER';

 

OBJECT_NAME           OBJECT_TYPE           SHARING           NAMESPACE

----------------------- ----------------------- ------------------ ---------

CUSTOMERS             TABLE                  NONE                      1

 

 

SALES_APP_ROOT>select object_name, object_type, sharing, namespace

               from dba_objects where owner = 'SALES_APP_USER';

 

OBJECT_NAME           OBJECT_TYPE SHARING               NAMESPACE

----------------------- ------------- -------------------- ---------

CUSTOMERS             TABLE        NONE                        1

PRODUCTS              TABLE        NONE                        1

ZIP_CODES             TABLE        NONE                        1

 

SALES_APP_ROOT1>alter table sales_app_user.customers

               modify (cust_name varchar2(31));

 

Table altered.

 

  • It can be seen that currently there is only an implicit application installed in the application container sales_app_root.

SALES_APP_ROOT>@get_app_status

 

APP_NAME                       APP_VERSION APP_ID APP_STATUS  IMPLICIT

-------------------------------------- ------------ ------ ------------ ------

APP$6177CEC316D62D55E05364C909C0DB70   1.0            2 NORMAL      Y

 

  • With sales_app_root as the current container, start installation of the sales application sales_app by issuing an ALTER PLUGGABLE DATABASE ... BEGIN INSTALL statement.

SALES_APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION sales_app BEGIN INSTALL '1.0';

 

  • Run the procedure DBMS_PDB.SET_USER_EXPLICIT to associate user sales_app_user with the application sales_app as an application common user.

SALES_APP_ROOT>exec dbms_pdb.set_user_explicit ('SALES_APP_USER');

 

  • Run the DBMS_PDB.SET_DATA_LINKED procedure to associate the table sales_app_user.products with the application sales_app as a data-linked application common table.

SALES_APP_ROOT>exec dbms_pdb.SET_DATA_LINKED (schema_name =>'SALES_APP_USER',-

                                               object_name => 'PRODUCTS',-

                                               namespace => 1);

 

  • Run the DBMS_PDB.SET_METADATA_LINKED procedure to associate table sales_app_user.customers with the application sales_app as a metadata-linked application common table.

SALES_APP_ROOT>exec dbms_pdb.SET_METADATA_LINKED (schema_name =>'SALES_APP_USER',-

                                               object_name => 'CUSTOMERS',-

                                               namespace => 1);

 

  • Run the DBMS_PDB.SET_EXT_DATA_LINKED procedure to associate table sales_app_user.zip_codes with the application sales_app as an extended data-linked application common table.

SALES_APP_ROOT>exec dbms_pdb.SET_EXT_DATA_LINKED (schema_name =>'SALES_APP_USER',-

                                               object_name => 'ZIP_CODES',-

                                               namespace => 1);

 

  • End the application installation operation by issuing an ALTER PLUGGABLE DATABASE ... END INSTALL statement.

SALES_APP_ROOT> ALTER PLUGGABLE DATABASE APPLICATION sales_app END INSTALL '1.0';

 

  • With the application root as the current container, ensure that application sales_apphas been installed and
    • The common property of the user sales_app_user is correct
    • The SHARING and APPLICATION properties of the common application tables are set appropriately

SALES_APP_ROOT> @get_app_status

 

APP_NAME                       APP_VERSION APP_ID APP_STATUS  IMPLICIT

-------------------------------------- ------------ ------ ------------ ------

APP$6177CEC316D62D55E05364C909C0DB70   1.0            2 NORMAL      Y

SALES_APP                              1.0            3 NORMAL   N

 

SALES_APP_ROOT>select username, common, oracle_maintained from dba_users

             where common = 'YES' and oracle_maintained = 'N';

 

USERNAME       COMMON     ORACLE_MAINTAINED

--------------- ---------- --------------------

SALES_APP_USER YES        N

 

SALES_APP_ROOT>select object_name, object_type, sharing, application

              from dba_objects

               where owner = 'SALES_APP_USER';

 

 

OBJECT_NAME           OBJECT_TYPE  SHARING           APPLICATION

----------------------- --------------- ------------------ ------------

CUSTOMERS             TABLE         METADATA LINK     Y

PRODUCTS              TABLE         DATA LINK         Y

ZIP_CODES             TABLE         EXTENDED DATA LINK Y

 

  • Now that the user sales_app_user and the application table sales_app_user.customers have been associated with the application sales_app, it is not possible to perform a DDL on them from outside an application install / upgrade / patch operation.

SALES_APP_ROOT>alter user sales_app_user identified by oracle;

 

alter user sales_app_user identified by oracle

*

ERROR at line 1:

ORA-65274: operation not allowed from outside an application action

 

SALES_APP_ROOT1>alter table sales_app_user.customers modify (cust_name varchar2(32));

alter table sales_app_user.customers modify (cust_name varchar2(32))

*

ERROR at line 1:

ORA-65274: operation not allowed from outside an application action

 

  • In the application root sales_app_root, clone the existing regular PDBs east_pdb, west_pdb, north_pdb and south_pdb as application PDBs east_app_pdb, west_app_pdb, north_app_pdb and south_app_pdbrespectively.
    • Note that currently no application PDBs are associated with sales_app_root.

SALES_APP_ROOT>@get_app_containers

 

   CON_ID NAME         OPEN_MODE APP_ROOT APP_PDB APP_SEED

---------- -------------------- ---------- -------- -------- --------

      7 SALES_APP_ROOT READ WRITE YES       NO           NO

 

  • Create a database link from application root sales_app_root to CDB$root

SALES_APP_ROOTR>CREATE PUBLIC DATABASE LINK cdbroot_link

            CONNECT TO system IDENTIFIED BY oracle

            USING 'orclcdb';

 

  • Clone regular PDB west_pdb as application PDB west_app_pdb in the application root sales_app_root. Violations will be reported during opening of the application PDB.

SALES_APP_ROOT>ho mkdir -p /u02/app/oracle/oradata/orclcdb/sales_app_root/west_app_pdb

 

SALES_APP_ROOT>ALTER session SET

db_create_file_dest='/u02/app/oracle/oradata/orclcdb/sales_app_root/west_app_pdb';

 

SALES_APP_ROOT>CREATE PLUGGABLE DATABASE west_app_pdb FROM west_pdb@cdbroot_link ;

 

SALES_APP_ROOT>Alter PLUGGABLE DATABASE west_app_pdb open;

 

Warning: PDB altered with errors.

 

  • Switch to the application PDB west_app_pdb as a user with the required privileges and run the pdb_to_apppdb.sql script in the ORACLE_HOME/rdbms/admin directory. Optionally, drop source regular PDB west_pdb.

WEST_APP_PDB>@$ORACLE_HOME/rdbms/admin/pdb_to_apppdb

 

WEST_APP_PDB>sho pdbs

 

   CON_ID CON_NAME                       OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

         8 WEST_APP_PDB                   READ WRITE YES

 

-- Reopen in non-restricted mode

 

WEST_APP_PDB>alter pluggable database west_app_pdb close immediate;

 

             alter pluggable database west_app_pdb open;

 

             sho pdbs

P

 CON_ID CON_NAME                       OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

         8 WEST_APP_PDB                   READ WRITE NO

 

 

ORCLCDB$ROOT>alter pluggable database west_pdb close immediate;

 

            drop pluggable database west_pdb including datafiles;

 

  • Similarly clone rest of the existing regular PDBs east_pdb, north_pdb and south_pdb as application PD's east_app_pdb, north_app_pdb and south_app_pdb respectively. Check that all four regular PDBs have been converted to application PDBs and are in sync with application sales_app version 1.0.

ORCLCDB$ROOT>@get_app_containers

 

   CON_ID NAME         OPEN_MODE APP_ROOT APP_PDB APP_SEED

---------- -------------------- ---------- -------- -------- --------

      1 CDB$ROOT       READ WRITE NO        NO           NO

      2 PDB$SEED       READ ONLY NO         NO           NO

      3 SOUTH_APP_PDB  READ WRITE NO        YES          NO

      4 NORTH_APP_PDB  READ WRITE NO        YES          NO

      7 SALES_APP_ROOT READ WRITE YES       NO           NO

      8 WEST_APP_PDB   READ WRITE NO        YES          NO

      9 EAST_APP_PDB   READ WRITE NO        YES          NO

SALES_APP_ROOT>@get_app_pdb_status

 

NAME              CON_UID APP_NAME       APP_VERSIO APP_STATUS

-------------------- ---------- -------------------- ---------- ------------

WEST_APP_PDB           2620482286 SALES_APP          1.0      NORMAL

EAST_APP_PDB           3418848181 SALES_APP          1.0      NORMAL

NORTH_APP_PDB          1856849412 SALES_APP          1.0      NORMAL

SOUTH_APP_PDB            65437774 SALES_APP          1.0      NORMAL

 

  • Ensure that in all the application PDBs
    • The common property of the user sales_app_user is correct
    • The SHARING and APPLICATION properties of the common application tables are correct

SALES_APP_ROOT>select con$name, username, common from containers (dba_users)

               where username = 'SALES_APP_USER';

 

CON$NAME              USERNAME           COMMON

--------------------  -------------------- ----------

SALES_APP_ROOT         SALES_APP_USER     YES

SOUTH_APP_PDB          SALES_APP_USER     YES

NORTH_APP_PDB          SALES_APP_USER     YES

WEST_APP_PDB           SALES_APP_USER     YES

EAST_APP_PDB           SALES_APP_USER     YES

.

 

SALES_APP_ROOT>select con$name, object_name, owner, application, sharing

               from containers(dba_objects)

              where owner = 'SALES_APP_USER';

  

CON$NAME       OBJECT_NAME   OWNER       APPLICATION SHARING

-------------- ------------- --------------- ------------ --------------------

SALES_APP_ROOT CUSTOMERS     SALES_APP_USER Y        METADATA LINK

SALES_APP_ROOT PRODUCTS     SALES_APP_USER Y        DATA LINK

SALES_APP_ROOT ZIP_CODES     SALES_APP_USER Y        EXTENDED DATA LINK

SOUTH_APP_PDB CUSTOMERS     SALES_APP_USER Y        METADATA LINK

SOUTH_APP_PDB PRODUCTS     SALES_APP_USER Y        DATA LINK

SOUTH_APP_PDB ZIP_CODES     SALES_APP_USER Y        EXTENDED DATA LINK

NORTH_APP_PDB CUSTOMERS     SALES_APP_USER Y        METADATA LINK

NORTH_APP_PDB PRODUCTS     SALES_APP_USER Y        DATA LINK

NORTH_APP_PDB ZIP_CODES     SALES_APP_USER Y        EXTENDED DATA LINK

WEST_APP_PDB   CUSTOMERS     SALES_APP_USER Y        METADATA LINK

WEST_APP_PDB   PRODUCTS     SALES_APP_USER Y        DATA LINK

WEST_APP_PDB   ZIP_CODES     SALES_APP_USER Y        EXTENDED DATA LINK

EAST_APP_PDB   CUSTOMERS     SALES_APP_USER Y        METADATA LINK

EAST_APP_PDB   PRODUCTS     SALES_APP_USER Y        DATA LINK

EAST_APP_PDB   ZIP_CODES     SALES_APP_USER Y        EXTENDED DATA LINK

 

15 rows selected.

 

Thus, we have successfully migrated four regular PDBs (east_pdb, west_pdb, north_pdb and south_pdb) running the same sales application to an application container sales_app_root as application PDBs (east_app_pdb, west_app_pdb, north_app _pdb and south_app _pdb), thereby enabling them to share application objects and be administered easily as one.

References:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/creating-removing-application-containers-seeds-with-sql-plus.html#GUID-20B0B12F-A6A5-4403-95FF-64B3A5CC00A9

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_PDB.html#GUID-3CBF9467-9430-44F1-8D22-91CC796B3A3E 

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/creating-removing-application-containers-seeds-with-sql-plus.html#GUID-4A0E90BA-C0A8-4454-B82F-2CB7A75A9761 

https://docs.oracle.com/database/122/ADMIN/administering-application-containers-with-sql-plus.htm#ADMIN-GUID-D2F30723-9E70-469C-8048-CB06DE00173E 

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/administering-application-containers-with-sql-plus.html#GUID-D7448903-C3BB-47DA-9EC7-5D1B212C9D02 

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/administering-application-containers-with-sql-plus.html#GUID-4C6F56CF-1FA1-4490-8262-818A6DA7CE05

 

Scripts used in this article:

get_app_containers.sql

-- Find out the containers in an application root when executed from application root

SELECT con_id, name, open_mode, application_root app_root,

            application_pdb app_pdb, application_seed app_seed

from v$containers 

order by con_id; 

 

get_app_status.sql

-- Find out the status of various applications in an application container when executed from an application root

select app_name, app_version, app_id, app_status, app_implicit implicit from dba_applications;

 

get_app_pdb_status.sql

-- Find out the available versions of application sales_app with which various application / seed PDBs are in sync currently

SELECT c.name,  aps.con_uid,   aps.app_name,   aps.app_version,   aps.app_status

      FROM   dba_app_pdb_status aps

       JOIN v$containers c

               ON c.con_uid = aps.con_uid

      WHERE  aps.app_name = 'SALES_APP';

----------------------------------------------------------------------------------------------------------------------------------------