Introduction

As we saw in my previous article, if application containers in multiple CDBs need to have the same application definition (for example, same tables and PL/SQL packages), then the application can be kept synchronized across CDBs by the use of proxy PDBs.  We also saw that using proxy PDBs with application containers, a query using the CONTAINERS clause that is issued from the master application root can aggregate application data across CDBs. As a result, we can build location-transparent applications which can aggregate data from multiple sources that can be in the same data center or distributed across data centers. In this article, we will further explore the use of proxy PDBs with application containers to load balance an application across CDBs.

Application Load Balancing Using Proxy PDBs

In order to keep an application synchronized across CDBs, a proxy PDB can be created in the master application root for every application root replica in another CDB.  Consequently, application PDBs plugged into the master application root and various application root replicas can be synchronized so that they can access the latest version of the application while storing PDB-specific data in one or more metadata-linked tables. This leads to the possibility of load balancing an application across CDBs by distributing the application PDB(‘s) across synchronized application containers.  In this article, I will demonstrate that when an application is load balanced across CDBs by relocating application PDB(‘s) across synchronized application containers the application code to aggregate application data across CDBs need not be changed at all.

Current scenario

We have an Oracle database 12.2.0.1 CDB called orclcdb as shown below. Within this CDB, besides the CDB Seed PDB pdb$seed, we have an application container with the master 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.  We also have another 12.2.0.1 CDB called rmtcdb having the application root app_root_rr, which is a replica of the master application root sales_app_root and is kept synchronized with it  by means of a proxy PDB px_app_root_rr . The application PDB south_app_pdb for the south zone regional office is plugged into the application root replica app_root_rr. Any changes made to the application in the master application root sales_app_root are propagated to south_app_pdb when it synchronizes with app_root_rr.

While sharing the structure of the metadata-linked customers table stored in the master application root sales_app_root, each application PDB can store region specific customer data in the table.

 

Fig. 1

At present, there are three application PDBs in the CDB orclcdb and one application PDB in the CDB rmtcdb. All four application PDBs are synchronized. Now, in order to load balance the application sales_app across CDBs orclcdb and rmtcdb, we will relocate one of the application PDBs in CDB orclcdb; say, north_app_pdb, to rmtcdb so that the configuration becomes as shown below.

Fig. 2

Subsequently, when a query containing the CONTAINERS clause is issued in the master application root sales_app_root, it is executed recursively in each application PDB.

  • The query executes in the non-proxy open application PDBs (east_app_pdb and west_app_pdb) in the current application container (sales_app_root) and returns results to the master application root.
  • The query, when propagated to proxy PDB px_app_root_rr in the current application container (sales_app_root), is sent to the referenced application root replica app_root_rr in CDB rmtcdb, where it is executed in the open application PDBs  (north_app_pdb and south_app_pdb) plugged into app_root_rr. The results are returned to the master application root.

The results received from all the open application PDBs (east_app_pdb , west_app_pdb, north_app_pdb and south_app_pdb) across CDBs orclcdb and rmtcdb are aggregated in the master application root.

Thus, even after relocation of the application PDB north_app_pdb from CDB orclcdb to rmtcdb, the query with the CONTAINERS clause, without any changes at all, can still aggregate application data from all the application PDBs across CDBs.

Now, I will demonstrate application load balancing. The current setup corresponds to the configuration depicted in Fig. 1, above.

Demonstration

  • Connect to CDB orclcdb and note that there is one application container 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 application root sales_app_root and note that there are 3 application PDBs (north_app_pdb, east_app_pdb, west_app_pdb) and a proxy application PDB (px_app_root_rr) associated with it. Also, besides an implicit application, another application sales_app is currently installed in this container. All three application PDBs and the proxy PDB are synced with sales_app version 2.0.

 

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

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

       3 PX_APP_ROOT_RR 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

 

 

4 rows selected.

 

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

 

SALES_APP_ROOT>@get_app_status

 

APP_NAME                       APP_VERSION  APP_ID APP_STATUS  IMPLICIT

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

APP$5DED1EE7F9C418C7E05364C909C0F9BD   1.0            2 NORMAL      Y

SALES_APP                     2.0        21 NORMAL   N

 

SALES_APP_ROOT>@get_sales_app_pdb_status

 

 

NAME             APP_NAME             APP_VERSION APP_STATUS

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

WEST_APP_PDB           SALES_APP            2.0       NORMAL

NORTH_APP_PDB          SALES_APP            2.0       NORMAL

EAST_APP_PDB           SALES_APP            2.0       NORMAL

PX_APP_ROOT_RR         SALES_APP            2.0       NORMAL

 

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

 REMARKS                            VARCHAR2(30)

 

  • Connect to CDB rmtcdb and note that there is one application container root app_root_rr in this CDB.

[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 con_name

 

CON_NAME

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

CDB$ROOT

 

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

 

  • Note that by querying CDB_PDBS in sales_app_root, we learn that app_root_rr is the PDB referenced by the proxy PDB px_app_rr in the CDB orclcdb.  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, respectively.

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.

 

  • Connect to application root app_root_rr and note that the application sales_app version 2.0 has been replicated in app_root_rr from the master application root sales_app_root by means of the proxy PDB px_app_root_rr. In other words, app_root_rr is a replica of the master application root sales_app_root.

RMTCDB$ROOT>conn sys/oracle@host01:1522/app_root_rr as sysdba

            set sqlprompt APP_ROOT_RR>

 

APP_ROOT_RR>@get_app_status

 

APP_NAME                       APP_VERSION  APP_ID APP_STATUS  IMPLICIT

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

APP$5F08D9B5092E42D0E05364C909C03B6B   1.0            2 NORMAL      Y

SALES_APP                      2.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>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)

 

  • Note that there is one application PDB (south_app_pdb) associated with the application root replica app_root_rr.

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

 

  • The application PDB south_app_pdb is synced with sales_app version 2.0.

APP_ROOT_RR>@get_sales_app_pdb_status

 

NAME             APP_NAME             APP_VERSION APP_STATUS

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

SOUTH_APP_PDB          SALES_APP            2.0       NORMAL

 

  • Let us view customer records in the application shared table sales_app_user.customers stored in application PDBs across orclcdb and rmtcdb. Note that there is one customer record in each of the four application PDBs.

SALES_APP_ROOT>select CUST_ID, CUST_NAME,CUST_ADD, CUST_ZIP,con$name

               from containers(sales_app_user.customers);

 

   CUST_ID CUST_NAME       CUST_ADD           CUST_ZIP CON$NAME

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

       104 South_Cust_1    South_Cust_1_address         24 SOUTH_APP_PDB

       106 Cust1(North)    USA(North) address           27 NORTH_APP_PDB

       105 Cust1(East)     USA(East) address            25 EAST_APP_PDB

       106 Cust1(West)     USA(West) address            26 WEST_APP_PDB

 

At present, there are three application PDBs in the CDB orclcdb and one application PDB in the CDB rmtcdb. Now, in order to load balance the application sales_app across CDBs orclcdb and rmtcdb, we will relocate the application PDB north_app_pdb from the CDB orclcdb to rmtcdb, so that the configuration becomes as shown in Fig 2, above. For relocation, we will leverage the Oracle Database 12.2 feature “Near-zero downtime PDB relocation”  so that while connected to the target CDB rmtcdb, a single DDL statement will relocate the application PDB north_app_pdb by pulling it from the source CDB orclcdb .

-- Create a database link from application root app_root_rr to source CDB orclcdb.

APP_ROOT_RR>CREATE PUBLIC DATABASE LINK orclcdb_link

            CONNECT TO system IDENTIFIED BY oracle

            USING 'orclcdb';

-- In the source CDB orclcdb, grant the SYSOPER privilege to the user (system) defined in the DB link

ORCLCDB$ROOT>GRANT sysoper TO system CONTAINER=all;

-- Create a folder on the target server to hold the files of the relocated application PDB

APP_ROOT_RR> ho mkdir -p /u02/app/oracle/oradata/rmtcdb/app_root_rr/north_app_pdb

-- While connected to application root app_root_rr in the target CDB rmtcdb, relocate the application PDB north_app_pdb from CDB orclcdb to rmtcdb via database link orclcdb_link.

APP_ROOT_RR>ALTER session SET

db_create_file_dest='/u02/app/oracle/oradata/rmtcdb/app_root_rr/north_app_pdb';

APP_ROOT_RR>CREATE PLUGGABLE DATABASE north_app_pdb FROM north_app_pdb@orclcdb_link RELOCATE;

 

Pluggable database created.

  • Note that the status of the target application PDB north_app_pdb in the target application root replica app_root_rr is ‘RELOCATING’ and that the source application PDB north_app_pdb is still visible in application root sales_app_root in the source CDB orclcdb.

APP_ROOT_RR>select pdb_id, pdb_name, status from cdb_pdbs;

 

    PDB_ID PDB_NAME                   STATUS

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

       3 APP_ROOT_RR                  NORMAL

       4 SOUTH_APP_PDB          NORMAL

       6 NORTH_APP_PDB          RELOCATING

 

SALES_APP_ROOT>select pdb_id, pdb_name, status from cdb_pdbs;

 

   PDB_ID PDB_NAME                    STATUS

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

       3 PX_APP_ROOT_RR         NORMAL

       5 EAST_APP_PDB           NORMAL

       6 WEST_APP_PDB           NORMAL

       7 NORTH_APP_PDB          NORMAL         

      15 SALES_APP_ROOT         NORMAL

 

  • When the newly created PDB is opened in read-write mode for the first time, the source PDB is automatically closed and dropped, and the relocation operation is completed, with the relocated PDB being fully available

APP_ROOT_RR>alter pluggable database north_app_pdb open;

 

Pluggable database altered.

 

APP_ROOT_RR>select pdb_id, pdb_name, status from cdb_pdbs;

 

    PDB_ID PDB_NAME                   STATUS

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

       3 APP_ROOT_RR                  NORMAL

       4 SOUTH_APP_PDB          NORMAL

       6 NORTH_APP_PDB          NORMAL

 

SALES_APP_ROOT>select pdb_id, pdb_name, status from cdb_pdbs;

 

    PDB_ID PDB_NAME                   STATUS

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

       3 PX_APP_ROOT_RR         NORMAL

       5 EAST_APP_PDB           NORMAL

       6 WEST_APP_PDB           NORMAL

      15 SALES_APP_ROOT         NORMAL

  • Even after relocation of application PDB north_app_pdb from orclcdb to rmtcdb, the earlier CONTAINERS query still aggregates application data from all the application PDBs across orclcdb and rmtcdb.

 

SALES_APP_ROOT>select CUST_ID, CUST_NAME,CUST_ADD, CUST_ZIP,con$name

               from containers(sales_app_user.customers);

 

   CUST_ID CUST_NAME       CUST_ADD           CUST_ZIP CON$NAME

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

       104 South_Cust_1    South_Cust_1_address         24 SOUTH_APP_PDB

       106 Cust1(North)    USA(North) address           27 NORTH_APP_PDB

       105 Cust1(East)     USA(East) address            25 EAST_APP_PDB

       106 Cust1(West)     USA(West) address            26 WEST_APP_PDB

 

Hence, we have been able to leverage a proxy PDB to load balance the application sales_app across CDBs orclcdb and rmtcdb without the need to modify the application code to aggregate application data across CDBs.

Summary

  • Using proxy PDBs with application containers, location-transparent applications can be built which can aggregate data from multiple sources in the same data center or distributed across data centers.
  • In order to keep an application synchronized across CDBs, a proxy PDB has to be created in the master application root for every application root replica in another CDB. 
  • The application PDBs plugged into the master application root and various synchronized application root replicas can be synchronized, so that they can access the latest version of the application while storing PDB-specific data in one or more metadata-linked tables.
  • An application can be load balanced across CDBs by relocating one or more application PDBs across synchronized application containers, without any need to modify the application code to aggregate application data across CDBs.

 

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;

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