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 thesales_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_appis currently installed in this container. All the application PDBs are synced with the application sales_appversion 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.customerswith 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_userand a local table l_user.ltabin 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 orclpdbas an application PDB centre_app_pdbin the application container sales_app_root, let us open orclpdbin 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_pdband clone the regular PDB orclpdbas application PDB centre_app_pdb in the application containersales_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_VIOLATIONSview explains that the script pdb_to_apppdb.sqlscript must be executed on the converted regular PDB centre_app_pdbfor it to become a full application PDB so that the application sales_appis 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 orclpdbare accessible in the newly created application PDB centre_app_pdb, the common application table  sales_app_user.customersfor the application sales_appis not accessible yet because the application PDB centre_app_pdbhas 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.sqlon 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_pdbis 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.sqlfails when it is executed after connecting to the application PDB centre_app_pdbremotely. 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_pdbto 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_pdbcan  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_pdbin 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;

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/

Start the discussion at forums.toadworld.com