Introduction
The need to communicate with external systems and exchange data made Oracle develop a way to connect to different Oracle databases to execute operations. Traditionally, whenever we wanted to bring data in from a different database, we used a Database Link. But 12.1.0.1.0 Oracle introduced a major new multi-tenant architecture. With Multitenant, a database could be either Container Database or non-Container Database. If we decided to create a new database as a Container (CDB) we could create Pluggable Databases connected to the CDB. However, DBAs still needed to use Database Links to exchange data between the pluggable databases within a Container.
In the newest version of Oracle Database 12.2.0.1.0 introduces a feature called “Proxy PDB”. A Proxy PDB is physically an empty PDB that has the minimum tablespaces required (SYSTEM, SYSAUX, UNDO), created in one CDB that references a remote Pluggable Database in a different CDB. All the operations (DDLs & DMLs) that are executed within the Proxy PDB are sent to the referenced Pluggable Database and remotely executed in it, except for the operations ALTER PLUGGABLE DATABASE and ALTER DATABASE. This is why it is called “Proxy”.
The benefit of a Proxy PDB is that it’s exactly as if the referenced PDB was in the local CDB, but the data is stored remotely and the operations are executed remotely in the referenced Pluggable Database. For instance, if we have Database Resource Manager active in the local CDB, the current Resource Manager Plan also applies to the Proxy PDB. Another example is the CONTAINERS clause, which allows retrieval of data from all the Pluggable Databases; this clause also works for a Proxy PDB. For all operations, the Proxy PDB will be seen as a normal PDB.
The image below sets up our example. It shows two containers, CDB1 and CDB2. The remote container is shown at the top of the illustration: CDB1. The local CDB is shown at the bottom of the illustration: CDB2. Each container has two pluggable databases within it, designated as PDB1 and PDB2. The PDB2 in the local container is a Proxy PDB that references the PDB2 within CDB1.
In the illustration we see a user connected to the CDB$ROOT of CDB2 who is executing a query using the CONTAINERS clause across all the PDBs that belong to CDB2. The data returned includes “Guatemala”, which is physically stored in the referenced PDB, that is, the PDB2 within CDB1. the PDB2. The row with the value “Guatemala” is returned because the query was sent to the Referenced PDB and executed there. (The referenced PDB can be either a normal PDB or an application PDB. In this example the referenced PDB is a normal PDB.)
To create a Proxy PDB there are some prerequisites:
- The CDB that contains the referenced PDB must be in local undo mode.
- The CDB that contains the referenced PDB must be in ARCHIVELOG mode.
- The referenced PDB must be in open read/write mode when the proxy PDB is created.
We will go through the example presented in the above image. First I will connect to CDB1 and create the PDB1 and PDB2 Pluggable Databases, and then I will jump to CDB2 to create its PDB1 and then the Proxy PDB called PDB2. Once everything is completed I will perform the query with the CONTAINERS clause from CDB2, my local container.
Preparation in CDB1:
I will create the PDB1 and PDB2 in CDB1:
SQL> create pluggable database pdb1 admin user pdbadmin identified by nuvola;
Pluggable database created.
SQL> create pluggable database pdb2 admin user pdbadmin identified by nuvola;
Pluggable database created.
Opening PDB1 and PDB2:
SQL> alter pluggable database all open;
Pluggable database altered.
One of the prerequisites is that the referenced PDB is in read/write; in this example both are in read/write:
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
---------- ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB2 READ WRITE
Another prerequisite is that the user that connects to the referenced PDB has to be a common user:
SQL> select username, common from dba_users where username='C##DGOMEZ';
USERNAME COM
---------- ---
C##DGOMEZ YES
Another prerequisite is that the remote CDB, in this case CDB1, has to be configured with Local Undo:
SQL>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';
PROPERTY_NAME PROPERTY_VALUE
-------------------- --------------------
LOCAL_UNDO_ENABLED TRUE
In the previous image, you can see that there is a table with 1 row inserted. I will load these rows into the PDB1 and the PDB2 in CDB1 to make this environment match with the image:
SQL> alter session set container=pdb1;
Session altered.
SQL> create table c##dgomez.country (name varchar2(25));
Table created.
SQL> insert into c##dgomez.country values ('Brazil');
1 row created.
SQL> commit;
Commit complete.
The PDB2 of CDB1 will be our referenced PDB. In the image you can see that the value in the referenced PDB is “Guatemala”:
SQL> alter session set container=pdb2;
Session altered.
SQL> create table c##dgomez.country (name varchar2(25));
Table created.
SQL> insert into c##dgomez.country values ('Guatemala');
1 row created.
SQL> commit;
Commit complete.
The work in CDB is done. Two PDBs were created, the table was created and the rows were inserted. Now it’s time to configure CDB2 and create the Proxy PDB.
Preparation in CDB2:
We will start from the CDB$ROOT:
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
I will create a common user in order to perform the example with the CONTAINERS clause.
SQL> create user c##dgomez identified by nuvola container=all;
User created.
SQL> grant connect, resource, unlimited tablespace to c##dgomez container=all;
Grant succeeded.
I will create the same table in CDB$ROOT in CDB2 and insert a row in order to follow the example in the image:
SQL> create table c##dgomez.country (name varchar2(25));
Table created.
SQL> insert into c##dgomez.country values ('USA');
1 row created.
SQL> commit;
Commit complete.
Creating the PDB1 in CDB2:
SQL> create pluggable database pdb1 admin user pdbadmin identified by nuvola;
Pluggable database created.
Opening the PDB1 of CDB2:
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
Creating the table country in the PDB1 of CDB2:
SQL> alter session set container=pdb1;
Session altered.
SQL> create table c##dgomez.country (name varchar2(25));
Table created.
SQL> insert into c##dgomez.country values ('Canada');
1 row created.
SQL> commit;
Commit complete.
Creation of “Proxy PDB”:
Well, so far everything we have done is only to build the environment in the example in the image shown at the beginning of this article. We have not seen how “Proxy PDB” works; I have only provided concepts and some prerequisites. The next sentence creates a database link in the CDB$ROOT of CDB2. The database link is required only at the time of the Proxy PDB creation. Once the Proxy PDB has been created the database link is no longer required; Proxy PDB connects directly to the referenced PDB without using the database link.
Note that the database link references directly to a common user in the PDB that will be the Referenced PDB, in this case PBD2 of CDB1.
SQL> CREATE DATABASE LINK link_to_pdb2_in_cdb1 CONNECT TO c##dgomez IDENTIFIED BY nuvola USING '192.168.1.22:1521/pdb2';
Database link created.
Note that the database like uses the common user in CDB1, this was one of the prerequisites I mentioned before. The database link connects to the PDB2 in CDB1 since this will be our Referenced PDB.
Once the database link is created, the next step is to create the Proxy PDB.
SQL> create pluggable database pdb2 AS PROXY FROM pdb2@link_to_pdb2_in_cdb1;
Pluggable database created.
And that’s it! The Proxy PDB was created successfully. I will proceed to open it in read/write to start using it:
SQL> alter pluggable database pdb2 open;
Pluggable database altered.
Now it’s time to test how Proxy PDB works! Since the example in this article is based on the CONTAINERS clause, I will connect to the CDB$ROOT of CDB2 using password authentication and execute a query:
[oracle@nuvola2 ~]$ sqlplus c##dgomez/nuvola@'192.168.1.22:1521/cdb2'
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
Note that the query from CDB$ROOT of CDB2 returns the value “Guatemala”; this is because of the Proxy PDB. The value “Guatemala” is not stored in the PDB2 of CDB2 (the Proxy PDB) but, as I said before, the Proxy PDB will behave transparently for all the DDLs and DMLs, it as if a normal PDB was there..
SQL> select name from containers(c##dgomez.country);
NAME
-------------------------
USA
Canada
Guatemala
There is a limitation on Proxy PDBs, they don’t support OS authentication. If you login to the CDB2 with OS authentication and try to run a query from the PDB2 you will get no data. This is because the Proxy PDB will not be able to connect to the referenced PDB and get the data from it (. Proxy PDB supports only password authentication.
[oracle@nuvola2 ~]$ sqlplus / as sysdba
SQL> show con_name
CON_NAME
-----------------------------
CDB$ROOT
SQL> select name from containers(c##dgomez.country);
NAME
-------------------------
USA
Canada
If we connect with OS authentication to the PDB2 in CDB2 and we try to execute a query, the query will fail, saying that the password used is not correct. Of course, we know that there was not a password provided since we used OS authentication.
[oracle@nuvola2 ~]$ sqlplus / as sysdba
SQL> alter session set container=pdb2;
Session altered.
SQL> select * from c##dgomez.country;
select * from c##dgomez.country
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from PROXYPDB$DBLINK
When we use password authentication the Selects works well:
[oracle@nuvola2 ~]$ sqlplus c##dgomez/nuvola@'192.168.1.22:1521/cdb2'
SQL> alter session set container=pdb2;
Session altered.
SQL> select * from c##dgomez.country;
NAME
-------------------------
Guatemala
Now I will test an INSERT operation in the Proxy PDB, but since it is a Proxy, the operation will be executed in the referenced PDB, which means that the row will be stored in the referenced PDB:
SQL> insert into c##dgomez.country values ('Costa Rica');
1 row created.
SQL> commit;
Commit complete.
In PDB2 of CDB1, I will verify if the row was inserted there:
SQL> select name from v$database;
NAME
---------
DB12C
SQL> alter session set container=pdb2;
Session altered.
SQL> select * from c##dgomez.country;
NAME
-------------------------
Guatemala
Costa Rica
This confirms that the Proxy PDB sends SELECTs and also INSERTS (DDLs+DMLs) to be processed inside the referenced PDB.
Conclusion
We have seen that a Proxy PDB is a special PDB that receives operations (DDLs and DMLs) in a local CDB but sends all the operations to its referenced PDB, and processes the operations remotely within the referenced PDB. This brings is the advantage of “Location Transparency”. Location Transparency means that it doesn’t matter where the data is located physically; when we use Proxy PDBs, we can present a PDB in other CDBs as if the PBD that has all the data stored physically was there. All the operations will be processed remotely. Data can be used everywhere in 12.2.0.1.0 without actually having the data physically in all the sites.
Can we have advantages of proxy pdb over normal pdb?
Thanks, Nainesh