sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle故障恢复报错ORA-01190 ORA-600 [krhpfh_03-1202]

时间:2016-11-30 21:15   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库无法启动需要紧急恢复,非归档数据库恢复过程中遇到报错ORA-01190 ORA-600 [krhpfh_03-1202]。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 非归档恢复遭遇ORA-01190 和 ORA-600 [krhpfh_03-1202]–恢复小记

群中一位网友遇到的问题,硬件故障导致undo损坏,且是非归档,通过dbv检测发现大量坏块。

C:\> dbv file="D:\ORACLE\PRODUCT\10.2.0\ORADATA\CL7101\UNDOTBS01.DBF" blocksize=8192

spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x98190202
check value in block header: 0xf293
computed block checksum: 0x1b0d
....................................
....................................
............华丽的省略线............
....................................
....................................
DBVERIFY - 验证完成

检查的页总数: 10240
处理的页总数 (数据): 0
失败的页总数 (数据): 0
处理的页总数 (索引): 0
失败的页总数 (索引): 0
处理的页总数 (其它): 9882
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 0
标记为损坏的总页数: 410
流入的页总数: 89
最高块 SCN            : 336701341 (0.336701341)


通过trace,我发现里面有如下信息:
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
*** 2012-03-22 21:27:31.406
SMON: following errors trapped and ignored:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00376: 此时无法读取文件 2
ORA-01110: 数据文件 2: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\CL7101\UNDOTBS01.DBF'

*** 2012-03-22 21:27:31.421
SMON: following errors trapped and ignored:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00376: 此时无法读取文件 2
ORA-01110: 数据文件 2: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\CL7101\UNDOTBS01.DBF'

于是使用
_corrupted_rollback_segments= _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$


然后再次mount,然后recover一下,成功open数据库,但是查询发现file 5和file 6有问题。
显示为missing,经过询问在另外一个路径下找到了文件,然后我进行了rename操作,接着进行recover,发现报错。

SQL> SELECT file#,STATUS,checkpoint_change# FROM v$datafile ORDER BY 1;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 SYSTEM           336751539
         2 ONLINE           336751539
         3 ONLINE           336751539
         4 ONLINE           336751539
         5 RECOVER                  0
         6 RECOVER                  0

已选择6行。

SQL> recover datafile 5;

ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [krhpfh_03-1202], [fno =], [5], [fhcrt =], [754361723], [cptim =], [0], []
ORA-01110: 数据文件 5: D:\ORACLE\PRODUCT\10.2.0\ORADATA\CL7101\CL7101.DBF


SQL> recover datafile 6;

ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [krhpfh_03-1202], [fno =], [6], [fhcrt =], [754361803], [cptim =], [0], []
ORA-01110: 数据文件 6: D:\ORACLE\PRODUCT\10.2.0\ORADATA\CL7101\CL781.DBF

SQL> SELECT file#,STATUS FROM v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 OFFLINE
         6 OFFLINE

已选择6行。

SQL> ALTER DATABASE datafile 5 online;

ALTER DATABASE datafile 5 online
*
第 1 行出现错误:
ORA-01190: 控制文件或数据文件 %s 来自最后一个 RESETLOGS 之前

SQL> ALTER DATABASE datafile 6 online;

ALTER DATABASE datafile 6 online
*
第 1 行出现错误:
ORA-01190: 控制文件或数据文件 %s 来自最后一个 RESETLOGS 之前

我们可以看到报错ora-01190,换句话说这个2个datafile的resetlogs scn跟其他文件不一致。

下面我们在mount状态下,dump datafile header看看就明白了。

SQL> oradebug setmypid

已处理的语句

SQL> oradebug dump file_hdrs 8;

已处理的语句

SQL> oradebug tracefile_name

d:\oracle\product\10.2.0\admin\cl7101\udump\cl7101_ora_8464.trc

###### datafile header dump ######

Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002629 08/30/2005 13:51:05
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x2e69d3cf scn: 0x0000.14126bb4 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2e69b02e scn: 0x0000.14113318 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 03/23/2012 14:13:27
status:0x4 root dba:0x00000000 chkpt cnt: 1776 ctl cnt:1775
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.1412ba54 03/23/2012 14:13:28
thread:1 rba:(0x2.2.10)


Tablespace #6 - CL7101  rel_fn:5
Creation   at   scn: 0x0000.000860af 06/21/2011 00:55:23
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x2cf6a1b4 scn: 0x0000.0008297b reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x21d66184 scn: 0x0000.00000001 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 03/17/2012 12:35:33
status:0x4 root dba:0x00000000 chkpt cnt: 1708 ctl cnt:1707
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.14113317 03/17/2012 00:10:17
thread:1 rba:(0x667.2.10)


Tablespace #7 - CL781  rel_fn:6
Creation   at   scn: 0x0000.00086318 06/21/2011 00:56:43
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x2cf6a1b4 scn: 0x0000.0008297b reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x21d66184 scn: 0x0000.00000001 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 03/17/2012 12:35:33
status:0x4 root dba:0x00000000 chkpt cnt: 1708 ctl cnt:1707
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.14113317 03/17/2012 00:10:17
thread:1 rba:(0x667.2.10)

我们可以看到,reset logs count 和scn 都不一致,这里我们其实可以bbed直接修改即可。
由于网友这里是windows 平台,比较麻烦,我就不用bbed了,直接用oracle的10015 event。

首先启动到mount状态,执行如下命令:
alter session set events '10015 trace name adjust_scn level 10';
alter database daOracleоtafile 5 online;
alter database datafile 6 online;
recover database;
alter database open;

成功open数据库,由于undo datafile本身损坏了,且又是非归档,故直接重建undo了,搞定。

补充下:在操作过程中发现smon在进程recover时有点问题,故使用了下面的event:
event='10513 trace name context forever,level 2'
event='10512 trace name context forever,level 1'
event='10511 trace name context forever,level 2'
event='10510 trace name context forever,level 1'

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle故障恢复报错ORA-01190 ORA-600 [krhpfh_03-1202]

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1347.html

Oracle研究中心

关键词:

ORA-00600

oracle的10015 event修改SCN的方法

Oracle报错ORA-01190

ORA-600 [krhpfh_03-1202]解决办法