8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Multitenant : Proxy PDB in Oracle Database 12c Release 2 (12.2)

Introduction
Prerequisites
Create a Proxy PDB
Test It
Local Datafiles
Alternate Host and Port
Proxy Views

Introduction

A proxy PDB can provide a local connection point that references a remote PDB. There are a few situations where this might be of interest to you.

You want to relocate a PDB to a different machine or data centre, without having to change any of the existing connection details. In this case you can relocate the PDB and create a proxy PDB of the same name in the original location.
You want to run a PDB in the cloud, but you don't want to open access to multiple applications, having each of them connecting directly. Instead you make all your applications connect to the local PDB, which in turn connects to the referenced PDB, so there is only a single route in and out of the cloud PDB.
You want to share a single application root container between multiple databases.

Multitenant : Proxy

Here are a few things to consider.

DML and DDL is sent to the referenced PDB for execution and the results returned.
When connected to the proxy PDB, ALTER DATABASE and ALTER PLUGGABLE DATABASE commands refer to the proxy only, they are not passed to the referenced PDB.
In the same way, when connected to the root container, ALTER PLUGGABLE DATABASE commands refer to the proxy only.
A database link is used for the initial creation of the proxy PDB, but all subsequent communication between the servers doesn't use the DB link, so it can be removed once the creation is complete.
The database link used to create a proxy PDB must be created in the root container of the local instance, but can point to a common user in the referenced CDB root container, or a common or local user in the referenced PDB itself.
The SYSTEM, SYSAUX, TEMP and UNDO tablespaces are copied to the local instance and kept synchronized. As a result, you still need to consider file name conversion like a normal clone, unless you are using Oracle Managed Files (OMF).
There will be performance implications due to all the network traffic. This won't magically make remote data transfer faster.

From 12.2 onward we are allowed to have a Proxy PDB, Application Root Container and a single user-defined PDB (regular or Application PDB) inside a single CDB without having to pay for the Multitenant Option. Notice we are still limited to a single user-defined PDB.

Prerequisites

The prerequisites for creating a proxy PDB are similar to that of hot-cloning, so rather than repeat them, you can read them here.

In the examples below I have two databases running on the same virtual machine, but they could be running on separate physical or virtual servers.

cdb1 : The local database that will eventually house the proxy PDB.
cdb3 : The remote CDB, housing the remote referenced PDB (pdb5).

The databases use Oracle Managed Files (OMF) so I don't need to worry about the FILE_NAME_CONVERT or PDB_FILE_NAME_CONVERT settings.

The proxy PDB and referenced PDB share the same listener, so they can't have the same name. If they had different listeners, either on the same machine or on separate machines, they could have the same name.

Create a Proxy PDB

Connect to the root container of the local instance (cdb1). With the prerequisites in place we can create and open the proxy PDB using the following commands.

CONN sys@cdb1 AS SYSDBA

CREATE PLUGGABLE DATABASE pdb5_proxy AS PROXY FROM pdb5@clone_link;
ALTER PLUGGABLE DATABASE pdb5_proxy OPEN;

If you connect to the root container using OS authentication, switch to the proxy PDB container and try to perform a query you will get the following error.

CONN / AS SYSDBA

ALTER SESSION SET CONTAINER = pdb5_proxy;

SQL> SELECT name FROM v$database;
SELECT name FROM v$database
                 *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from PROXYPDB$DBLINK

If you connect to SYS using a service, the switch works fine.

CONN sys@cdb1 AS SYSDBA

ALTER SESSION SET CONTAINER = pdb5_proxy;

SELECT name FROM v$database;

NAME
---------
CDB3

SQL>

Create a new entry in the "tnsnames.ora" file for the proxy PDB in the local instance.

PDB5_PROXY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mydomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb5_proxy)
    )
  )

You can now connect directly to the proxy PDB. Notice in the output below, the database name is showing as CDB3, even though we are connected to the pdb5_proxy container in the cdb1 instance.

CONN sys@pdb5_proxy AS SYSDBA

SELECT name FROM v$database;

NAME
---------
CDB3

SQL>

Once the proxy PDB is created the database link and link user are no longer needed.

CONN sys@cdb1 AS SYSDBA

DROP DATABASE LINK clone_link;

CONN sys@cdb3 AS SYSDBA

DROP USER c##remote_clone_user CASCADE CONTAINER=ALL;

Test It

We will test the proxy PDB by making changes in both the proxy PDB and the referenced PDB. First, create a new tablespace and a test user with a quota in the new tablespace.

CONN sys@pdb5_proxy AS SYSDBA

CREATE TABLESPACE test_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test IDENTIFIED BY test
  DEFAULT TABLESPACE test_ts
  QUOTA UNLIMITED ON test_ts;

GRANT CREATE SESSION, CREATE TABLE TO test;

Connect to the referenced PDB using the newly created user and create a test table.

CONN test/test@pdb5

CREATE TABLE t1 (id NUMBER);
INSERT INTO t1 VALUES (1);
COMMIT;

Return to the proxy PDB and query the table.

CONN test/test@pdb5_proxy

SELECT * FROM t1;

	ID
----------
	 1

SQL>

Insert another record into the table in the proxy PDB.

CONN test/test@pdb5_proxy

INSERT INTO t1 VALUES (2);
COMMIT;

Return to the referenced PDB and query the table.

CONN test/test@pdb5

SELECT * FROM t1;

	ID
----------
	 1
	 2

SQL>

We can see the proxy PDB and referenced PDB are working as expected.

Local Datafiles

What might seem a little odd is the SYSTEM, SYSAUX, TEMP and UNDO tablespaces are copied to the local instance and kept synchronized. All other tablespaces are only present in the referenced instance.

If we query datafiles and tempfiles in the proxy PDB we are shown those of the referenced PDB. Notice the datafiles associated with the USERS and TEST_TS tablespaces.

CONN sys@pdb5_proxy AS SYSDBA

SET LINESIZE 100

SELECT name FROM v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/cdb3/pdb5/system01.dbf
/u02/app/oracle/oradata/cdb3/pdb5/sysaux01.dbf
/u02/app/oracle/oradata/cdb3/pdb5/undotbs01.dbf
/u02/app/oracle/oradata/cdb3/pdb5/users01.dbf
/u02/app/oracle/oradata/CDB3/469D84C85D196311E0538738A8C0B97D/datafile/o1_mf_test_ts_d877rjoo_.dbf

SQL>


SELECT name FROM v$tempfile;

NAME
----------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/cdb3/pdb5/temp01.dbf

SQL>

If we check in the local instance we see a different pattern. Notice the datafiles associated with the USERS and TEST_TS tablespaces are not present.

CONN / AS SYSDBA

SHOW PDBS

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 5 PDB5_PROXY			  READ WRITE NO
SQL>


SET LINESIZE 100

SELECT name FROM v$datafile WHERE con_id = 5;

NAME
----------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/CDB1/469F256E1081028AE0538738A8C079C7/datafile/o1_mf_system_d876rtd8_.dbf
/u02/app/oracle/oradata/CDB1/469F256E1081028AE0538738A8C079C7/datafile/o1_mf_sysaux_d876rtd9_.dbf
/u02/app/oracle/oradata/CDB1/469F256E1081028AE0538738A8C079C7/datafile/o1_mf_undotbs1_d876rtd9_.dbf

SQL>


SELECT name FROM v$tempfile WHERE con_id = 5;

NAME
----------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/CDB1/469F256E1081028AE0538738A8C079C7/datafile/o1_mf_temp_d876rtdb_.dbf

SQL>

Alternate Host and Port

The CREATE PLUGGABLE DATABASE ... AS PROXY FROM command can also include the HOST and PORT clauses.

CREATE PLUGGABLE DATABASE pdb5_proxy AS PROXY FROM pdb5@clone_link PORT=1526 HOST='ol7-122.localdomain';

The PORT clause should be used if the referenced PDB is accessed by a port other than 1521. The HOST clause is used if the referenced PDB is to be accessed using a name other that produced by the hostname command on the remote server, for example a DNS alias or SCAN. The host and port are amended using the following commands, issued from the referenced PDB.

CONN sys@pdb5 AS SYSDBA

-- Alter and reset HOST.
ALTER PLUGGABLE DATABASE CONTAINERS HOST='myhost.example.com';
ALTER PLUGGABLE DATABASE CONTAINERS HOST REST;

-- Alter and reset HOST.
ALTER PLUGGABLE DATABASE CONTAINERS PORT=1526;
ALTER PLUGGABLE DATABASE CONTAINERS PORT REST;

After a change, any proxy PDBs pointing to the referenced PDB must be recreated.

Proxy Views

You can see which are proxy PDBs using the V$PDBS.PROXY_PDB column or CDB_PDBS.IS_PROXY_PDB column.

COLUMN name FORMAT A30

SELECT name, proxy_pdb
FROM   v$pdbs;

NAME                           PRO
------------------------------ ---
PDB$SEED                       NO
PDB1                           NO
PDB5_PROXY                     YES

SQL>


COLUMN pdb_name FORMAT A30

SELECT pdb_name, is_proxy_pdb
FROM   cdb_pdbs;

PDB_NAME                       IS_
------------------------------ ---
PDB1                           NO
PDB$SEED                       NO
PDB5_PROXY                     YES

SQL>

The V$PROXY_PDB_TARGETS displays information about the connection details for the referenced PDB used by a proxy PDB.

COLUMN target_host FORMAT A20
COLUMN target_service FORMAT A32
COLUMN target_user FORMAT A20

SELECT con_id,
       target_port,
       target_host,
       target_service,
       target_user
FROM   v$proxy_pdb_targets;

    CON_ID TARGET_PORT TARGET_HOST          TARGET_SERVICE                   TARGET_USER
---------- ----------- -------------------- -------------------------------- --------------------
         5        1521 my-server            469d84c85d196311e0538738a8c0b97d       

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.