当前位置：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研究中心
本站文章除注明转载外，均为本站原创： 转载自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
