通过dblink跨CDB复制PDB

2020年2月1日2020年12月11日 handsup

1、在tnsnames.ora中添加连接PDB2_WHS，过程略。

2、在目标CDB创建dblink

[oracle@cat1 ~]$ export ORACLE_SID=whss1
[oracle@cat1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 1 15:18:12 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2650796208 bytes
Fixed Size                  9138352 bytes
Variable Size             620756992 bytes
Database Buffers         2013265920 bytes
Redo Buffers                7634944 bytes
Database mounted.
Database opened.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
SQL> create database link cppdb connect to system identified by oracle using 'PDB2_WHS';

Database link created.

SQL>  SELECT * FROM DUAL@cppdb;

DUM
---
X

3、在目标库复制PDB

SQL> CREATE PLUGGABLE DATABASE PDB2_WHS FROM PDB2_WHS@cppdb ;
CREATE PLUGGABLE DATABASE PDB2_WHS FROM PDB2_WHS@cppdb
*
ERROR at line 1:
ORA-17628: Oracle error 1031 returned by remote Oracle server
ORA-01031: insufficient privileges

以上创建数据库失败，为system用户没有权限，需要到源pdb数据库上赋权：
[oracle@cat1 admin]$ export ORACLE_SID=whs11
[oracle@cat1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 1 15:52:52 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1_WHS                       MOUNTED
         4 PDB2_WHS                       READ WRITE NO
         5 PDB3_WHS                       MOUNTED
SQL> alter session set container=PDB2_WHS;

SQL> grant create pluggable database to system;

Grant succeeded.

SQL>

再次登录目标数据库，创建pdb

SQL> CREATE PLUGGABLE DATABASE PDB2_WHS FROM PDB2_WHS@cppdb ;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDB2_WHS                       MOUNTED
SQL> alter session set container=PDB2_WHS;

Session altered.

SQL> startup
Pluggable Database opened.
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA2/WHSS/B5633DCD0104C05EE0532900A8C0652C/DATAFILE/system.278.1058025469
+DATA2/WHSS/B5633DCD0104C05EE0532900A8C0652C/DATAFILE/sysaux.279.1058025469
+DATA2/WHSS/B5633DCD0104C05EE0532900A8C0652C/DATAFILE/undotbs1.276.1058025469
+DATA2/WHSS/B5633DCD0104C05EE0532900A8C0652C/DATAFILE/undo_2.277.1058025469
+DATA2/WHSS/B5633DCD0104C05EE0532900A8C0652C/DATAFILE/user02.273.1058025469
+DATA2/WHSS/B5633DCD0104C05EE0532900A8C0652C/DATAFILE/users.274.1058025469
+DATA2/WHSS/B5633DCD0104C05EE0532900A8C0652C/DATAFILE/undotbs2.275.1058025469

7 rows selected.