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)
