ITPub博客

首页 > 数据库 > Oracle > Oracle 12c Non CDB 数据库切换成PDB

Oracle 12c Non CDB 数据库切换成PDB

原创 Oracle 作者:pingdanorcale 时间:2020-08-05 16:23:56 0 删除 编辑

数据库从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)


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10201716/viewspace-2709371/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-01-02

  • 博文量
    58
  • 访问量
    112627