12C把non-CDB的單庫匯入到CDB裡面當成PDB的方法

官方文件

原理：

在non-CDB裡面建立xml檔案，這樣non-CDB的xml檔案和資料檔案就組成了一個unplugged PDB，再在CDB裡面執行create pluggable database orcl using建立這個PDB，切換到這個PDB執行指令碼$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql,清除不屬於這個PDB的object,就可以正常啟動這個PDB了

結論1:所以沒有辦法在RMAN裡面把non-CDB的備份restore成CDB裡面的一個PDB，因為需要先把這個non-CDB變成一個unplugged PDB

結論2:該non-CDB的sys和system密碼變成了和CDB一樣，原來CDB的sys和system密碼都是123456，non-CDB的sys和system密碼都是666666，把該non-CDB當成PDB匯入CDB後，該PDB的sys和system密碼都是123456

結論3:該non-CDB的歸檔模式變成了和CDB一樣，原來CDB是歸檔模式，non-CDB是非歸檔模式，把該non-CDB當成PDB匯入CDB後，該PDB變成歸檔模式

結論4:該non-CDB當成PDB匯入CDB後，每個檔案的頭部都會重寫，檔案號也會更改

同一臺伺服器上安裝了兩套例項，non-CDB例項名稱為orcl，CDB例項名稱為ocp，把orcl當成PDB匯入到CDB

[oracle@ocp12c ~]$ lsnrctl status

...

Service "ocp" has 1 instance(s).

  Instance "ocp", status READY, has 1 handler(s) for this service...

Service "orcl" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

Service "pocp1" has 1 instance(s).

  Instance "ocp", status READY, has 1 handler(s) for this service...

Service "pocp2" has 1 instance(s).

  Instance "ocp", status READY, has 1 handler(s) for this service...

The command completed successfully

1、登入non-CDB，建立使用者user_orcl密碼為666666，system密碼為666666，建立表system.system2020

[oracle@ocp12c ~]$ ORACLE_SID=orcl

[oracle@ocp12c ~]$ sqlplus / as sysdba

SQL> show con_name;

CON_NAME

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

orcl

SQL> create user user_orcl identified by 666666;

SQL> grant dba to user_orcl;

SQL> conn system/666666

Connected.

SQL> create table system2020 as select * from dba_users;

2、關閉non-CDB資料庫並重啟至只讀狀態，再對non-CDB資料庫建立一個xml檔案

SQL> shutdown immediate;

SQL> startup open read only;

SQL> exec dbms_pdb.describe(pdb_descr_file=>'/home/oracle/orcl.xml');

3、關閉non-CDB資料庫

SQL> shutdown immediate;

4、登入CDB，必須要執行$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql，否則PDB啟動會有報錯

[oracle@ocp12c ~]$ ORACLE_SID=ocp

[oracle@ocp12c ~]$ sqlplus / as sysdba

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         4 POCP2                          READ WRITE NO

         5 POCP1                          READ WRITE NO

SQL> create pluggable database orcl using '/home/oracle/orcl.xml' nocopy tempfile reuse;

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 ORCL                           MOUNTED

         4 POCP2                          READ WRITE NO

         5 POCP1                          READ WRITE NO

SQL> select file_name from cdb_data_files a,cdb_pdbs b where a.con_id=b.con_id and b.pdb_name='ORCL';

no rows selected

SQL> select name from v$datafile a,cdb_pdbs b where a.con_id=b.con_id and b.pdb_name='ORCL';

NAME

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

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

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

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

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

SQL> alter session set container=ORCL;

Session altered.

SQL> startup

Warning: PDB altered with errors.

Pluggable Database opened.

SQL> shutdown immediate;

Pluggable Database closed.

SQL> startup

Warning: PDB altered with errors.

Pluggable Database opened.

SQL> shutdown immediate;

Pluggable Database closed.

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

SQL> startup

Pluggable Database opened.

--使用CDB的sys和system密碼才可以正常登入，原來non-CDB 的sys和system密碼變成了和CDB一樣

[oracle@ocp12c ~]$ sqlplus system/666666

ERROR:

ORA-01017: invalid username/password; logon denied

[oracle@ocp12c ~]$ sqlplus system/123456

SQL> 

[oracle@ocp12c ~]$ sqlplus sys/666666@pocp1 as sysdba

ERROR:

ORA-01017: invalid username/password; logon denied

[oracle@ocp12c ~]$ sqlplus sys/123456@pocp1 as sysdba

SQL> 

[oracle@ocp12c ~]$ lsnrctl status

...

Service "ocp" has 1 instance(s).

  Instance "ocp", status READY, has 1 handler(s) for this service...

Service "orcl" has 1 instance(s).

  Instance "ocp", status READY, has 1 handler(s) for this service...

Service "pocp1" has 1 instance(s).

  Instance "ocp", status READY, has 1 handler(s) for this service...

Service "pocp2" has 1 instance(s).

  Instance "ocp", status READY, has 1 handler(s) for this service...

The command completed successfully

原來的non-CDB可以啟動到mount模式，再啟動到open模式報錯

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

alert檔案資訊為

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_38299.trc:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

ORA-1157 signalled during: ALTER DATABASE OPEN...

2020-02-19T18:57:28.276363+08:00

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_38301.trc:

ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

ORA-01204: file number is 19 rather than 1 - wrong file

2020-02-19T18:57:30.142285+08:00

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_38301.trc:

ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'

ORA-01204: file number is 20 rather than 3 - wrong file

2020-02-19T18:57:31.286909+08:00

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_38301.trc:

ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/undotbs01.dbf'

ORA-01204: file number is 21 rather than 4 - wrong file

2020-02-19T18:57:31.649863+08:00

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_38301.trc:

ORA-01110: data file 7: '/u01/app/oracle/oradata/orcl/users01.dbf'

ORA-01204: file number is 22 rather than 7 - wrong file