一 实验目的

目前数据库环境有一个普通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

--本篇文章主要参考自：

112-云数据库中-把普通数库变成PDB_哔哩哔哩_bilibili

---

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，会注明原创字样,如未注明都非原创，如有侵权请联系删除！；3.作者投稿可能会经我们编辑修改或补充；4.本站不提供任何储存功能只提供收集或者投稿人的网盘链接。