标签归档：ORA-01190

存储掉电强制拉库引起ORA-01555和ORA-01189/ORA-01190故障处理

发表于 2025 年 8 月 30 日 由 惜分飞

机房存储突然掉电导致Oracle数据库访问存储异常,数据库报出大量的ORA-27072: File I/O error,Linux-x86_64 Error: 5: Input/output error,ORA-15081: failed to submit an I/O operation to a disk等错误,实例直接crash

Wed Aug 27 07:11:53 2025
Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_m000_17596.trc:
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 6297632
Additional information: -1
WARNING: Read Failed. group:1 disk:0 AU:3075 offset:16384 size:16384
Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ckpt_6165.trc:
ORA-00202: control file: '+DG/xff/controlfile/current.284.918834897'
ORA-15081: failed to submit an I/O operation to a disk
WARNING: failed to read mirror side 1 of virtual extent 0 logical extent 0 of 
  file 284 in group [1.2747812198] from disk DG_0000  allocation unit 3075 reason error; 
  if possible, will try another mirror side
Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_m000_17596.trc:
ORA-00202: control file: '+DG/xff/controlfile/current.284.918834897'
ORA-15081: failed to submit an I/O operation to a disk
Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ckpt_6165.trc:
ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 16384
WARNING: Write Failed. group:1 disk:0 AU:3080 offset:49152 size:16384
Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ckpt_6165.trc:
ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 16384
WARNING: Write Failed. group:1 disk:0 AU:3075 offset:49152 size:16384
Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ckpt_6165.trc:
ORA-15080: synchronous I/O operation to a disk failed
WARNING: failed to write mirror side 1 of virtual extent 0 logical 
 extent 0 of file 284 in group 1 on disk 0 allocation unit 3075 
Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ckpt_6165.trc:
ORA-15080: synchronous I/O operation to a disk failed
WARNING: failed to write mirror side 1 of virtual extent 0 logical extent 0
  of file 283 in group 1 on disk 0 allocation unit 3080 
Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ckpt_6165.trc:
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: '+DG/xff/controlfile/current.283.918834897'
ORA-15081: failed to submit an I/O operation to a disk
ORA-15081: failed to submit an I/O operation to a disk
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: '+DG/xff/controlfile/current.284.918834897'
ORA-15081: failed to submit an I/O operation to a disk
ORA-15081: failed to submit an I/O operation to a disk
Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ckpt_6165.trc:
ORA-00221: error on write to control file
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: '+DG/xff/controlfile/current.283.918834897'
ORA-15081: failed to submit an I/O operation to a disk
ORA-15081: failed to submit an I/O operation to a disk
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: '+DG/xff/controlfile/current.284.918834897'
ORA-15081: failed to submit an I/O operation to a disk
ORA-15081: failed to submit an I/O operation to a disk
CKPT (ospid: 6165): terminating the instance due to error 221
Wed Aug 27 07:11:53 2025
ORA-1092 : opitsk aborting process

存储恢复之后,尝试open数据库报ORA-00333错误（该错误一般是由于redo写丢失导致)

Wed Aug 27 16:36:32 2025
ALTER DATABASE OPEN
This instance was first to open
Beginning crash recovery of 2 threads
 parallel recovery started with 31 processes
Started redo scan
Incomplete read from log member '+DG/xff/onlinelog/group_2.287.918834905'. Trying next member.
Incomplete read from log member '+DG/xff/onlinelog/group_2.288.918834911'. Trying next member.
Incomplete read from log member '+DG/xff/onlinelog/group_2.287.918834905'. Trying next member.
Abort recovery for domain 0
Aborting crash recovery due to error 333
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_10257.trc:
ORA-00333: redo log read error block 1275904 count 5721
Abort recovery for domain 0
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_10257.trc:
ORA-00333: redo log read error block 1275904 count 5721
ORA-333 signalled during: ALTER DATABASE OPEN...
1
现场人员使用隐含参数,尝试直接拉库操作报ORA-00704 ORA-01555错误,导致拉库失败
1
Wed Aug 27 16:47:11 2025
ALTER DATABASE RECOVER  database until cancel  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 32 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER  database until cancel  ...
Wed Aug 27 16:47:56 2025
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log +DG
Wed Aug 27 16:47:56 2025
Errors with log +DG
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_pr00_24154.trc:
ORA-00308: cannot open archived log '+DG'
ORA-17503: ksfdopn:2 Failed to open file +DG
ORA-15045: ASM file name '+DG' is not in reference form
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER CANCEL 
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_pr00_24154.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DG/xff/datafile/system.279.918834827'
Slave exiting with ORA-1547 exception
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_pr00_24154.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DG/xff/datafile/system.279.918834827'
ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...
Wed Aug 27 16:48:09 2025
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 25330611827626
Resetting resetlogs activation ID 3307041102 (0xc51d714e)
Deleted Oracle managed file +DG/xff/onlinelog/group_1.285.918834899
Deleted Oracle managed file +DG/xff/onlinelog/group_1.286.918834901
Deleted Oracle managed file +DG/xff/onlinelog/group_2.287.918834905
Deleted Oracle managed file +DG/xff/onlinelog/group_2.288.918834911
Wed Aug 27 16:48:28 2025
Deleted Oracle managed file +DG/xff/onlinelog/group_3.289.918834917
Deleted Oracle managed file +DG/xff/onlinelog/group_3.290.918834923
Deleted Oracle managed file +DG/xff/onlinelog/group_4.293.918835035
Deleted Oracle managed file +DG/xff/onlinelog/group_4.294.918835037
Wed Aug 27 16:48:48 2025
Deleted Oracle managed file +DG/xff/onlinelog/group_5.295.918835041
Deleted Oracle managed file +DG/xff/onlinelog/group_5.296.918835047
Deleted Oracle managed file +DG/xff/onlinelog/group_6.297.918835055
Wed Aug 27 16:48:58 2025
Deleted Oracle managed file +DG/xff/onlinelog/group_6.298.918835061
Wed Aug 27 16:49:10 2025
Setting recovery target incarnation to 3
Wed Aug 27 16:49:10 2025
This instance was first to open
Picked broadcast on commit scheme to generate SCNs
Wed Aug 27 16:49:10 2025
Assigning activation ID 3598492411 (0xd67ca2fb)
Thread 2 opened at log sequence 1
  Current log# 4 seq# 1 mem# 0: +DG/xff/onlinelog/group_4.294.1210265317
  Current log# 4 seq# 1 mem# 1: +DG/xff/onlinelog/group_4.293.1210265323
Successful open of redo thread 2
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Aug 27 16:49:10 2025
SMON: enabling cache recovery
Instance recovery: looking for dead threads
Instance recovery: lock domain invalid but no dead threads
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x1709.be1eb3b1):
select ctime, mtime, stime from obj$ where obj# = :1
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_23787.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20_1295954159$" too small
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_23787.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20_1295954159$" too small
Error 704 happened during db open, shutting down database
USER (ospid: 23787): terminating the instance due to error 704
Instance terminated by USER, pid = 23787
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (23787) as a result of ORA-1092

现场进行了一系列尝试操作,最后我接手数据库之时报错为:ORA-01190 ORA-01110,无法recover,也无法重建controlfile,陷入了死局

Completed: ALTER DATABASE   MOUNT
Sat Aug 30 10:03:20 2025
ALTER DATABASE OPEN
This instance was first to open
Abort recovery for domain 0
Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_6435.trc:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '+DG/xff/datafile/system0829.dbf'
ORA-1190 signalled during: ALTER DATABASE OPEN...

Sat Aug 30 00:56:32 2025
NOTE: Loaded library: System 
SUCCESS: diskgroup DG was mounted
Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_17302.trc:
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01110: data file 2: '+DG/xff/datafile/sysaux.280.918834827'
ORA-1503 signalled during: create controlfile reuse database xff noarchivelog noresetlogs

对于这种情况,通过Oracle recovery check脚本可以直接发现异常(WRONG RESETLOGS)
wrong-resetlogs

使用Oracle Recovery Tools小工具实现快速恢复
orarecovery
再尝试重建ctl成功
rectl
然后修改数据库scn信息,顺利open数据库
open
后续建议客户逻辑迁移该库

发表在 Oracle备份恢复 | 标签为 , , | 评论关闭

Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障

发表于 2021 年 1 月 6 日 由 惜分飞

今天有一个客户数据库恢复请求,通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)脚本分析发现resetlog信息异常
20210106182747

导致数据库恢复报ORA-01190 ORA-01110错

alter database open
Errors in file c:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4404.trc:
ORA-01190: 控制文件或数据文件 1 来自最后一个 RESETLOGS 之前
ORA-01110: 数据文件 1: 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
ORA-1190 signalled during: alter database open...

通过Oracle Recovery Tools工具进行修复resetlog 信息
20210106183450

再次尝试open数据库报ORA-1248错

SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01248: ?? 44 ????????????
ORA-01110: ???? 44: 'E:\ORADATA\ORCL\XIFENFEI.DBF'

Wed Jan 06 14:44:44 2021
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
ORA-1248 signalled during: alter database open resetlogs...

再次通过Oracle Recovery Tools进行修复SCN,数据库open成功

T:\xff>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 1月 6 14:47:36 2021

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

已连接到空闲例程。

SQL> startup mount 
ORACLE 例程已经启动。

Total System Global Area 6.9214E+10 bytes
Fixed Size                  2182712 bytes
Variable Size            3.5165E+10 bytes
Database Buffers         3.3823E+10 bytes
Redo Buffers              224296960 bytes
数据库装载完毕。
SQL>
SQL>
SQL> alter database open;

数据库已更改。

软件下载:OraRecovery下载
使用说明:使用说明

发表在 小工具 | 标签为 , , | 评论关闭

_ALLOW_RESETLOGS_CORRUPTION

发表于 2016 年 7 月 31 日 由 惜分飞

我相信_ALLOW_RESETLOGS_CORRUPTION 这个参数一定很多人都熟悉,是redo异常恢复的杀手锏之一,以下文章是来自官方的解释

DB_Parameter _ALLOW_RESETLOGS_CORRUPTION 
========================================
 
This documentation has been prepared avoiding the mention of the complex 
structures from the code and to simply give an insight to the 'damage it could 
cause'.  The usage of this parameter leads to an in-consistent Database with no 
other alternative but to rebuild the complete Database.  This parameter could 
be used when we realize that there are no stardard options available and are 
convinced that the customer understands the implications of using the Oracle's 
secret parameter.  The factors to be considered are ;-- 
 
1. Customer does not have a good backup. 
2. A lot of time and money has been invested after the last good backup and     
   there is no possibility for reproduction of the lost data. 
3. The customer has to be ready to export the full database and import it     
   back after creating a new one. 
4. There is no 100% guarantee that by using this parameter the database would 
   come up. 
5. Oracle does not support the database after using this parameter for        
   recovery.    
6. ALL OPTIONS including the ones mentioned in the action part of the error   
   message have been tried. 
 
 

 
By setting _ALLOW_RESETLOGS_CORRUPTION=TRUE, certain consistency checks are 
SKIPPED during database open stage.  This basically means it does not check 
the datafile headers as to what the status was before the shutdown and how it 
was shutdown.  The following cases mention few of the checks that were skipped. 
 
Case-I 
------ 
Verification that the datafile present has not been restored from a BACKUP 
taken before the database was opened successfully by using RESETLOGS.   

ORA-01190: control file or data file %s is from before the last RESETLOGS
    Cause: Attempting to use a data file when the log reset information in  
           the file does not match the control file.  Either the data file or  
           the control file is a backup that was made before the most recent 
           ALTER DATABASE OPEN RESETLOGS. 
   Action: Restore file from a more recent backup. 

 
Case-II 
------- 
Verification that the status bit of the datafile is not in a FUZZY state. 
The datafile could be in this state due to the database going down when the  
 - Datafile was on-line and open 
 - Datafile was not closed cleanly (maybe due to OS). 

ORA-01194: file %s needs more recovery to be consistent 
    Cause: An incomplete recover session was started, but an insufficient 
           number of logs were applied to make the file consistent.  The  
           reported file was not closed cleanly when it was last opened by 
           the database.  It must be recovered to a time when it was not  
           being updated.  The most likely cause of this error is forgetting 
           to restore the file from a backup before doing incomplete  
           recovery. 
   Action: Either apply more logs until the file is consistent or restore the 
           file from an older backup and repeat recovery. 
 

Case-III 
-------- 
Verification that the COMPLETE recover strategies have been applied for 
recovering the datafile and not any of the INCOMPLETE recovery options.  
Basically because the complete recovery is one in which we even apply the 
ON-LINE redo log files and open the DB without reseting the logs. 

ORA-01113: file '%s' needs media recovery starting at log sequence # %s 
    Cause: An attempt was made to open a database file that is in need of  
           media recovery. 
   Action: First apply media recovery to the file. 

 
Case-IV 
------- 
Verification that the datafile has been recovered through an END BACKUP if the 
control file indicates that it was in backup mode. 
This is useful when the DB has crashed while in hot backup mode and we lost 
all log files in DB version's less than V7.2. 

ORA-01195: on-line backup of file %s needs more recovery to be consistent" 
    Cause: An incomplete recovery session was started, but an insufficient  
           number of logs were applied to make the file consistent.  The 
           reported file is an on-line backup which must be recovered to the 
           time the backup ended. 
   Action: Either apply more logs until the file is consistent or resotre 
          the database files from an older backup and repeat recovery. 
 
In version 7.2, we could simply issue the ALTER DATABASE DATAFILE xxxx END 
BACKUP statement and proceed with the recovery.  But again to issue this 
statement, we need to have the ON-LINE redo logs or else we still are forced to
use this parameter. 
 

Case-V 
------ 
Verification that the data file status is not still in (0x10) MEDIA recovery 
FUZZY. 
When recovery is started, a flag is set in the datafile header status flag to 
indicate that the file is presently in media recovery.  This is reset when 
recovery is completed and at times when it has not been reset we are forced to 
use this paramter. 

ORA-01196: file %s is inconsistent due to a failed media recovery session 
    Cause: The file was being recovered but the recovery did not terminate 
           normally.  This left the file in an inconsistent state.  No more  
           recovery was successfully completed on this file. 
   Action: Either apply more logs until the file is consistent or restore the 
           backup again and repeat recovery. 
 
 
Case-VI 
------- 
Verification that the datafile has been restored form a proper backup to 
correspond with the log files.  This situation could happen when we have 
decided that the data file is invalid since its SCN is ahead of the last 
applied logs SCN but it has not failed on one of the ABOVE CHECKS. 

ORA-01152: file '%s' was not restored from a sufficientluy old backup" 
    Cause: A manual recovery session was started, but an insufficient number 
           of logs were applied to make the database consistent.  This file is 
           still in the future of the last log applied.  Note that this  
           mistake can not always be caught. 
   Action: Either apply more logs until the database is consistent or 
           restore the database file from an older backup and repeat  
           recovery.

使用_ALLOW_RESETLOGS_CORRUPTION 参数需谨慎,因为该参数可能导致数据库逻辑不一致,甚至可能把本来很简单的一个恢复弄的非常复杂甚至不可恢复的后果,建议在oracle support支持下使用.另外使用该参数resetlogs库之后,强烈建议通过逻辑方式重建库

发表在 Oracle备份恢复 | 标签为 , , , , , , | 评论关闭