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/