“PROXY PDB” THE TRANSPARENT PDB 19C IN ORACLE

“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.0

Connect 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 PDB

Note: 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 PDB

Below 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

Leave a Reply

%d bloggers like this: