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
Related articles.
- Multitenant : Proxy PDB
- Multitenant : All Articles
- Multitenant : Hot Clone a Remote PDB or Non-CDB in Oracle Database 12c Release 2 (12.2)
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c (12.1 and 12.2)
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.
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
andALTER 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
andUNDO
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:
- Creating a PDB as a Proxy PDB
- Multitenant : Proxy PDB
- Multitenant : All Articles
- Multitenant : Hot Clone a Remote PDB or Non-CDB in Oracle Database 12c Release 2 (12.2)
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c (12.1 and 12.2)
Hope this helps. Regards Tim...