导出non-cdb作为一个pdb到cdb中。（change a non-cdb to a pdb）

今天做cdb,pdb的另外一个实验。
就是将non-cdb作为一个pdb导入到cdb当中去。

我现在的库是cdb的库，所以需要切换成non-cdb。

export ORACLE_SID=testdb

SQL> show con_name;

CON_NAME
------------------------------
testdb

停止这个库，然后用read-only方式启动

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup open read only;
ORACLE instance started.

SQL>

导出non-cdb的信息到XML文件。

SQL> BEGIN
  2    DBMS_PDB.DESCRIBE(
  3      pdb_descr_file => '/tmp/db12c.xml');
  4  END;
  5  /

PL/SQL procedure successfully completed.

确认一下这个文件是否导出。

[root@localhost tmp]# ls -ltr
total 24
srwxr-xr-x 1 root   root        0 Mar  9 22:57 mapping-root
drwxr-xr-x 2 root   root     4096 Mar 10 02:27 image
drwx------ 2 root   root     4096 Mar 10 05:01 gconfd-root
drwxr-xr-x 2 oracle oinstall 4096 Mar 10 21:47 hsperfdata_oracle
**-rw-r--r-- 1 oracle oinstall 5686 Mar 10 21:47 db12c.xml**

好了，关闭这个non-cdb。

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

切换回cdb的testdb1.

export ORACLE_SID=testdb1

确认一下。

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL>

导入XML文件，嗯？出错了.

SQL> CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/db12c.xml'
  2    COPY
  3    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/testdb/', '/u01/app/oracle/oradata/TESTDB1/pdb2/');
CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/db12c.xml'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access

状态是OPEN的啊，为什么。。。

SQL> CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/db12c.xml'
  2    COPY
  3    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/testdb/', '/u01/app/oracle/oradata/TESTDB1/pdb2/');
CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/db12c.xml'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access


SQL> select status from v$instance;

STATUS
------------
OPEN

重启一下试试。

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             562039536 bytes
Database Buffers          268435456 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL>

再次执行导入XML，成功了。。。

SQL> CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/db12c.xml'
  2    COPY
  3    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/testdb/', '/u01/app/oracle/oradata/TESTDB1/pdb2/');

Pluggable database created.

SQL>

看看文件系统。

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB_PLUG_NOCOPY                MOUNTED
         4 PDB2                           MOUNTED
SQL>

SQL> select name from v$datafile where con_id=4;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TESTDB1/pdb2/system01.dbf
/u01/app/oracle/oradata/TESTDB1/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/TESTDB1/pdb2/users01.dbf

对比下文件大小

[oracle@localhost ~]$ ll /u01/app/oracle/oradata/testdb/
total 2435276
-rw-r----- 1 oracle oinstall   10371072 Mar 10 21:53 control01.ctl
-rw-r----- 1 oracle oinstall   10371072 Mar 10 21:53 control02.ctl
-rw-r----- 1 oracle oinstall   10371072 Mar 10 21:53 control03.ctl
-rw-r----- 1 oracle oinstall   52429312 Mar 10 21:44 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Mar 10 21:45 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Mar 10 21:44 redo03.log
-rw-r----- 1 oracle oinstall  545267712 Mar 10 21:45 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1017126912 Mar 10 21:45 system01.dbf
-rw-r----- 1 oracle oinstall   79699968 Mar 10 04:49 temp01.dbf
-rw-r----- 1 oracle oinstall  707796992 Mar 10 21:45 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Mar 10 21:45 users01.dbf
[oracle@localhost ~]$ ll /u01/app/oracle/oradata/TESTDB1/pdb2/
total 1532476
-rw-r----- 1 oracle oinstall  545267712 Mar 10 22:11 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1017126912 Mar 10 22:11 system01.dbf
-rw-r----- 1 oracle oinstall   20979712 Mar 10 22:11 temp01.dbf
-rw-r----- 1 oracle oinstall    5251072 Mar 10 22:11 users01.dbf

主要的数据文件大小是一样的。

---

切换到这个pdb2,然后执行脚本noncdb_to_pdb.sql去清除不属于这个pdb的object，这里需要一段时间。

SQL> alter session set container=pdb2;

Session altered.

SQL>
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
....

PL/SQL procedure successfully completed.

看到清理程序已经完成了。

最后一步，启动PDB2。

SQL>
SQL> ALTER SESSION SET CONTAINER=pdb2;

Session altered.

SQL> ALTER PLUGGABLE DATABASE OPEN;

Pluggable database altered.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB2                           READ WRITE

1 row selected.

SQL>