Oracle Multitenant Application Containers – Part IX (Move application container across CDBs)

    Mar 12, 2018 4:16:02 PM by Anju Garg

    Introduction

    In my previous article, I discussed the use of proxy PDBs with application containers to load balance an application across CDBs by relocating application PDB‘s). In this article, I will discuss the moving of an application container to another CDB by unplugging and plugging. It is useful when you need to move the production application container to another CDB.

    In order to move the application container to a different CDB, the application container can be unplugged from the source CDB and plugged into the destination CDB. Unplugging an application container involves the unplugging of files for each constituent container (the application root, the application seed, and the application PDBs). An unplug operation can be performed using

    • An XML metadata file (.xml extension) that describes the PDB and includes the full paths to the locations of the datafiles associated with the PDB.  In order to plug the PDB into another CDB, the ".xml" file and all the relevant database files must be copied or moved individually over to the destination server.
    • PDB archive file (.pdb extension), introduced in Oracle 12.2.0.1, which is a compressed file that contains
      • The XML file that describes the PDB and contains names of the datafiles associated with the PDB
      • All the datafiles associated with the PDB.

    In this case, the transfer of the files between servers is simplified. Only the PDB archive file needs to be copied or moved to the destination server since both the XML metadata file and the PDB datafiles required by the plugging operation are already a part of the archive file.

    In this article, I will demonstrate moving an application container to another CDB by unplugging and plugging using archive files. 

     

    Current scenario

    The current configuration is shown below in in Fig. 1. We have an Oracle database 12.2.0.1 CDB called orclcdb as shown. Within this CDB, besides the CDB Seed PDB pdb$seed, we have an application container with the application root sales_app_root  for the sales application (sales_app). The application container sales_app_root houses one application PDB app_pdb1 which supports the sales_app application. We also have another 12.2.0.1 CDB called rmtcdb having only one PDB, namely, CDB Seed PDB pdb$seed.

    6011.garg 1-1.png-1100x19998

    6011.garg 1-2.png-1100x19998

    Fig. 1

    I will demonstrate moving application container sales_app_root from CDB orclcdb to CDB rmtcdb so that the configuration becomes as shown below in Fig. 2.

      

    8176.garg 2 -2.png-1100x19998

    Fig. 2

     

    Demonstration

    • Connect to CDB orclcdb and note that there is one application container sales_app_root in this CDB.
    SQL> conn sys/oracle@orclcdb as sysdba
         set sqlprompt ORCLCDB$ROOT>
         sho con_name
     
    CON_NAME
    ------------------------------
    CDB$ROOT
     
     
    CDB$ROOT>SELECT con_id, name, open_mode, application_root app_root,
               application_pdb app_pdb, application_seed app_seed
            from v$containers
            where application_root = 'YES' and application_PDb = 'NO' ;
     
       CON_ID NAME       OPEN_MODE APP_ROOT APP_PDB      APP_SEED
    ---------- --------------- ---------- -------- -------- --------
          3 SALES_APP_ROOT READ WRITE YES     NO NO 
    • Connect to application root sales_app_root and note that there is one application seed (sales_app_root$seed) and one application PDB (app_pdb1) associated with it. Also, besides an implicit application, another application sales_app is currently installed in this container. Both the application seed and the application PDB app_pdb1 are synced with sales_app version 1.0.
    CDB$ROOT> conn sys/oracle@host01:1522/sales_app_root as sysdba
         set sqlprompt SALES_APP_ROOT>
     
    SALES_APP_ROOT>sho con_name
     
    SALES_APP_ROOT>@get_app_containers
     
       CON_ID NAME         OPEN_MODE APP_ROOT APP_PDB APP_SEED
    --------- -------------------- ---------- -------- -------- --------
         3 SALES_APP_ROOT READ WRITE YES       NO      NO
         4 SALES_APP_ROOT$SEED  READ WRITE NO        YES     YES
         5 APP_PDB1       READ WRITE NO        YES     NO
     
    SALES_APP_ROOT>@get_app_status
     
     
    APP_NAME                       APP_VERSION APP_ID APP_STATUS  IMPLICIT
    -------------------------------------- ------------ ----- ------------ ------
    APP$6020BA8D64AC386FE05364C909C02795   1.0            2 NORMAL      Y
    SALES_APP                   1.0           3  NORMAL       N
     
    SALES_APP_ROOT>@get_sales_app_pdb_status
     
    NAME             APP_NAME           APP_VERSION APP_STATUS
    -------------------- -------------------- ----------- ------------
    SALES_APP_ROOT$SEED SALES_APP            1.0       NORMAL
    APP_PDB1         SALES_APP          1.0       NORMAL
     
    • There is one common application object associated with the application sales_app, namely the metadata linked table customers owned by sales_app_user.
    SALES_APP_ROOT>@get_sales_app_objects
     
    APP_NAME   OWNER     OBJECT_NAME     OBJECT_TYPE     SHARING          APPLICATION
    ---------- ---------------- --------------- --------------- ---------------- ------------
    SALES_APP SALES_APP_USER CUSTOMERS       TABLE       METADATA LINK     Y
     
    SALES_APP_ROOT>desc sales_app_user.customers
     
    Name                   Null?       Type
    ----------------------- --------    ----------------
    CUST_ID                NOT NULL   NUMBER
    CUST_NAME                        VARCHAR2(30)
    CUST_ADD                         VARCHAR2(30)
    CUST_ZIP                         NUMBER

    • Connect to CDB rmtcdb and note that there is only one PDB, namely, the CDB seed PDB PDB$seed, in this CDB.
    SQL> conn sys/oracle@rmtcdb as sysdba
         set sqlprompt RMTCDB$ROOT>
     
    RMTCDB$ROOT>sho pdbs
     
      CON_ID CON_NAME             OPEN MODE RESTRICTED
    ---------- --------------------- ---------- ----------
          2 PDB$SEED             READ ONLY NO
     

    Now we will unplug the application container sales_app_root from the CDB orclcdb using archive files and plug it into CDB rmtcdb so as to create a new application container sales_app_root in CDB rmtcdb.

    • Unplug the application container sales_app_root from CDB orclcdb using archive files for each container (the application root sales_app_root, the application seed sales_app_root$seed, and the application PDB app_pdb1). Note that we need to proceed in the following order to unplug an application container:
    1. The application PDBs dependent on the application root
    2. The application seed, if it exists
    3. The application root.
    ORCLCDB$ROOT>ALTER PLUGGABLE DATABASE sales_app_root CLOSE;
     
                 SHOW pdbs
     
      CON_ID CON_NAME               OPEN MODE RESTRICTED
    ---------- ----------------------- ---------- ----------
        2 PDB$SEE         READ ONLY NO
        3 SALES_APP_ROOT   MOUNTED
        4 SALES_APP_ROOT$SEED     MOUNTED
        5 APP_PDB1            MOUNTED
     
    ORCLCDB$ROOT>ALTER PLUGGABLE DATABASE app_pdb1
                   UNPLUG INTO '/tmp/app_pdb1.pdb';
     
               ALTER PLUGGABLE DATABASE sales_app_root$SEED
                   UNPLUG INTO '/tmp/sales_app_root_seed.pdb';
     
              ALTER PLUGGABLE DATABASE sales_app_root
                    UNPLUG INTO '/tmp/sales_app_root.pdb';

    As a result of the unplug operation, one compressed archive file having a .pdb extension has been created for each PDB. Since each archive file contains the manifest as well as all the data files of the respective PDB, even if we drop the unplugged PDBs we will still be able to plug them into the destination CDB.

    ORCLCDB$ROOT>DROP PLUGGABLE DATABASE app_pdb1 INCLUDING DATAFILES;
     
                DROP PLUGGABLE DATABASE sales_app_root$SEED INCLUDING DATAFILES;
         
                DROP PLUGGABLE DATABASE sales_app_root INCLUDING DATAFILES;
      
                sho pdbs
     
      CON_ID CON_NAME              OPEN MODE RESTRICTED
    ---------- ------------------------ ---------- ----------
          2 PDB$SEED           READ ONLY NO
    • Create folders on the target server to hold datafiles of the application PDBs to be plugged in.
    RMTCDB$ROOT>ho mkdir -p /u02/app/oracle/oradata/rmtcdb/sales_app_root/app_pdb1
     
               ho mkdir -p /u02/app/oracle/oradata/rmtcdb/sales_app_root/sales_app_root_seed
    • Plug the sales_app_root application container into CDB rmtcdb. Note that we need to proceed in the following order to plug an application container:
    1. The application root.
    2. The application seed, if it exists
    3. The application PDBs
    -- Plug in application root sales_app_root
    RMTCDB$ROOT>ALTER SESSION SET
             db_create_file_dest='/u02/app/oracle/oradata/rmtcdb/sales_app_root/';    
     
             CREATE PLUGGABLE DATABASE sales_app_root AS APPLICATION CONTAINER
             AS CLONE USING '/tmp/sales_app_root.pdb';
     
    Pluggable database created.
     
    RMTCDB$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 SALES_APP_ROOT MOUNTED   YES       NO           NO
     
    RMTCDB$ROOT>ALTER PLUGGABLE DATABASE sales_app_root OPEN;
     
    -- Plug in application seed sales_app_root$seed
    RMTCDB$ROOT>CONNECT sys/oracle@host01:1522/sales_app_root AS SYSDBA
               sho parameter db_name
     
    NAME                     TYPE   VALUE
    ------------------------ ---------- -----------------------------
    db_name                   string rmtcdb
     
    RMTCDB$ROOT>sho con_name
     
    CON_NAME
    ------------------------------
    SALES_APP_ROOT
     
    RMTCDB$ROOT>set sqlprompt SALES_APP_ROOT>
     
    SALES_APP_ROOT>ALTER SESSION SET
    db_create_file_dest='/u02/app/oracle/oradata/rmtcdb/sales_app_root/sales_app_root_seed';
     
    SALES_APP_ROOT>CREATE PLUGGABLE DATABASE AS SEED
                  AS CLONE USING '/tmp/sales_app_root_seed.pdb';
     
                   sho pdbs
     
      CON_ID CON_NAME                 OPEN MODE RESTRICTED
    --------- ------------------------- ------------ ----------
      3 SALES_APP_ROOT       READ WRITE NO
      5 SALES_APP_ROOT$SEED       MOUNTED
     
                 ALTER PLUGGABLE DATABASE sales_app_root$seed OPEN;
     
    -- Plug in application PDB app_pdb1
    SALES_APP_ROOT>ALTER SESSION SET
    db_create_file_dest='/u02/app/oracle/oradata/rmtcdb/sales_app_root/app_pdb1';
     
                   CREATE PLUGGABLE DATABASE app_pdb1
                 AS CLONE USING '/tmp/app_pdb1.pdb';
     
                   sho pdbs
     
       CON_ID CON_NAME               OPEN MODE RESTRICTED
    ---------- --------------------- ----------- ----------
          3 SALES_APP_ROOT        READ WRITE NO
          4 APP_PDB1             MOUNTED
          5 SALES_APP_ROOT$SEED      READ WRITE NO
     
     
    SALES_APP_ROOT>ALTER PLUGGABLE DATABASE app_pdb1 OPEN;
     
    SALES_APP_ROOT>@get_app_containers
     
     CON_ID NAME         OPEN_MODE APP_ROOT APP_PDB APP_SEED
    --------- -------------------- ----------- -------- -------- --------
      3 SALES_APP_ROOT READ WRITE YES       NO      NO
      4 APP_PDB1       READ WRITE NO        YES     NO
      5 SALES_APP_ROOT$SEED  READ WRITE NO        YES     YES
    • Verify that the common application table sales_app_user.customers is accessible from application PDB app_pdb1.
    APP_PDB1>desc sales_app_user.customers
    Name                            Null?   Type
    ------------------------------ ---------- ----------------------------
    CUST_ID                         NOT NULL NUMBER
    CUST_NAME                             VARCHAR2(30)
    CUST_ADD                               VARCHAR2(30)
    CUST_ZIP                               NUMBER

    Thus, we have been able to move the application container sales_app_root from CDB orclcdb to CDB rmtcdb successfully.

     

    Summary

    • An application container can be moved to a different CDB by unplugging it from the source CDB and plugging it into the destination CDB.
    • The unplug operation can be performed using
      • An XML metadata file (.xml extension) that describes the PDB
      • PDB archive file (.xml extension), which is a compressed file that contains
        • The XML file that describes the PDB
        • All the datafiles associated with the PDB.
        • While plugging in using an archive file, only the PDB archive file needs to be copied or moved to the destination server.

     

    References:

    Scripts use in the 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_sales_app_pdb_status

    -- Find out various versions of application SALES_APP and synchronization status of various application PDBs with it. Execute from application root.

       SELECT c.name,
              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';
     
    get_sales_app_objects

    -- Find out objects belonging to sales_app application.

         select app.app_name, obj.owner, obj.object_name, obj.object_type,
                   obj.sharing, obj.application
       from dba_objects obj, dba_applications app
       where obj.owner in
             (select username from dba_users
               where oracle_maintained = 'N')
             and obj.application = 'Y'
    and obj.created_appid = app.app_id;

    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/