oracle克隆pdb,ORACLE_CDB/PDB克隆迁移转换

Oracle 12c PDB/CDB相关测试

1. 本地克隆pdb

1.1 环境检查

源环境和目标环境执行

数据库版本12.2.0.1.0.

$ sqlplus / as sysdba

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 ORCLPDB                 READ WRITE NO

4 TESTCLONEPDB              MOUNT NO

1.2 环境克隆

$ sqlplus / as sysdba

SQL> alter session set container= TESTCLONEPDB;

Session altered.

SQL> alter database open read only;

Database altered.

SQL > exit;

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 ORCLPDB                 READ WRITE NO

4 TESTCLONEPDB              READ WRITE NO

SQL> exit

SQL> create pluggable database testpdb from ORCLPDB file_name_convert=('orclpdb','testpdb');

Pluggable database created.

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 ORCLPDB                 READ WRITE NO

4 TESTCLONEPDB              READ WRITE NO

5 TESTPDB                   MOUNTED

SQL> alter session set container= TESTPDB;

Session altered.

SQL> show pdbs

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

5 TESTPDB                   MOUNTED

SQL> alter database open;

Database altered.

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

5 TESTPDB                   READ WRITE NO

2. 跨服务器克隆pdb

2.1 本地复制异地进行插入方式

2.1.1 源环境克隆

源端执行：

$ sqlplus / as sysdba

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 ORCLPDB                 READ WRITE NO

SQL>

SQL> alter pluggable database ORCLPDB close;

Pluggable database altered.

SQL> alter pluggable database ORCLPDB open read only;

Pluggable database altered.

SQL> create pluggable database ORCLPDB_clone from ORCLPDB

2  file_name_convert=('/u02/app/oracle/oradata/orcl/orclpdb/','/u02/app/oracle/oradata/orcl/orclpdb_clone/');

Pluggable database created.

SQL> alter pluggable database ORCLPDB close;

Pluggable database altered.

SQL> alter pluggable database ORCLPDB open;

Pluggable database altered.

SQL> alter pluggable database ORCLPDB_clone open;

Pluggable database altered.

2.1.2 源环境导出XML文件

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 ORCLPDB                 READ WRITE NO

4 ORCLPDB_CLONE            READ WRITE NO

SQL> alter pluggable database ORCLPDB_CLONE close;

Pluggable database altered.

SQL> alter pluggable database ORCLPDB_CLONE unplug into '/u02/app/oracle/oradata/orcl/orclpdb_clone/orclpdb_clone.xml';

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 ORCLPDB                 READ WRITE NO

4 ORCLPDB_CLONE            MOUNTED

2.1.3 复制文件到目标环境

-- 目标端执行

在目标端创建与源端一致的数据文件路径；

$ mkdir -p /u02/app/oracle/oradata/orcl/orclpdb_clone

$ mkdir -p /u02/app/oracle/oradata/TEST/orclpdb/

$ cd /u02/app/oracle/oradata/orcl/orclpdb_clone

$ scp root@192.168.1.161:/u02/app/oracle/oradata/orcl/orclpdb_clone/* .

2.1.4 目标环境插入PDB

-- 目标端执行

[oracle@pdb2 orclpdb]$ sqlplus / as sysdba

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 TESTPDB                   READ WRITE NO

SQL> create pluggable database orclpdb_clone using '/u02/app/oracle/oradata/orcl/orclpdb_clone/orclpdb_clone.xml'

2  file_name_convert=('/u02/app/oracle/oradata/orcl/orclpdb_clone/','/u02/app/oracle/oradata/TEST/orclpdb/')

3  copy;

Pluggable database created.

2.1.5 检查目标环境

-- 目标端执行

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 TESTPDB                   READ WRITE NO

5 ORCLPDB_CLONE            MOUNTED

SQL> alter pluggable database ORCLPDB_CLONE open;

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 TESTPDB                   READ WRITE NO

5 ORCLPDB_CLONE            READ WRITE NO

2.1.6 删除源环境的备份

-- 在源端执行

$ sqlplus / as sysdba

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 ORCLPDB                 READ WRITE NO

4 ORCLPDB_CLONE            MOUNTED

SQL> drop pluggable database ORCLPDB_CLONE;

Pluggable database dropped.

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 ORCLPDB                 READ WRITE NO

-- 在源端执行

$ cd /u02/app/oracle/oradata/orcl

$ ls -al orclpdb_clone

$ rm -rf orclpdb_clone

2.2 dblink方式

2.2.1 源端创建用户

--源端

SQL> show pdbs;

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 DEMOPDB                READ WRITE NO

4 TESTPDB                   READ WRITE NO

SQL> create user c##andy identified by andy container=all;

User created.

SQL> grant dba to c##andy container=all;

Grant succeeded.

2.2.2 目标端创建DBLINK

-- 目标端cdb执行

SQL> create database link clonepdb connect to c##andy identified by andy using '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.165)(PORT = 1522))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME =demo)))';

Database link created.

2.2.3 目标端执行克隆

-- 目标端cdb

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 ORCLPDB                 READ WRITE NO

SQL> alter session set pdb_file_name_convert='testpdb','testclonepdb';

Session altered.

-- 通过 dblink 克隆源端的 pdb

SQL> create pluggable database testclonepdb from TESTPDB@clonepdb;

Pluggable database created.

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 ORCLPDB                 READ WRITE NO

4 TESTCLONEPDB              MOUNTED

SQL>

SQL> alter pluggable database TESTCLONEPDB open;

Pluggable database altered.

2.2.4 检查目标环境

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 ORCLPDB                 READ WRITE NO

4 TESTCLONEPDB              READ WRITE NO

3. NO CDB转换成CDB

3.1 源环境检查

SQL> select name, decode(cdb, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option" , open_mode, con_id from v$database;

NAME      Multitenant Option       OPEN_MODE                  CON_ID

--------- -------------------------- -------------------- ----------

DEV   Regular 12c Database:      READ WRITE             0

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/DEV/system01.dbf

/u01/app/oracle/oradata/DEV/sysaux01.dbf

/u01/app/oracle/oradata/DEV/undotbs01.dbf

/u01/app/oracle/oradata/DEV/users01.dbf

3.2 搭建目标数据库

3.3 迁移

源环境

SQL> shutdown immediate;

SQL> alter database open read only;

SQL> begin

dbms_pdb.describe(PDB_DESCR_FILE => '/u01/test/ DEV.xml');

end;

/

目标环境

SQL> create pluggable database ORCL using '/u01/test/DEV.xml' file_name_convert=('/u01/app/oracle/oradata/DEV','/u01/app/oracle/oradata/ORCL/ORCLpdb');

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 ORCL                       MOUNTED

SQL> alter session set container=ORCL;

Session altered.

SQL>

SQL> @?/rdbms/admin/noncdb_to_pdb.sql

SQL> alter pluggable database ORCL open;

3.4 目标环境检查

SQL> show pdbs;

CON_ID CON_NAME                   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                READ ONLY  NO

3 ORCL                       READ WRITE NO