# [OCI] Proxy PDB

*If you prefer to read in Spanish* [***Spanish version***](https://dbaenlasombra.com/oci-proxy-pdb?showSharer=true)*.*

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

So, [what is a **Prox**](https://dbaenlasombra.com/oci-26ai-refreshable-clone-pdbs?showSharer=true)**y 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:

```plaintext
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.

```plaintext
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**.

```plaintext
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**:

```plaintext
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.

```plaintext
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.
    

```plaintext
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.
    

```plaintext
[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 :)