How to build proxy pdb in multitenant architecture

 

Problem statement: You have multiple CDBs deployed across different data centers or environments (e.g., PROD, DR, Reporting, Cloud migration setup).
Application teams require unified access to PDBs across these environments, but:

• You cannot consolidate all databases into a single CDB
• Network latency and storage constraints prevent full database duplication
• You want to avoid complex database links embedded in application code
• You need transparent SQL access as if the database is local

Proxy PDB solves this by:

• Creating a local PDB representation of a remote database
• Allowing applications to connect without knowing about remote location
• Eliminating the need for application-level DB links

Below is a simple architectural diagram

Now I will show some hands on for this concept.

Source container database:cdborcl

Source pluugable database:pdborcl

Target container database:cdbtest

Target pluggable database:pdbtest

 

1. Create a common user in the source database from where we want to create proxy pdb and provide necessary privileges

From the root container execute below SQL

create user c##dblinkuser identified by "welcome123##" default tablespace users temporary tablespace temp;

grant create session,create pluggable database,select_catalog_role to  c##dblinkuser container=ALL;

grant read on sys.enc$ to c##dblinkuser container=ALL;

Please make sure to use container=ALL otherwise it will not be a common role

Check privileges like below

SQL> select GRANTEE,PRIVILEGE,common from dba_sys_privs where GRANTEE='C##DBLINKUSER';

 

GRANTEE       PRIVILEGE                                COM

------------- ---------------------------------------- ---

C##DBLINKUSER CREATE PLUGGABLE DATABASE                YES

C##DBLINKUSER CREATE SESSION                           YES

 

 

2. Create database link in target database where porxy pdb will be created

SQL> create database link cdborcl_link connect to c##dblinkuser identified by "welcome123##" using 'cdborcl';

 

Database link created.

 

SQL> select OWNER,DB_LINK,HOST,VALID from dba_db_links;

 

OWNER                          DB_LINK                        HOST                           VAL

------------------------------ ------------------------------ ------------------------------ ---

SYS                            CDBORCL_LINK.WORLD                XXXX                         YES

 

validate

 

alter system set global_names=false;

 

System altered.

 

SQL> select sysdate from dual@cdborcl_link;

SYSDATE

---------

19-APR-26

 

3. Create proxy pdb

Here pdborcl is the pluggable database under cdborcl

 

CREATE PLUGGABLE DATABASE proxy_pdb AS PROXY FROM pdborcl@ccborcl_link;

alter pluggable database proxy_pdb open;

4. Check existence and status  of proxy pdb

SQL>  SELECT name,open_mode,proxy_pdb from v$pdbs where PROXY_PDB = 'YES';

NAME                           OPEN_MODE  PROXY_PDB

------------------------------ ---------- ------------------------------

PROXY_PDB                      MOUNTED    YES

 

4. Check new service with the same name as proxy pdb which is also registered in listener

 

SQL> select NAME,NETWORK_NAME from  v$services;

 

NAME                           NETWORK_NAME

------------------------------ ------------------------------

SYS$BACKGROUND

SYS$USERS

cdbtest.world                  cdbtest.world

cdbtestXDB                     cdbtestXDB

pdbtest                        pdbtest

proxy_pdb                      proxy_pdb

 

lsnrctl status listener | grep proxy_pdb

Service "proxy_pdb.world" has 1 instance(s).

 

Also in target host a system generated service will be created like below using which proxy pdb fetch data

 

SQL> SELECT CON_ID, TARGET_HOST, TARGET_PORT, TARGET_SERVICE FROM V$PROXY_PDB_TARGETS;

 

    CON_ID TARGET_HOST               TARGET_PORT  TARGET_SERVICE

---------- ------------------------------ ------------------   ------------------------------------------------------------

         4       XXXXXXX                                1521                      4fd1dba80f4d4496e063cb09800ac57c.world

 

5. Validation of objects created in source pdb and proxy_pdb

Execute below code in  source pdb to check whether we can see same table and its row count

SQL>  create table pdbadmin.temp_objects as select * from dba_objects;

 

Table created.

 

SQL> select count(*) from pdbadmin.temp_objects;

 

  COUNT(*)

----------

     73926

 

6. Add TNS entry of proxy pdb and connect using that tnsnames

 

SQL> conn sys/XXX@cdbtest as sysdba

Connected.

SQL> alter session set container=PROXY_PDB;

 

Session altered.

Use below query to check registered proxy pdb

SELECT CON_ID, TARGET_HOST, TARGET_PORT, TARGET_SERVICE

FROM V$PROXY_PDB_TARGETS;

Validate row count of the table for remote pdb like below

select count(*) from pdbadmin.temp_objects;

COUNT(*)

----------

     73926

 Hope you have learnt something useful....