Oracle Multi-tenant Application Containers - Part VI - Convert Regular PDB to Application PDB

    Dec 14, 2017 2:15:42 PM by Anju Garg

    Overview

    In my most recent article, I discussed the cross-container DML statement, which is a convenient way for the Application Root administrator to perform DML operations on a table in any application PDB within the application container, without connecting to it or creating corresponding database link.

    In this article I will discuss conversion of a regular PDB to an application PDB so that applications that are already installed in it can also take advantage of application containers.  

     

    Current Scenario

    Here, we have an Oracle database 12.2.0.1 CDB called orclcdb as shown. Within this CDB, besides CDB Seed PDB pdb$seed, we have a regular PDB called orclpdb which supports its own application. Also, we have created an application container sales_app_root  for the sales application (sales_app) of an organization. The application container sales_app_root houses four application PDBs north_app_pdb,   south_app_pdbeast_app_pdb  and west_app_pdb which support the sales_app application for various regional offices of the organization.  While sharing the structure of the metadata-linked customers table stored in the application root sales_app_root, each application PDB can store region-specific customer data in the table.

                 

    It is desired that the regular PDB orclpdb, besides supporting its own application, should also be able to access common application objects of the application sales_app. In order to fulfill this requirement, we will clone the regular PDB orclpdb to application PDB centre_app_pdb within the application container sales_app_root as shown below so that common application objects of the sales_app application are accessible to it

     

    The process involves following steps:

    • Clone the regular PDB orclpdb into the application root sales_app_root. as application PDB centre_app_pdb.  Violations will be reported during the opening of application PDB as application sales_app in the application root does not exist in application PDB yet.
    • Connect to the new application PDB centre_app_pdb and execute the $ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql script so that common application objects are accessible in the application PDB. For example, the common application user sales_app_user that exists in the application root is marked as common in the plugged application PDB centre_app_pdb. The script automatically synchronizes the application PDB with the application root.

     

    Demonstration

    Let us first connect to CDB orclcdb and verify that there is one regular PDB, orclpdb, in this CDB.

    SQL> conn sys/oracle@orclcdb as sysdba
         set sqlprompt CDB$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 = 'NO' and application_PDb = 'NO'
           and application_seed = 'NO';
     
        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
           8 ORCLPDB     READ WRITE NO       NO     NO

    Note that there is one application container sales_app_root in this CDB.

    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
    ---------- -------------------- ---------- ---------- ---------- ----------
          15 SALES_APP_ROOT READ WRITE YES          NO    NO

    Connect to application root sales_app_root and note that there are four application PDBs (north_app_pdb, east_app_pdb, west_app_pdb, south_app_pdb) ) associated with it.

    SQL> conn sys/oracle@host01:1522/sales_app_root as sysdba
         set sqlprompt  SALES_APP_ROOT>
     
    SALES_APP_ROOT>sho con_name
     
    CON_NAME
    ------------------------------
    SALES_APP_ROOT
     
     
    SALES_APP_ROOT>@get_app_containers
     
        CON_ID NAME         OPEN_MODE  APP_ROOT APP_PDB  APP_SEED
    ---------- -------------------- ---------- -------- -------- --------
           4 SOUTH_APP_PDB  READ WRITE NO         YES      NO
           5 EAST_APP_PDB   READ WRITE NO         YES      NO
           6 WEST_APP_PDB   READ WRITE NO         YES      NO
           7 NORTH_APP_PDB  READ WRITE NO         YES      NO
          15 SALES_APP_ROOT READ WRITE YES        NO      NO
     
    5 rows selected.

    Besides an implicit application, another application sales_app is currently installed in this container. All the application PDBs are synced with the application sales_app version 1.0.

    SALES_APP_ROOT>@get_app_status
     
    APP_NAME                             APP_VERSION  APP_ID APP_STATUS   IMPLICIT
    ------------------------------------ ------------ ------ ------------ --------
    APP$5DED1EE7F9C418C7E05364C909C0F9BD 1.0        2 NORMAL      Y
    SALES_APP                            1.0        21 NORMAL      N
     
    SALES_APP_ROOT>@get_sales_app_pdb_status
     
    NAME             APP_NAME             APP_VERSION APP_STATUS
    -------------------- -------------------- ----------- ------------
    SOUTH_APP_PDB          SALES_APP            1.0       NORMAL
    EAST_APP_PDB           SALES_APP            1.0       NORMAL
    WEST_APP_PDB           SALES_APP            1.0       NORMAL
    NORTH_APP_PDB          SALES_APP            1.0       NORMAL

    Let us check the common objects currently in the application sales_app. There is a metadata-linked common application table sales_app_user.customers with structure as shown.

    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

    Let us create a local user l_user and a local table l_user.ltab in the regular pdb orclpdb to represent an application supported by it.

    ORCLPDB>create user l_user identified by oracle;
     
    User created.
     
    ORCLPDB>grant connect, unlimited tablespace, create table to l_user;
     
    Grant succeeded.
     
    ORCLPDB>create table l_user.l_tab (id number);
             insert into l_user.l_tab values (1);
             commit;
             select * from l_user.l_tab;
     
          ID
    ----------
           1

    In order to clone the regular pdb orclpdb as an application PDB centre_app_pdb in the application container sales_app_root, let us open orclpdb in read only mode.

    ORCLPDB>ALTER PLUGGABLE DATABASE orclpdb CLOSE immediate;
     
            ALTER PLUGGABLE DATABASE orclpdb OPEN READ ONLY;
     
    Pluggable database altered.

    Create a folder to house datafiles for the new application PDB centre_app_pdb and clone the regular PDB orclpdb as application PDB centre_app_pdb in the application container sales_app_root.

    SALES_APP_ROOT>ho mkdir /u01/app/oracle/oradata/orclcdb/sales_app_root/centre_app_pdb
     
    SALES_APP_ROOT>ALTER SESSION SET db_create_file_dest =
      '/u01/app/oracle/oradata/orclcdb/sales_app_root/centre_app_pdb';
     
            CREATE PLUGGABLE DATABASE centre_app_pdb FROM orclpdb;
     
    Pluggable database created.
     
    SALES_APP_ROOT>sho pdbs
     
        CON_ID CON_NAME                   OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
           3 CENTRE_APP_PDB         MOUNTED
           4 SOUTH_APP_PDB          READ WRITE NO
           5 EAST_APP_PDB           READ WRITE NO
           6 WEST_APP_PDB           READ WRITE NO
           7 NORTH_APP_PDB          READ WRITE NO
          15 SALES_APP_ROOT         READ WRITE NO

    When we try to open the newly created application PDB centre_app_pdb, it opens with a warning.

    SALES_APP_ROOT>alter pluggable database centre_app_pdb open;
     
    Warning: PDB altered with errors.
     
     
    SALES_APP_ROOT>@get_app_containers
     
        CON_ID NAME         OPEN_MODE  APP_ROOT APP_PDB  APP_SEED
    ---------- -------------------- ---------- -------- -------- --------
           3 CENTRE_APP_PDB READ WRITE NO         YES      NO
           4 SOUTH_APP_PDB  READ WRITE NO         YES      NO
           5 EAST_APP_PDB   READ WRITE NO         YES      NO
           6 WEST_APP_PDB   READ WRITE NO         YES      NO
           7 NORTH_APP_PDB  READ WRITE NO         YES      NO
          15 SALES_APP_ROOT READ WRITE YES        NO           NO
     
    6 rows selected.

    The content of the view PDB_PLUG_IN_VIOLATIONS view explains that the script pdb_to_apppdb.sql script must be executed on the converted regular PDB centre_app_pdb for it to become a full application PDB so that the application sales_app is accessible in it.

    SALES_APP_ROOT>SELECT cause, type, message, status, action
                   FROM pdb_plug_in_violations
                   WHERE name='CENTRE_APP_PDB';
     
    CAUSE           TYPE      MESSAGE                         STATUS   ACTION
    --------------- --------- ------------------------------ --------- ------------------------------
    Application WARNING      Application SALES_APP in Appli  PENDING   Fix the application in the PDB
                             cation Root does not exist in             or the application root
                             Application PDB.
     
    Non-Application ERROR     Non-Application PDB plugged in PENDING   Run pdb_to_apppdb.sql.
    PDB to Applica            as an Application PDB, requir
    tion PDB                  es pdb_to_apppdb.sql be run.

    Note that while the local user(l_user) / table (l_tab) created in the parent regular PDB orclpdb are accessible in the newly created application PDB centre_app_pdb, the common application table  sales_app_user.customers for the application sales_app is not accessible yet because the application PDB centre_app_pdb has not been synchronized with the application root sales_app_root.

    CENTRE_APP_PDB>select * from l_user.l_tab;
     
          ID
    ----------
           1
     
    CENTRE_APP_PDB>select * from sales_app_user.customers;
    select * from sales_app_user.customers
                                 *
    ERROR at line 1:
    ORA-00942: table or view does not exist
     
    SALES_APP_ROOT>@get_sales_app_pdb_status
     
    NAME             APP_NAME             APP_VERSION APP_STATUS
    -------------------- -------------------- ----------- ------------
    SOUTH_APP_PDB          SALES_APP            1.0       NORMAL
    EAST_APP_PDB           SALES_APP            1.0       NORMAL
    WEST_APP_PDB           SALES_APP            1.0       NORMAL
    NORTH_APP_PDB          SALES_APP            1.0       NORMAL

    Let us execute the conversion script pdb_to_apppdb.sql on the newly created application PDB centre_app_pdb.

    As I connect remotely to centre_app_pdb and execute the script, the script fails on a step and returns me to the OS prompt.

    SALES_APP_ROOT>CONNECT sys/oracle@host01:1522/centre_app_pdb AS SYSDBA
                   set sqlprompt CENTRE_APP_PDB>
     
    CENTRE_APP_PDB>@$ORACLE_HOME/rdbms/admin/pdb_to_apppdb
     
     
    CENTRE_APP_PDB>create or replace view sys.cdb$common_root_objects&pdbid sharing=object as
      2  select u.name owner, o.name object_name, o.type# object_type, o.namespace nsp,
      3         o.subname object_subname, o.signature object_sig,
      4         decode(bitand(o.flags, &sharing_bits),
      5                &edl+&mdl, 'EDL', &dl, 'DL', 'MDL') sharing
      6    from sys.obj$ o, sys.user$ u
      7   where o.owner#=u.user# and bitand(o.flags, &sharing_bits) <> 0
      8     and bitand(o.flags,&fedobjflag)=&fedobjflag;
    old   1: create or replace view sys.cdb$common_root_objects&pdbid sharing=object as
    new   1: create or replace view sys.cdb$common_root_objects4 sharing=object as
    old   4:        decode(bitand(o.flags, &sharing_bits),
    new   4:        decode(bitand(o.flags, (65536+131072+4294967296)),
    old   5:               &edl+&mdl, 'EDL', &dl, 'DL', 'MDL') sharing
    new   5:               4294967296+65536, 'EDL', 131072, 'DL', 'MDL') sharing
    old   7:  where o.owner#=u.user# and bitand(o.flags, &sharing_bits) <> 0
    new   7:  where o.owner#=u.user# and bitand(o.flags, (65536+131072+4294967296)) <> 0
    old   8:    and bitand(o.flags,&fedobjflag)=&fedobjflag
    new   8:    and bitand(o.flags,134217728)=134217728
    create or replace view sys.cdb$common_root_objects4 sharing=object as
    *
    ERROR at line 1:
    ORA-65021: illegal use of SHARING clause
     
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    [oracle@host01 bin]$

    After reconnecting to the application root sales_app_root, I note that centre_app_pdb is open in migrate mode.

    SALES_APP_ROOT>sho pdbs


        CON_ID CON_NAME                   OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
           3 CENTRE_APP_PDB         MIGRATE    YES
           4 SOUTH_APP_PDB          READ WRITE NO
           5 EAST_APP_PDB           READ WRITE NO
           6 WEST_APP_PDB           READ WRITE NO
           7 NORTH_APP_PDB          READ WRITE NO
          15 SALES_APP_ROOT         READ WRITE NO

    After various repeated trials, I realized that there is a bug. The script pdb_to_apppdb.sql fails when it is executed after connecting to the application PDB centre_app_pdb remotely. Connection using “Alter session set container ..“ results in successful execution.

    SALES_APP_ROOT>alter session set container= centre_app_pdb;

                   set sqlprompt CENTRE_APP_PDB>
     
    CENTRE_APP_PDB>sho pdbs
     
        CON_ID CON_NAME                   OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
           3 CENTRE_APP_PDB         MIGRATE    YES
     
     
    CENTRE_APP_PDB>alter pluggable database centre_app_pdb close immediate;
     
                   alter pluggable database centre_app_pdb open;
     
    Warning: PDB altered with errors.
     
    -- This time script completes successfully
     
    CENTRE_APP_PDB>@$ORACLE_HOME/rdbms/admin/pdb_to_apppdb
     
    .
    .
    .
    CENTRE_APP_PDB>
    CENTRE_APP_PDB>-- leave the PDB in the same state it was when we started
    CENTRE_APP_PDB>BEGIN
      2    execute immediate '&open_sql &restricted_state';
      3  EXCEPTION
      4    WHEN OTHERS THEN
      5    BEGIN
      6      IF (sqlcode <> -900) THEN
      7        RAISE;
      8      END IF;
      9    END;
     10  END;
     11  /
     
    PL/SQL procedure successfully completed.
     
    CENTRE_APP_PDB>
    CENTRE_APP_PDB>WHENEVER SQLERROR CONTINUE;
     


    CENTRE_APP_PDB>sho pdbs
     
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             3 CENTRE_APP_PDB                 READ WRITE YES

    Let us reopen the newly created application PDB centre_app_pdb to bring it out of RESTRICTED mode.

    CENTRE_APP_PDB>alter pluggable database centre_app_pdb close immediate;
     
                   alter pluggable database centre_app_pdb open;
     
    Pluggable database altered.
     
    CENTRE_APP_PDB>sho pdbs
     
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             4 CENTRE_APP_PDB                 READ WRITE NO

    The script automatically synchronizes the application PDB with the application root.

    SALES_APP_ROOT>@get_sales_app_pdb_status
     
    NAME             APP_NAME             APP_VERSION APP_STATUS
    -------------------- -------------------- ----------- ------------
    SOUTH_APP_PDB          SALES_APP            1.0       NORMAL
    EAST_APP_PDB           SALES_APP            1.0       NORMAL
    WEST_APP_PDB           SALES_APP            1.0       NORMAL
    NORTH_APP_PDB          SALES_APP            1.0       NORMAL
    CENTRE_APP_PDB         SALES_APP            1.0       NORMAL
     
    5 rows selected.

    Note that now newly created application PDB centre_app_pdb can  recognize:

    • Common application entities of the application sales_app; i.e., metadata-linked application table customers owned by the common application user sales_app_user
    • Application objects created in the parent regular PDB orclpdb
    CENTRE_APP_PDB>select username, common from dba_users where username = 'SALES_APP_USER';
     
    USERNAME         COMMON
    --------------- ----------
    SALES_APP_USER    YES
     
    CENTRE_APP_PDB>select owner, object_name, object_type, sharing from dba_objects where owner = 'SALES_APP_USER';
     
     
    OWNER            OBJECT_NAME        OBJECT_TYPE             SHARING
    --------------- -------------------- ----------------------- -----------------
    SALES_APP_USER    CUSTOMERS          TABLE                  METADATA LINK
     
     
    CENTRE_APP_PDB>select * from sales_app_user.customers;
     
    no rows selected
     
     
    CENTRE_APP_PDB>select * from l_user.l_tab;
     
          ID
    ----------
           1

    Hence, we have successfully converted a regular PDB orclpdb to an application PDB centre_app_pdb in the application container sales_app_root so that besides supporting its existing application(s), it can take advantage of the common application objects of application sales_app in the application container as well.

     

    Summary

    • A regular PDB can be converted to an application PDB so that applications already installed in it can take advantage of application containers. 
    • The process involves following steps:
      • Clone / Plug the regular PDB into application root as application PDB.  Violations will be reported during the opening of the application PDB as the application in the application root does not exist in the application PDB yet.
      • Connect to the new application PDB and execute the $ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql script so that the application PDB is

    -      converted to a full application PDB

    -      synchronized with the application root

    • An application PDB so created can access
      • Existing application objects
      • Common application objects in the application container

     

    References:

    https://docs.oracle.com/database/122/ADMIN/administering-application-containers-with-sql-plus.htm#ADMIN-GUID-27A78D0C-B90B-4BB9-A8D2-787353A667B1

    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/