参考文档blog.csdn.net/su377486/article/details/103104705
学习并记录一下
背景：
目标端 19c cdb pdb 10.*.98.103
源端 19c noncdb 10.*.98.150
方式：
1、可以使用拔出插入的方式将一个非CDB库转换为PDB
2、使用dblink克隆的方式.
################################################################################
第一种方式：将非cdb作为pdb插入cdb
1、检查数据库兼容性
在noncdb创建xml文件
SQL> begin 2 dbms_pdb.describe(PDB_DESCR_FILE=>'/home/oracle/noncdb.xml'); 3 end; 4 / PL/SQL procedure successfully completed. SQL>
将xml文件传到cdb同样的目录，在cdb中进行兼容性检查。
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROD_PDB1 MOUNTED SQL> set serveroutput on SQL> declare 2 compatible constant varchar2(3):= 3 case DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=>'/home/oracle/noncdb.xml',pdb_name=>'noncdb') 4 WHEN TRUE THEN 'YES' 5 ELSE 'NO' 6 end; 7 begin dbms_output.put_line(compatible); 8 end; 9 / YES PL/SQL procedure successfully completed. SQL>
并在cdb中查看表PDB_PLUG_IN_VIOLATIONS查看检查结果，
这里的NONCDB需要大写。
SQL> select message,action from pdb_plug_in_violations where name='NONCDB'; MESSAGE ACTION ---------------------------------------------------------------------------------------------------- ------------------------------------------------------------ PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. Run noncdb_to_pdb.sql. CDB parameter processes mismatch: Previous 320 Current 1000 Please check the parameter in the current CDB CDB parameter sga_target mismatch: Previous 2352M Current 3568M Please check the parameter in the current CDB CDB parameter pga_aggregate_target mismatch: Previous 782M Current 1185M Please check the parameter in the current CDB Service name or network name of service prodXDB in the PDB is invalid or conflicts with an existing Drop the service and recreate it with an appropriate name. service name or network name in the CDB. Service name or network name of service prod in the PDB is invalid or conflicts with an existing ser Drop the service and recreate it with an appropriate name. vice name or network name in the CDB. 6 rows selected.
如上：
a、需要运行nocdb_to_pdb.sql
b、因为我的非cdb的sid也是prod，所以插入的时候注意一下修改。
检查ok之后就是正式的操作了
非CDB开启到read only模式， 再生成xml文件。原来的xml两台机器上的记得删掉。
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup open read only; ORACLE instance started. Total System Global Area 2466249672 bytes Fixed Size 8899528 bytes Variable Size 536870912 bytes Database Buffers 1912602624 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL> begin 2 dbms_pdb.describe(PDB_DESCR_FILE=>'/home/oracle/noncdb.xml'); 3 end; 4 / PL/SQL procedure successfully completed. SQL>
计划好拷贝过去cdb之后 此非cdb的路径以及pdb的名称
源目录（非cdb）/u01/app/oracle/oradata/PROD/
目标目录（CDB）/u01/app/oracle/oradata/PROD/noncdb/
关闭源非cdb数据库，拷贝数据文件和xml文件到目标端
[oracle@dbserver PROD]$ scp * 10.8.98.103:/u01/app/oracle/oradata/PROD/noncdb/ oracle@10.8.98.103's password: control01.ctl 100% 10MB 60.2MB/s 00:00 control02.ctl 100% 10MB 52.0MB/s 00:00 noncdb01.dbf 100% 100MB 76.7MB/s 00:01 redo01.log 100% 200MB 100.0MB/s 00:02 redo02.log 100% 200MB 100.0MB/s 00:02 redo03.log 100% 200MB 100.0MB/s 00:02 sysaux01.dbf 100% 530MB 88.3MB/s 00:06 system01.dbf 100% 890MB 89.0MB/s 00:10 temp01.dbf 100% 32MB 69.4MB/s 00:00 undotbs01.dbf 100% 340MB 85.0MB/s 00:04 users01.dbf 100% 5128KB 58.2MB/s 00:00 [oracle@dbserver PROD]$ cd [oracle@dbserver ~]$ scp noncdb.xml 10.8.98.103:/home/oracle/ oracle@10.8.98.103's password: noncdb.xml 100% 7635 4.1MB/s 00:00 [oracle@dbserver ~]$
插入数据库
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PROD_PDB1 MOUNTED
SQL> create pluggable database noncdb using '/home/oracle/noncdb.xml' source_file_name_convert=('/u01/app/oracle/oradata/PROD','/u01/app/oracle/oradata/PROD/noncdb') nocopy tempfile reuse ;
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PROD_PDB1 MOUNTED
4 NONCDB MOUNTED
SQL>
特别注意，原来测试老是出错，参考上面文章操作才发现问题。
注意点
1.这里指定的source_file_name_convert指定xml文件中数据文件的路径和我们实际存放数据文件的路径
2.这里使用了nocopy而没有指定file_name_convert这是因为数据文件我们是通过手工进行拷贝的，
所以不需要oracle再来帮我们拷贝了,所以不需要中file_name_convert,而是使用了nocopy
3.这里设置了tempfile reuse,如果不设置oracle会自动尝试创建tempfile,但是此tempfile已经手工拷贝了,就会报错:
执行转换脚本 noncdb_to_pdb.sql
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 NONCDB MOUNTED SQL> alter session set container = noncdb; Session altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 NONCDB MOUNTED SQL> @?/rdbms/admin/noncdb_to_pdb.sql SQL> SET FEEDBACK 1 SQL> SET NUMWIDTH 10 SQL> SET LINESIZE 80 SQL> SET TRIMSPOOL ON SQL> SET TAB OFF SQL> SET PAGESIZE 100 SQL> SET VERIFY OFF 。。。。。。。。。。。。。。。。。。。。。 。。。。。。。。。。。。。。。。。。。。 09:02:36 SQL> set time OFF SQL> set timing OFF SQL> set trimout ON SQL> set trimspool ON SQL> set underline "-" SQL> set verify OFF SQL> set wrap ON SQL> set xmloptimizationcheck OFF
会执行挺长时间。执行完成之后就可以打开数据库了
SQL> alter pluggable database open; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 NONCDB READ WRITE NO SQL> [oracle@dbserver ~]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2022 09:18:13 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 29-JAN-2022 09:16:55 Uptime 0 days 0 hr. 1 min. 17 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/dbserver/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "d69dee198a891431e0539662080a487d" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "noncdb" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "prod" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "prodXDB" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... The command completed successfully
############################################################
第二种方式克隆
1、也需要做第一种方式的兼容性检查（同上）
2、在源端创建用户用于dblink
SQL> create user remote_clone_user identified by remote_clone_user; User created. SQL> grant create session,create pluggable database to remote_clone_user; Grant succeeded.
3、在目标端创建到非cdb的数据库链接
首先目标端的tnsnames.ora添加本地链接名称给dblink使用，我这里加了tns_150
[oracle@dbserver admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PROD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) ) ) tns_150 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.150)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) ) ) LISTENER_PROD = (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521)) [oracle@dbserver admin]$
4、目标端创建dblink
SQL> create database link noncdb_clone_link connect to remote_clone_user identified by remote_clone_user using 'tns_150'; Database link created.
这里我直接用了源端非cdb的创建的用户。
登录测试
SQL> desc user_tables@NONCDB_CLONE_LINK; Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE_NAME NOT NULL VARCHAR2(128) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(128) IOT_NAME VARCHAR2(128) STATUS VARCHAR2(8) PCT_FREE NUMBER PCT_USED NUMBER 。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
5、在目标端执行数据库克隆，记住 先在目标端创建好对应的目录
mkdir -p /u01/app/oracle/oradata/PROD/noncdb
SQL> create pluggable database noncdb from NON$CDB@NONCDB_CLONE_LINK file_name_convert=('/u01/app/oracle/oradata/PROD/','/u01/app/oracle/oradata/PROD/noncdb/');
Pluggable database created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/system01.dbf
/u01/app/oracle/oradata/PROD/sysaux01.dbf
/u01/app/oracle/oradata/PROD/undotbs01.dbf
/u01/app/oracle/oradata/PROD/pdbseed/system01.dbf
/u01/app/oracle/oradata/PROD/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/PROD/users01.dbf
/u01/app/oracle/oradata/PROD/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/PROD/noncdb/system01.dbf
/u01/app/oracle/oradata/PROD/noncdb/sysaux01.dbf
/u01/app/oracle/oradata/PROD/noncdb/undotbs01.dbf
/u01/app/oracle/oradata/PROD/noncdb/users01.dbf
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/noncdb/noncdb01.dbf
12 rows selected.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
5 NONCDB MOUNTED
SQL>
注意点
6、执行 noncdb_to_pdb.sql，时间有点长等待。
SQL> @?/rdbms/admin/noncdb_to_pdb.sql
7、打开pdb
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 5 NONCDB MOUNTED SQL> alter database open; Database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 5 NONCDB READ WRITE NO [oracle@dbserver noncdb]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2022 10:35:35 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 29-JAN-2022 09:16:55 Uptime 0 days 1 hr. 18 min. 39 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/dbserver/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "d6afe25e92641fd4e0536762080a7812" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "noncdb" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "prod" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "prodXDB" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... The command completed successfully