Oracle 12c Non CDB 數據庫切換成PDB

數據庫從10.2（11g）升級到12c或者在12c中創建的,數據庫就是NON CDB，和12c 之前的版本就沒有什么區別,所以一般會把12cnoncdb轉為pdb進行管理。

測試步驟如下：

一：源庫

1.

select name,CDB from v$database;

NAME      CDB

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

ERPDB     NO

SQL> 

SQL> set lines 150 

SQL> select * from v$version;

BANNER                                                                               CON_ID

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

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0

PL/SQL Release 12.1.0.2.0 - Production                                                    0

CORE    12.1.0.2.0      Production                                                                0

TNS for Linux: Version 12.1.0.2.0 - Production                                            0

NLSRTL Version 12.1.0.2.0 - Production                                                    0

注意：在轉換前先運行如下腳本

2.

@?/rdbms/admin/utluppkg.sql 

SET SERVEROUTPUT ON; 

exec dbms_preup.run_fixup_and_report('INVALID_SYS_TABLEDATA') 

exec dbms_preup.run_fixup_and_report('INVALID_USR_TABLEDATA');

如果不運行在執行noncdb_to_pdb.sql會報如下錯誤

SQL> SELECT rpad(u.name,128) TABLENAME, rpad(o.name,128) OWNER, 

2 rpad(c.name,128) COLNAME FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t, 

3 SYS.USER$ u 

4 WHERE o.OBJ# = t.OBJ# AND c.OBJ# = t.OBJ# AND c.COL# = t.COL# 

5 AND t.INTCOL# = c.INTCOL# AND BITAND(t.FLAGS, 256) = 256 

6 AND o.OWNER# = u.USER# AND o.OWNER# NOT IN 

7 (SELECT UNIQUE (d.USER_ID) FROM SYS.DBA_USERS d, SYS.REGISTRY$ r 

8 WHERE d.USER_ID = r.SCHEMA# and r.NAMESPACE=’SERVER’); 

WHERE d.USER_ID = r.SCHEMA# and r.NAMESPACE=’SERVER’) 

* 

ERROR at line 8: 

3.把數據庫啟動到只讀模式

startup open read only

4.生成xml 文件

BEGIN

DBMS_PDB.DESCRIBE(pdb_descr_file => '/home/oracle/scripts/erpdbPDB.xml');

END;

/

Shut immediate

二:目標庫

 1.檢查兼容性

SET SERVEROUTPUT ON;

DECLARE

compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/home/oracle/scripts/erpdbPDB.xml')

WHEN TRUE THEN 'YES'

ELSE 'NO'

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(compatible);

END;

/

2.檢查在Convert 時會出現的錯誤

elect name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='ERPDB’;

3.創建pluggable 數據庫

CREATE PLUGGABLE DATABASE  erpdb  USING '/home/oracle/scripts/erpdbPDB.xml'

     copy  

   FILE_NAME_CONVERT = ('/u01/oracle/oradata/erpdb/sys01.bdf', '/u01/app/oracle/oradata/pdbprod/erpdb/sys01.bdf',

    '/u01/oracle/oradata/erpdb/sy03.bdf', '/u01/app/oracle/oradata/pdbprod/erpdb/sys03.bdf',

    '/u01/oracle/oradata/erpdb/sy04.bdf', '/u01/app/oracle/oradata/pdbprod/erpdb/sys04.bdf',

    '/u01/oracle/oradata/erpdb/temp01.dbf','/u01/app/oracle/oradata/pdbprod/erpdb/temp01.dbf');

Pluggable database created.

4.運行腳本$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

SQL> SQL>  ALTER SESSION SET CONTAINER=erpdb;

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql(大概在40分鐘左右)

SQL> 

SQL> -- leave the PDB in the same state it was when we started

SQL> BEGIN

  2    execute immediate '&open_sql &restricted_state';

  3  EXCEPTION

  4    WHEN OTHERS THEN

  5    BEGIN

  6      IF (sqlcode <> -900) THEN

  7        RAISE;

  8      END IF;

  9    END;

 10  END;

 11  /

PL/SQL procedure successfully completed.

SQL> 

SQL> 

SQL> WHENEVER SQLERROR CONTINUE;

SQL>

show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         3 ERPDB                          MOUNTED

5.打開pdb數據庫

alter pluggable database  erpdb open ;

Pluggable database altered.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE

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

ERPDB                          READ WRITE

SQL> col cause for a20

SQL>  col name for a20

SQL> col message for a35 word_wrapped

SQL>  select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='ERPDB';

no rows selected

參考文檔

How to Convert Non-CDB to PDB Database in 12c - Testcase (文檔 ID 2012448.1)