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_useridentified 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 S ALES_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/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';

—————————————————————————————————————————————

 

About the Author

Anju Garg

Anju Garg is an Oracle Ace with over 14 years of experience in IT Industry in various roles. Since 2010, she has been involved in teaching and has trained more than a hundred DBAs from across the world in various core DBA technologies like RAC, Data guard, Performance Tuning, SQL statement tuning, Database Administration etc. She is a regular speaker at Sangam and OTNYathra. She writes articles about Oracle and is one of the reviewers of the following book published by Pearson Oracle Problem-Solving and Troubleshooting Handbook. She is passionate about learning and has keen interest in RAC and Performance Tuning. You can learn all about Anju’s credentials and read more from her via her technical blog site at http://oracleinaction.com/

Notable Replies

  1. says:
    iatco.n97

    hello, I have a general question regarding the applications that are installed in Applicaton Container. How can I access, for example, the earliest version of the application (for example1.1) when the Application with version 1.3 is already installed and synchronized in Application Rot.