nocdb转换成cdb

方式:
1.克隆方式dblink
2.DBMS_PDB.DESCRIBE方式

转换成cdb后,原来no-cdb用户的sys、system账号的密码会使用cdb的密码

######################克隆方式dblink###############################
环境:
源端(非cdb) :192.168.1.101 sid:slnngk 非归档模式数据库版本:19.3.0.0
目标端(cdb) :192.168.1.100 sid:ora19c 非归档模式数据库版本:19.3.0.0

1.目标端创建到源端的dblink
在目标端机器上操作

tnsnocdb =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
        (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = slnngk)
   )
  )

2.赋予创建dblink使用的用户具有如下权限
在源端机器上执行
SQL> grant create pluggable database to system;

Grant succeeded.

3.目标库创建DB link
在cdb模式下创建

SQL> connect / as sysdba
Connected.
SQL> create public database link link_to_nocdb connect to system identified by oracle using 'tnsnocdb';

Database link created.

通过dblink可以查询得到源端非cdb下的表数据

SQL> select * from hxl.tb_test@link_to_nocdb;

        ID NAME
---------- --------------------------------
         1 name1
         2 name2
         3 name3
         4 name4
         5 name5
         6 name6

6 rows selected.

4.将源库设置为read only
SQL>shutdown immediate;
SQL>startup open read only;

5.在目标端使用db link克隆远程数据库
在目标端上执行

SQL> create pluggable database pdb1 from NON$CDB@link_to_nocdb file_name_convert=('/u01/app/oracle/oradata/SLNNGK','/u01/app/oracle/oradata/ORA19C/pdb1');
                   
Pluggable database created.

目标机器上的pdb1目录会自动创建

6.尝试打开pdb1
在目标端上执行
SQL> alter pluggable database pdb1 open;

Warning: PDB altered with errors.

查看失败原因

SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONs order by name;

NAME
--------------------------------------------------------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB$SEED
SQL Patch                                                        ERROR
'19.3.0.0.0 Release_Update 1904101227' is installed in the CDB but no release up
dates are installed in the PDB
RESOLVED

NAME
--------------------------------------------------------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------

PDB1
Non-CDB to PDB                                                   ERROR
PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
PENDING

NAME
--------------------------------------------------------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------

7.目标端执行noncdb_to_pdb.sql
在目标端上执行
SQL>alter session set container=pdb1;
Session altered.
SQL>@/u01/app/oracle/product/19.3.0.0/db_1/rdbms/admin/noncdb_to_pdb.sql

执行该过程需要些时间,执行完成后pdb会自动打开成write模式

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 PDB1                           READ WRITE YES

查看数据

SQL> select * from hxl.tb_test;

        ID NAME
---------- --------------------------------
         1 name1
         2 name2
         3 name3
         4 name4
         5 name5
         6 name6

6 rows selected.

#####################DBMS_PDB.DESCRIBE方式#######################

1.将源库设置在read-only模式
shutdown immediate
startup mount
alter database open read only;

2.sysdba用户登录 non-cdb 数据库执行DBMS_PDB.DEXCRIBE过程生成非cdb数据库的xml文件
在源库上执行

BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/ncdb.xml');
END;
/

3.在cdb机器上建立非cdb的数据文件路径并拷贝xml和数据文件
在目标机器上创建如下文件目录
mkdir -p /u01/app/oracle/oradata/SLNNGK

从源端建如下文件拷贝到目标机器
scp /tmp/ncdb.xml oracle@192.168.1.102:/tmp/
scp -r /u01/app/oracle/oradata/SLNNGK/* oracle@192.168.1.102:/u01/app/oracle/oradata/SLNNGK/

4.在cdb上运行兼容性检测(DBMS_PDB.CHECK_PLUG_COMPATIBILITY)

SQL> connect / as sysdba
Connected.
 
set serveroutput on
declare
compatible constant varchar2(3) :=
case dbms_pdb.check_plug_compatibility(pdb_descr_file => '/tmp/ncdb.xml',pdb_name => 'pdb1')
when true then 'yes'
else 'no'
end;
begin
dbms_output.put_line(compatible);
end;
/

若是返回结果为no 则查询语句纠正错误,没有问题就返回yes
select con_id,cause,message from pdb_plug_in_violations;

5.建立新的pdb

CREATE PLUGGABLE DATABASE pdb1 USING '/tmp/ncdb.xml' copy FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/SLNNGK', '/u01/app/oracle/oradata/ORA19C/pdb1');

6.执行noncdb_to_pdb.sql脚本

SQL>alter session set container=pdb1;
SQL>@/u01/app/oracle/product/19.3.0.0/db_1/rdbms/admin/noncdb_to_pdb.sql;

7.打开新建立的pdb

SQL> alter pluggable database pdb1 open;

Pluggable database altered.
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDB1                           READ WRITE NO

8.删除原来创建的临时目录

[oracle@localhost pdb1]$ cd /u01/app/oracle/oradata
[oracle@localhost oradata]$ rm -rf SLNNGK