nocdb转换cdb

Non-CDB name: orcl

CDB name: orclcdb

version: 12c or later

1 创建cdb

略

2.只读方式打开数据库（non-CDB）

SQL> shutdown immediate;

SQL> startup open read only;

3.生成xml文件

这个xml文件可以理解为non-CDB的模板文件

执行以下语句

SQL> BEGIN

DBMS_PDB.DESCRIBE(pdb_descr_file => '/home/oracle/19cNonPDB.xml');

END;

/

4.关闭数据库(non-CDB)

SQL> shutdown immediate;

为了保持元数据文件和数据文件的一致性，不要再次打开数据库，否则会报错

ORA-65139: Mismatch between XML metadata file and data file

5.在目标CDB中检查兼容性

$ export ORACLE_SID=orclcdb

$ sqlplus / as sysdba

SET SERVEROUTPUT ON;

DECLARE

compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/home/oracle/19cNonPDB.xml')

WHEN TRUE THEN 'YES'

ELSE 'NO'

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(compatible);

END;

/

执行上述过程如果报错，需要检查PDB_PLUG_IN_VIOLATIONS视图

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='orcl';

会得到类似下面的结果

ERROR PSU bundle patch 1 (PSU Patch 12345): Installed in the CDB but not in the PDB.

处理过程请参考MOS Note 1935365.1

6.在CDB中创建一个PDB

$ export ORACLE_SID=orclcdb

$ sqlplus / as sysdba

SQL> CREATE PLUGGABLE DATABASE PDB1 USING '/home/oracle/19cNonPDB.xml' COPY FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl/', '/u01/app/oracle/oradata/ORCLCDB/PDB1/');

在创建 pdb1报错，

ERROR at line 1:

ORA-01276: Cannot add file

/oracle/oradata/ORCLCDB/PDB1/datafile/o1_mf_system_m0sg82ym_.dbf.  File has an

Oracle Managed Files file name.处理如下

 SQL> alter system set db_create_file_dest='/oracle/oradata/ORCLDB';

System altered.

SQL> CREATE PLUGGABLE DATABASE PDB1 USING '/home/oracle/19cNonPDB.xml';

7.切换到PDB容器，运行转换脚本

$ export ORACLE_SID=orclcdb

$ sqlplus / as sysdba

SQL> alter session set container=pdb1;

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

8.启动PDB，检查open mode

SQL> alter pluggable database pdb1 open;

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

----- ----------

PDB1        READ WRITE

1 row selected

打开情况下有报错

alter pluggable database testpdb open;

Warning: PDB altered with errors.

SQL> select message,time from pdb_plug_in_violations;

报错如下:

Character set mismatch: PDB character set AL32UTF8. CDB character set ZHS16GBK.

14-APR-24 11.44.34.608477 PM

pdb的字符集和cdb的字符集不匹配

解决如下:

--lang.sql:
col PROPERTY_NAME for a30
col PROPERTY_VALUE for a30
col DESCRIPTION for a30
select * from database_properties where PROPERTY_NAME='NLS_CHARACTERSET';
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBANBOB                       MOUNTED
         5 PDBTEST2_PROXY                 MOUNTED
         6 PDBWEEJAR                      MOUNTED
SQL> @lang
PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ ------------------------------
NLS_CHARACTERSET               AL32UTF8                       Character set
SQL> alter pluggable database PDBANBOB open;
Pluggable database altered.
SQL> alter session set container=pdbanbob;
Session altered.
SQL> @lang
PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ ------------------------------
NLS_CHARACTERSET               AL32UTF8                       Character set
SQL> alter database character set ZHS16GBK;
alter database character set ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
Database altered.
SQL> @lang 
PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ ------------------------------
NLS_CHARACTERSET               ZHS16GBK                       Character set