联系:手机/微信(+86 17813235971) QQ(107644445)
标题:resetlogs强制拉库失败并使用备份system文件还原数据库故障处理
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
接手一个库,在open的过程中遭遇到ORA-600 2662错误
Sun May 26 10:15:54 2024 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 84303583 Clearing online redo logfile 1 /data/OracleData/xff/redo01 .log Clearing online log 1 of thread 1 sequence number 8330 Clearing online redo logfile 1 complete Clearing online redo logfile 2 /data/OracleData/xff/redo02 .log Clearing online log 2 of thread 1 sequence number 8327 Clearing online redo logfile 2 complete Clearing online redo logfile 3 /data/OracleData/xff/redo03 .log Clearing online log 3 of thread 1 sequence number 8329 Clearing online redo logfile 3 complete Clearing online redo logfile 4 /data/OracleData/xff/redo04 .log Clearing online log 4 of thread 1 sequence number 8328 Clearing online redo logfile 4 complete Resetting resetlogs activation ID 1431370398 (0x5550fa9e) Online log /data/OracleData/xff/redo01 .log: Thread 1 Group 1 was previously cleared Online log /data/OracleData/xff/redo02 .log: Thread 1 Group 2 was previously cleared Online log /data/OracleData/xff/redo03 .log: Thread 1 Group 3 was previously cleared Online log /data/OracleData/xff/redo04 .log: Thread 1 Group 4 was previously cleared Sun May 26 10:15:59 2024 Setting recovery target incarnation to 3 Sun May 26 10:15:59 2024 Read of datafile '/data/OracleData/xff/temp01.dbf' (fno 201) header failed with ORA-01200 Rereading datafile 201 header failed with ORA-01200 Errors in file /data/u01/app/oracle/diag/rdbms/xff/xff/trace/xff_dbw0_1563 .trc: ORA-01186: file 201 failed verification tests ORA-01122: database file 201 failed verification check ORA-01110: data file 201: '/data/OracleData/xff/temp01.dbf' ORA-01200: actual file size of 3711 is smaller than correct size of 3712 blocks File 201 not verified due to error ORA-01122 Sun May 26 10:15:59 2024 Assigning activation ID 1509069065 (0x59f29109) Thread 1 opened at log sequence 1 Current log # 1 seq# 1 mem# 0: /data/OracleData/xff/redo01.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Sun May 26 10:15:59 2024 SMON: enabling cache recovery Errors in file /data/u01/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_1590 .trc (incident=163897): ORA-00600: internal error code, arguments: [2662], [0], [84303590], [0], [84314659], [12583040] Incident details in : /data/u01/app/oracle/diag/rdbms/xff/xff/incident/incdir_163897/xff_ora_1590_i163897 .trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file /data/u01/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_1590 .trc: ORA-00600: internal error code, arguments: [2662], [0], [84303590], [0], [84314659], [12583040] Errors in file /data/u01/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_1590 .trc: ORA-00600: internal error code, arguments: [2662], [0], [84303590], [0], [84314659], [12583040] Error 600 happened during db open , shutting down database USER (ospid: 1590): terminating the instance due to error 600 |
然后客户使用备份的system01.dbf文件替换了被resetlogs之后文件,导致数据库后续操作无法继续
SQL> recover database ; ORA-00283: recovery session canceled due to errors ORA-19909: datafile 1 belongs to an orphan incarnation ORA-01110: data file 1: '/data/OracleData/xff/system01.dbf' |
这个问题比较简单,通过bbed或者Oracle Recovery Tools修改文件头相关信息,然后open数据库成功
重建控制文件丢失数据文件导致悲剧
Oracle Recovery Tools快速恢复ORA-19909
SQL> recover datafile 1; Media recovery complete. SQL> recover database ; Media recovery complete. SQL> alter database open ; Database altered. |
但是由于system文件有大量坏块导致数据库无法正常登录和导出
[oracle@et-dbserver ~]$ exp "'/ as sysdba'" owner=USERNAME file = /tmp/2user .dmp log= /tmp/2user .log Export: Release 11.2.0.4.0 - Production on Sun May 26 13:00:50 2024 Copyright (c) 1982, 2011, Oracle and /or its affiliates. All rights reserved. EXP-00056: ORACLE error 604 encountered ORA-00604: error occurred at recursive SQL level 1 ORA-01578: ORACLE data block corrupted ( file # 1, block # 86500) ORA-01110: data file 1: '/data/OracleData/xff/system01.dbf' Username: / as sysdba EXP-00056: ORACLE error 604 encountered ORA-00604: error occurred at recursive SQL level 1 ORA-01578: ORACLE data block corrupted ( file # 1, block # 86500) ORA-01110: data file 1: '/data/OracleData/xff/system01.dbf' Username: Password: EXP-00056: ORACLE error 604 encountered ORA-00604: error occurred at recursive SQL level 1 ORA-01578: ORACLE data block corrupted ( file # 1, block # 86500) ORA-01110: data file 1: '/data/OracleData/xff/system01.dbf' ORA-01017: invalid username /password ; logon denied EXP-00005: all allowable logon attempts failed EXP-00000: Export terminated unsuccessfully |
通过dbv检查system数据文件
DBVERIFY: Release 11.2.0.4.0 - Production on Sun May 26 12:33:28 2024 Copyright (c) 1982, 2011, Oracle and /or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /data/OracleData/xff/system01 .dbf Page 1044 is influx - most likely media corrupt Corrupt block relative dba: 0x00400414 ( file 1, block 1044) Fractured block found during dbv: Data in bad block: type : 0 format : 2 rdba: 0x00400414 last change scn: 0x0000.00000000 seq : 0x1 flg: 0x05 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail : 0x1d7f550b check value in block header: 0xa354 computed block checksum: 0x6830 Page 1103 is marked corrupt Corrupt block relative dba: 0x0040044f ( file 1, block 1103) Bad header found during dbv: Data in bad block: type : 0 format : 0 rdba: 0x00000000 last change scn: 0x508f.5f74492e seq : 0x53 flg: 0x0c spare1: 0xc spare2: 0xa6 spare3: 0xc757 consistency value in tail : 0x00000001 check value in block header: 0x8925 computed block checksum: 0x5d3b Page 1143 is marked corrupt Corrupt block relative dba: 0x00400477 ( file 1, block 1143) Bad header found during dbv: Data in bad block: type : 0 format : 0 rdba: 0x00000001 last change scn: 0x65c4.52eb202e seq : 0x28 flg: 0x0e spare1: 0xe spare2: 0xe2 spare3: 0xfa46 consistency value in tail : 0x00000001 check value in block header: 0x6405 computed block checksum: 0x28b1 ……………… Page 124805 is influx - most likely media corrupt Corrupt block relative dba: 0x0041e785 ( file 1, block 124805) Fractured block found during dbv: Data in bad block: type : 6 format : 2 rdba: 0x0041e785 last change scn: 0x0000.0434fc6c seq : 0x2 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail : 0x1991255b check value in block header: 0x6386 computed block checksum: 0x1384 DBVERIFY - Verification complete Total Pages Examined : 130560 Total Pages Processed (Data) : 95634 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 14949 Total Pages Failing (Index): 0 Total Pages Processed (Other): 1774 Total Pages Processed (Seg) : 1669 Total Pages Failing (Seg) : 0 Total Pages Empty : 16251 Total Pages Marked Corrupt : 283 Total Pages Influx : 149 Total Pages Encrypted : 0 Highest block SCN : 84314727 (0.84314727) |
对于这样问题,通过Oracle Recovery Tools实战批量坏块修复,实现数据库可以完美导出数据
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战