首页 » ORACLE 9i-23ai » Oracle 12c R2新特性： Proxy PDB
Oracle 12c R2新特性： Proxy PDB
这篇继续ORACLE 12.2的新特性, Proxy PDB顾名思义就是一种代理PDB也可以叫 referenced PDB， 实现的是像访问本地CDB中的PDB一样访问远程的PDB，简单可以说可以快捷方式，它的应用场景是当一个程序在application Containers中需要调用两个不同CDB的数据源时，如SaaS这种大规模的应用场景中APP CDB的管理员不需要再单独分配远程PDB的权限，应用同时也只是访问本地service, 方便集中管理，application Containers也是12.2的引入的新的Container后面再讲。
Proxy PDB的注意事项
1， 在本地CDB ROOT中创建一个DBLINK指向远程的PDB所在的CDB或者PDB自身，如果是指向远程CDB，DB LINK使用的用户必须是Common user, 如果是远程PDB，DB LINK的用户要用CREATE PLUGGABLE DATABASE的系统权限
2， 当前用户要用CREATE PLUGGABLE DATABASE 系统权限
3， 本地CDB必须为ARCHIVELOG MODE和LOCAL UNDO
4, 创建Proxy PDB时，远程PDB必须为OPEN READ/WRITE 模式，创建成功后后期可以修改
5， DB LINK中是用于create proxy pdb时传速初始数据，当PROXY PDB创建成功后DB LINK可以删除，元数据已记录数据字典。
6, 创建Proxy PDB是的初始数据包含The SYSTEM, SYSAUX, TEMP and UNDO 表空间并保持同步，不会同步用户数据表空间，所以较依赖网络资源。
7， 访问Proxy PDB时和local PDB一样使用SERVICE NAME, 只是连接上后是远程的PDB
8, 在Proxy PDB中执行的DDL\DML 都是发送到远程PDB执行，结果返回Proxy PDB.
9, 在Proxy PDB中执行的Alter Pluggable Database操作只影响Proxy PDB不会传递到远程Proxy 执行，如close
10, 访问Proxy PDB可以使用Proxy Service也可以使用之前笔记中提到的Proxy PDB GUID service,
11, 不可以创建Proxy PDB基于Proxy PDB
demo
当前环境是在同一个server主机中，两个不同的CDB为anbob和CDB2. CDB2是空无USER PDB, CDB anbob有2个USER PDB:PDBANBOB\PDBWEEJAR, 这里演示在CDB2中创建PROXY PDB指向CDB ANBOB的PDBANBOB.
# 源 CDB ANBOB
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBANBOB READ WRITE NO
6 PDBWEEJAR MOUNTED
[oracle@anbob ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 09-APR-2017 17:00:23
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anbob.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 09-APR-2017 16:01:49
Uptime 0 days 0 hr. 58 min. 33 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u02/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Listener Log File /u02/app/oracle/diag/tnslsnr/anbob/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anbob.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=anbob.com)(PORT=5500))(Security=(my_wallet_directory=/u02/app/oracle/admin/anbob/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "465fa0ab8243396ae0530338a8c0fc9e.com" has 1 instance(s).
Instance "anbob", status READY, has 1 handler(s) for this service...
Service "4a0d4eafec1b1916e0530338a8c07871.com" has 1 instance(s).
Instance "anbob", status READY, has 1 handler(s) for this service...
Service "anbob.com" has 1 instance(s).
Instance "anbob", status READY, has 1 handler(s) for this service...
Service "anbobXDB.com" has 1 instance(s).
Instance "anbob", status READY, has 1 handler(s) for this service...
Service "cdb2" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "pdbanbob.com" has 1 instance(s).
Instance "anbob", status READY, has 1 handler(s) for this service...
Service "pdbweejar.com" has 1 instance(s).
Instance "anbob", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@anbob admin]$ tnsping pdbanbob
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 09-APR-2017 16:57:48
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = anbob.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdbanbob.com)))
OK (10 msec)
[oracle@anbob ~]$ sqlplus anbob/anbob@pdbanbob
SQL*Plus: Release 12.2.0.1.0 Production on Sun Apr 9 17:01:37 2017
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
SQL> col name for a55
SQL> select name from v$datafile;
NAME
-------------------------------------------------------
/u02/app/oracle/oradata/anbob/pdbanbob/system01.dbf
/u02/app/oracle/oradata/anbob/pdbanbob/sysaux01.dbf
/u02/app/oracle/oradata/anbob/pdbanbob/undotbs01.dbf
/u02/app/oracle/oradata/anbob/pdbanbob/users01.dbf
/u02/app/oracle/oradata/anbob/pdbanbob/lower01.dbf
SQL> select pdb_id,pdb_name,con_uid,guid from dba_pdbs;
PDB_ID PDB_NAME CON_UID GUID
---------- ---------- ---------- --------------------------------
3 PDBANBOB 3629755513 465FA0AB8243396AE0530338A8C0FC9E
# 目标 CDB CDB2
[oracle@anbob admin]$ sqlplus sys/oracle@//anbob.com/cdb2 as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Apr 9 18:21:41 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL> select pdb_id,pdb_name,con_uid,guid from dba_pdbs;
PDB_ID PDB_NAME CON_UID GUID
---------- ------------------ ---------- --------------------------------
2 PDB$SEED 2142920550 4CB99CF839F05C0AE0530338A8C0350C
SQL> create database link link_anbob connect to anbob identified by anbob using 'pdbanbob';
Database link created.
SQL> select sysdate from dual@link_anbob;
SYSDATE
-------------------
2017-04-09 18:23:27
Note:
当前CDB2无user PDBS,在CDB2 ROOT container中创建了DBLINK， 这里使用的是直接指向远程PDB的方式，该用户ANBOB有创建PDB的权限。
# 目标CDB CDB2 创建目录 [oracle@anbob admin]$ cd /u01/oradata/cdb2/ [oracle@anbob cdb2]$ mkdir pdbproxy [oracle@anbob cdb2]$ sqlplus sys/oracle@//anbob.com/cdb2 as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sun Apr 9 18:25:33 2017 Connected to: Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production SQL> create pluggable database pdbanbob_proxy as proxy from pdbanbob@link_anbob file_name_convert=('/u02/app/oracle/oradata/anbob/pdbanbob','/u01/oradata/cdb2/pdbproxy'); Pluggable database created. SQL> col pdb_name for a30 SQL> select pdb_id,pdb_name,con_uid,guid from dba_pdbs; PDB_ID PDB_NAME CON_UID GUID ---------- ------------------------------ ---------- -------------------------------- 3 PDBANBOB_PROXY 3295139282 4CBA478FA72368F3E0530338A8C0C830 2 PDB$SEED 2142920550 4CB99CF839F05C0AE0530338A8C0350C SQL> select con_id,name from v$datafile CON_ID NAME ---------- ----------------------------------------------------------------------------- 1 /u01/oradata/cdb2/system01.dbf 1 /u01/oradata/cdb2/sysaux01.dbf 1 /u01/oradata/cdb2/undotbs01.dbf 2 /u01/oradata/cdb2/pdbseed/system01.dbf 2 /u01/oradata/cdb2/pdbseed/sysaux01.dbf 1 /u01/oradata/cdb2/users01.dbf 2 /u01/oradata/cdb2/pdbseed/undotbs01.dbf 3 /u01/oradata/cdb2/pdbproxy/system01.dbf 3 /u01/oradata/cdb2/pdbproxy/sysaux01.dbf 3 /u01/oradata/cdb2/pdbproxy/undotbs01.dbf SQL> drop database link link_anbob; Database link dropped. SQL> select con_id,name,PDB_COUNT,open_mode,PROXY_PDB,APPLICATION_PDB from v$pdbs; CON_ID NAME PDB_COUNT OPEN_MODE PRO APP ---------- ------------------------------ ---------- ---------- --- --- 2 PDB$SEED 0 READ ONLY NO NO 3 PDBANBOB_PROXY 0 MOUNTED YES NO SQL> alter pluggable database pdbanbob_proxy open; Pluggable database altered [oracle@anbob cdb2]$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 09-APR-2017 18:27:47 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anbob.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 09-APR-2017 16:01:49 Uptime 0 days 2 hr. 25 min. 57 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u02/app/oracle/product/12.2.0/db_1/network/admin/listener.ora Listener Log File /u02/app/oracle/diag/tnslsnr/anbob/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anbob.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=anbob.com)(PORT=5500))(Security=(my_wallet_directory=/u02/app/oracle/admin/anbob/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "465fa0ab8243396ae0530338a8c0fc9e.com" has 1 instance(s). Instance "anbob", status READY, has 1 handler(s) for this service... Service "4a0d4eafec1b1916e0530338a8c07871.com" has 1 instance(s). Instance "anbob", status READY, has 1 handler(s) for this service... Service "4cba478fa72368f3e0530338a8c0c830" has 1 instance(s). Instance "cdb2", status READY, has 1 handler(s) for this service... Service "anbob.com" has 1 instance(s). Instance "anbob", status READY, has 1 handler(s) for this service... Service "anbobXDB.com" has 1 instance(s). Instance "anbob", status READY, has 1 handler(s) for this service... Service "cdb2" has 1 instance(s). Instance "cdb2", status READY, has 1 handler(s) for this service... Service "cdb2XDB" has 1 instance(s). Instance "cdb2", status READY, has 1 handler(s) for this service... Service "pdbanbob.com" has 1 instance(s). Instance "anbob", status READY, has 1 handler(s) for this service... Service "pdbanbob_proxy" has 1 instance(s). Instance "cdb2", status READY, has 1 handler(s) for this service... Service "pdbweejar.com" has 1 instance(s). Instance "anbob", status READY, has 1 handler(s) for this service... The command completed successfully
Note:
到这里CDB2 中的PROXY PDB已创建成功，proxy pdb service已注册到监听中，已经可以在CDB2主机上使用pdbanbob_proxy服务名连接数据库了，还有之前说的PDB GUID servie 4cba478fa72368f3e0530338a8c0c830，因为数据库没有使用OMF所以这里使用了file_name_convert选项转换了源端数据文件的位置。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。