目录
1. 问题现象
2. 原因
3. 方案
1. 问题现象
在备份数据库后,删除PDB某个表空间(DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES),执行可插入数据库(PDB)的时间点恢复,基于当前控制文件和完整日志,对PDB进行PITR恢复时(采用控制文件,非Catalog目录库),预期将失败。
数据库版本信息
SQL> select banner from v$version; BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
LOCAL UNDO是否启用
SQL> select property_name,property_value from database_properties where property_name like '%UNDO%'; PROPERTY_NAME PROPERTY_VALUE
------------------ --------------
LOCAL_UNDO_ENABLED TRUE
备份数据库
RMAN> backup database format '/u01/app/oracle/backup/%s_%d_%U.full' tag 'FULL';
记录当前SCN
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile; System altered. SQL> select current_scn from v$database; CURRENT_SCN
-----------
7779561 SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered.
连接PDB删除表空间
SQL> alter session set container=pdb1; SQL> drop tablespace test including contents and datafiles; Tablespace dropped. SQL> alter pluggable database pdb1 close; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED
---------- --------- ---------- ----------
3 PDB1 MOUNTED
执行PDB PITR恢复
run{
set until scn 7779561;
restore pluggable database pdb1;
recover pluggable database pdb1 auxiliary destination='/u01/app/oracle/au';
alter pluggable database pdb1 open resetlogs;
}
日志截取片段
2019-08-04T06:53:04.851088+08:00
Errors in file /u01/app/oracle/diag/rdbms/cdbocp/CDBOCP/trace/CDBOCP_m003_43519.trc:
ORA-01110: data file 24: '/u01/app/oracle/product/12.2.0/db_1/dbs/MISSING00024'
ORA-01565: error in identifying file '/u01/app/oracle/product/12.2.0/db_1/dbs/MISSING00024'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2019-08-04T06:53:05.525255+08:00
Errors in file /u01/app/oracle/diag/rdbms/cdbocp/CDBOCP/trace/CDBOCP_m003_43519.trc:
ORA-01110: data file 25: '/u01/app/oracle/product/12.2.0/db_1/dbs/MISSING00025'
ORA-01565: error in identifying file '/u01/app/oracle/product/12.2.0/db_1/dbs/MISSING00025'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
至此,被删除的表空间TEST(及数据文件)无法恢复出来,只是将信息写至控制文件中,数据文件无法通过RMAN Restore出来。因为当前控制文件已经没有了 TEST 表空间的记录,而控制文件又是整个CDB共享的。如果在非CDB环境中,没有使用Catalog的情况下,可以通过恢复之前的 controlfile 副本或(极端情况下的手工重建)来解决这个问题,但是你不希望在CDB中这样做,因为它会影响到所有数据库。
查看控制文件中关于数据文件的记录信息
SQL> select file# "A",checkpoint_change# "B",name from v$datafile where con_id=3; A B NAME
---- --------- ----------------------------------------------------
9 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/system01.dbf
10 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/sysaux01.dbf
12 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/users01.dbf
21 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/tbs_c01.dbf
22 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/henry01.dbf
23 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/henry02.dbf
24 0 /u01/app/oracle/product/12.2.0/db_1/dbs/MISSING00024
25 0 /u01/app/oracle/product/12.2.0/db_1/dbs/MISSING00025
31 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/impdata01.dbf
37 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/flash_arc01.dbf
38 7837930 /u01/app/oracle/oradata/CDBOCP/bbb01.dbf A B NAME
---- --------- ---------------------------------------------------
43 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/rman01.dbf
44 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/undotbs02.dbf
45 7839301 /u01/app/oracle/oradata/CDBOCP/PDB1/scuti01.dbf
查看数据文件头记录的数据文件信息
SQL> select file# "A",checkpoint_change# "B",name from v$datafile_header where con_id=3; A B NAME
---- --------- --------------------------------------------------
9 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/system01.dbf
10 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/sysaux01.dbf
12 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/users01.dbf
21 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/tbs_c01.dbf
22 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/henry01.dbf
23 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/henry02.dbf
24 0
25 0
31 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/impdata01.dbf
37 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/flash_arc01.dbf
38 7837930 /u01/app/oracle/oradata/CDBOCP/bbb01.dbf A B NAME
---- --------- ---------------------------------------------------
43 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/rman01.dbf
44 7837930 /u01/app/oracle/oradata/CDBOCP/PDB1/undotbs02.dbf
45 7839301 /u01/app/oracle/oradata/CDBOCP/PDB1/scuti01.dbf
2. 原因
This issue is reported in the below bugs bug 27966859 closed as duplicate of unpublished bug 27855651 Bug 27855651 reported for 12.2 and the fix is included in 18.1
3. 方案
1) 采用Catalog恢复目录替代控制文件。参考《通过恢复目录(Catalogue)进行PDB级别的PITR恢复》
2) 当采用Catalog进行可插入数据库恢复失败时,请检查补丁27855651的可用性。