Oracle Database 12cR2 new feature: Application Root Replica

    May 24, 2017 9:01:00 AM by Deiby Gomez

    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:

    1. Create an empty application container and then synchronize the application.
    2. 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.

    Tags: Oracle

    Deiby Gomez

    Written by Deiby Gomez

    Deiby Gómez is the youngest Oracle ACE and Oracle ACE Director in the world and the first Guatemalan with these awards. Deiby is the youngest Latin American with the highest certifications “Oracle Certified Master 11g” and “Oracle Certified Master 12c”. He received In Vegas, United States the "SELECT Journal Editor’s Choice Award 2016", he became the first Guatemalan with that award. He is a frequent speaker in several Oracle Events around the world like “Technology Network Latin American Tour 2013, 2014, 2015 and 2016 in several countries like Guatemala, Costa Rica, Nicaragua, El Salvador, Uruguay, Argentina, Mexico, Brazil, Ecuador, Colombia, Peru; Collaborate in Vegas, USA; Latin American Oracle Open World in Brazil (2015 and 2016) and Oracle Open World in San Francisco, USA (2015 and 2016). He is the first Guatemalan who was accepted by Oracle Corporation as “Beta Tester” for the version “12cR2” in 2015. He is the official Technical Reviewer of the Book “Oracle Database 12c Release 2 Multitenant (1st Edition, McGraw-Hill)" and Co-Author of the book "Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability", both can be found in Amazon. He is the first Guatemala who appeared as an outstanding expert in the official magazine of Oracle Corporation called “Oracle Magazine” in the Edition November/December in 2014, this magazine is delivered around the world in several countries. He has published several articles in the Official Website of Oracle (www.oracle.com) in Portuguese, Spanish and English. Currently he is President of Guatemalan Oracle Users Group (GOUG). Director of Support Quality en Latin American Oracle Users Group Community (LAOUC) for 2016-2017, founder of a very well known group in Oracle Community called “Oraworld Team” that has members from India, Guatemala, Brazil, France and Switzerland having in total 4 Oracle ACE Directors, 3 Oracle ACE, 5 Oracle Certified Masters (OCM) and 1 PhD. Deiby was part of Oracle ACE Hackaton in Amsterdam, Netherlands in April 2016 where he was building several solutions using Oracle Cloud Products. Currently Deiby Gómez is CEO in Nuvola Consulting Group, a company that provides excellence on Support and Consulting services with Oracle Technology like Databases, Middleware, Cloud and Engineered systems. Deiby is well known in the community because of his resilience, entrepreneurship and his availability to help and share his knowledge. Deiby loves to travel, to play chess and to enjoy a good cup of coffee with friends. Oracle ACE (at the age of 23) Oracle Certified Master 11g (at the age of 24) Oracle ACE Director (at the age of 25) Oracle Certified Master 12c (at the age 26) SELECT Journal Editor’s Choice Award 2016 Speaker in several Universities, OTN Tour 2013,2014,2015. Collaborate15, LA Oracle Open World. Technical Reviewer of the Book "Oracle Database 12c Release 2 Multitenant (Oracle Press) 1st Edition" Blogger. Oracle Certifications: Oracle Linux Certified Implementation Specialist. Oracle Database 11g Administrator Certified Professional. Oracle Database 11g Administrator Certified Master (OCM 11g) Oracle Database 12c Administrator Certified Master (OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Master (MAA OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Expert (MAA OCE 12c) Oracle Database 12c Administrator Certified Professional. Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert. Oracle Exadata Database Machine Administrator. Oracle RAC 11g and Grid Infraestructure Administrator Oracle RAC 12c and Grid Infraestructure Administrator Oracle Real Application Clusters 12c Certified Implementation Specialist Oracle Database 12c: Data Guard Administrator