Oracle Recovery Tools快速恢复ORA-19909----惜分飞

数据库服务器异常断电，数据库启动报ORA-01113 ORA-01110错误,无法正常open

Sun Jan 01 17:02:55 2023 alter database mount exclusive Successful mount of redo thread 1, with mount id 1652739647 Database mounted in Exclusive Mode Lost write protection disabled Completed: alter database mount exclusive alter database open Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_4396.trc: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'E:\ORACLE11G\ORADATA\ORCL\SYSTEM01.DBF' ORA-1113 signalled during: alter database open...

offline datafile 4，并open数据库

Sun Jan 01 20:36:22 2023 alter database datafile 4 offline drop Completed: alter database datafile 4 offline drop Sun Jan 01 20:37:40 2023 ALTER DATABASE OPEN Thread 1 opened at log sequence 13068   Current log# 3 seq# 13068 mem# 0: E:\ORACLE11G\ORADATA\ORCL\REDO03.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set SMON: enabling cache recovery Successfully onlined Undo Tablespace 2. Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK No Resource Manager plan active WARNING: AQ_TM_PROCESSES is set to 0. System operation                     might be adversely affected. Completed: ALTER DATABASE OPEN

尝试recover datafile 4和online datafile 4失败

Sun Jan 01 22:33:19 2023 ALTER DATABASE RECOVER  datafile 4  Media Recovery Start Serial Media Recovery started WARNING! Recovering data file 4 from a fuzzy backup. It might be an online backup taken without entering the begin backup command. ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 4  ... Sun Jan 01 22:34:02 2023 ALTER DATABASE RECOVER    CONTINUE DEFAULT  Media Recovery Log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC Errors with log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ... ALTER DATABASE RECOVER CANCEL Media Recovery Canceled Completed: ALTER DATABASE RECOVER CANCEL ALTER DATABASE RECOVER  datafile 4  Media Recovery Start Serial Media Recovery started WARNING! Recovering data file 4 from a fuzzy backup. It might be an online backup taken without entering the begin backup command. ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 4  ... Sun Jan 01 22:34:15 2023 ALTER DATABASE RECOVER    CONTINUE DEFAULT  Media Recovery Log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC Errors with log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ... ALTER DATABASE RECOVER    CONTINUE DEFAULT  Media Recovery Log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC Errors with log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ... ALTER DATABASE RECOVER CANCEL Media Recovery Canceled Completed: ALTER DATABASE RECOVER CANCEL Sun Jan 01 22:36:34 2023 alter database datafile 4 online ORA-1113 signalled during: alter database datafile 4 online

在datafile 4 offline的情况下，resetlogs库

Sun Jan 01 23:50:01 2023 ALTER DATABASE RECOVER  database until cancel  Media Recovery Start  started logmerger process Parallel Media Recovery started with 56 slaves Sun Jan 01 23:50:02 2023 Warning: Datafile 4 (E:\ORACLE11G\ORADATA\ORCL\USERS01.DBF)     is offline during full database recovery and will not be recovered Media Recovery Not Required Completed: ALTER DATABASE RECOVER  database until cancel  Sun Jan 01 23:50:15 2023 alter database open Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open ORA-1589 signalled during: alter database open... Sun Jan 01 23:50:34 2023 alter database open RESETLOGS RESETLOGS after complete recovery through change 158902238 Resetting resetlogs activation ID 1504008459 (0x59a5590b) Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc: ORA-00367: checksum error in log file header ORA-00322: log 1 of thread 1 is not current copy ORA-00312: online log 1 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO01.LOG' Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc: ORA-00367: checksum error in log file header ORA-00322: log 2 of thread 1 is not current copy ORA-00312: online log 2 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO02.LOG' Sun Jan 01 23:50:36 2023 Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_m000_8340.trc: ORA-00316: log 1 of thread 1, type 0 in header is not log file ORA-00312: online log 1 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO01.LOG' Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc: ORA-00367: checksum error in log file header ORA-00322: log 3 of thread 1 is not current copy ORA-00312: online log 3 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO03.LOG' Sun Jan 01 23:50:38 2023 Setting recovery target incarnation to 3 Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_m000_8340.trc: ORA-00314: log 2 of thread 1, expected sequence# 13070 doesn't match 0 ORA-00312: online log 2 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO02.LOG' Sun Jan 01 23:50:39 2023 Assigning activation ID 1652808490 (0x6283db2a) Thread 1 opened at log sequence 1   Current log# 1 seq# 1 mem# 0: E:\ORACLE11G\ORADATA\ORCL\REDO01.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Sun Jan 01 23:50:39 2023 SMON: enabling cache recovery Checker run found 5 new persistent data failures Successfully onlined Undo Tablespace 2. Dictionary check beginning File #4 is offline, but is part of an online tablespace. data file 4: 'E:\ORACLE11G\ORADATA\ORCL\USERS01.DBF' Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK No Resource Manager plan active WARNING: AQ_TM_PROCESSES is set to 0. System operation                     might be adversely affected. LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Sun Jan 01 23:50:47 2023 Completed: alter database open RESETLOGS

后续尝试恢复datafile 4报ORA-19909

Mon Jan 02 00:02:10 2023 alter database datafile 4 online Completed: alter database datafile 4 online Mon Jan 02 00:03:31 2023 ALTER DATABASE RECOVER  database using backup controlfile  Media Recovery Start  started logmerger process Mon Jan 02 00:03:31 2023 Datafile 4 is on orphaned branch           File status = 4         Abs fuzzy SCN = 0  Hot backup fuzzy SCN = 0 Media Recovery failed with error 19909 Slave exiting with ORA-283 exception Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_pr00_8868.trc: ORA-00283: recovery session canceled due to errors ORA-19909: datafile 4 belongs to an orphan incarnation ORA-01110: data file 4: 'E:\ORACLE11G\ORADATA\ORCL\USERS01.DBF' Recovery Slave PR00 previously exited with exception 283 ORA-283 signalled during: ALTER DATABASE RECOVER  database using backup controlfile  ...

通过Oracle Database Recovery Check检查发现,确实datafile 4的状态为:WRONG RESETLOGS
 

---

对于此类情况,参考：Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障快速解决
 

---

ALTER DATABASE RECOVER  database  Media Recovery Start  started logmerger process Mon Jan 02 16:14:15 2023 Media Recovery failed with error 264 Slave exiting with ORA-283 exception Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_pr00_10712.trc: ORA-00283: 恢复会话因错误而取消 ORA-00264: 不要求恢复 Recovery Slave PR00 previously exited with exception 283 ORA-283 signalled during: ALTER DATABASE RECOVER  database  ... Mon Jan 02 16:14:29 2023 ALTER DATABASE RECOVER  database  Media Recovery Start  started logmerger process Mon Jan 02 16:14:29 2023 Media Recovery failed with error 264 Slave exiting with ORA-283 exception Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_pr00_20032.trc: ORA-00283: 恢复会话因错误而取消 ORA-00264: 不要求恢复 Recovery Slave PR00 previously exited with exception 283 ORA-283 signalled during: ALTER DATABASE RECOVER  database  ... alter database open Mon Jan 02 16:14:37 2023 Thread 1 advanced to log sequence 2 (thread open) Thread 1 opened at log sequence 2   Current log# 2 seq# 2 mem# 0: H:\BAIDUNETDISK\ORCL\REDO02.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Mon Jan 02 16:14:37 2023 SMON: enabling cache recovery Successfully onlined Undo Tablespace 2. Dictionary check beginning Tablespace 'TEMP' #3 found in data dictionary, but not in the controlfile. Adding to controlfile. Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed ********************************************************************* WARNING: The following temporary tablespaces contain no files.          This condition can occur when a backup controlfile has          been restored.  It may be necessary to add files to these          tablespaces.  That can be done using the SQL statement:             ALTER TABLESPACE <tablespace_name> ADD TEMPFILE             Alternatively, if these temporary tablespaces are no longer          needed, then they can be dropped.            Empty temporary tablespace: TEMP ********************************************************************* Database Characterset is ZHS16GBK No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Mon Jan 02 16:14:37 2023 QMNC started with pid=22, OS id=14152 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Completed: alter database open

后续增加tempfile,导出数据完成本次恢复