超融合oracle rac,超融合硬件损坏导致Oracle RAC异常恢复实录

待我称王封你为后i 2023-01-18 09:05 172阅读 0赞

墨墨导读：一套Oracle RAC环境运行在HW超融合环境中，由于硬件问题导致数据库crash，期间出现了不少数据坏块，本文详述整个恢复过程，希望对大家有帮助。

前几天某客户遇到一个棘手问题：其一套Oracle RAC环境运行在HW超融合环境中，由于硬件问题导致数据库crash，期间出现了不少数据坏块，不过还好客户有RMAN物理备份，因此客户提前进行了全库Restore。

首先我们来看下相关日志信息：Trying mirror side DATADG\_0002.Reread of blocknum=145005, file=+DATADG/xxxxx/datafile/supervise. found same corrupt dataReread of blocknum=145005, file=+DATADG/xxxxx/datafile/supervise. found valid dataSun Apr 26 01:22:35 2020Hex dump of (file 11, block 230454) in trace file /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/trace/xxxxx1\_ora\_13254.trcCorrupt block relative dba: 0x02c38436 (file 11, block 230454)Bad header found during backing up datafileData in bad block:type: 6 format: 2 rdba: 0x0082f736last change scn: 0x0000.4b5a95d7 seq: 0x1 flg: 0x06spare1: 0x0 spare2: 0x0 spare3: 0x0consistency value in tail: 0x95d70601check value in block header: 0xa3d1computed block checksum: 0x0Trying mirror side DATADG\_0002.Reread of blocknum=230454, file=+DATADG/xxxxx/datafile/supdb\_etyy.dbf. found same corrupt dataReread of blocknum=230454, file=+DATADG/xxxxx/datafile/supdb\_etyy.dbf. found valid dataHex dump of (file 11, block 230458) in trace file /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/trace/xxxxx1\_ora\_13254.trcCorrupt block relative dba: 0x02c3843a (file 11, block 230458)

上述类似的大量坏块信息，最终导致数据库挂掉。

我们可以看到客户这里asm diskgroup为normal冗余；当primary extent数据有问题时，Oracle会尝试从mirror extent去获取；如果mirror extent是正常的，那么Oracle会自动进行修复，否则会导致数据丢失，严重的话会导致数据库宕机。Mon Apr 27 02:01:15 2020Read of datafile '+DATADG/orclsk/datafile/sysaux.260.956077231' (fno 2) header failed with ORA-01207Rereading datafile 2 header from mirror side 'DATADG\_0000' failed with ORA-01207Rereading datafile 2 header from mirror side 'DATADG\_0006' failed with ORA-01207Errors in file /u01/app/oracle/diag/rdbms/orclsk/orclsk1/trace/orclsk1\_ckpt\_4893.trc:ORA-63999: data file suffered media failureORA-01122: database file 2 failed verification checkORA-01110: data file 2: '+DATADG/orclsk/datafile/sysaux.260.956077231'ORA-01207: file is more recent than control file - old control fileErrors in file /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/trace/xxxxx1\_ckpt\_4893.trc:ORA-63999: data file suffered media failureORA-01122: database file 2 failed verification checkORA-01110: data file 2: '+DATADG/xxxxx/datafile/sysaux.260.956077231'ORA-01207: file is more recent than control file - old control fileCKPT (ospid: 4893): terminating the instance due to error 63999

因为部分文件需要介质恢复(因为primary和mirror 数据都异常)，而数据库强行终止了实例。

数据库实例重启后则开始出现大量错误：Mon Apr 27 02:02:03 2020Read of datafile '+DATADG/xxxxx/datafile/mscsz.dbf' (fno 40) header failed with ORA-01207Rereading datafile 40 header from mirror side 'DATADG\_0000' failed with ORA-01207Rereading datafile 40 header from mirror side 'DATADG\_0005' failed with ORA-01207Errors in file /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/trace/xxxxx1\_dbw0\_115709.trc:ORA-01186: file 40 failed verification testsORA-01122: database file 40 failed verification checkORA-01110: data file 40: '+DATADG/xxxxx/datafile/mscsz.dbf'ORA-01207: file is more recent than control file - old control fileFile 40 not verified due to error ORA-01122.......Mon Apr 27 02:02:07 2020QMNC started with pid=46, OS id=115887Errors in file /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/trace/xxxxx1\_ora\_115771.trc (incident=299881):ORA-01578: ORACLE data block corrupted (file \# 1, block \# 3620)ORA-01110: data file 1: '+DATADG/xxxxx/datafile/system.259.956077225'Incident details in: /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/incident/incdir\_299881/xxxxx1\_ora\_115771\_i299881.trcMon Apr 27 02:02:16 2020Dumping diagnostic data in directory=\[cdmp\_20200427020216\], requested by (instance=1, osid=115771), summary=\[incident=299881\].Mon Apr 27 02:02:17 2020Sweep \[inc\]\[299881\]: completedMon Apr 27 02:02:18 2020Completed: ALTER DATABASE OPEN /\* db agent \*//\* \{0:13:1899\} \*/Mon Apr 27 02:02:18 2020ORA-21700 encountered when generating server alert SMG-4120ORA-21700 encountered when generating server alert SMG-4121ORA-21700 encountered when generating server alert SMG-4121Mon Apr 27 02:02:20 2020Starting background process CJQ0Mon Apr 27 02:02:20 2020CJQ0 started with pid=54, OS id=115990Mon Apr 27 02:02:20 2020Errors in file /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/trace/xxxxx1\_m004\_115984.trc (incident=300025):ORA-01578: ORACLE data block corrupted (file \# 2, block \# 101561)ORA-01110: data file 2: '+DATADG/xxxxx/datafile/sysaux.260.956077231'Incident details in: /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/incident/incdir\_300025/xxxxx1\_m004\_115984\_i300025.trcDumping diagnostic data in directory=\[cdmp\_20200427020222\], requested by (instance=1, osid=115984 (M004)), summary=\[incident=300025\].Mon Apr 27 02:02:22 2020Sweep \[inc\]\[300025\]: completedErrors in file /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/trace/xxxxx1\_m004\_115984.trc (incident=300026):ORA-01578: ORACLE data block corrupted (file \# , block \# )Incident details in: /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/incident/incdir\_300026/xxxxx1\_m004\_115984\_i300026.trcErrors in file /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/trace/xxxxx1\_m004\_115984.trc (incident=300027):ORA-01578: ORACLE data block corrupted (file \# , block \# )ORA-06512: at "SYS.PRVT\_ADVISOR", line 6144ORA-06512: at line 1Incident details in: /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/incident/incdir\_300027/xxxxx1\_m004\_115984\_i300027.trcDumping diagnostic data in directory=\[cdmp\_20200427020224\], requested by (instance=1, osid=115984 (M004)), summary=\[incident=300026\].Dumping diagnostic data in directory=\[cdmp\_20200427020224\], requested by (instance=1, osid=115984 (M004)), summary=\[incident=300027\].Mon Apr 27 02:02:46 2020Hex dump of (file 1, block 24895) in trace file /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/trace/xxxxx1\_ora\_116134.trcCorrupt block relative dba: 0x0040613f (file 1, block 24895)Bad header found during buffer readData in bad block:type: 6 format: 2 rdba: 0x00405c3flast change scn: 0x0000.0002a412 seq: 0x1 flg: 0x06spare1: 0x0 spare2: 0x0 spare3: 0x0consistency value in tail: 0xa4120601check value in block header: 0xa486computed block checksum: 0x0Reading datafile '+DATADG/xxxxx/datafile/system.259.956077225' for corruption at rdba: 0x0040613f (file 1, block 24895)Read datafile mirror 'DATADG\_0001' (file 1, block 24895) found same corrupt data (no logical check)Read datafile mirror 'DATADG\_0003' (file 1, block 24895) found valid dataHex dump of (file 1, block 24895) in trace file /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/trace/xxxxx1\_ora\_116134.trcRepaired corruption at (file 1, block 24895)

不难看出数据库控制文件和system都出现了异常，这也难怪数据库无法正常打开。

期间还出现了ora-00600 \[4193\]等常见错误：Mon Apr 27 02:55:26 2020QMNC started with pid=44, OS id=3072Mon Apr 27 02:55:26 2020Errors in file /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/trace/xxxxx1\_mmon\_2939.trc (incident=323867):ORA-00600: internal error code, arguments: \[4193\], \[\], \[\], \[\], \[\], \[\], \[\], \[\], \[\], \[\], \[\], \[\]Incident details in: /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/incident/incdir\_323867/xxxxx1\_mmon\_2939\_i323867.trcUse ADRCI or Support Workbench to package the incident.See Note 411.1 at My Oracle Support for error and packaging details.Completed: ALTER DATABASE OPEN /\* db agent \*//\* \{0:17:166\} \*/ARC3: Archival startedARC0: STARTING ARCH PROCESSES COMPLETEMon Apr 27 02:55:29 2020Dumping diagnostic data in directory=\[cdmp\_20200427025529\], requested by (instance=1, osid=2939 (MMON)), summary=\[incident=323867\].Block recovery from logseq 17833, block 46 to scn 1551059886Recovery of Online Redo Log: Thread 1 Group 1 Seq 17833 Reading mem 0Mem\# 0: +DATADG/xxxxx/onlinelog/group\_1.257.956077213Mem\# 1: +ARCHDG/xxxxx/onlinelog/group\_1.257.956077217Block recovery stopped at EOT rba 17833.54.16Block recovery completed at rba 17833.54.16, scn 0.1551059805Block recovery from logseq 17833, block 46 to scn 1551059575Recovery of Online Redo Log: Thread 1 Group 1 Seq 17833 Reading mem 0Mem\# 0: +DATADG/xxxxx/onlinelog/group\_1.257.956077213Mem\# 1: +ARCHDG/xxxxx/onlinelog/group\_1.257.956077217Block recovery completed at rba 17833.46.16, scn 0.1551059664Errors in file /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/trace/xxxxx1\_mmon\_2939.trc (incident=323868):ORA-00600: internal error code, arguments: \[4193\], \[\], \[\], \[\], \[\], \[\], \[\], \[\], \[\], \[\], \[\], \[\]Incident details in: /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/incident/incdir\_323868/xxxxx1\_mmon\_2939\_i323868.trcUse ADRCI or Support Workbench to package the incident.See Note 411.1 at My Oracle Support for error and packaging details.Dumping diagnostic data in directory=\[cdmp\_20200427025534\], requested by (instance=1, osid=2939 (MMON)), summary=\[incident=323868\].Block recovery from logseq 17833, block 46 to scn 1551059886Recovery of Online Redo Log: Thread 1 Group 1 Seq 17833 Reading mem 0Mem\# 0: +DATADG/xxxxx/onlinelog/group\_1.257.956077213Mem\# 1: +ARCHDG/xxxxx/onlinelog/group\_1.257.956077217Block recovery completed at rba 17833.54.16, scn 0.1551059888Block recovery from logseq 17833, block 46 to scn 1551059888Recovery of Online Redo Log: Thread 1 Group 1 Seq 17833 Reading mem 0Mem\# 0: +DATADG/xxxxx/onlinelog/group\_1.257.956077213Mem\# 1: +ARCHDG/xxxxx/onlinelog/group\_1.257.956077217Block recovery completed at rba 17833.56.16, scn 0.1551059889Mon Apr 27 02:55:36 2020。。。。。。

对于此次恢复case总体来讲比较简单，这里提供一下处理思路：首先利用客户的归档和Redo(Redo log客户已copy到了本地进行备份)，进行正常的recover database using backup controlfile until cancel操作；

由于恢复过程中出现了ora-00600 \[3020\]错误，因此需要通过recover database allow xx corruption的方式进行；

完成恢复之后尝试打开数据库；

打开数据库时仍然提示ora-01113和ora-01110错误，即system文件还需要进行恢复；这种情况下只能先强制拉库；通过加入\*.\_allow\_resetlogs\_corruption=TRUE \*.\_allow\_error\_simulation=true 等隐含参数即可；

若上述参数后仍然提示undo 存在坏块，由于该数据库版本在报错时不会直接提示具体是哪个回滚段有问题，在alter database open resetlogs之前通过10046 event定位error cursor，找到回滚段名称即可；

使用\_corrupted\_rollback\_segments参数屏蔽回滚段；

open resetlogs数据库成功；

最后就是重建undo以及处理相关坏块等善后工作。

恢复完成之后，由于客户担心HW超融合环境再次出现故障因此进行了全库备份并进行数据迁移到新平台，到这里这个case告一段落。