Oracle 12.1版本中，UNDO表空间仅存在CDB级别（共享UNDO），来自于AskScuti博客园。
Oracle 12.2版本开始，UNDO表空间同时可以存在每个PDB级别（本地UNDO）。
问题表现前提：在12.2.0.1 多租户环境、备份后删除表空间并进行PDB PITR恢复、PDB采用本地UNDO（LOCAL_UNDO_ENABLED=TRUE）
MOS 文档 ID 2435452.1 （建议使用 Catalog）
目录
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（及数据文件）无法恢复出来，只是将未命名的数据文件添加至控制文件中。因为当前控制文件已经没有了 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恢复目录替代控制文件。
2） 当采用Catalog进行可插入数据库恢复失败时，请检查补丁27855651的可用性。
执行PDB的PITR恢复失败的说明 (文档 ID 2435452.1)
原文：https://www.cnblogs.com/askscuti/p/11297145.html