ORA-01172 ORA-01151 故障恢复---惜分飞

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

标题：ORA-01172 ORA-01151 故障恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

节点2报Error: Controlfile sequence number in file header is different from the one in memory,导致实例异常

Tue May 09 23:03:24 2023

Thread 2 cannot allocate new log, sequence 16728

Checkpoint not complete

  Current log# 3 seq# 16727 mem# 0: +DATA/xff/onlinelog/group_3.265.941900045

  Current log# 3 seq# 16727 mem# 1: +FRA/xff/onlinelog/group_3.259.941900045

Thread 2 advanced to log sequence 16728 (LGWR switch)

  Current log# 4 seq# 16728 mem# 0: +DATA/xff/onlinelog/group_4.266.941900045

  Current log# 4 seq# 16728 mem# 1: +FRA/xff/onlinelog/group_4.260.941900045

Tue May 09 23:03:31 2023

LNS: Standby redo logfile selected for thread 2 sequence 16728 for destination LOG_ARCHIVE_DEST_2

Tue May 09 23:03:32 2023

Archived Log entry 431615 added for thread 2 sequence 16727 ID 0x5ffc99b5 dest 1:

Tue May 09 23:05:30 2023

Error: Controlfile sequence number in file header is different from the one in memory

       Please check that the correct mount options are used if controlfile is located on NFS

USER (ospid: 30162): terminating the instance

Tue May 09 23:05:30 2023

System state dump requested by (instance=2, osid=30162), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_diag_6650.trc

Instance terminated by USER, pid = 30162

在节点1 进行实例重组之后,节点1 实例异常

Tue May 09 23:04:54 2023

Thread 1 cannot allocate new log, sequence 2060

Checkpoint not complete

  Current log# 1 seq# 2059 mem# 0: +DATA/xff/onlinelog/group_1.261.941899887

  Current log# 1 seq# 2059 mem# 1: +FRA/xff/onlinelog/group_1.257.941899887

Thread 1 advanced to log sequence 2060 (LGWR switch)

  Current log# 2 seq# 2060 mem# 0: +DATA/xff/onlinelog/group_2.262.941899889

  Current log# 2 seq# 2060 mem# 1: +FRA/xff/onlinelog/group_2.258.941899889

Tue May 09 23:04:58 2023

********************* ATTENTION: ********************

 The controlfile header block returned by the OS

 has a sequence number that is too old.

 The controlfile might be corrupted.

 PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE

 without following the steps below.

 RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE

 TO THE DATABASE, if the controlfile is truly corrupted.

 In order to re-start the instance safely,

 please do the following:

 (1) Save all copies of the controlfile for later

     analysis and contact your OS vendor and Oracle support.

 (2) Mount the instance and issue:

     ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

 (3) Unmount the instance.

 (4) Use the script in the trace file to

     RE-CREATE THE CONTROLFILE and open the database.

*****************************************************

Tue May 09 23:05:31 2023

Reconfiguration started (old inc 20, new inc 22)

List of instances:

 1 (myinst: 1)

 Global Resource Directory frozen

 * dead instance detected - domain 0 invalid = TRUE

 Communication channels reestablished

 Master broadcasted resource hash value bitmaps

 Non-local Process blocks cleaned out

Tue May 09 23:05:31 2023

 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

Tue May 09 23:05:31 2023

 LMS 0: 3 GCS shadows cancelled, 0 closed, 0 Xw survived

 Set master node info

 Submitted all remote-enqueue requests

 Dwn-cvts replayed, VALBLKs dubious

 All grantable enqueues granted

 Post SMON to start 1st pass IR

Tue May 09 23:05:32 2023

Instance recovery: looking for dead threads

 Submitted all GCS remote-cache requests

 Post SMON to start 1st pass IR

 Fix write in gcs resources

Reconfiguration complete

Tue May 09 23:06:00 2023

ARC1 (ospid: 26512): terminating the instance

Tue May 09 23:06:00 2023

System state dump requested by (instance=1, osid=26512 (ARC1)), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_diag_26311.trc

Tue May 09 23:06:01 2023

ORA-1092 : opitsk aborting process

Instance terminated by ARC1, pid = 26512

实例重启报错

Recovery of Online Redo Log: Thread 1 Group 1 Seq 2059 Reading mem 0

  Mem# 0: +DATA/dbm/onlinelog/group_1.261.941899887

  Mem# 1: +FRA/dbm/onlinelog/group_1.257.941899887

Recovery of Online Redo Log: Thread 2 Group 3 Seq 16727 Reading mem 0

  Mem# 0: +DATA/dbm/onlinelog/group_3.265.941900045

  Mem# 1: +FRA/dbm/onlinelog/group_3.259.941900045

Recovery of Online Redo Log: Thread 2 Group 4 Seq 16728 Reading mem 0

  Mem# 0: +DATA/dbm/onlinelog/group_4.266.941900045

  Mem# 1: +FRA/dbm/onlinelog/group_4.260.941900045

Hex dump of (file 1, block 102777) in trace file /u01/app/oracle/diag/rdbms/dbm/dbm2/trace/dbm2_ora_30749.trc

Reading datafile '+DATA/dbm/datafile/system.256.941899799' for corruption at rdba: 0x00419179 (file 1, block 102777)

Reread (file 1, block 102777) found different corrupt data (logically corrupt)

Hex dump of (file 1, block 102777) in trace file /u01/app/oracle/diag/rdbms/dbm/dbm2/trace/dbm2_ora_30749.trc

RECOVERY OF THREAD 2 STUCK AT BLOCK 102777 OF FILE 1

Abort recovery for domain 0

Aborting crash recovery due to error 1172

Errors in file /u01/app/oracle/diag/rdbms/dbm/dbm2/trace/dbm2_ora_30749.trc:

ORA-01172: recovery of thread 2 stuck at block 102777 of file 1

ORA-01151: use media recovery to recover block, restore backup if needed

Abort recovery for domain 0

Errors in file /u01/app/oracle/diag/rdbms/dbm/dbm2/trace/dbm2_ora_30749.trc:

ORA-01172: recovery of thread 2 stuck at block 102777 of file 1

ORA-01151: use media recovery to recover block, restore backup if needed

ORA-1172 signalled during: ALTER DATABASE OPEN /* db agent *//* {0:890:17} */...

人工recover操作失败报ORA-600 3020错误

SQL> recover datafile 1;

ORA-00283: recovery session canceled due to errors

ORA-00600: internal error code, arguments: [3020], [1], [102777], [4297081],[], []

ORA-10567: Redo is inconsistent with data block (file# 1, block# 102777, file

offset is 841949184 bytes)

ORA-10564: tablespace SYSTEM

ORA-01110: data file 1: '+DATA/dbm/datafile/system.256.941899799'

ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 469884

 

---alert日志

Tue May 09 23:28:44 2023

ALTER DATABASE RECOVER  datafile 1 

Media Recovery Start

Serial Media Recovery started

Recovery of Online Redo Log: Thread 2 Group 3 Seq 16727 Reading mem 0

  Mem# 0: +DATA/xff/onlinelog/group_3.265.941900045

  Mem# 1: +FRA/xff/onlinelog/group_3.259.941900045

ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 1  ...

ALTER DATABASE RECOVER    CONTINUE DEFAULT 

Media Recovery Log +FRA/xff/archivelog/2023_05_09/thread_1_seq_2055.20899.1136415701

ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...

ALTER DATABASE RECOVER    CONTINUE DEFAULT 

Media Recovery Log +FRA/xff/archivelog/2023_05_09/thread_1_seq_2056.20837.1136415753

ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...

ALTER DATABASE RECOVER    CONTINUE DEFAULT 

Media Recovery Log +FRA/xff/archivelog/2023_05_09/thread_1_seq_2057.20911.1136415803

ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...

ALTER DATABASE RECOVER    CONTINUE DEFAULT 

Media Recovery Log +FRA/xff/archivelog/2023_05_09/thread_1_seq_2058.21898.1136415853

Recovery of Online Redo Log: Thread 2 Group 4 Seq 16728 Reading mem 0

  Mem# 0: +DATA/xff/onlinelog/group_4.266.941900045

  Mem# 1: +FRA/xff/onlinelog/group_4.260.941900045

Recovery of Online Redo Log: Thread 1 Group 1 Seq 2059 Reading mem 0

  Mem# 0: +DATA/xff/onlinelog/group_1.261.941899887

  Mem# 1: +FRA/xff/onlinelog/group_1.257.941899887

Hex dump of (file 1, block 102777) in trace file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_16246.trc

Reading datafile '+DATA/xff/datafile/system.256.941899799' for corruption at rdba: 0x00419179 (file 1, block 102777)

Reread (file 1, block 102777) found different corrupt data (logically corrupt)

Hex dump of (file 1, block 102777) in trace file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_16246.trc

Tue May 09 23:28:59 2023

Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_16246.trc  (incident=6868615):

ORA-00600: internal error code, arguments: [3020], [1], [102777], [4297081], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 1, block# 102777, file offset is 841949184 bytes)

ORA-10564: tablespace SYSTEM

ORA-01110: data file 1: '+DATA/xff/datafile/system.256.941899799'

ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 469884

Incident details in: /u01/app/oracle/diag/rdbms/xff/xff1/incident/incdir_6868615/xff1_ora_16246_i6868615.trc

Tue May 09 23:29:00 2023

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Media Recovery failed with error 600

ORA-283 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...

ALTER DATABASE RECOVER CANCEL

ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...

根据上述报错信息可以确认报错的是一个index,而且非系统核心对象,可以通过allow 1 corruption方式进行恢复,并且open库成功

SQL> recover  datafile 1 allow 1 corruption;

Media recovery complete.

SQL> alter database open;

 

Database altered.

 

SQL> select owner,object_name,object_type from dba_objects where object_id=469884;

 

OWNER

--------------------------------------------------------------------------------

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_TYPE

---------------------------------------------------------

SYSTEM

PK_XFF_SERVERS

INDEX

 

SQL> alter index system.PK_XFF_SERVERS rebuild online;

 

Index altered.

数据库完美恢复,数据0丢失,业务可以直接正常使用

• ORA-600 3417和ORA-600 3005故障处理
• ORA-00322 ORA-00312恢复
• 某医院存储掉线导致Oracle数据库故障恢复
• ORA-600 3020错误引起ORA-600 2663
• ORA-600 kcbzib_kcrsds_1报错
• Oracle 启动后一会儿就挂掉故障处理—ORA-600 17182
• Controlfile sequence number in file header is different from the one in memory
• ORA-600 kcbzpbuf_1故障恢复
• ORA-00742 ORA-00312故障恢复
• ORA-600 3417故障处理
• Control file mount id mismatch!故障处理
• Oracle Recovery Tools 解决ORA-600 3020故障