ORACLE 19C NONCDB 转 PDB

原创于 2025-02-27 19:52:42 发布 · 383 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

使用DBMS_PDB.DESCRIBE ，将非CDB 库插入到CDB 中

主机信息

角色	主机名	IP地址	数据库版本	实例名	DB名
NONCDB	test01	192.168.65.169	19.30	orcltest01	orcltest01
CDB	echodd	192.168.65.168	19.30	orcl	orcl

NONCDB 库操作

1、以只读模式打开：

SQL> startup mount;
ORACLE instance started.

Total System Global Area 4731172128 bytes
Fixed Size                  8906016 bytes
Variable Size             872415232 bytes
Database Buffers         3841982464 bytes
Redo Buffers                7868416 bytes
Database mounted.

// 开启到只读状态
SQL> alter database open read only;
Database altered.

2、执行 DBMS_PDB.DESCRIBE 包，创建 XML 文件，其中包含有关 NONCDB 数据库数据文件的描述：

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

PL/SQL procedure successfully completed.

3、关闭 NONCDB 数据库：

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

注意：这里不能打开数据库，详细原因在后面，先看

4、完整拷贝数据文件目录和 XML文件拷贝到目标端：

[oracle@test01:/home/oracle]$ scp testdb01.xml 192.168.65.168:/home/oracle
oracle@192.168.65.168's password: 
testdb01.xml                   100% 6945     7.2MB/s   00:00  
[oracle@test01:/home/oracle]$ cd /u01/app/oracle/oradata
[oracle@test01:/u01/app/oracle/oradata]$ scp -r ORCLTEST01 192.168.65.168:/oradata
oracle@192.168.65.168's password: 
system01.dbf                                                                                                                                100%  990MB 123.7MB/s   00:08    
sysaux01.dbf                                                                                                                                100%  710MB 177.4MB/s   00:04    
undotbs01.dbf                                                                                                                               100%  605MB 121.0MB/s   00:05    
users01.dbf                                                                                                                                 100% 5128KB 114.3MB/s   00:00    
control01.ctl                                                                                                                               100%   10MB 162.5MB/s   00:00    
control02.ctl                                                                                                                               100%   10MB 165.3MB/s   00:00    
redo01.log                                                                                                                                  100%  200MB 100.0MB/s   00:02    
redo02.log                                                                                                                                  100%  200MB 199.9MB/s   00:01    
redo03.log                                                                                                                                  100%  200MB 199.9MB/s   00:01    
temp01.dbf                                                                                                                                  100%   95MB  95.0MB/s   00:01

CDB 库操作

1、执行 DBMS_PDB.CHECK_PLUG_COMPATIBILITY ，检查 NON-CDB 兼容性，修改新pdb名称：

SQL> set serveroutput on
DECLARE
  compatible CONSTANT VARCHAR2(3) := CASE dbms_pdb.check_plug_compatibility(pdb_descr_file => '/home/oracle/testdb01.xml',
                                                                        pdb_name       => 'Echodd')
                                       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.

注：输出 YES，代表正常兼容，可以正常转换

（如果返回 FALSE/NO，需在 PDB_PLUG_IN_VIOLATIONS 视图中检查）

select time, name, type, error_number error, status, line, message, cause, action from pdb_plug_in_violations order by time desc;

2、插入为新pdb：

// copy 创建
SQL> CREATE PLUGGABLE DATABASE echodd USING '/home/oracle/testdb01.xml'
    COPY FILE_NAME_CONVERT = ('/oradata/ORCLTEST01', '/oradata/ORCL/ECHODD');  2  

Pluggable database created.

// nocopy 创建
SQL> create pluggable database echodddb using '/home/oracle/testdb01.xml' nocopy tempfile reuse;

Pluggable database created.

注意：在上面生成XML文件后如果打开了数据库，关闭后再传输，会导致数据不一致，如下XML文件中记录的fcpsb值是2292714，而数据文件中的值是2292717）

SQL> CREATE PLUGGABLE DATABASE echodd USING '/home/oracle/testdb01.xml'
    COPY FILE_NAME_CONVERT = ('/oradata/ORCLTEST01', '/oradata/ORCL/ECHODD');
CREATE PLUGGABLE DATABASE echodd USING '/home/oracle/testdb01.xml'
    COPY FILE_NAME_CONVERT = ('/oradata/ORCLTEST01', '/oradata/ORCL/ECHODD')
*
ERROR at line 1:
ORA-65139: Mismatch between XML metadata file and data file
/oradata/ORCLTEST01/system01.dbf for value of fcpsb (2292714 in the plug XML
file, 2292717 in the data file)

3、连接到 pdb并执行noncdb_to_pdb.sql 脚本，对新创建的PDB进行必要的数据字典和信息转换：

SQL> show pdbs

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

Session altered.

SQL> @?/rdbms/admin/noncdb_to_pdb.sql
。。。。。。很长。。。等等等等。。。

4、打开 PDB：

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 ECHODD                         MOUNTED
SQL> alter pluggable database echodd open;

Pluggable database altered.

SQL> 
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 ECHODD                         READ WRITE NO
SQL> SELECT * FROM CUST;  //查看原库数据

   CUST_ID LAST_NAME                      FIRST_NAME
---------- ------------------------------ ------------------------------
         1 Smith                          John
         2 Jones                          Jane
         3 Brown                          Michael
         4 Davis                          Emily
         5 Wilson                         David

5 rows selected.

完成。

注：

1、如果是copy方式，会在目标路径下生成如下文件：

[root@echodd:/oradata/ORCL/echodd]# ll
total 1054808
-rw-r----- 1 oracle oinstall 482353152 Feb 25 21:01 sysaux01.dbf
-rw-r----- 1 oracle oinstall 377495552 Feb 25 21:01 system01.dbf
-rw-r----- 1 oracle oinstall  84942848 Feb 25 20:37 temp01.dbf
-rw-r----- 1 oracle oinstall 214966272 Feb 25 21:01 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Feb 25 21:01 users01.dbf

2、如果是nocopy方式，还会复用传输过来的文件，但是control、redo不再使用：

[root@echodd:/oradata/ORCLTEST01]# ll
total 3446036
-rw-r----- 1 oracle oinstall   10600448 Feb 27 17:47 control01.ctl
-rw-r----- 1 oracle oinstall   10600448 Feb 27 17:47 control02.ctl
-rw-r----- 1 oracle oinstall  209715712 Feb 27 17:47 redo01.log
-rw-r----- 1 oracle oinstall  209715712 Feb 27 17:47 redo02.log
-rw-r----- 1 oracle oinstall  209715712 Feb 27 17:47 redo03.log
-rw-r----- 1 oracle oinstall  744497152 Feb 27 19:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1038098432 Feb 27 19:05 system01.dbf
-rw-r----- 1 oracle oinstall   99622912 Feb 27 17:56 temp01.dbf
-rw-r----- 1 oracle oinstall  990912512 Feb 27 19:04 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Feb 27 18:01 users01.dbf