Oracle Multitenant Application Containers - Part VIII - Application Load Balancing Using Proxy PDBs

Introduction

As we saw in myprevious 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 rootsales_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_pdb, east_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 andwest_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 andsouth_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
       7NORTH_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 CDBrmtcdb 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_ROOTAPP_PDB      APP_SEED
---------- --------------- ---------- -------- -------- --------
       3 APP_ROOT_RR       READ WRITEYES      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 orclcdband one application PDB in the CDB rmtcdb. Now, in order to load balance the application sales_appacross CDBs orclcdband rmtcdb, we will relocate the application PDB north_app_pdbfrom the CDB orclcdbto 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_pdbby 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 CDBorclcdb.

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_appacross CDBs orclcdband rmtcdbwithout 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;

—————————–