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