ORA-19909 故障恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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,导出数据完成本次恢复

• ORA-600 3600恢复—-resetlogs scn异常
• ORA-00742 ORA-00312故障恢复
• Oracle Recovery Tools 解决ORA-600 3020故障
• 分享一次ORA-01113 ORA-01110故障处理过程
• 非归档数据库异常恢复一例
• ORA-00322 ORA-00312恢复
• 记录一次ORA-600 3004 恢复过程和处理思路
• Automatic datafile offline due to write error on
• commit_wait和commit_logging设置不当导致数据库无法正常启动
• 硬件故障数据库异常恢复
• File #xxx found in data dictionary but not in controlfile. Creating OFFLINE file ‘MISSING00XXX’ in the controlfile
• ORA-27069: skgfdisp: 尝试在文件范围外执行 I/O