[OCI] Proxy PDB

If you prefer to read in Spanish Spanish version.

Today, we’re going to talk about Proxy PDB.

So, what is a Proxy PDB? Basically, it’s when one PDB references another PDB remotely, or even within the same CDB. It works like DB Links, but in this case, it’s not about a single table, it's about an entire PDB.

Here are some notes on this topic:

• The user must have the CREATE PLUGGABLE DATABASE privilege.

• The PDB must be in local undo mode.

• The PDB must be in ARCHIVELOG mode.

• The reference PDB must be in OPEN READ WRITE mode before creating our the Proxy PDB. Once the Proxy PDB is created, we can change it.

• We must create a DB Link in order to reference either the CDB or the PDB.

In this lab, we will use the following::

• Source: 26ai VM DB System Oracle Cloud (test_ORI).

• Target: 26ai VM DB System Oracle Cloud (test_TARGET).

So, let's get down to business!

Step 1: Let’s create the PDB PDB_DAVID on TEST_ORI:

SQL> 
CREATE PLUGGABLE DATABASE PDB_DAVID ADMIN USER ADMIN 
  IDENTIFIED BY ADMIN keystore identified by external store;
Pluggable database created.
SQL> ALTER PLUGGABLE DATABASE PDB_DAVID OPEN;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE PDB_DAVID SAVE STATE;
Pluggable database altered.
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDB_DAVID                      READ WRITE NO
SQL> ! hostname
source

Step 2: Let’s create a common user, not a local user, on both machines. In this case, the DBA role includes the CREATE SESSION and CREATE PLUGGABLE DATABASE privileges.

SQL> CREATE USER C##MANAGER IDENTIFIED BY "XXXXXXX";
User created.
SQL> GRANT RESOURCE, DBA, UNLIMITED TABLESPACE TO C##MANAGER CONTAINER=ALL;
Grant succeeded.

Step 3: We’re going to check that the PDB is using local undo mode.

SQL> r
  1* Select property_name, property_value 
       From database_properties 
      Where property_name='LOCAL_UNDO_ENABLED'

PROPERTY_NAME        PROPERTY_VALUE
-------------------- ----------------------------------------
LOCAL_UNDO_ENABLED   TRUE

Step 4: Let’s create the DB Links on the target. In this case, we’re going to use easy connect instead of local naming:

SQL> 
CREATE DATABASE LINK SOURCE_PROXY CONNECT TO C##MANAGER 
  IDENTIFIED BY "XXXXX" 
    USING 'source:1521/pdb_david.XXXXXXXXXXXXXXXX';

Database link created.

SQL> 
SQL>  SELECT * FROM DUAL@SOURCE_PROXY;

D
-
X

Step 5: Let’s create our PDB using the AS PROXY clause in order to indicate that it’s a Proxy PDB, and the FROM clause in order to specify the referenced PDB.

SQL> 
CREATE PLUGGABLE DATABASE PDB_DAVID AS PROXY 
  FROM PDB_DAVID@SOURCE_PROXY keystore identified by external store;
Pluggable database created.
SQL> alter pluggable database pdb_david open;
Pluggable database altered.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB_DAVID                      READ WRITE NO

SQL> r
  1  SELECT pdb_name, is_proxy_pdb
  2* FROM   cdb_pdbs

PDB_NAME                       IS_PROXY_PDB
------------------------------ ----------------------------------------
PDB$SEED                       NO
PDB_DAVID                      YES

Here are some key considerations:

• V$PROXY_PDB_TARGETS: The view shows information about the details of the target for a proxy PDB.

SQL> r
  1* select * from v$proxy_pdb_targets

    CON_ID TARGET_PORT TARGET_HOST          TARGET_SERVICE                                                                   TARGET_USER
---------- ----------- -------------------- -------------------------------------------------------------------------------- --------------------
         3        1521 source               4843e8f3860c707be063cfdf680abf32.XXXXXXX.XXXXXXX.XXXXXXX.com

• The SYSTEM and SYSAUX tablespaces are copied and synchronized with the source.

• Only password authentication is supported, not OS authentication. Below, you can see how both authentication methods work.

[oracle@target ~]$ sqlplus / as sysdba
Connected to:
Oracle AI Database 26ai EE High Perf Release 23.26.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.26.0.0.0
SQL> alter session set container= PDB_DAVID;
Session altered.
SQL>  Select * From C##MANAGER.CODIGO_POSTAL;
 Select * From C##MANAGER.CODIGO_POSTAL
                          *
ERROR at line 1:
ORA-00942: table or view "C##MANAGER"."CODIGO_POSTAL" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/

SQL> conn C##MANAGER/"XXXXXX"@'target:1521/pdb_david.XXXXX.XXXXX.XXXXXX.com'
Connected.
SQL>  Select * From C##MANAGER.CODIGO_POSTAL;

ID    POBLACION                                                                                            PAIS
----- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
46000 Valencia                                                                                             Espa??a
08000 Barcelona                                                                                            Espa??a
28000 Madrid                                                                                               Espa??a
55500 Wisconsin                                                                                            U.S.A.

Looking forward to seeing you in the next article :)