19c+将NONCDB转换为PDB

可以使用拔出插入的方式将一个非CDB库转换为PDB.还可以使用克隆的方式.

NONCDB转换为PDB的的条件:

1.NONCDB和CDB的版本必须在12.1.0.2以上

2.字节顺序必须一致

下面测试这两种方法

一.插入NONCDB

1.检查数据库兼容性

1)在NONCDB创建xml文件

 

SQL> begin

dbms_pdb.describe(

pdb_descr_file=>'/home/oracle/nonecdb.xml');

end;

/  2    3    4    5  

PL/SQL procedure successfully completed

创建完成之后将xml文件拷贝到CDB系统中.

2)在CDB中进行兼容性检查

 

set serveroutput on

declare

    compatible constant varchar2(3):=

    case DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

        pdb_descr_file=>'/home/oracle/nonecdb.xml',

        pdb_name=>'nonecdb')

    WHEN TRUE THEN 'YES'

    ELSE 'NO'

end;

begin dbms_output.put_line(compatible);

end;

/

PL/SQL procedure successfully completed.

运行上面的代码检查是否有问题或者违反约束

3)查看表PDB_PLUG_IN_VIOLATIONS查看检查结果.

 

SQL> select message,action from pdb_plug_in_violations where name='NONECDB';

MESSAGE                                      ACTION

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

PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.          Run noncdb_to_pdb.sql.

CDB parameter sga_target mismatch: Previous 1552M Current 908M           Please check the parameter in the current CDB

CDB parameter open_cursors mismatch: Previous 300 Current 400            Please check the parameter in the current CDB

CDB parameter pga_aggregate_target mismatch: Previous 448M Current 303M      Please check the parameter in the current CDB

如上面的结果提示了需要运行nocdb_to_pdb.sql,还有一些参数由于PDB中会被CDB覆盖.

 

2.重新生成xml文件,并关闭原库

为了SCN和目标端的CDB一致.必须将原库打开到read only,再生成xml文件.生成完成之后就可以关闭原库了

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup read only;

SP2-0714: invalid combination of STARTUP options

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1627386464 bytes

Fixed Size          8897120 bytes

Variable Size         385875968 bytes

Database Buffers     1224736768 bytes

Redo Buffers            7876608 bytes

Database mounted.

SQL> alter database open read only;

Database altered.

SQL>  begin

dbms_pdb.describe(

pdb_descr_file=>'/home/oracle/nonecdb.xml');

end;

/   2    3    4    5  

PL/SQL procedure successfully completed.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

将生成的xml文件拷贝到目标端.

 

3.拷贝数据文件

将源端的数据文件目录拷贝到目标端指定位置:

 

[oracle@ora19c ORA19C]$ pwd

/u01/app/oracle/oradata/ORA19C

[oracle@ora19c ORA19C]$ scp -r ora19c2:/oradata/NONECDB .

oracle@ora19c2's password: 

control01.ctl                 100%   10MB  35.6MB/s   00:00    

control02.ctl                 100%   10MB  40.4MB/s   00:00    

redo01.log                    100%  200MB  46.5MB/s   00:04    

redo02.log                    100%  200MB  45.8MB/s   00:04    

redo03.log                    100%  200MB  43.8MB/s   00:04    

system01.dbf                  100%  700MB  31.5MB/s   00:22    

sysaux01.dbf                  100%  550MB  43.8MB/s   00:12    

undotbs01.dbf                 100%  230MB  42.8MB/s   00:05    

temp01.dbf                    100%   20MB  43.9MB/s   00:00    

users01.dbf                   100% 5128KB  41.8MB/s   00:00    

源端数据文件存放在/oradata/NONECDB,目标端存放在/u01/app/oracle/oradata/ORA19C/NONECDB

4.插入数据库

插入的语法如下:

 

SQL> create pluggable database nonecdb using '/home/oracle/nonecdb.xml'

source_file_name_convert=('/oradata/NONECDB','/u01/app/oracle/oradata/ORA19C/NONECDB') nocopy tempfile reuse;  2  

Pluggable database created.

特别说明一下:

1.这里指定了source_file_name_convert指定xml文件中数据文件的路径和我们实际存放数据文件的路径

2.这里使用了nocopy而没有指定file_name_convert这是因为数据文件我们是通过手工进行拷贝的,所以不需要oracle再来帮我们拷贝了,所以不需要中file_name_convert,而是使用了nocopy

3.这里设置了tempfile reuse,如果不设置oracle会自动尝试创建tempfile,但是此tempfile已经手工拷贝了,就会报错:

 

SQL> create pluggable database nonecdb using '/home/oracle/nonecdb.xml'

source_file_name_convert=('/oradata/NONECDB','/u01/app/oracle/oradata/ORA19C/NONECDB') nocopy;  2  

create pluggable database nonecdb using '/home/oracle/nonecdb.xml'

*

ERROR at line 1:

ORA-27038: created file already exists

ORA-01119: error in creating database file

'/u01/app/oracle/oradata/ORA19C/NONECDB/temp01.dbf'

因此需要指定tempfile reuse.

 

5.执行转换脚本

正如兼容性检查的提示,需要执行noncdb_to_pdb.sql

 

alter session set container=nonecdb;

@?/rdbms/admin/noncdb_to_pdb.sql

执行完成之后就可以打开数据库使用了:

 

SQL> alter pluggable database open;

Pluggable database altered.

 

二.克隆非CDB

克隆的方法更简单,同样需要执行兼容性检查.

1.创建到源端NONECDB的数据库链接

 

SQL> create database link tononecdb connect to system identified by manager using 'ora19c2/nonecdb';

Database link created.

SQL> select CDB from v$database@tononecdb;

CDB

---

NO

 

2.执行数据库克隆

执行数据库克隆:

 

SQL> create pluggable database nonecdb2 from NON$CDB@tononecdb 

file_name_convert=('/oradata/NONECDB','/u01/app/oracle/oradata/ORA19C/NONECDB2');  2  

Pluggable database created.

这里的注意点:

1.PDB指定NON$CDB这个特殊的PDB名字

2.远程克隆会拷贝数据文件,因此需要指定文件名的转换

你可能会遇到ORA-01031的错误:

 

ORA-17628: Oracle error 1031 returned by remote Oracle server

ORA-01031: insufficient privileges

需要在源端执行如下赋权:

 

SQL> grant cdb_dba to system;

Grant succeeded.

SQL> grant create session,connect,resource,cdb_dba,sysoper to system;

Grant succeeded.

SQL> grant  CREATE PLUGGABLE DATABASE to system;

Grant succeeded.

 

3.执行noncdb_to_pdb.sql

 

SQL> alter session set container=nonecdb2;

Session altered.

SQL> @?/rdbms/admin/noncdb_to_pdb.sql

 

4.打开pdb

执行完成之后就可以打开PDB了

 

SQL> alter pluggable database open;

Pluggable database altered.