目标:在CDB1上创建代理PDB ,引用 CDB2的PDB1
环境:
本地(创建代理PDB所在CDB) :CDB1
远端(被引用PDB所在CDB) :CDB2
版本:ORACLE 19.20
要求:
1.两个CDB都开启归档
2.两个CDB都使用LOCAL UNDO
3.DBLINK连接用户要求create pluggable database 权限
操作步骤:
1. 在远端 cdb2 创建对应用户
[oracle@dbserver ~]$ . oraenv
ORACLE_SID = [cdb2] ?
[oracle@dbserver ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 22 17:28:33 2023
Version 19.20.0.0.0
@ >conn / as sysdba
Connected.
SYS@cdb2 >
SYS@cdb2 >create user c##remote_proxy identified by oracle_4U container=all;
SYS@cdb2 >grant create session to c##remote_proxy container=all;
SYS@cdb2 >grant create pluggable database to c##remote_proxy container=all;
SYS@cdb2 >
2.在本地 cdb1 创建 到 CDB2 的 DBLINK
[oracle@dbserver ~]$ . oraenv
ORACLE_SID = [cdb1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@dbserver ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 22 17:30:12 2023
Version 19.20.0.0.0
@ >conn / as sysdba
Connected.
SYS@cdb1 >
SYS@cdb1 >create database link link_cdb2
2 connect to c##remote_proxy identified by oracle_4U
3 using 'cdb2';
SYS@cdb1 >
3.在CDB1上创建代理PDB
SYS@cdb1 >create pluggable database proxy_pdb1 as proxy
2 from pdb1@link_cdb2;
SYS@cdb1 >
4.确认代理PDB已经创建成功
col pdb_name for a30
col IS_PROXY_PDB for a20
SYS@cdb1 > select pdb_name,status,IS_PROXY_PDB from cdb_pdbs
PDB_NAME STATUS IS_
------------------------------ ---------- ---
PROXY_PDB1 NEW YES
PDB$SEED NORMAL NO
SYS@cdb1 >show pdbdetails
APP APP
ROOT APP APP APP ROOT PXY
CONID CONID CON_NAME ROOT PDB SEED CLONE PDB OPEN MODE REST
----- ----- ---------------------------- ---- --- ---- ----- --- ---------- ----
2 PDB$SEED NO NO NO NO NO READ ONLY NO
3 PROXY_PDB1 NO NO NO NO YES MOUNTED
5.打开PROXY_PDB1
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PROXY_PDB1 MOUNTED
SQL>
SYS@cdb1 >alter pluggable database PROXY_PDB1 open;
Pluggable database altered.
SYS@cdb1 >show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PROXY_PDB1 READ WRITE NO
SYS@cdb1 >
6.模拟往代理PDB中 插入数据
--使用的网络服务别登陆,看到的才是真实的代理PDB对应的引用PDB的数据,即PDB1的东西。
SYS@cdb1 >conn sys/oracle_4U@cdb1 as sysdba
Connected.
SYS@cdb1 >show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PROXY_PDB1 READ WRITE NO
SYS@cdb1 >alter session set container=proxy_pdb1;
Session altered.
SYS@cdb1 >show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB1 READ WRITE NO
SYS@cdb1 >
SYS@cdb1 >show pdbdetails
APP APP
ROOT APP APP APP ROOT PXY
CONID CONID CON_NAME ROOT PDB SEED CLONE PDB OPEN MODE REST
----- ----- ---------------------------- ---- --- ---- ----- --- ---------- ----
4 PDB1 NO NO NO NO NO READ WRITE NO
SYS@cdb1 >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB2/07BE9065CAE69697E0637885A8C080A5/datafile/o1_mf_system_lm9r8rnz_.dbf
/u01/app/oracle/oradata/CDB2/07BE9065CAE69697E0637885A8C080A5/datafile/o1_mf_sysaux_lm9r8ro2_.dbf
/u01/app/oracle/oradata/CDB2/07BE9065CAE69697E0637885A8C080A5/datafile/o1_mf_undotbs1_lm9r8ro3_.dbf
/u01/app/oracle/oradata/CDB2/07BE9065CAE69697E0637885A8C080A5/datafile/o1_mf_tsp_pdb1_lm9r8ro5_.dbf
--使用操作系统认证登陆,看到的是PROXY_PDB1在CDB1里面的本地的东西
SYS@cdb1 >conn / as sysdba
Connected.
SYS@cdb1 >show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PROXY_PDB1 READ WRITE NO
SYS@cdb1 >alter session set container=PROXY_PDB1;
Session altered.
SYS@cdb1 >show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PROXY_PDB1 READ WRITE NO
SYS@cdb1 >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB1/084BC5D53AE5BA81E0637885A8C06CBB/datafile/o1_mf_system_lm9tzygr_.dbf
/u01/app/oracle/oradata/CDB1/084BC5D53AE5BA81E0637885A8C06CBB/datafile/o1_mf_sysaux_lm9tzygy_.dbf
/u01/app/oracle/oradata/CDB1/084BC5D53AE5BA81E0637885A8C06CBB/datafile/o1_mf_undotbs1_lm9tzygz_.dbf
SYS@cdb1 >conn sys/oracle_4U@192.168.133.120:1521/proxy_pdb1 as sysdba
Connected.
SYS@192.168.133.120:1521/proxy_pdb1 >show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB1 READ WRITE NO
SYS@192.168.133.120:1521/proxy_pdb1 >
SYS@192.168.133.120:1521/proxy_pdb1 >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB2/07BE9065CAE69697E0637885A8C080A5/datafile/o1_mf_system_lm9r8rnz_.dbf
/u01/app/oracle/oradata/CDB2/07BE9065CAE69697E0637885A8C080A5/datafile/o1_mf_sysaux_lm9r8ro2_.dbf
/u01/app/oracle/oradata/CDB2/07BE9065CAE69697E0637885A8C080A5/datafile/o1_mf_undotbs1_lm9r8ro3_.dbf
/u01/app/oracle/oradata/CDB2/07BE9065CAE69697E0637885A8C080A5/datafile/o1_mf_tsp_pdb1_lm9r8ro5_.dbf
SYS@192.168.133.120:1521/proxy_pdb1 >create table app1 (name varchar2(20));
SYS@192.168.133.120:1521/proxy_pdb1 >insert into app1 values('pdb1');
SYS@192.168.133.120:1521/proxy_pdb1 >commit;
SYS@192.168.133.120:1521/proxy_pdb1 >select * from app1;
NAME
--------------------
pdb1
SYS@192.168.133.120:1521/proxy_pdb1 >
--通过操作系统认证,看不到数据
SYS@192.168.133.120:1521/proxy_pdb1 >conn / as sysdba
Connected.
SYS@cdb1 >show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PROXY_PDB1 READ WRITE NO
SYS@cdb1 >alter session set container=PROXY_PDB1;
SYS@cdb1 >show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PROXY_PDB1 READ WRITE NO
SYS@cdb1 >select * from app1;
select * from app1
*
ERROR at line 1:
ORA-00942: table or view does not exist
--通过网络服务别名登陆,就可以看到被代理PDB引用的PDB的内容
--登陆远端 CDB2(被代理PDB引用的CDB),去看
SYS@cdb2 >show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB2 READ WRITE NO
4 PDB1 READ WRITE NO
SYS@cdb2 >alter session set container=pdb1;
Session altered.
SYS@cdb2 >show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB1 READ WRITE NO
SYS@cdb2 >select * from app1;
NAME
--------------------
pdb1
SYS@cdb2 >insert into app1 values('cdb2');
SYS@cdb2 >commit;
SYS@cdb2 >select * from app1;
NAME
--------------------
pdb1
cdb2
--在本地登陆代理PDB看,可以看到是远端CDB2中PDB1的信息:
SYS@cdb1 >conn sys/oracle_4U@cdb1 as sysdba
Connected.
SYS@cdb1 >show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PROXY_PDB1 READ WRITE NO
SYS@cdb1 >alter session set container=PROXY_PDB1;
Session altered.
SYS@cdb1 >show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB1 READ WRITE NO
SYS@cdb1 >select * from app1;
NAME
------------------------------
pdb1
cdb2
7.查看代理PDB的相关信息
--本地查看
SYS@cdb1 >set linesize 300
SYS@cdb1 >col target_host format a20
SYS@cdb1 >col target_service format a40
SYS@cdb1 >col target_user format a20
SYS@cdb1 >select con_id,
2 target_port,
3 target_host,
4 target_service,
5 target_user
6 from v$proxy_pdb_targets;
CON_ID TARGET_PORT TARGET_HOST TARGET_SERVICE TARGET_USER
---------- ----------- -------------------- ---------------------------------------- --------------------
3 1521 dbserver 07be9065cae69697e0637885a8c080a5
--源端查看
@ >conn / as sysdba
Connected.
SYS@cdb2 >
SYS@cdb2 >col name for a20
SYS@cdb2 >select name,open_mode from v$pdbs where guid=upper('07be9065cae69697e0637885a8c080a5');
NAME OPEN_MODE
-------------------- ----------
PDB1 READ WRITE
附录:
1.错误1:
*
ERROR at line 1:
ORA-65005: missing or invalid file name pattern for file -
+DATA/CDB2/CF7E1F666AA53F68E0537985A8C03BE5/DATAFILE/system.323.1087246105
-- 加路径映射
create_file_dest='/u01/app/oracle/oradata';
2.错误2:
SQL> create pluggable database pxpdb1 as proxy from pdb1@link_cdb2;
create pluggable database pxpdb1 as proxy from pdb1@link_cdb2
*
ERROR at line 1:
ORA-17628: Oracle error 65035 returned by remote Oracle server
ORA-65035: unable to create pluggable database from
--应用pdb需要使用 本地UNDO