[2022-04-24]NOCDB转换PDB步骤

背景，如果从11g数据库升级12c或以后，升级完成之后默认还是NOCDB，如果这个时候想把NOCDB转换为CDB，可以把NOCDB转换为一个已经存在的CDB的PDB，参考文档：Adopting a Non-CDB as a PDB

1、提前创建CDB

Create the CDB if it does not exist

提前创建一个CDB容器，后续把NOCDB数据库插入到新创建的CDB容器。
CDB创建过程略。

2、确保NOCDB的事务一致性

Ensure that the non-CDB is in a transactionally consistent state

3、启动NOCDB为read only模式

SQL> select cdb, name from v$database;

CDB NAME
--- ---------
NO  PROD

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.

Total System Global Area 1040187072 bytes
Fixed Size                  8904384 bytes
Variable Size             780140544 bytes
Database Buffers          243269632 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> 

4、生成数据库描述文件

Connect to the non-CDB, and run the DBMS_PDB.DESCRIBE procedure to construct an XML file that describes the non-CDB.
The current user must have SYSDBA administrative privilege. The user must exercise the privilege using AS SYSDBA at connect time.
For example, to generate an XML file named ncdb.xml in the /disk1/oracle directory, run the following procedure:
BEGIN
  DBMS_PDB.DESCRIBE(
    pdb_descr_file => '/disk1/oracle/ncdb.xml');
END;
/

After the procedure completes successfully, you can use the XML file and the non-CDB database files to plug the non-CDB into a CDB.

连接到NOCDB，使用DBMS_PDB.DESCRIBE生产xml格式的描述文件，连接NOCDB的用户需要有SYSDB权限。

SQL> exec dbms_pdb.describe(pdb_descr_file =>'/home/oracle/prod.xml');

PL/SQL procedure successfully completed.

SQL> 

5、NOCDB和CDB兼容校验

Run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether the non-CDB is compatible with the CDB.
When you run the function, set the following parameters:
pdb_descr_file - Set this parameter to the full path to the XML file.
pdb_name - Specify the name of the new PDB. If this parameter is omitted, then the PDB name in the XML file is used.
For example, to determine whether a non-CDB described by the /disk1/oracle/ncdb.xml file is compatible with the current CDB, run the following PL/SQL block:
SET SERVEROUTPUT ON
DECLARE
  compatible CONSTANT VARCHAR2(3) := 
    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
           pdb_descr_file => '/disk1/oracle/ncdb.xml',
           pdb_name       => 'NCDB')
    WHEN TRUE THEN 'YES'
    ELSE 'NO'
END;
BEGIN
  DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
If the output is YES, then the non-CDB is compatible, and you can continue with the next step. If the output is NO, then the non-CDB is not compatible, and you can check the PDB_PLUG_IN_VIOLATIONS view to see why it is not compatible. All violations must be corrected before you continue. For example, any version or patch mismatches should be resolved by running an upgrade or the datapatch utility. After correcting the violations, run DBMS_PDB.CHECK_PLUG_COMPATIBILITY again to ensure that the non-CDB is compatible with the CDB.

连接CDB数据库，运行CHECK_PLUG_COMPATIBILITY校验NOCDB和CDB是否兼容，预期结果应该为YES，pdb_descr_file为第4步生成的prod.xml， pdb_name可以随便指定一个CDB中不存在的PDB名字。

[oracle@oracle19cnoadg ~]$ export ORACLE_SID=prodpdb
[oracle@oracle19cnoadg ~]$ o

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 24 11:12:37 2022
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          READ WRITE NO
SQL> SET SERVEROUTPUT ON
DECLARE
  compatible CONSTANT VARCHAR2(3) := 
    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
           pdb_descr_file => '/home/oracle/prod.xml',
           pdb_name       => 'NCDB')
    WHEN TRUE THEN 'YES'
    ELSE 'NO'
END;
BEGIN
  DBMS_OUTPUT.PUT_LINE(compatible);
END;
/SQL>   2    3    4    5    6    7    8    9   10   11   12  
YES

PL/SQL procedure successfully completed.

SQL> 

6、关闭NOCDB数据库

Shut down the non-CDB

SQL> show pdbs
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

7、把NOCDB插入CDB中

Plug in the non-CDB.
For example, the following SQL statement plugs in a non-CDB, copies its files to a new location, and includes only the tbs3 user tablespace from the non-CDB:
CREATE PLUGGABLE DATABASE ncdb USING '/disk1/oracle/ncdb.xml'
  COPY
  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/', '/disk2/oracle/ncdb/')
  USER_TABLESPACES=('tbs3');
If there are no violations, then do not open the new PDB. You will open it in the following step.

The USER_TABLESPACES clause enables you to separate data that was used for multiple tenants in a non-CDB into different PDBs. You can use multiple CREATE PLUGGABLE DATABASE statements with this clause to create other PDBs that include the data from other tablespaces that existed in the non-CDB.

SQL> CREATE PLUGGABLE DATABASE ncdb USING '/home/oracle/prod.xml'
  2    COPY
  3    FILE_NAME_CONVERT = ('/oracle/app/oradata/PROD/datafile/', '/oracle/app/oradata/PRODPDB/datafile');

Pluggable database created.

SQL> 
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          READ WRITE NO
         4 NCDB                           MOUNTED

这个时候不要打开NCDB容器。

注意：

     这里创建PDB的时候，可以使用USER_TABLESPACES参数，该参数的作用就是如果NOCDB有多个表空间，如果在迁移PDB的时候，不同的表空间想分为不同的PDB，这个时候就可以指定该参数了，比如源库有两个业务表空间， TEST、TEST_1，如果这个时候想把TEST表空间迁移到PDB02，TEST_1表空间迁移到PDB03，就可以使用如下方法：

NOCDB数据库创建两个表空间

SQL> create tablespace test datafile '/oracle/app/oradata/PROD/datafile/test.dbf' size 10M; 

Tablespace created.

SQL> create table test (id int) tablespace test;

Table created.

SQL> insert into test values (10);

1 row created.

SQL> commit;

Commit complete.

SQL>

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST

6 rows selected.

SQL> create tablespace test_1 datafile '/oracle/app/oradata/PROD/datafile/test_1.dbf' size 10M;

Tablespace created.

SQL> 
SQL> create table test_1 (id int) tablespace test_1;

Table created.

SQL> insert into test_1 values (10);

1 row created.

SQL> commit;

Commit complete.

SQL>

在插入CDB容器时，PDB02使用USER_TABLESPACES参数 ，只迁移TEST

SQL> CREATE PLUGGABLE DATABASE pdb02 USING '/home/oracle/prod.xml'
  2       COPY
  3        FILE_NAME_CONVERT = ('/oracle/app/oradata/PROD/datafile/', '/oracle/app/oradata/PRODPDB/datafile')
  4        USER_TABLESPACES=('test');

Pluggable database created.

SQL> 

最后的效果就是，在PDB02中，可以查询TEST表，而不能查询TEST_1表，但是查询DBA_OBJECTS视图是可以查询到TEST_1表的。

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          READ WRITE NO
         4 PDB02                          MOUNTED
SQL> alter pluggable database pdb02 open read write;

Pluggable database altered.

SQL> alter session set container=PDB02;

Session altered.

SQL> select * from test_1;
select * from test_1
              *
ERROR at line 1:
ORA-00376: file 20 cannot be read at this time
ORA-01110: data file 20: 'No file with this number, file does not exist'


SQL> select * from test;

        ID
----------
        10

SQL>  select object_id, object_name from dba_objects where object_name = 'TEST_1';


 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     87800 TEST_1

SQL> 

创建新的PDB03，只包含表空间TEST_1, 创建方式和PDB02有区别，需要加上as clone, 让新容器PDB03 产生新的唯一标识，否则会报错：

SQL> CREATE PLUGGABLE DATABASE pdb03 USING '/home/oracle/prod.xml'
  2           COPY
  3            FILE_NAME_CONVERT = ('/oracle/app/oradata/PROD/datafile/', '/oracle/app/oradata/PRODPDB/datafile')
  4            USER_TABLESPACES=('test_1');
CREATE PLUGGABLE DATABASE pdb03 USING '/home/oracle/prod.xml'
*
ERROR at line 1:
ORA-65122: Pluggable database GUID conflicts with the GUID of an existing
container.

SQL> CREATE PLUGGABLE DATABASE pdb03 as clone USING '/home/oracle/prod1.xml'
  2           COPY
  3            FILE_NAME_CONVERT = ('/oracle/app/oradata/PROD/datafile/', '/oracle/app/oradata/PRODPDB03/datafile')
  4            USER_TABLESPACES=('test_1');

Pluggable database created.

SQL> 

这样新创建的PDB03就只包含了TEST_1表空间的对象，测试如下：

SQL> alter session set container=PDB03;

Session altered.

SQL> select * from test_1;

        ID
----------
        10

SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00376: file 39 cannot be read at this time
ORA-01110: data file 39: 'No file with this number, file does not exist'


SQL> 

SQL> drop table test;

Table dropped.

SQL> 

8、noncdb_to_pdb.sql转换完成NOCDB到CDB

Run the ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script. This script must be run before the PDB can be opened for the first time.
If the PDB was not a non-CDB, then running the noncdb_to_pdb.sql script is not required. To run the noncdb_to_pdb.sql script, complete the following steps:
Access the PDB.
The current user must have SYSDBA administrative privilege, and the privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS SYSDBA at connect time.
Run the noncdb_to_pdb.sql script:
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
The script opens the PDB, performs changes, and closes the PDB when the changes are complete.

首先使用具有SYSDBA权限的用户连接到PDB， 然后运行noncdb_to_pdb.sql

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          READ WRITE NO
         4 NCDB                           MOUNTED
SQL> alter session set container=NCDB;

Session altered.

SQL> 
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

--此步运行时间比较长

9、打开新插入的PDB

Open the new PDB in read/write mode

SQL> alter pluggable database NCDB open;

Pluggable database altered.

SQL> 

10、备份新插入的PDB

Back up the PDB.

略