联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
由于系统性能问题或者底层io问题,数据库alert日志报一下控制文件损坏错误然后crash掉
Mon Nov 13 08:06:44 2023
Thread 1 advanced to log sequence 12100 (LGWR switch)
Current log# 1 seq# 12100 mem# 0: /u01/oracle/oradata/xifenfei/redo01.log
Mon Nov 13 09:23:59 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.
*****************************************************
USER (ospid: 17064): terminating the instance
Mon Nov 13 09:24:00 2023
System state dump requested by (instance=1, osid=17064), summary=[abnormal instance termination].
重启数据库报ORA-01122 ORA-01110 ORA-01207错误
Mon Nov 13 10:11:21 2023 ALTER DATABASE OPEN Errors in file /u01/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_25824.trc: ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '/u01/oracle/oradata/xifenfei/system01.dbf' ORA-01207: file is more recent than control file - old control file ORA-1122 signalled during: ALTER DATABASE OPEN...
处理好上述错误之后遭遇ORA-01122 ORA-01110 ORA-01200,类似文章:
bbed处理ORA-01200故障
ORA-01122 ORA-01200故障处理
Mon Nov 13 10:51:48 2023 alter database open Read of datafile '/u01/oracle/oradata/xifenfei/sysaux01.dbf' (fno 2) header failed with ORA-01200 Rereading datafile 2 header failed with ORA-01200 Errors in file /u01/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_24148.trc: ORA-01122: database file 2 failed verification check ORA-01110: data file 2: '/u01/oracle/oradata/xifenfei/sysaux01.dbf' ORA-01200: actual file size of 2860800 is smaller than correct size of 2867200 blocks ORA-1122 signalled during: alter database open...
解决上述错误之后,尝试open库报ORA-00314 ORA-00312之类错误
Mon Nov 13 18:00:43 2023 alter database open Beginning crash recovery of 1 threads parallel recovery started with 15 processes Started redo scan Completed redo scan read 61894 KB redo, 589 data blocks need recovery Started redo application at Thread 1: logseq 12100, block 112760 Recovery of Online Redo Log: Thread 1 Group 1 Seq 12100 Reading mem 0 Mem# 0: /u01/oracle/oradata/xifenfei/redo01.log Completed redo application of 1.20MB Mon Nov 13 18:00:44 2023 Hex dump of (file 2, block 39078) in trace file /u01/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p011_27469.trc Reading datafile '/u01/oracle/oradata/xifenfei/sysaux01.dbf' for corruption at rdba: 0x008098a6 (file 2, block 39078) Reread (file 2, block 39078) found same corrupt data (logically corrupt) RECOVERY OF THREAD 1 STUCK AT BLOCK 39078 OF FILE 2 Mon Nov 13 18:00:44 2023 Exception [type: SIGSEGV, Address not mapped to object][ADDR:0xC][PC:0x95FB838, kdxlin()+4946][flags: 0x0, count: 1] Mon Nov 13 18:00:44 2023 Exception [type: SIGSEGV, Address not mapped to object][ADDR:0xC][PC:0x95FB4DE, kdxlin()+4088][flags: 0x0, count: 1] Errors in file /u01/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p011_27469.trc: ORA-00314: log 2 of thread 1, expected sequence# 12093 doesn't match 12085 ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/xifenfei/redo02.log' Errors in file /u01/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p011_27469.trc: ORA-00314: log 3 of thread 1, expected sequence# 12096 doesn't match 12080 ORA-00312: online log 3 thread 1: '/u01/oracle/oradata/xifenfei/redo03.log' ORA-00314: log 2 of thread 1, expected sequence# 12093 doesn't match 12085 ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/xifenfei/redo02.log'
后面继续处理遇到类似这样错误
ALTER DATABASE RECOVER CANCEL Errors in file /u01/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_pr00_31110.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: '/u01/oracle/oradata/xifenfei/system01.dbf' ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ... ALTER DATABASE RECOVER CANCEL ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ... Mon Nov 13 19:06:28 2023 alter database open resetlogs ORA-1194 signalled during: alter database open resetlogs...
最后确认其他数据文件均可recover 成功,只有file 2 无法正常recover
SQL> recover datafile 1; Media recovery complete. SQL> recover datafile 2; ORA-00283: recovery session canceled due to errors ORA-00600: internal error code, arguments: [3020], [2], [950840], [9339448], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 2, block# 950840, file offset is 3494313984 bytes) ORA-10564: tablespace SYSAUX ORA-01110: data file 2: '/u01/oracle/oradata/xifenfei/sysaux01.dbf' ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK' SQL> recover datafile 3; Media recovery complete. SQL> recover datafile 4; Media recovery complete. SQL> recover datafile 5; Media recovery complete. SQL> recover datafile 6; Media recovery complete. SQL> recover datafile 7; Media recovery complete. SQL> recover datafile 2 allow 1 corruption; ORA-00283: recovery session canceled due to errors ORA-00600: internal error code, arguments: [3020], [2], [2410240], [10798848], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 2, block# 2410240, file offset is 2564816896 bytes) ORA-10564: tablespace SYSAUX ORA-01110: data file 2: '/u01/oracle/oradata/xifenfei/sysaux01.dbf' ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
通过bbed修改文件头,直接open数据库成功,并协助客户顺利导出数据
参考类似文章:
使用bbed修复损坏datafile header
使用bbed让rac中的sysaux数据文件online
