Toad World Blog

Oracle Multitenant Application Containers - Part VII Querying Data Across CDBs using Proxy PDB

Jan 8, 2018 2:57:07 PM by Anju Garg

Introduction

In my previous article, I discussed 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.  

In this article, I will discuss the use of Proxy PDBs with application containers. Oracle Database 12.2.0.1.0 introduces a new type of PDB, the Proxy PDB. A proxy PDB provides access to another PDB, called the referenced PDB, in the same / different CDB on the same / different host. All the SQL statements (DDLs, DMLs and queries) except ALTER PLUGGABLE DATABASE and ALTER DATABASE statements, that are submitted for execution in the Proxy PDB are sent to the referenced PDB and remotely executed in it. The results of the remote execution are returned to the proxy PDB. Consequently, although the data is stored remotely and the operations are executed remotely in the referenced PDB, it appears as if the referenced PDB were in the local CDB.  Thus, proxy PDB provides a local connection point that references a remote PDB and allows you to execute SQL statements in the remote PDB as if it were a local PDB in the CDB.

In this article, I will discuss the use of Proxy PDBs with application containers to:

  • Propagate application definition to another application container in another CDB
  • Keep the application synchronized across application containers in various CDBs
  • Aggregate application data across multiple application containers

Application containers and Proxy PDB

If application containers in multiple CDBs need to have the same application definition (for example, same tables and PL/SQL packages), then the application is installed, upgraded, and patched in one of the application containers considered as the master application root. A proxy PDB, referencing the application root in a different CDB, is created in the application container of the master application root. When application installation scripts are run in the master root, Oracle Database propagates these statements to the proxy PDB, which in turn sends them remotely to the referenced application root. As a result, the referenced application root becomes a replica of the master application root and hence is termed ‘application root replica’. Thus, using proxy PDB, the application definition can be propagated to another application container in another CDB.

Similarly, when changes are made to the application in the master application root and the proxy PDB is synchronized with the application, DDLs and DMLs propagated to the Proxy PDB are sent to its referenced application root replica and remotely executed in it, thereby synchronizing the application root replica with the master application root. Thus, proxy PDB can keep an application synchronized across application containers in different CDBs. .After synchronization of an application root replica, the application PDBs plugged into it can also synchronize with it to get the application changes.

One master application root can have multiple application root replicas in application containers in the same / different CDBs. In order to propagate the application definition to multiple application containers, one proxy PDB needs to be created in the master application root for each target application container.

When a query containing the CONTAINERS clause is issued against an application table in the master application root, it is executed recursively in each application PDB.

  • The query executes in all the local, non-proxy open application PDBs in the current application container (the application container containing the master application root) and returns results to master application root.
  • The query, when propagated to proxy PDB(s) in the current application container, is sent to the referenced application root replica(s), where it is executed in any open application PDB(s) plugged into the referenced application root replica(s). The results are returned to the master Application Root

The results received from all the open application PDBs across CDBs are aggregated in the master application root.

Thus, using proxy PDBs with application containers, the data of an application can be aggregated across multiple application containers. Consequently, location-transparent applications can be built which can aggregate data from multiple sources that can be in the same data center or distributed across data centers.

Hence, in order to keep the application synchronized across various application containers, we need to create:

  • A master application root – The application root in one of the application containers where the application is installed, upgraded, and patched.
  • One or more application root replicas – Copies of the master application root in the other application containers.
  • One or more proxy PDBs – PDBs in the master application root referencing application root replicas, such that there is one proxy PDB per referenced application root replica.

Now, I will demonstrate the above features of proxy PDBs with application containers.

 

Current Scenario

Here, 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 created an application container with the application root sales_app_root  for the sales application (sales_app) of an organization. The application container sales_app_root houses three application PDBs; i.e.,  north_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.

We also have another 12.2.0.1 CDB called rmtcdb having only a CDB Seed PDB pdb$seed.

Having opened a regional office in the south zone, the organization intends to house the PDB south_app_pdb for the south zone regional office in the CDB rmtcdb. Also, it is desired that the application sales_app is accessible to the PDB south_app_pdb such that

  • Any changes made to the application in the application container sales_app_root are propagated to south_app_pdb .
  • A query issued in sales_app_root can aggregate data from application PDBs across orclcdb and rmtcdb.

In order to achieve this objective, we will leverage proxy PDB and create a configuration as shown below. This configuration synchronizes the application root replica app_root_rr in the CDB rmtcdb by means of a proxy PDB px_app_root_rr in the master application root sales_app_root in the CDB orclcdb. The application PDB south_app_pdb  plugged into application root replica app_root_rr can synchronize with it to access the application sales_app

 

To create above configuration, we will:

  • Create an empty application container with the application root  app_root_rr in the CDB rmtcdb
  • Create a proxy PDB named px_app_root_rr in the master application root sales_app_root that references the application root replica app_root_rr in the CDB rmtcdb. The proxy PDB provides a context in which to execute SQL statements and perform operations in the proxied app_root_rr.
  • Synchronize the proxy PDB px_app_root_rr with the master application root sales_app_root so that the application sales_app is propagated from the master application root sales_app_root and installed in the application root replica app_root_rr.
  • Create an application PDB south_app_pdb for the regional south zone office in the the application root replica app_root_rr.
  • Synchronize application PDB south_app_pdb with the application sales_app in the application root replica app_root_rr so that it can access common application objects.
  • Insert rows into the metadata-linked common application table sales_app_user.customers in the south_app_pdb application PDB.
  • Issue a query containing the CONTAINERS clause against the metadata-linked common application table sales_app_user.customers in the master application root sales_app_root and note that the query aggregates data from the application PDBs north_app_pdbeast_app_pdb , west_app_pdb, and south_app_pdb across CDBs orclcdb and rmtcdb.
  • Upgrade the application sales_app in the master application root sales_app_root by adding another column to the metadata-linked table customers
  • Synchronize the proxy PDB px_app_root_rr with the master application root sales_app_root so that changes to the application sales_app are propagated from the master application root sales_app_root to the application root replica app_root_rr.
  • Synchronize the application PDB south_app_pdb with the application sales_app in the application root replica app_root_rr so that it can access the upgraded application sales_app 

Demonstration

  • Connect to CDB orclcdb and note that there is one application container root sales_app_root in this CDB.
SQL> conn sys/oracle@orclcdb as sysdba
     set sqlprompt ORCLCDB$ROOT>
 
ORCLCDB$ROOT>sho parameter db_name
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
db_name                              string      orclcdb
ORCLCDB$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
 
1 row selected.
  • Connect to the application root sales_app_root and note that there are three application PDBs (north_app_pdb, east_app_pdb, west_app_pdb) associated with it. Also, besides an implicit application, another application sales_app is currently installed in this container.
ORCLCDB$ROOT>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
---------- -------------------- ---------- -------- -------- --------
         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
 
4 rows selected.
 
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
-- All the three application PDBs are synced with application sales_app version 1.0
SALES_APP_ROOT>@get_sales_app_pdb_status
 
NAME                 APP_NAME             APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
EAST_APP_PDB         SALES_APP            1.0         NORMAL
WEST_APP_PDB         SALES_APP            1.0         NORMAL
NORTH_APP_PDB        SALES_APP            1.0         NORMAL

-- There is one common application object associated with the application sales_app; i.e., 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

-- Currently, there is one customer record in each application PDB

SALES_APP_ROOT> select * from containers (sales_app_user.customers);
 
   CUST_ID CUST_NAME            CUST_ADD               CUST_ZIP     CON_ID
---------- -------------------- -------------------- ---------- ----------
       105 Cust1(East)          USA(East) address            25          5
       106 Cust1(West)          USA(West) address            26          6
       106 Cust1(North)         USA(North) address           27          7
  • Connect to CDB rmtcdb and check that currently it has only a CDB seed PDB; i.e., PDB$SEED
[oracle@host01 trace]$ sqlplus sys/oracle@host01:1522/rmtcdb as sysdba
 
SQL> set sqlprompt RMTCDB$ROOT>
 
RMTCDB$ROOT>sho parameter db_name
 
NAME                         TYPE   VALUE
------------------------------------ ----------- ----------------------------
db_name                      string rmtcdb
 
RMTCDB$ROOT>sho pdbs
 
    CON_ID CON_NAME                   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
       2 PDB$SEED                     READ ONLY  NO
  • Create and open an empty application container with the application root  app_root_rr in the CDB rmtcdb.
RMTCDB$ROOT>ho mkdir -p /u01/app/oracle/oradata/rmtcdb/app_root_rr
RMTCDB$ROOT>alter session set db_create_file_dest='/u01/app/oracle/oradata/rmtcdb/app_root_rr';
Session altered.
RMTCDB$ROOT>create pluggable database app_root_rr AS APPLICATION CONTAINER admin user app_root_rr_admin identified by oracle;
Pluggable database created.

-- The application container root app_root_rr has been created in CDB rmtcdb

RMTCDB$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 APP_ROOT_RR       MOUNTED    YES      NO     NO
 
RMTCDB$ROOT>ALTER PLUGGABLE DATABASE app_root_rr OPEN;
 
             sho pdbs
 
    CON_ID CON_NAME                   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
       2 PDB$SEED                     READ ONLY      NO
       3 APP_ROOT_RR                  READ WRITE     NO
  • Connect to the newly created application root app_root_rr and check that at present it can’t access the common application object sales_app_user.customers
RMTCDB$ROOT>conn sys/oracle@host01:1522/app_root_rr as sysdba
            set sqlprompt APP_ROOT_RR>
 
APP_ROOT_RR>select * from sales_app_user.customers;
select * from sales_app_user.customers
                             *
ERROR at line 1:
ORA-00942: table or view does not exist
  • Create a proxy PDB named px_app_root_rr in the master application root sales_app_root that references the application root replica app_root_rr in the CDB rmtcdb. The proxy PDB provides a context to execute SQL statements and perform operations in the proxied app_root_rr.
SALES_APP_ROOT>ho mkdir -p /u02/app/oracle/oradata/orclcdb/app_root/px_app_root_rr

-- To create a proxy PDB, create a database link rmtcdb_link in the master application root sales_app_root pointing to a common user in the root container of the referenced CDB rmtcdb .

SALES_APP_ROOT>create public database link rmtcdb_link connect to system identified by oracle using 'host01:1522/rmtcdb';    
 
Database link created.

-- Create a proxy PDB px_app_root_rr in the master application root sales_app_root by executing CREATE PLUGGABLE DATABASE with the AS PROXY FROM clause, where FROM specifies the referenced PDB name (app_root_rr) and the database link (rmtcdb_link).

SALES_APP_ROOT>ALTER SESSION SET
db_create_file_dest='/u02/app/oracle/oradata/orclcdb/app_root/px_app_root_rr';
     
Session altered.
 
SALES_APP_ROOT>CREATE PLUGGABLE DATABASE px_app_root_rr AS PROXY
       FROM app_root_rr@rmtcdb_link;   
 
Pluggable database created.
 
SALES_APP_ROOT>sho pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PX_APP_ROOT_RR                 MOUNTED
         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
 
SALES_APP_ROOT>SELECT name, proxy_pdb
               FROM   v$pdbs;
 
NAME                           PROXY_PDB
------------------------------ ----------
PX_APP_ROOT_RR                 YES
EAST_APP_PDB                   NO
WEST_APP_PDB                   NO
NORTH_APP_PDB                  NO
SALES_APP_ROOT                 NO

5 rows selected.

-- The columns FOREIGN_CDB_DBID and FOREIGN_PDB_ID in CDB_PDBS display the DBID of the remote CDB (rmtcdb) and the CON_ID of the proxied PDB app_root_rr.

SALES_APP_ROOT>SELECT pdb_name, con_id, is_proxy_pdb, foreign_cdb_dbid,
                      foreign_pdb_id
               FROM cdb_pdbs; 
 
PDB_NAME                CON_ID IS_PROXY_PDB    FOREIGN_CDB_DBID FOREIGN_PDB_ID
------------------- ---------- --------------- ---------------- --------------
PX_APP_ROOT_RR                3 YES                  3974641045              3
EAST_APP_PDB                  5 NO                   2713315233              3
WEST_APP_PDB                  6 NO                   2713315233              3
NORTH_APP_PDB                 7 NO                   2713315233              3
SALES_APP_ROOT               15 NO                   2713315233              2
 
5 rows selected.
 
SALES_APP_ROOT>SELECT dbid FOREIGN_CDB_DBID FROM v$database@rmtcdb_link;
 
FOREIGN_CDB_DBID
----------------
      3974641045
 
1 row selected.
 
SALES_APP_ROOT>SELECT pdb_name, con_id FOREIGN_PDB_ID FROM cdb_pdbs@rmtcdb_link where pdb_name =
'APP_ROOT_RR';    
 
PDB_NAME             FOREIGN_PDB_ID
-------------------- --------------
APP_ROOT_RR                       3
 
1 row selected.
-- Open proxy PDB px_app_root_rr
SALES_APP_ROOT>ALTER PLUGGABLE DATABASE px_app_root_rr OPEN;
 
       sho pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PX_APP_ROOT_RR                 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
  • Synchronize the proxy PDB px_app_root_rr with the master application root sales_app_root so that the application sales_app is propagated from the master application root sales_app_root and installed in the application root replica app_root_rr.
SALES_APP_ROOT>conn sys/oracle@host01:1522/PX_APP_ROOT_RR as sysdba
               set sqlprompt PX_APP_ROOT_RR>
 
PX_APP_ROOT_RR>alter pluggable database application sales_app sync;
 
Pluggable database altered.
 
SALES_APP_ROOT>@get_sales_app_pdb_status
 
NAME             APP_NAME             APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
EAST_APP_PDB           SALES_APP            1.0       NORMAL
WEST_APP_PDB           SALES_APP            1.0       NORMAL
NORTH_APP_PDB          SALES_APP            1.0       NORMAL
PX_APP_ROOT_RR         SALES_APP            1.0       NORMAL

-- Check that, as a result of the above synchronization, the application sales_app has been installed in the referenced application root app_root_rr also and it can now access common application objects

APP_ROOT_RR>@get_app_status
 
APP_NAME                       APP_VERSION  APP_ID APP_STATUS  IMPLICIT
-------------------------------------- ------------ ------ ------------ ------
APP$5F08D9B5092E42D0E05364C909C03B6B   1.0            2 NORMAL      Y
SALES_APP                                 1.0         21 NORMAL      N
 
APP_ROOT_RR>@get_sales_app_objects
 
APP_NAME   OWNER     OBJECT_NAME       OBJECT_TYPE       SHARING           APPLICATION
---------- --------------- --------------- --------------- ------------------ ------------
SALES_APP  SALES_APP_USER  CUSTOMERS         TABLE       METADATA LINK      Y
 
APP_ROOT_RR>select * from sales_app_user.customers;
 
no rows selected
  • Create and open an application PDB south_app_pdb for the regional south zone office in the application root replica app_root_rr.
APP_ROOT_RR>ho mkdir -p /u02/app/oracle/oradata/rmtcdb/app_root_rr/south_app_pdb
 
APP_ROOT_RR>alter session set db_create_file_dest =
'/u02/app/oracle/oradata/rmtcdb/app_root_rr/south_app_pdb';
 
Session altered.
 
APP_ROOT_RR>CREATE PLUGGABLE DATABASE south_app_pdb
         ADMIN USER south_app_pdb_admin IDENTIFIED BY Password;  
 
Pluggable database created.
 
APP_ROOT_RR>sho pdbs
 
    CON_ID CON_NAME                   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
       3 APP_ROOT_RR                  READ WRITE NO
       4 SOUTH_APP_PDB                MOUNTED
 
APP_ROOT_RR>@get_app_containers
 
    CON_ID NAME                 OPEN_MODE  APP_ROOT APP_PDB  APP_SEED
---------- -------------------- ---------- -------- -------- --------
       3 APP_ROOT_RR            READ WRITE YES      NO           NO
       4 SOUTH_APP_PDB          MOUNTED    NO       YES          NO
 
APP_ROOT_RR>alter pluggable database south_app_pdb open;
 
Pluggable database altered.
 
APP_ROOT_RR>@get_app_containers


    CON_ID NAME         OPEN_MODE  APP_ROOT APP_PDB  APP_SEED
---------- -------------------- ---------- -------- -------- --------
       3 APP_ROOT_RR          READ WRITE YES        NO           NO
       4 SOUTH_APP_PDB  READ WRITE NO         YES      NO

-- Currently the application PDB south_app_pdb is not in sync with the application sales_app and hence can’t access the common application object sales_app_user.customers

APP_ROOT_RR>@get_sales_app_pdb_status
 
no rows selected
 
SOUTH_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
  • Synchronize the application PDB south_app_pdb with the application sales_app in the application root replica app_root_rr so that it can access common application objects.
SOUTH_APP_PDB>alter pluggable database application sales_app sync;
 
Pluggable database altered.
 
APP_ROOT_RR>@get_sales_app_pdb_status
 
NAME             APP_NAME             APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
SOUTH_APP_PDB          SALES_APP            1.0       NORMAL
 
SOUTH_APP_PDB>desc sales_app_user.customers
 Name                            Null?    Type
 ----------------------------------------- -------- --------------------------
 CUST_ID                         NOT NULL NUMBER
 CUST_NAME                              VARCHAR2(30)
 CUST_ADD                               VARCHAR2(30)
 CUST_ZIP                               NUMBER
 
SOUTH_APP_PDB>select * from sales_app_user.customers;
 
no rows selected
  • Insert a row into the metadata-linked common application table sales_app_user.customers in the south_app_pdb application PDB.  Note that inserted records are not visible until committed.
APP_ROOT_RR>insert into containers(sales_app_user.customers) (con_id,cust_id,cust_name,cust_add,
cust_zip) values (4, 104, 'South_Cust_1', 'South_Cust_1_address', 24);
   
1 row created.
 
APP_ROOT_RR>commit;
 
Commit complete.
 
APP_ROOT_RR>select * from containers(sales_app_user.customers);
 
   CUST_ID CUST_NAME                  CUST_ADD                 CUST_ZIP CON_ID
---------- ------------------------------ ------------------------------ ---------- ----------
       104 South_Cust_1               South_Cust_1_address                  24        4
  • Issue a query containing  the CONTAINERS clause against the metadata-linked common application table sales_app_user.customers in the master application root sales_app_root and note that the query aggregates data from the application PDBs north_app_pdbeast_app_pdb , west_app_pdb, and south_app_pdb across CDBs orclcdb and rmtcdb.
SALES_APP_ROOT> select * from containers(sales_app_user.customers);
 
   CUST_ID CUST_NAME    CUST_ADD                  CUST_ZIP      CON_ID
---------- ------------- ---------------------- ---------- --------
       104 South_Cust_1  South_Cust_1_address         24            4
       105 Cust1(East)  USA(East) address             25            5
       106 Cust1(West)  USA(West) address             26            6
       106 Cust1(North)  USA(North) address           27            7
  • Upgrade the application sales_app in the master application root sales_app_root by adding another column to the metadata-linked table customers.
SALES_APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION sales_app BEGIN UPGRADE '1.0' TO '2.0';
 
         alter table sales_app_user.customers add (remarks varchar2(30));
        
         ALTER PLUGGABLE DATABASE APPLICATION sales_app END UPGRADE TO '2.0';
 
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
 REMARKS                  VARCHAR2(30)
 
SALES_APP_ROOT>@get_sales_app_status
 
APP_NAME                        APP_VERSION  APP_ID APP_STATUS   IMPLICIT
------------------------------- ------------ ------ ------------ ------
SALES_APP                       2.0             21 NORMAL            N
-- Currently none of the PDB's (in sales_app_root  and  app_root_rr) is in sync with the application sales_app version 2.0
SALES_APP_ROOT>@get_sales_app_pdb_status

NAME             APP_NAME             APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
EAST_APP_PDB           SALES_APP            1.0       NORMAL
WEST_APP_PDB           SALES_APP            1.0       NORMAL
NORTH_APP_PDB          SALES_APP            1.0       NORMAL
PX_APP_ROOT_RR         SALES_APP            1.0       NORMAL
 
APP_ROOT_RR>@get_sales_app_pdb_status
 
NAME             APP_NAME             APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
SOUTH_APP_PDB          SALES_APP            1.0       NORMAL
  • Synchronize the proxy PDB px_app_root_rr with the master application root sales_app_root  so that changes to the application sales_app are propagated from master application root sales_app_root  to the application root replica app_root_rr.
PX_APP_ROOT_RR>alter pluggable database application sales_app sync;
 
Pluggable database altered.
 
SALES_APP_ROOT>@get_sales_app_pdb_status
 
NAME             APP_NAME             APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
EAST_APP_PDB           SALES_APP            1.0       NORMAL
WEST_APP_PDB           SALES_APP            1.0       NORMAL
NORTH_APP_PDB          SALES_APP            1.0       NORMAL
PX_APP_ROOT_RR         SALES_APP            2.0       NORMAL

-- As soon as proxy PDB px_app_rr is synchronized with application sales_app, referenced PDB app_root_rr also  gets synchronized

APP_ROOT_RR>@get_sales_app_status
 
APP_NAME                       APP_VERSION  APP_ID APP_STATUS  IMPLICIT
-------------------------------------- ------------ ------ ------------ ------
SALES_APP                      2.0        21 NORMAL   N

-- However, application PDB south_app_pdb is not yet in sync with new version 2.0 of application sales_app  

APP_ROOT_RR>@get_sales_app_pdb_status

 
NAME             APP_NAME             APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
SOUTH_APP_PDB          SALES_APP            1.0       NORMAL 
  • Synchronize application PDB south_app_pdb with  the application sales_app in the application root replica app_root_rr so that  it can access upgraded application  sales_app 
SOUTH_APP_PDB>alter pluggable database application sales_app sync;
Pluggable database altered.
 
APP_ROOT_RR>@get_sales_app_pdb_status
 
NAME             APP_NAME             APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
SOUTH_APP_PDB          SALES_APP            2.0      NORMAL
 
SOUTH_APP_PDB>desc sales_app_user.customers
 Name                            Null?    Type
 ----------------------------------------- -------- --------------------------
 CUST_ID                         NOT NULL NUMBER
 CUST_NAME                              VARCHAR2(30)
 CUST_ADD                               VARCHAR2(30)
 CUST_ZIP                               NUMBER
REMARKS                                VARCHAR2(30)

 Hence, we have been able to leverage proxy PDB to

  • Propagate the application sales_app in the application root sales_app_root in CDB orclcdb to the application PDB south_app_pdb in the application root app_root_rr in CDB rmtcdb
  • Synchronize changes in the application sales_app across CDBs orclcdb and rmtcdb so that any changes made to the application in the application container sales_app_root are propagated to south_app_pdb
  • Spread data of the application sales_app across CDBs such that a query issued in the master application root sales_app_root can aggregate data from application PDBs across orclcdb and rmtcdb

Summary

  • A proxy PDB provides a local connection point that references a remote PDB.
  • A proxy PDB can be used with sn application container to propagate the application definition to the application root in a different CDB. As a result, the referenced application root becomes a replica of the master application root and hence is termed an ‘application root replica’.
  • When any changes are made to the application in the master application root, the proxy PDB can keep the application synchronized across application containers in various CDBs.
    • After synchronization of an application root replica, the application PDBs plugged into it can also synchronize with it to get the application changes.
    • Using proxy PDBs with application containers, location-transparent applications can be built that can aggregate data from multiple sources, which can be in the same data center or distributed across data centers.

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;
 
get_sales_app_status.sql

-- Find out the status of sales_app application in application container when executed from application root

         select app_name, app_version, app_id, app_status,
                app_implicit implicit
         from dba_applications
         where 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/