“PROXY PDB” THE TRANSPARENT PDB 19C IN ORACLE
Similar to how a symbolic link works in a linux system to link binaries to a file, PROXY PDB acts like a shadow PDB to a remote PDB. All DDL’s and DML’s performed in the source PDB are replayed in the PROXY PDB like a snapshot. This feature was introduced in 12.2, to overcome the challenges of communicating between local and remote database systems with database link. This PDB acts as a dummy PDB copying the data operations from a referenced PDB. Not all the tablespaces of referenced PDB’s(source PDB) are copied except SYSTEM,SYSAUX and UNDO
Things to check:
LOCAL UNDO should be enabled in referenced PDB
kIsH@Xhydra<>select property_value from database_properties where property_name like '%LOCAL_UNDO%';
PROPERTY_VALUE
--------------------------------------------------------------------------------
TRUE
kIsH@Xhydra<>select property_value from database_properties where property_name like '%LOCAL_UNDO%';
PROPERTY_VALUE
--------------------------------------------------------------------------------
TRUE
2) Check if the source database is in archive log mode and enable it if it is disabled
kIsH@Xhydra<>select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
kIsH@Xhydra<>shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
kIsH@Xhydra<>startup mount;
ORACLE instance started.
Total System Global Area 1459614512 bytes
Fixed Size 9134896 bytes
Variable Size 369098752 bytes
Database Buffers 1073741824 bytes
Redo Buffers 7639040 bytes
Database mounted.
kIsH@Xhydra<>alter database archivelog;
Database altered.
kIsH@Xhydra<>alter database open;
Database altered.
kIsH@Xhydra<>select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
3) Also referenced PDB should be in open read write mode
Let us perform a test case of proxy pdb
Create a test common user –> grant the required privileges –> create a PDB –> Open the PDB
kIsH@Xhydra<>create user c##proxy identified by password container=all;
User created.
kIsH@Xhydra<>grant unlimited tablespace,connect,resource,create pluggable database to c##proxy container=all;
Grant succeeded.
kIsH@Xhydra<>create pluggable database prox1 admin user pdbadmin identified by password;
Pluggable database created.
kIsH@Xhydra<>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBTRNST MOUNTED
5 PROX1 MOUNTED
kIsH@Xhydra<>alter pluggable database prox1 open;
Pluggable database altered.Add the tns connection string in tnsnames.ora. Check the tns connectivity of the newly created PDB.
[oracle@xhydra ~]$ tnsping prox1
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 30-AUG-2022 11:28:32
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.33)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PROX1)))
OK (0 msec)
[oracle@xhydra ~]$ vi /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
[oracle@xhydra ~]$
[oracle@xhydra ~]$ tnsping prox2
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 30-AUG-2022 11:29:12
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.33)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PROX2)))
OK (0 msec)
CDB1 (orcl):
Create a sample table from c##proxy user –> Populate records –> Check the rows
kIsH@Xhydra<>conn c##proxy/password@prox1
Connected.
kIsH@Xhydra<>create table pr1(proxy varchar2(10));
Table created.
kIsH@Xhydra<>insert into pr1 values('p');
1 row created.
kIsH@Xhydra<>commit;
Commit complete.
kIsH@Xhydra<>select * from pr1;
PROXY
----------
p
CDB2(staxdb):
Now that CDB1 has a PDB called prox1. In CDB2, create the same user with same permissions.
Create the same table and insert values different from prox1 to differentiate the referenced PDB value and also to verify if the proxy PDB works normally
kIsH@Xhydra<>create user c##proxy identified by password container=all;
User created.
kIsH@Xhydra<>grant unlimited tablespace,connect,resource,create pluggable database,create session to c##proxy container=all;
Grant succeeded.
kIsH@Xhydra<>conn c##proxy/password
Connected.
kIsH@Xhydra<>create table pr1(proxy varchar2(10));
Table created.
kIsH@Xhydra<>insert into pr1 values('y');
1 row created.
kIsH@Xhydra<>commit;
Commit complete.
Create a database link from CDB2 to prox1 with port 1521. Note that it is not compulsory to use default port 1521. Any port can be used as per the availability.
kIsH@Xhydra<>create database link prox_link connect to c##proxy identified by "password" using '192.168.56.33:1521/PROX1';
Database link created.
kIsH@Xhydra<>select * from dual@prox_link;
D
-
X
kIsH@Xhydra<>select * from c##proxy.pr1@prox_link; --CDB1 remote
PROXY
----------
p
kIsH@Xhydra<>select * from c##proxy.pr1; --CDB2 local
PROXY
----------
y
DBlink connection is verified. It is time to create a proxy PDB
kIsH@Xhydra<>create pluggable database prox1 as proxy from prox1@prox_link;
Pluggable database created.
kIsH@Xhydra<>alter pluggable database PROX1 open;
Pluggable database altered.
kIsH@Xhydra<>exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0Connect to the CDB2 with the credentials and verify if the data from prox1 from CDB1 is reflected in prox1 (proxy PDB) in CDB2. In connection identifier, specify (ipaddress or hostname : port number / service name of CDB2)
[oracle@xhydra ~]$ sqlplus c##proxy/password@'192.168.56.33:1521/orcl'
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 30 11:59:41 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Tue Aug 30 2022 11:57:50 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
kIsH@Xhydra<>select * from containers(c##proxy.pr1);
PROXY CON_ID
---------- ----------
y 1
p 5 <======= This is the PROXY PDB record from source PDB!!If i insert some records into the referenced PDB, the same will be reflected in the proxy PDB
kIsH@Xhydra<>alter session set container=PROX1;
Session altered.
kIsH@Xhydra<>insert into c##proxy.pr1 values('v');
1 row created.
kIsH@Xhydra<>commit;
Commit complete.
Login to proxy PDB
[oracle@xhydra ~]$ sqlplus c##proxy/password@'192.168.56.33:1521/orcl'
kIsH@Xhydra<>select * from containers(c##proxy.pr1);
PROXY CON_ID
---------- ----------
y 1
p 5
v 5 <==== Newly inserted rows from referenced PDBNote: OS authentication will not be work for proxy PDB.
[oracle@xhydra ~]$ sqlplus / as sysdba
kIsH@Xhydra<>alter session set container=PROX1;
Session altered.
kIsH@Xhydra<>select * from containers(c##proxy.pr1);
PROXY CON_ID
---------- ----------
y 1 <=== Records from referenced PDB is not reflected in proxy PDBBelow output shows the tablespaces which are copied from referenced PDB except TEMP
--Connecting to proxy PDB
kIsH@Xhydra<>alter session set container=prox1;
Session altered.
kIsH@Xhydra<>select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
To check the existence of all proxy PDB’s, use the below query
kIsH@Xhydra<>col THOST for a20
kIsH@Xhydra<>select inst_id, con_id, tport, thost, tsrvc, tuser from x$kpdbvproxy;
INST_ID CON_ID TPORT THOST TSRVC TUSER
---------- ---------- ---------- -------------------- -------------------- --------------------
1 4 1521 xhydra e697035f4a8a2ef4e053
6338a8c0b448
Can we create a proxy PDB with name different from the referenced PDB?
Yes, i tested the condition with different name and the results are reflected from referenced PDB
kIsH@Xhydra<>select * from c##proxy.pr1@prox_link2;
PROXY
----------
o
kIsH@Xhydra<>select * from c##proxy.pr1;
PROXY
----------
y
kIsH@Xhydra<>create pluggable database prox_2 as proxy from prox2@prox_link2;
Pluggable database created.
kIsH@Xhydra<>alter pluggable database prox_2 open;
Pluggable database altered.
[oracle@xhydra ~]$ sqlplus c##proxy/password@'192.168.56.33:1521/orcl'
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 31 10:46:27 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Wed Aug 31 2022 10:45:43 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
kIsH@Xhydra<>select * from containers(c##proxy.pr1);
PROXY CON_ID
---------- ----------
y 1
o 9
Proxy PDB is also considered similar to physical PDB which adds one count to max_pdbs parameter. If the value for max_pdbs is limited, then further PDB cannot be created unless the parameter is increased.
kIsH@Xhydra<>alter system set max_pdbs=3;
System altered.
kIsH@Xhydra<>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PROX_2 READ WRITE NO
6 PROX1 MOUNTED
kIsH@Xhydra<>create pluggable database prox2 admin user pdbadmin identified by password;
Pluggable database created.
kIsH@Xhydra<>create pluggable database prox3 admin user pdbadmin identified by password;
create pluggable database prox3 admin user pdbadmin identified by password
*
ERROR at line 1:
ORA-65010: maximum number of pluggable databases created