Introduction:
In my previous articles we have seen concepts like “Application Containers” and “Proxy PDB”, which are new in Oracle Database 12cR2. With Application Containers, you can install applications in an Application Root and synchronize the application (metadata without data) to Application PDBs.On the other hand, a Proxy PDB provides location transparency; this is useful when we want to access data or objects remotely from another Container Database (CDB). An advantage of a Proxy PDB is that we don’t have to copy all the data to the remote CDB in order to access the objects and its data, however this is also a disadvantage.
If something goes wrong with the Application Root in the Master Application Container, all the remote Proxy PDBs in others CDB will be broken. To avoid this, we would probably want to have a physical replica of all the objects and data in another remote Container Database. Here is where a new feature called “Application Root Replica”, also introduced in 12.2.0.1.0, is helpful.
Application Root Replica is a physical replica of a master Application Root but in another remote Container Database. This lets us synchronize applications in an Application Container across different and remote Container Databases without using solutions like RMAN, Data Pump, or remote cloning.
There are two methods to create an Application Root Replica:
- Create an empty application container and then synchronize the application.
- Clone the master application root.
In this article, I will show you a use-case example.
Preparation of the Environment
With these steps I will create the environment described in the following image. I already have the two Container Databases, CDB1 and CDB2. So I will start by creating the Application Root “AppRoot” and the Application PDB “AppPDB1” in CDB1. I will create an application in “AppRoot” and I will sync that application to “AppPDB1”. Then I will create the Application Root “AppRoot2” and the Application PDB “AppPDB2” in CDB2.
Creating an Application Root named “AppRoot”
SQL> create pluggable database AppRoot as application container admin user pdbadmin identified by nuvola;
Pluggable database created.
SQL> alter pluggable database AppRoot open;
Pluggable database altered.
Creating the Application PDB named “AppPDB1”
SQL> alter session set container=AppRoot;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
APPROOT
SQL> create pluggable database AppPDB1 admin user pdbadmin identified by nuvola;
Pluggable database created.
SQL> alter pluggable database AppPDB1 open;
Pluggable database altered.
Installing the application named “MyApp” in the Application Root “AppRoot” in CDB1:
SQL> alter pluggable database application MyApp begin install '1.0';
Pluggable database altered.
SQL> create table c##dgomez.dataLinkedTable SHARING=DATA (name varchar2(20));
Table created.
SQL> insert into c##dgomez.dataLinkedTable values ('Guatemala');
1 row created.
SQL> commit;
Commit complete.
SQL> alter pluggable database application MyApp end install '1.0';
Pluggable database altered.
Synchronizing the Application PDB “AppPDB1”:
SQL> alter session set container=AppPDB1;
Session altered.
SQL> alter pluggable database application MyApp sync;
Pluggable database altered.
Confirming that the table and data were synchronized:
SQL> select * from c##dgomez.dataLinkedTable;
NAME
--------------------
Guatemala
In the Container Database “CDB2” I will create the Application Root named “AppRoot2”
SQL> create pluggable database AppRoot2 as application container admin user pdbadmin identified by nuvola;
Pluggable database created.
SQL> alter pluggable database AppRoot2 open;
Pluggable database altered.
Creating the Application PDB “AppPDB2” in CDB2:
SQL> alter session set container=AppRoot2;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
APPROOT2
SQL> create pluggable database AppPDB2 admin user pdbadmin identified by nuvola;
Pluggable database created.
SQL> alter pluggable database AppPDB2 open;
Pluggable database altered.
Confirming that the table c##dgomez.dataLinkedTable doesn’t exist in “AppPDB2”. This is just to confirm that the environment we have created matches with the previous image.
SQL> alter session set container=AppPDB2;
Session altered.
SQL> select * from c##dgomez.dataLinkedTable;
select * from c##dgomez.dataLinkedTable
*
ERROR at line 1:
ORA-00942: table or view does not exist
The problem:
At this time we have two CDBs, one called CDB1, which has an Application Container created with one application installed. However, I also want to have that Application in CDB2 in the Application Container that has already been created there. Also I would like to be able to synchronize all the data whenever the “master” Application receives any change. In the past, we would have used a Full Backup and Restore with RMAN or perhaps an export and import with Data Pump, or even a materialized view. In 12.1.0.2.0 we would use “Remote PDB Cloning”. However, all these solutions are not the best!
The solution:
The best solution to this problem is “Application Root Replica”. An Application Root Replica is a physical replica of one Application Root in another CDB. In this case our Master Application Root is “AppRoot” in CDB1, and the Application Root Replica is “AppRoot2” in CDB2. The Application Root Replica uses a Proxy PDB to synchronize the data with the Master Application Root. In the following image you can see that the Proxy PDB is created in the CDB1, this is because the Proxy PDB will be seen as a normal PDB in the Application Container in CDB1, which means that the Proxy PDB will get the data (via synchronization) from the Master Application Root. Since the “Referenced PDB” of that Proxy PDB is “AppRoot2”, it is as if “AppRoot2” was physically located in CDB1. This is the concept of a Proxy PDB, and this is how “AppRoot2” can get all the data from “AppRoot2”. Once the Application Root “AppRoot2” get synchronized with the Application Root “AppRoot” through the Proxy PDB, we will have to synchronize the Application PDB “AppPDB2” in CDB2.
In the Application Root “AppRoot” in CDB1:
SQL> alter session set container=AppRoot;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
APPROOT
Since the Proxy PDB needs a database link to be created:
SQL> CREATE DATABASE LINK link_to_AppRoot CONNECT TO c##dgomez IDENTIFIED BY nuvola USING '192.168.1.22:1521/approot2';
Database link created.
Note that the database link connects to the Application Root “AppRoot2” in CDB2.
Creating the Proxy PDB in CDB1:
SQL> create pluggable database ProxyPDB AS PROXY FROM approot2@link_to_AppRoot;
Pluggable database created.
SQL> alter pluggable database ProxyPDB open;
Pluggable database altered.
Unfortunately Proxy PDB doesn’t support OS Authentication, so I have to open a session to “ProxyPDB” in CDB1 using password authentication:
[oracle@nuvola2 apex]$ sqlplus sys/manager1@'192.168.1.22:1521/ProxyPDB' as sysdba
The following step will synchronize the “Proxy PDB”, which automatically will fill up the “Application Root Replica” called “AppRoot2” in CDB2:
SQL> alter pluggable database application MyApp sync;
Pluggable database altered.
If we connect to the application root replica “AppRoot2” in CDB2 we will see that the application is there as well as its data, physically.
SQL> show con_name
CON_NAME
------------------------------
APPROOT2
SQL> select app_name, app_version from dba_app_versions where app_name='MYAPP'
APP_NAME APP_VERSION
-------------------- ------------------------------
MYAPP 1.0
So the application “MyApp” has been synchronized to the Application Root [Replica] “AppRoot2”. It’s time to synchronize all the Application PDBs in the Application Container in CDB2:
SQL> alter session set container=AppPDB2;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
APPPDB2
SQL> alter pluggable database application MyApp sync;
Pluggable database altered.
We can confirm that the Application “MyApp” was successfully replicated from AppRoot to ProxyPDB in CDB1, from ProxyPDB in CDB1 to AppRoot2 in CDB2, and from AppRoot2 to AppPDB2 in CDB:
SQL> select * from c##dgomez.dataLinkedTable;
NAME
--------------------
Guatemala
Starting now, we only have to keep performing “SYNC” operations to replicate the data through all of the configuration that involves both Container Databases.
Conclusion
We have seen through this article how to synchronize application data in an Application Container across Container Databases without using Backup and Recovery operations with RMAN, Export & Import with Data Pump or Remote PDB Cloning. When we are working with Application Containers, both Proxy PDB and Application Root Replica are useful for replicating our installed applications to other Containers Databases.
Start the discussion at forums.toadworld.com