Feb 12, 2018 11:02:26 AM by Anju Garg
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.
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.
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 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.
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.
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
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)
[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
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.
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)
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
APP_ROOT_RR>@get_sales_app_pdb_status
NAME APP_NAME APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
SOUTH_APP_PDB SALES_APP 2.0 NORMAL
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.
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
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
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.
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 :
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/
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.