非归档恢复遭遇ORA-01190 和 ORA-600 [krhpfh_03-1202]–恢复小记

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

本文链接地址: 非归档恢复遭遇ORA-01190 和 ORA-600 [krhpfh_03-1202]–恢复小记

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

1	群中一位网友遇到的问题，硬件故障导致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)

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，我发现里面有如下信息：

1	通过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'

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，发现报错。

于是使用

_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

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)

###### 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状态，执行如下命令：

我们可以看到，reset logs count 和scn 都不一致，这里我们其实可以bbed直接修改即可。

由于网友这里是windows 平台，比较麻烦，我就不用bbed了，直接用oracle的10015 event。

首先启动到mount状态，执行如下命令：

alter session set events '10015 trace name adjust_scn level 10';
alter database datafile 5 online;
alter database datafile 6 online;
recover database;
alter database open;

alter session set events '10015 trace name adjust_scn level 10';

alter database datafile 5 online;

alter database datafile 6 online;

recover database;

alter database open;

成功open数据库，由于undo datafile本身损坏了，且又是非归档，故直接重建undo了，搞定。
补充下：在操作过程中发现smon在进程recover时有点问题，故使用了下面的event:

成功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'

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'

Tags: ORA-01110, ORA-01190, [krhpfh_03-1202]
Posted in backup & rcovery on 3月 24, 2012

7 Responses to “非归档恢复遭遇ORA-01190 和 ORA-600 [krhpfh_03-1202]–恢复小记”

清风 Says:

3月 24, 2012 at 上午 10:54

顶下
清风 Says:

3月 24, 2012 at 上午 10:58

roger，请问第一次open是使用resetlogs吗？
roger Says:

3月 24, 2012 at 上午 11:50

to 清风：第一次是直接open的。
惜分飞 Says:

3月 25, 2012 at 上午 8:15

file 5和file 6有可能不是原来的文件，在库出问题前就resetlogs过
roger Says:

3月 25, 2012 at 上午 9:17

to 惜分飞：经过网友确认，file 5，file6是需要的文件，他们是从拷贝过来的，具体过程不太清楚，恢复以后，里面的数据确实是他们需要的。
郑凯彬 Says:

3月 29, 2012 at 下午 12:56

我是郑凯彬，一起交流下，可以来我博客看看。
Zhaox-huan Says:

3月 31, 2012 at 下午 12:42

GOOD

You must be logged in to post a comment.

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

非归档恢复遭遇ORA-01190 和 ORA-600 [krhpfh_03-1202]–恢复小记

7 Responses to “非归档恢复遭遇ORA-01190 和 ORA-600 [krhpfh_03-1202]–恢复小记”

Leave a Reply