Toad World Blog
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_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 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
- https://docs.oracle.com/database/122/CNCPT/overview-of-the-multitenant-architecture.htm#GUID-893AB508-9113-405E-BE45-E73CC76994C3
- https://docs.oracle.com/database/122/ADMIN/creating-and-removing-pdbs-with-sql-plus.htm#ADMIN-GUID-7C23D560-5AD5-4AF2-93B4-F4FDC6DE768C
- https://docs.oracle.com/database/122/ADMIN/administering-application-containers-with-sql-plus.htm#ADMIN-GUID-7D442C17-28E6-4337-8EF5-6930FBC0BCC8
- https://docs.oracle.com/database/122/CNCPT/introduction-to-the-multitenant-architecture.htm#GUID-53820A0C-E88A-40C0-AAE0-01A6371A146B
- https://docs.oracle.com/database/122/ADMIN/creating-and-removing-pdbs-with-sql-plus.htm#ADMIN-GUID-75519361-3DA2-4558-A7E5-64BC16FAFC7D
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;
-----------------------------
Tags: Oracle
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/