一 实验目的
目前数据库环境有一个普通oracle实例(orcl)，一个cdb(里面有两个pdb)：
?想新建一个orclpdb3,将普通数据库orcl里的数据迁移到orclpdb3里。
二 实验步骤
2.1 将源端普通数据库启动到read only状态
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area? 788529152 bytes
Fixed Size????????? ??? 8625656 bytes
Variable Size????????????? ? 612368904 bytes
Database Buffers??????? ? 163577856 bytes
Redo Buffers????????????? ??? 3956736 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
----------------------------------------
READ ONLY
/*
注意，需要正常关库shutdown immediate，不要shutdown abort，否则执行alter database open read only;的时候会报错：
ORA-16005: database requires recovery
*/
2.2 导出源端普通数据库
exec dbms_pdb.describe('/tmp/orcl.xml');
2.3 创建pdb库
2.3.1 查询数据文件目录
#查询下源端普通数据库和目标端pdb数据文件目录
2.3.2 创建目录
#在目标端为orclpdb3创建目录
[oracle@PC ~]$ mkdir /u01/app/oracle/oradata/oradb/orclpdb3
2.3.3 创建pdb库
[oracle@PC ~]$ export ORACLE_SID=orclcdb
[oracle@PC ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 14 17:50:10 2022
Copyright (c) 1982, 2016, Oracle.? All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs;
??? CON_ID CON_NAME?????????????????? ? OPEN MODE? RESTRICTED
---------- ------------------------------ ---------- ----------
??????? ?2 PDB$SEED???????????????????? ? READ ONLY? NO
??????? ?3 ORCLPDB1??????????????????? ? READ WRITE NO
??????? ?4 ORCLPDB2??????????????????? ? READ WRITE NO
SQL>create pluggable database? orclpdb3 using '/tmp/orcl.xml' file_name_convert=('/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/oradb/orclpdb3/') ;
Pluggable database created.
SQL> show pdbs;
??? CON_ID CON_NAME???????????????????????????? ? OPEN MODE? RESTRICTED
---------- ------------------------------ ---------- ----------
?????????? ?2 PDB$SEED???????????????????????????? ? READ ONLY? NO
?????????? ?3 ORCLPDB1???????????????????????????? ? READ WRITE NO
?????????? ?4 ORCLPDB2???????????????????????????? ? READ WRITE NO
?????????? ?5 ORCLPDB3???????????????????????????? ? MOUNTED
2.3.4 启动pdb
SQL> alter pluggable database orclpdb3 open;
Warning: PDB altered with errors.
/*
注意，若启动的时候没有指定pluggable关键字，则会报错：
ERROR at line 1:
ORA-01509: specified name 'ORCLPDB3' does not match actual 'ORADB'
*/
SQL> show pdbs;
??? CON_ID CON_NAME???????????????????????????? ? OPEN MODE? RESTRICTED
---------- ------------------------------ ---------- ----------
?????????? ?2 PDB$SEED???????????????????????????? ? READ ONLY? NO
?????????? ?3 ORCLPDB1???????????????????????????? ? READ WRITE NO
?????????? ?4 ORCLPDB2???????????????????????????? ? READ WRITE NO
?????????? ?6 ORCLPDB3???????????????????????????? ? READ WRITE YES
orclpdb3是restricted模式，估计是因为上面的Warnning。
2.3.5 运行noncdb_to_pdb.sql
#查看下‘2.3.4中启动pdb’时报错Warning: PDB altered with errors的原因
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONs order by name;
?说明应该允许noncdb_to_pdb.sql。
#切换到该pdb
SQL> alter session set container = orclpdb3;
Session altered.
#运行sql文件
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
#重启下orclpdb3，使其取消restricted模式
SQL> alter pluggable database orclpdb3 close;
Pluggable database altered.
SQL> alter pluggable database orclpdb3 open;
Pluggable database altered.
SQL> show pdbs;
??? CON_ID CON_NAME?????????????????? ? OPEN MODE? RESTRICTED
---------- ------------------------------ ---------- ----------
??????? ?2 PDB$SEED???????????????????? ? READ ONLY? NO
??????? ?3 ORCLPDB1??????????????????? ? READ WRITE NO
??????? ?4 ORCLPDB2??????????????????? ? READ WRITE NO
??????? ?6 ORCLPDB3??????????????????? ? READ WRITE NO
2.3.6 配tnsname.ora
cd /u01/app/oracle/product/12c/db_1/network/admin
vi tnsnames.ora
新增如下内容：
orclpdb3 =
? (DESCRIPTION =
??? (ADDRESS = (PROTOCOL = TCP)(HOST = PC)(PORT = 1521))
??? (CONNECT_DATA =
????? (SERVER = DEDICATED)
????? (SERVICE_NAME = orclpdb3)
??? )
? )
2.4 验证
#验证下能否在新pdb里查看到之前orcl里的数据
?说明迁移成功。
注意：本次迁移只是复制数据，并不会删除之前orcl里的数据。
2.5 后续操作
若还需要使用源端普通数据库，记得将其再由read only状态还原为read write状态。
[oracle@PC admin]$ export ORACLE_SID=orcl
[oracle@PC admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 14 21:08:19 2022
Copyright (c) 1982, 2016, Oracle.? All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select open_mode from v$database;
OPEN_MODE
----------------------------------------
READ ONLY
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area? 788529152 bytes
Fixed Size???????????? ??? 8625656 bytes
Variable Size???????? ? 612368904 bytes
Database Buffers?? ? 163577856 bytes
Redo Buffers???????? ??? 3956736 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;
OPEN_MODE
----------------------------------------
READ WRITE
--本篇文章主要参考自：