克隆创建PDB--（2）远程克隆创建PDB

[oracle@12cr2 dbs]$ export ORACLE_SID=newcdb
[oracle@12cr2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 4 15:26:43 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  8798312 bytes
Variable Size             310382488 bytes
Database Buffers          515899392 bytes
Redo Buffers                3780608 bytes
Database mounted.
Database opened.
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO

源CDB:       zylong        源PDB:     TEST_PDB
目标CDB:   newcdb      目标PDB:  NEW_TEST_PDB

1. 配置目标端的监听

[oracle@12cr2 dbs]$ lsnrctl status

Service "test_pdb" has 1 instance(s).
  Instance "zylong", status READY, has 1 handler(s) for this service...

[oracle@12cr2 admin]$ vi tnsnames.ora
TEST_PDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.81)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test_pdb)
    )
  )

[oracle@12cr2 admin]$ tnsping TEST_PDB

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 04-JUN-2017 15:36:03
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.81)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = test_pdb)))
OK (0 msec)

2. 目标端测试监听连接出现的小问题

[oracle@12cr2 ~]$ sqlplus pdb_mgr/oracle@TEST_PDB
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 4 15:42:13 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SEED_PDB                       MOUNTED
         4 NONCDB_PDP                     MOUNTED
         5 CLON_PDB                       MOUNTED
         6 TEST_PDB                       READ WRITE YES
         7 PDB4                           READ WRITE YES

SQL> alter session set container=test_pdb;
Session altered.

SQL> shutdown immediate
Pluggable Database closed.

SQL> startup
Warning: PDB altered with errors.
Pluggable Database opened.

SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONs order by name;

NAME
-------------------------------------------------------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
-------------------------------------------------------------------------------
STATUS
---------
TEST_PDB
Sync Failure                                                     ERROR
Sync PDB failed with ORA-959 during 'create user c##cdbadmin identified by * def
ault tablespace users temporary tablespace temp container = all'
PENDING

##这是之前创建的common user，当前PDB中没有USERS表空间，为了不影响实验，将此user删除

SQL> conn / as sysdba
Connected.
SQL> drop user c##cdbadmin cascade;
User dropped.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size                  8792152 bytes
Variable Size             436209576 bytes
Database Buffers          754974720 bytes
Redo Buffers                7983104 bytes
Database mounted.
Database opened.

SQL> Alter pluggable database test_pdb open;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SEED_PDB                       MOUNTED
         4 NONCDB_PDP                     MOUNTED
         5 CLON_PDB                       MOUNTED
         6 TEST_PDB                       READ WRITE NO
         7 PDB4                           MOUNTED
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

3. 目标端测试监听连接

[oracle@12cr2 ~]$ sqlplus pdb_mgr/oracle@TEST_PDB
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 4 15:51:12 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>

4. 目标端创建DBLINK

[oracle@12cr2 admin]$ export ORACLE_SID=newcdb
[oracle@12cr2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 4 15:57:25 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create public database link testpdb_newcdb connect to pdb_mgr identified by oracle using 'TEST_PDB';
Database link created.

5. 目标端测试DBLINK出现的小问题

SQL> select cdb from v$database@testpdb_newcdb;
select cdb from v$database@testpdb_newcdb
                *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from TESTPDB_NEWCDB

[oracle@12cr2 admin]$ export ORACLE_SID=zylong
[oracle@12cr2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 4 16:09:43 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container=test_pdb;
Session altered.

SQL> grant dba to pdb_mgr;
Grant succeeded.

##授予DBA权限之后，在dblink端的报错窗口查询依然报错，必须退出后重连：

[oracle@12cr2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 4 16:16:22 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select cdb from v$database@testpdb_newcdb;
CDB
---
YES

SQL> select name from v$pdbs@testpdb_newcdb;
NAME
-------------------------------------------------------------------------------
TEST_PDB

6. 远程克隆PDB

SQL> CREATE PLUGGABLE DATABASE testpdb_newcdb FROM test_pdb@testpdb_newcdb
  2  file_name_convert=('/u01/app/oracle/oradata/zylong/test_pdb','/u01/app/oracle/oradata/newcdb/testpdb_newcdb');
Pluggable database created.

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TESTPDB_NEWCDB                 MOUNTED