Oracle Multitenant Application Containers – Part X: Migrate an existing application to an application container

    Apr 9, 2018 12:13:53 PM by Anju Garg

    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 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';

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

     

    Tags: Oracle

    Anju Garg

    Written by 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 certified for : Oracle 9i Database Administration OCP Oracle 11g Database Administration OCP Oracle 11g Performance Tuning OCE Oracle 11g R2 RAC OCE Oracle 11g SQL Tuning OCE Oracle 12c Database Administration OCP Oracle Real Application Clusters 12c Certified Implementation Specialist She is passionate about learning and has keen interest in RAC and Performance Tuning. She shares her knowledge via her technical blog at http://oracleinaction.com/