oracle12ccdb转换为pdb,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)