######################克隆方式dblink###############################
环境:
源端(非cdb) :192.168.1.101 sid:slnngk 非归档模式 数据库版本:19.3.0.0
目标端(cdb) :192.168.1.100 sid:ora19c 非归档模式 数据库版本:19.3.0.0
1.目标端创建到源端的dblink
在目标端机器上操作
tnsnocdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = slnngk)
)
)
2.赋予创建dblink使用的用户具有如下权限
在源端机器上执行
SQL> grant create pluggable database to system;
Grant succeeded.
3.目标库创建DB link
在cdb模式下创建
SQL> connect / as sysdba
Connected.
SQL> create public database link link_to_nocdb connect to system identified by oracle using 'tnsnocdb';
Database link created.
通过dblink可以查询得到源端非cdb下的表数据
SQL> select * from hxl.tb_test@link_to_nocdb;
ID NAME
---------- --------------------------------
1 name1
2 name2
3 name3
4 name4
5 name5
6 name6
6 rows selected.
4.将源库设置为read only
SQL>shutdown immediate;
SQL>startup open read only;
5.在目标端使用db link克隆远程数据库
在目标端上执行
SQL> create pluggable database pdb1 from NON$CDB@link_to_nocdb file_name_convert=('/u01/app/oracle/oradata/SLNNGK','/u01/app/oracle/oradata/ORA19C/pdb1');
Pluggable database created.
目标机器上的pdb1目录会自动创建
6.尝试打开pdb1
在目标端上执行
SQL> alter pluggable database pdb1 open;
Warning: PDB altered with errors.
查看失败原因
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONs order by name;
NAME
--------------------------------------------------------------------------------
CAUSE TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB$SEED
SQL Patch ERROR
'19.3.0.0.0 Release_Update 1904101227' is installed in the CDB but no release up
dates are installed in the PDB
RESOLVED
NAME
--------------------------------------------------------------------------------
CAUSE TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB1
Non-CDB to PDB ERROR
PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
PENDING
NAME
--------------------------------------------------------------------------------
CAUSE TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
7.目标端执行noncdb_to_pdb.sql
在目标端上执行
SQL>alter session set container=pdb1;
Session altered.
SQL>@/u01/app/oracle/product/19.3.0.0/db_1/rdbms/admin/noncdb_to_pdb.sql
执行该过程需要些时间,执行完成后pdb会自动打开成write模式
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 PDB1 READ WRITE YES
查看数据
SQL> select * from hxl.tb_test;
ID NAME
---------- --------------------------------
1 name1
2 name2
3 name3
4 name4
5 name5
6 name6
6 rows selected.