若只有PDB数据库文件，如何将PDB插入到CDB?
[TOC]
一、案例分享
问题描述：
客户19c CDB 单机环境中，数据盘损坏，数据库宕机，无法启动CDB，alter日志提示CDB中数据文件坏块，所以所有的PDB也无法启动，且没有备份。
解决思路：
-
接到客户反馈后，立马对原来的数据盘中所有的数据文件重新拷贝一份，做一个冷备，不破环当前环境。
-
通过设置一些参数，尝试强制打开CDB，均不行。
-
为了不丢失数据，我们尝试将冷备中的pdb数据文件拷贝到其他CDB环境中，尝试是否能够启动？ 尝试成功，但是插入到其他环境中的pdb状态不太对，最终选择通过exp将数据导出，导入到正常的环境中，业务恢复正常。 数据未丢失，备份很重要。
在案例中，主要用到了DBMS_PDB.RECOVER 包，用于恢复 xml 文件。适用于以下场景：
- 当 unplug 了一个pdb之后，xml文件丢失了，如何恢复xml文件，以便能够将PDB插入到一个新的 CDB 中？
- 若只有PDB数据库文件，如何将PDB插入到CDB?
二、模拟实验
1. 环境描述
将原本的orclcdb中的pdb插入到testcdb中。
源端 12.2.0.1.0 ORCLCDB：orclpdb1、orclpdb2 源目标端 19.3.0.1.0 TESTCDB复制
2. 模拟错误
1. 连接到PDB中操作
1）pdb1存在未提交事务
--session1:
SYS@ORCLCDB> alter session set container=orclpdb1;
Session altered.
SYS@ORCLCDB> create table test1 as select * from tab;
Table created.
SYS@ORCLCDB> select count(*) from test1;
COUNT(*)
----------
7945
SYS@ORCLCDB> delete from test1;
7945 rows deleted.
SYS@ORCLCDB>
-- 这里没commit，存在未提交事务。
复制2）pdb2不存在未提交事务
--session2:
SYS@ORCLCDB> alter session set container=orclpdb2;
Session altered.
SYS@ORCLCDB> create table test2 as select * from tab;
Table created.
SYS@ORCLCDB> select count(*) from test2;
COUNT(*)
----------
7945
SYS@ORCLCDB> delete from test2 where rownum<1000;
999 rows deleted.
SYS@ORCLCDB> commit;
Commit complete.
-- 这里已经commit，不存在未提交事务。
复制2. 删除CDB中system文件，模拟宕机
[oracle@oracle12 ~]$ rm /home/oracle/oradata/ORCLCDB/system01.dbf
[oracle@oracle12 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Dec 9 14:29:50 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SYS@ORCLCDB> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3965
Session ID: 72 Serial number: 13509
SYS@ORCLCDB> conn / as sysdba
Connected to an idle instance.
SYS@ORCLCDB> startup
ORACLE instance started.
Total System Global Area 805306368 bytes
Fixed Size 8797928 bytes
Variable Size 314573080 bytes
Database Buffers 478150656 bytes
Redo Buffers 3784704 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/home/oracle/oradata/ORCLCDB/system01.dbf'
SYS@ORCLCDB> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
复制结论：CDB因为system文件丢失，无法启动。此时，我们只有PDB的数据文件，是否可以将PDB插入到其他CDB中呢?
3. 将PDB插入到其他CDB中
1. 将pdb文件拷贝到其他CDB环境中
[oracle@oracle12 ORCLCDB]$ scp -r orclpdb1 oracle@172.17.0.3:/opt/oracle/oradata/TESTCDB/ oracle@172.17.0.3's password: undotbs01.dbf 100% 100MB 110.7MB/s 00:00 system01.dbf 100% 250MB 112.8MB/s 00:02 sysaux01.dbf 100% 340MB 114.4MB/s 00:02 temp012022-12-09_06-07-38-717-AM.dbf 100% 64MB 119.9MB/s 00:00 [oracle@oracle12 ORCLCDB]$ scp -r orclpdb2 oracle@172.17.0.3:/opt/oracle/oradata/TESTCDB/ oracle@172.17.0.3's password: undotbs01.dbf 100% 100MB 111.6MB/s 00:00 system01.dbf 100% 250MB 110.0MB/s 00:02 sysaux01.dbf 100% 340MB 109.4MB/s 00:03 temp012022-12-09_06-07-38-717-AM.dbf 100% 64MB 113.6MB/s 00:00复制
2.获取xml文件
-- 使用DBMS_PDB.RECOVER包
SYS@testcdb> BEGIN
2 DBMS_PDB.RECOVER(PDB_DESCR_FILE => '/tmp/orclpdb1.xml',
3 PDB_NAME => 'orclpdb1',
4 FILENAMES => '/opt/oracle/oradata/TESTCDB/orclpdb1');
5 END;
6 /
PL/SQL procedure successfully completed.
SYS@testcdb>
SYS@testcdb> BEGIN
2 DBMS_PDB.RECOVER(PDB_DESCR_FILE => '/tmp/orclpdb2.xml',
3 PDB_NAME => 'orclpdb2',
4 FILENAMES => '/opt/oracle/oradata/TESTCDB/orclpdb2/');
5 END;
6 /
PL/SQL procedure successfully completed.
-- 查看xml文件内容
[oracle@ora19c TESTCDB]$ more /tmp/orclpdb1.xml|grep .dbf
<path>/opt/oracle/oradata/TESTCDB/orclpdb1/undotbs01.dbf</path>
<path>/opt/oracle/oradata/TESTCDB/orclpdb1/system01.dbf</path>
<path>/opt/oracle/oradata/TESTCDB/orclpdb1/sysaux01.dbf</path>
[oracle@ora19c TESTCDB]$ more /tmp/orclpdb2.xml|grep .dbf
<path>/opt/oracle/oradata/TESTCDB/orclpdb2/undotbs01.dbf</path>
<path>/opt/oracle/oradata/TESTCDB/orclpdb2/system01.dbf</path>
<path>/opt/oracle/oradata/TESTCDB/orclpdb2/sysaux01.dbf</path>
复制3. 检查兼容性
SYS@testcdb> SET SERVEROUTPUT ON
SYS@testcdb> DECLARE
2 theResult BOOLEAN;
3 BEGIN
4 theResult := DBMS_PDB.check_plug_compatibility(
5 pdb_descr_file => '/tmp/orclpdb1.xml',
6 pdb_name => 'orclpdb1');
7 IF theResult THEN
8 DBMS_OUTPUT.PUT_LINE('Yes,PDB is compatible');
9 ELSE
10 DBMS_OUTPUT.PUT_LINE('No,PDB is not compatible');
11 END IF;
12 END;
13 /
Yes,PDB is compatible
PL/SQL procedure successfully completed.
SYS@testcdb> SET SERVEROUTPUT ON
SYS@testcdb> DECLARE
2 theResult BOOLEAN;
3 BEGIN
4 theResult := DBMS_PDB.check_plug_compatibility(
5 pdb_descr_file => '/tmp/orclpdb2.xml',
6 pdb_name => 'orclpdb1');
7 IF theResult THEN
8 DBMS_OUTPUT.PUT_LINE('Yes,PDB is compatible');
9 ELSE
10 DBMS_OUTPUT.PUT_LINE('No,PDB is not compatible');
11 END IF;
12 END;
13 /
Yes,PDB is compatible
PL/SQL procedure successfully completed.
--注意：
1、返回Yes，表示兼容，可以创建PDB;
2、返回No，表示不兼容，不可以创建PDB，通过查询视图pdb_plug_in_violations查看原因。
复制结论：我们这里输出都是yes,兼容。
4.创建PDB
SYS@testcdb> CREATE PLUGGABLE DATABASE orclpdb1 USING '/tmp/orclpdb1.xml' NOCOPY;
Pluggable database created.
SYS@testcdb> CREATE PLUGGABLE DATABASE orclpdb2 USING '/tmp/orclpdb2.xml' NOCOPY;
Pluggable database created.
SYS@testcdb> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 MOUNTED
4 ORCLPDB2 MOUNTED
SYS@testcdb> ALTER PLUGGABLE DATABASE orclpdb1 open;
Warning: PDB altered with errors.
SYS@testcdb> ALTER PLUGGABLE DATABASE orclpdb2 open;
Warning: PDB altered with errors.
SYS@testcdb> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 MIGRATE YES
4 ORCLPDB2 MIGRATE YES
SYS@testcdb> SELECT * FROM PDB_PLUG_IN_VIOLATIONS;
-------------------------------------------------------------------------------- ----------
2022-12-09 14:41:21 ORCLPDB2 VSN not match ERROR 1 PDB's version does not match CDB's version: PDB's version 12.2.0.1.0. CDB's version 19.0.0.0.0. PENDING
Either upgrade the PDB or reload the components in the PDB. 4
2022-12-09 14:41:17 ORCLPDB1 VSN not match ERROR 1 PDB's version does not match CDB's version: PDB's version 12.2.0.1.0. CDB's version 19.0.0.0.0. PENDING
Either upgrade the PDB or reload the components in the PDB.
复制结论：pdb的版本和CDB版本不一致，需要升级PDB版本。
5. 升级PDB
-- 1)升级orclpdb1
[oracle@ora19c TESTCDB]$ $ORACLE_HOME/bin/dbupgrade -c orclpdb1
Argument list for [/opt/oracle/product/19c/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = orclpdb1
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]
/opt/oracle/product/19c/dbhome_1/rdbms/admin/orahome = [/opt/oracle/product/19c/dbhome_1]
/opt/oracle/product/19c/dbhome_1/bin/orabasehome = [/opt/oracle/product/19c/dbhome_1]
catctlGetOraBaseLogDir = [/opt/oracle/product/19c/dbhome_1]
Analyzing file /opt/oracle/product/19c/dbhome_1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20221209144531]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20221209144531/catupgrd_catcon_1625.lst]
....
....
Identifier DV 22-12-09 03:51:23
SCRIPT = [/opt/oracle/product/19c/dbhome_1/rdbms/admin/catmaca.sql]
ERROR = [PL/SQL: Statement ignored]
STATEMENT = [as above]
------------------------------------------------------
LOG FILES: (/opt/oracle/product/19c/dbhome_1/cfgtoollogs/testcdb/upgrade20221209153626/catupgrdorclpdb1*.log)
Upgrade Summary Report Located in:
/opt/oracle/product/19c/dbhome_1/cfgtoollogs/testcdb/upgrade20221209153626/upg_summary.log
Time: 1525s For PDB(s)
Grand Total Time: 1525s
LOG FILES: (/opt/oracle/product/19c/dbhome_1/cfgtoollogs/testcdb/upgrade20221209153626/catupgrd*.log)
Pdb Upgrades With Errors
------------------------------------------------------
Error In [ORCLPDB1] Status is [1] Check Log Files
[/opt/oracle/product/19c/dbhome_1/cfgtoollogs/testcdb/upgrade20221209153626/catupgrdorclpdb1*.log]
------------------------------------------------------
Grand Total Upgrade Time: [0d:0h:25m:25s]
-- 2)升级orclpdb2
[oracle@ora19c TESTCDB]$ $ORACLE_HOME/bin/dbupgrade -c orclpdb2
......过程基本一样，省略。。。
------------------------------------------------------
Phases [0-107] End Time:[2022_12_09 16:33:06]
Container Lists Inclusion:[ORCLPDB2] Exclusion:[NONE]
------------------------------------------------------
Grand Total Time: 1466s [ORCLPDB2]
LOG FILES: (/opt/oracle/product/19c/dbhome_1/cfgtoollogs/testcdb/upgrade20221209160740/catupgrdorclpdb2*.log)
Upgrade Summary Report Located in:
/opt/oracle/product/19c/dbhome_1/cfgtoollogs/testcdb/upgrade20221209160740/upg_summary.log
Time: 1545s For PDB(s)
Grand Total Time: 1545s
LOG FILES: (/opt/oracle/product/19c/dbhome_1/cfgtoollogs/testcdb/upgrade20221209160740/catupgrd*.log)
Grand Total Upgrade Time: [0d:0h:25m:45s]
SYS@testcdb> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 MIGRATE YES
4 ORCLPDB2 MOUNTED
--升级完成。ORCLPDB1升级提示有报错，所以状态仍然是MIGRATE，ORCLPDB1升级无报错，状态变成mount。
-- 重启PDB
SYS@testcdb> alter pluggable database ORCLPDB1 close immediate;
Pluggable database altered.
SYS@testcdb> alter pluggable database ORCLPDB1 open;
Pluggable database altered.
SYS@testcdb> alter pluggable database ORCLPDB2 open;
Pluggable database altered.
SYS@testcdb> 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
复制结论：重启两个PDB之后，状态都变成READ WRITE了，似乎成功的将pdb插入到新环境中了。
5. 重建PDB的临时表空间
-- 1)ORCLPDB1
SYS@testcdb> alter session set container=ORCLPDB1;
Session altered.
SYS@testcdb> CREATE TEMPORARY TABLESPACE temp1 TEMPFILE '/opt/oracle/oradata/TESTCDB/orclpdb1/temp01.dbf' SIZE 50M;
Tablespace created.
SYS@testcdb> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;
Database altered.
SYS@testcdb> drop tablespace temp including contents and datafiles;
drop tablespace temp including contents and datafiles
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [krtlft-01], [3], [], [], [], [], [], [], [], [], [], []
-- 2)ORCLPDB2
SYS@testcdb> alter session set container=ORCLPDB2;
Session altered.
SYS@testcdb> CREATE TEMPORARY TABLESPACE temp1 TEMPFILE '/opt/oracle/oradata/TESTCDB/orclpdb2/temp01.dbf' SIZE 50M;
Tablespace created.
SYS@testcdb> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;
Database altered.
SYS@testcdb> drop tablespace temp including contents and datafiles;
drop tablespace temp including contents and datafiles
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [krtlft-01], [3], [], [], [], [], [], [], [], [], [], []
复制结论：抛出ORA-600错误。
6. 验证数据
-- 1)ORCLPDB1
SYS@testcdb> alter session set container=ORCLPDB1;
Session altered.
SYS@testcdb> select count(*) from test1;
COUNT(*)
----------
0
-- 2)ORCLPDB2
SYS@testcdb> alter session set container=ORCLPDB2;
Session altered.
SYS@testcdb> select count(*) from test2;
COUNT(*)
----------
6946
复制结论：test1的数据，也被删除了，但是源端并没有提交事务。且部分操作有ORA-600错误，拷贝是正式生产环境，建议通过exp将数据导出，重新导入到正常的环境中。
最后修改时间：2022-12-14 09:41:12
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
