免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 1861 | 回复: 0

[数据库] 异常断电导致current redo损坏处理 [复制链接]

论坛徽章:
8
数据库技术版块每日发帖之星
日期:2016-04-29 06:20:00数据库技术版块每日发帖之星
日期:2016-04-30 06:20:00数据库技术版块每日发帖之星
日期:2016-05-13 06:20:00数据库技术版块每日发帖之星
日期:2016-05-15 06:20:00数据库技术版块每日发帖之星
日期:2016-05-19 06:20:00数据库技术版块每日发帖之星
日期:2016-05-20 06:20:00数据库技术版块每日发帖之星
日期:2016-06-02 06:20:00数据库技术版块每日发帖之星
日期:2016-06-05 06:20:00
发表于 2016-05-25 16:18 |显示全部楼层
本帖最后由 我忒忙 于 2016-05-26 16:07 编辑

异常断电导致current redo损坏处理
一、数据库启动异常
网友因异常断电导致数据库不能正常启动,处理过程记录
1、sqlplus中提示
C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
 
SQL*Plus: Release 11.1.0.6.0 - Production on 星期三 12月 7 12:57:02 2011
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
已连接到空闲例程。
 
SQL> startup
ORACLE 例程已经启动。
 
Total System Global Area 1581916160 bytes
Fixed Size                  1336060 bytes
Variable Size             964693252 bytes
Database Buffers          603979776 bytes
Redo Buffers               11907072 bytes
数据库装载完毕。
ORA-00368: 重做日志块中的校验和错误
ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11
ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG'
2、alert日志文件
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_2960.trc  (incident=214262):
ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11
ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG'
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_214262\hzyl_ora_2960_i214262.trc
Aborting crash recovery due to error 368
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_2960.trc:
ORA-00368: 重做日志块中的校验和错误
ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11
ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG'
ORA-368 signalled during: ALTER DATABASE OPEN...
Wed Dec 07 12:57:17 2011
Trace dumping is performing id=[cdmp_20111207125717]
Wed Dec 07 12:57:17 2011
Sweep Incident[214262]: completed
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_m000_2400.trc  (incident=214326):
ORA-00353: log corruption near block 12014 change 9743799889 time 12/05/2011 09:21:11
ORA-00312: online log 3 thread 1: 'R:\ORADATA\HZYL\REDO03.LOG'
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_214326\hzyl_m000_2400_i214326.trc
Trace dumping is performing id=[cdmp_20111207125718]
二、查询是损坏日志组
SQL> col member for a35
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b
   2 where a.group#=b.group#;
 
    GROUP# STATUS           MEMBER
---------- ---------------- -----------------------------------
         3 CURRENT          R:\ORADATA\HZYL\REDO03.LOG
         2 INACTIVE         R:\ORADATA\HZYL\REDO02.LOG
         1 INACTIVE         R:\ORADATA\HZYL\REDO01.LOG
确定是因为断电导致当前redo 损坏,需要使用隐含参数+不完全恢复处理(经验)

三、不完全恢复
SQL> recover database until cancel;
ORA-00279: 更改 9743796718 (在 12/05/2011 09:07:40 生成) 对于线程 1 是必需的
ORA-00289: 建议: S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC
ORA-00280: 更改 9743796718 (用于线程 1) 在序列 #15114 中
 
 
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: 无法打开归档日志
'S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC'
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
 
 
ORA-00308: 无法打开归档日志
'S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC'
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
 
 
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'R:\ORADATA\HZYL\SYSTEM01.DBF'
四、启用隐含参数,尝试打开数据库
SQL> create pfile='d:/1.txt' from spfile;
 
文件已创建。
 
#############添加隐含参数##################
*._allow_resetlogs_corruption=TRUE
*._allow_error_simulation=TRUE
##########################################
 
SQL> shutdown immediate
ORA-01109: 数据库未打开
 
 
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup pfile ='d:\1.txt';
ORACLE 例程已经启动。
 
Total System Global Area 1581916160 bytes
Fixed Size                  1336060 bytes
Variable Size             964693252 bytes
Database Buffers          603979776 bytes
Redo Buffers               11907072 bytes
数据库装载完毕。
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项
 
 
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-00603: ORACLE 服务器会话因致命错误而终止
进程 ID: 3388
会话 ID: 335 序列号: 5
五、查看alert日志
Wed Dec 07 13:02:49 2011
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216664):
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216664\hzyl_ora_3388_i216664.trc
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc:
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 338: terminating the instance due to error 600
Wed Dec 07 13:02:50 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mmnl_3884.trc  (incident=216640):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216640\hzyl_mmnl_3884_i216640.trc
Wed Dec 07 13:02:50 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_diag_4020.trc  (incident=216552):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216552\hzyl_diag_4020_i216552.trc
Wed Dec 07 13:02:50 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dia0_2896.trc  (incident=216584):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216584\hzyl_dia0_2896_i216584.trc
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_pmon_2964.trc  (incident=216536):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216536\hzyl_pmon_2964_i216536.trc
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mmon_3764.trc  (incident=216632):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216632\hzyl_mmon_3764_i216632.trc
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dbrm_2060.trc  (incident=216560):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_psp0_1476.trc  (incident=21656:
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Wed Dec 07 13:02:52 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mman_3040.trc  (incident=216576):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Wed Dec 07 13:02:52 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dbw0_2116.trc  (incident=216592):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_lgwr_2624.trc  (incident=216600):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_smon_592.trc  (incident=216616):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ckpt_3052.trc  (incident=21660:
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Instance terminated by USER, pid = 3388
ORA-1092 signalled during: alter database open resetlogs...
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216665):
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-01092: ORACLE 实例终止。强制断开连接
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216666):
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-01092: ORACLE 实例终止。强制断开连接
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc:
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-01092: ORACLE 实例终止。强制断开连接
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Wed Dec 07 13:02:53 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=218920):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_218920\hzyl_ora_3388_i218920.trc
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_218920\hzyl_ora_3388_i218920.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
这里看到熟悉的ora-600 [2662],处理方法增加scn,参考ORA-00600 [2662]

六、增加SCN,打开数据库
SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断

 
C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
 
SQL*Plus: Release 11.1.0.6.0 - Production on 星期三 12月 7 13:04:51 2011
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
已连接到空闲例程。
 
SQL> startup pfile ='d:\1.txt' mount;
ORACLE 例程已经启动。
 
Total System Global Area 1581916160 bytes
Fixed Size                  1336060 bytes
Variable Size             964693252 bytes
Database Buffers          603979776 bytes
Redo Buffers               11907072 bytes
数据库装载完毕。
SQL> alter session set events '10015 trace name adjust_scn level 1';
 
会话已更改。
 
 
SQL> alter database open;
 
数据库已更改。
 
SQL> select status from v$instance;
 
STATUS
------------
OPEN
七、启动过程中,alert日志现ORA-00600[2256]
Completed: ALTER DATABASE   MOUNT
Wed Dec 07 13:06:07 2011
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Completed redo scan
 1 redo blocks read, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 1, block 2, scn 9743796722
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: R:\ORADATA\HZYL\REDO01.LOG
Completed redo application
Completed crash recovery at
 Thread 1: logseq 1, block 3, scn 9743816724
 0 data blocks read, 0 data blocks written, 1 redo blocks read
Wed Dec 07 13:06:08 2011
Thread 1 advanced to log sequence 2
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: R:\ORADATA\HZYL\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Dec 07 13:06:08 2011
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3096.trc  (incident=219072):
ORA-00600: 内部错误代码, 参数: [2256], [0], [1073741824], [2], [1153882138], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_219072\hzyl_ora_3096_i219072.trc
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Wed Dec 07 13:06:09 2011
Trace dumping is performing id=[cdmp_20111207130609]
Opening with internal Resource Manager plan
Wed Dec 07 13:06:10 2011
Sweep Incident[219072]: completed
Starting background process FBDA
Starting background process SMCO
Wed Dec 07 13:06:10 2011
FBDA started with pid=26, OS id=2436
Wed Dec 07 13:06:10 2011
SMCO started with pid=27, OS id=2876
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Dec 07 13:06:11 2011
QMNC started with pid=28, OS id=2812
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Wed Dec 07 13:06:15 2011
db_recovery_file_dest_size of 10000 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Dec 07 13:06:25 2011
Completed: alter database open
打开过程中,出现ORA-00600[2256],但是数据库还是正常打开,具体原因待研究

更多Oracle学习:
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP