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 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. 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 CDBrmtcdb 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 rootsales_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 PDBsouth_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_pdb, east_app_pdb , west_app_pdb, andsouth_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 rootsales_app_root so thatchanges 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 rootsales_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 PDBsouth_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 objectsales_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_pdb, east_app_pdb , west_app_pdb, andsouth_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 rootsales_app_root so thatchanges 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
- https://docs.oracle.com/database/122/CNCPT/overview-of-the-multitenant-architecture.htm#CNCPT89248
- 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
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';
—————————–
Start the discussion at forums.toadworld.com