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;

 

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