将Oracle 12c非CDB数据库转换为CDB多租户体系结构

本文讲述了如何将Oracle 12c非容器数据库转换为多租户体系结构中的可插入数据库。Oracle已将所有将来的发行版移至多租户体系结构，因此最终将需要进行转换。

这些步骤是针对Oracle 12.1编写的；但是，更高版本的基本前提和步骤仍然相似。

---------------------------
--- ENVIRONMENT INFORMATION
ORACLE_HOME: /u01/app/oracle/product/12.1.0.2/
NON-CDB DATABASE NAME: SANDBOX
CDB DATABASE NAME: CDB1
PDB DATABASE NAME: SANDBOX
---------------------------

1.创建数据库参数文件并创建所有引用的目录路径

[oracle@bigdatalite ~] vi $ORACLE_HOME/dbs/initCDB1.ora
DB_NAME = CDB1
DB_BLOCK_SIZE = 8192
DB_CREATE_FILE_DEST = /u01/app/oracle/oradata
DB_RECOVERY_FILE_DEST = /u01/app/oracle/fast_recovery_area
CONTROL_FILES = ('/u01/app/oracle/oradata/CDB1/control01.ctl', '/u01/app/oracle/fast_recovery_area/CDB1/controlfile/control2.ctl')

2.将环境和启动数据库设置为不包含参数文件

export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
export ORACLE_SID=CDB1
sqlplus / as sysdba
SYS@CDB1> startup nomount pfile='$ORACLE_HOME/dbs/initCDB1.ora';

3.创建容器数据库

SYS@CDB1>
CREATE DATABASE CDB1
USER SYS IDENTIFIED BY <PASSWORD>
USER SYSTEM IDENTIFIED BY <PASSWORD>
CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP01
UNDO TABLESPACE UNDOTBS01
ENABLE PLUGGABLE DATABASE
/

4.运行catcdb.sql脚本创建CDB所需的所有组件

@?/rdbms/admin/catcdb.sql

5.准备要转换的非CDB环境。

第一步是关闭非CDB数据库，以只读模式打开，并创建CDB转换所需的XML文件。

export ORACLE_SID=SANDBOX
sqlplus / as sysdba
--- check number of invalid objects and compile (if necessary)
SYS@SANDBOX> select * from dba_objects where status <> 'VALID';

no rows selected

SYS@SANDBOX> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@SANDBOX> startup open read only;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size		    2932632 bytes
Variable Size		  377487464 bytes
Database Buffers	  687865856 bytes
Redo Buffers		    5455872 bytes
Database mounted.
Database opened.

SYS@SANDBOX> show con_name;

CON_NAME
------------------------------
SANDBOX

SYS@SANDBOX>
BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/SANDBOXNonPDB.xml');
END;
/

PL/SQL procedure successfully completed.

SYS@SANDBOX> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

6.在CDB上，检查可插拔兼容性。

[oracle@bigdatalite ~]$ export ORACLE_SID=CDB1
[oracle@bigdatalite ~]$ echo $ORACLE_SID
CDB1
[oracle@bigdatalite ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 30 22:40:13 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@CDB1> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SYS@CDB1> SET SERVEROUTPUT ON;
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/SANDBOXNonPDB.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;

YES

PL/SQL procedure successfully completed.

7.如果CHECK_PLUG_COMPATIBILITY返回“是”，则说明一切正常。
但是，查看PDB_PLUG_IN_VIOLATIONS视图是否有错误/警告，并根据需要进行更正。

SYS@CDB1>col cause for a20
col name for a20
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='SANDBOX'
/

NAME		     CAUSE		  TYPE
-------------------- -------------------- ---------
MESSAGE 			    STATUS
----------------------------------- ---------
SANDBOX 	     Non-CDB to PDB	  WARNING
PDB plugged in is a non-CDB,	    PENDING
requires noncdb_to_pdb.sql be run.

8.使用之前生成的XML文件创建可插拔数据库。

如有必要，您可以在此处包括FILE_NAME_CONVERT参数，以将数据文件路径转换为单独的位置。默认情况下，文件将被复制到上述DB_CREATE_FILE_DEST参数中指定的位置。此外，我选择使用默认的COPY参数来保留以前的文件，以便以后删除。如果要执行直接转换，请改为包含NOCOPY。

SYS@CDB1> CREATE PLUGGABLE DATABASE SANDBOX USING '/tmp/SANDBOXNonPDB.xml';

Pluggable database created.

9.运行$ ORACLE_HOME / rdbms / admin / noncdb_to_pdb.sql脚本

SYS@CDB1> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 SANDBOX			  MOUNTED
	 
SYS@CDB1> alter session set CONTAINER=SANDBOX;

Session altered.

SYS@CDB1> show con_name;

CON_NAME
------------------------------
SANDBOX

SYS@CDB1> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
...
PL/SQL procedure successfully completed.

10.打开pdb并检查潜在错误

SYS@CDB1> show error
No errors.

SYS@CDB1> show con_name;

CON_NAME
------------------------------
SANDBOX
SYS@CDB1> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SANDBOX                        MOUNTED

SYS@CDB1> ALTER PLUGGABLE DATABASE OPEN;

Pluggable database altered.

SYS@CDB1> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SANDBOX                        READ WRITE NO

SYS@CDB1> select message,time from pdb_plug_in_violations;

no rows selected

11.检查无效的对象并在必要时进行编译

SYS@CDB1> select * from dba_objects where status <> 'VALID';

no rows selected

就这么简单！现在，我们已经完成了从非容器架构到容器架构的迁移。

文章来源：https://www.virtual-dba.com/converting-oracle-12c-non-cdb-database-to-cdb-multitenant-architecture/