一次ORA-1578的处理

联系:QQ(5163721)

标题:一次ORA-1578的处理

作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

今天本想测试一个东西,却遇到一个ORA-1578,比较郁闷,最近vm总是出现乱七八糟郁闷问题。。。。。

LUNAR@travel>select count(*) from ff;
select count(*) from ff
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 129)
ORA-01110: data file 4: '/stage/travel/users01.dbf'
LUNAR@travel>
 
SYS@travel>select file#,name from v$datafile;
 
FILE# NAME
---------- -------------------------------------------------------
1 /stage/travel/system01.dbf
2 /stage/travel/sysaux01.dbf
3 /stage/travel/undotbs01.dbf
4 /stage/travel/users01.dbf
5 /stage/travel/lunar01.dbf
 
SYS@travel>
 
使用dbv检测了一下,发现两个坏块:
[oracle@lunar ~]$ dbv
 
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Oct 26 20:32:04 2013
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
 
Keyword Description (Default)
----------------------------------------------------
FILE File to Verify (NONE)
START Start Block (First Block of File)
END End Block (Last Block of File)
BLOCKSIZE Logical Block Size (8192)
LOGFILE Output Log (NONE)
FEEDBACK Display Progress (0)
PARFILE Parameter File (NONE)
USERID Username/Password (NONE)
SEGMENT_ID Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN Highest Block SCN To Verify (NONE)
(scn_wrap.scn_base OR scn)
[oracle@lunar ~]$ dbv file=/stage/travel/users01.dbf
 
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Oct 26 20:32:26 2013
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
 
DBVERIFY - Verification starting : FILE = /stage/travel/users01.dbf
Page 128 is influx - most likely media corrupt
Corrupt block relative dba: 0x01000080 (file 4, block 128)
Fractured block found during dbv:
Data in bad block:
type: 30 format: 2 rdba: 0x01000080
last change scn: 0x0000.00055d9a seq: 0x4 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x5d9a2004
check value in block header: 0xd9f8
computed block checksum: 0x0
 
Page 129 is marked corrupt
Corrupt block relative dba: 0x01000081 (file 4, block 129)
Bad check value found during dbv:
Data in bad block:
type: 33 format: 2 rdba: 0x01000081
last change scn: 0x0000.00057901 seq: 0x3 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x79012103
check value in block header: 0xc13e
computed block checksum: 0x41f0

原因已经很清楚了,file 4 block 128是物理坏块;file 4 block 129是checksum问题

DBVERIFY - Verification complete
 
Total Pages Examined : 32000
Total Pages Processed (Data) : 29573
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 380
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2045
Total Pages Marked Corrupt : 2
Total Pages Influx : 1
Total Pages Encrypted : 0
Highest block SCN : 442564 (0.442564)
 
我来试试rman检测的效果:
[oracle@lunar ~]$ rman target /
 
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Oct 26 20:58:42 2013
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
 
connected to target database: TRAVEL (DBID=2874070637)
 
RMAN> backup validate check logical database;
 
Starting backup at 26-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=140 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/stage/travel/system01.dbf
input datafile file number=00002 name=/stage/travel/sysaux01.dbf
input datafile file number=00004 name=/stage/travel/users01.dbf
input datafile file number=00003 name=/stage/travel/undotbs01.dbf
input datafile file number=00005 name=/stage/travel/lunar01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 12609 44800 537449
File Name: /stage/travel/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 22483
Index 0 6617
Other 0 3091
 
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 24301 38400 537463
File Name: /stage/travel/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 4311
Index 0 3504
Other 0 6284
 
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 1 19840 537463
File Name: /stage/travel/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 19839
 
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 FAILED 0 2045 32000 442564
File Name: /stage/travel/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 29573
Index 0 0
Other 2 382
 
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 12475 12800 442504
File Name: /stage/travel/lunar01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 1
Other 0 324
 
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/travel/travel/trace/travel_ora_11181.trc for details
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 594
Finished backup at 26-OCT-13
 
RMAN> exit
Recovery Manager complete.
[oracle@lunar ~]$ ss
 
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 26 21:02:20 2013
 
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Real Application Testing options
 
SYS@travel>select * from v$database_block_corruption;
 
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ------------------
4 128 1 0 FRACTURED 这里的信息与dbv的检测完全一致,非常方便,block corrupt的类型清晰明了
4 129 1 0 CHECKSUM
 
SYS@travel>
尝试使用event 10231跳过都失败了:
LUNAR@travel>set autotrace on explain
LUNAR@travel>alter session set events '10231 trace name context forever, level 1';
 
Session altered.
 
LUNAR@travel>select count(*) from ff;
select count(*) from ff
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 129)
ORA-01110: data file 4: '/stage/travel/users01.dbf'
LUNAR@travel>
 
LUNAR@travel>alter session set events '10231 trace name context forever, level 10';
 
Session altered.
 
LUNAR@travel>select count(*) from ff;
select count(*) from ff
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 129)
ORA-01110: data file 4: '/stage/travel/users01.dbf'
LUNAR@travel>
 
尝试按照rowid抽取,也失败了:
declare
n number:=0;
bad_rows number := 0;
error_code number;
ora1578 EXCEPTION;
PRAGMA EXCEPTION_INIT(ora1578, -1578);
begin
for i in (select rowid rid from ff) loop
begin
insert into newff
select *
from ff
where rowid=i.rid;
n:=n+1;
exception
when ora1578 then
bad_rows := bad_rows + 1;
insert into bad_rows values(i.rid,1578);
commit;
when others then
error_code:=SQLCODE;
bad_rows := bad_rows + 1;
insert into bad_rows values(i.rid,error_code);
commit;
end;
end loop;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
dbms_output.put_line('Total Good rows: '||n);
end;
/
 
LUNAR@travel>declare
2 n number:=0;
3 bad_rows number := 0;
4 error_code number;
5 ora1578 EXCEPTION;
6 PRAGMA EXCEPTION_INIT(ora1578, -1578);
7 begin
8 for i in (select rowid rid from ff) loop
9 begin
10 insert into newff
11 select *
12 from ff
13 where rowid=i.rid;
14 n:=n+1;
15 exception
16 when ora1578 then
17 bad_rows := bad_rows + 1;
18 insert into bad_rows values(i.rid,1578);
19 commit;
20 when others then
21 error_code:=SQLCODE;
22 bad_rows := bad_rows + 1;
23 insert into bad_rows values(i.rid,error_code);
24 commit;
25 end;
26 end loop;
27 dbms_output.put_line('Total Bad Rows: '||bad_rows);
28 dbms_output.put_line('Total Good rows: '||n);
29 end;
30 /
declare
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 129)
ORA-01110: data file 4: '/stage/travel/users01.dbf'
ORA-06512: at line 8
LUNAR@travel>

尝试DBMS_REPAIR也不行:

execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('','', flags=>dbms_repair.noskip_flag);
 
select owner, segment_name, segment_type, partition_name
from dba_segments
where header_file = &AFN
and header_block = &BL;
exec DBMS_REPAIR.ADMIN_TABLES('REPAIR_TABLE',1,1,'SYSTEM');
exec DBMS_REPAIR.ADMIN_TABLES('ORPHAN_TABLE',2,1,'SYSTEM');
 
SET serveroutput ON
DECLARE
cc NUMBER;
BEGIN
DBMS_REPAIR.check_object(schema_name => 'LUNAR',object_name =>'FF',corrupt_count => cc);
DBMS_OUTPUT.put_line(a => TO_CHAR(cc));
END;
/
 
SYS@travel>SET serveroutput ON
DECLARE
SYS@travel> 2 cc NUMBER;
3 BEGIN
4 DBMS_REPAIR.check_object(schema_name => 'LUNAR',object_name =>'FF',corrupt_count => cc);
5 DBMS_OUTPUT.put_line(a => TO_CHAR(cc));
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 129)
ORA-01110: data file 4: '/stage/travel/users01.dbf'
ORA-06512: at "SYS.DBMS_REPAIR", line 294
ORA-06512: at line 4
SYS@travel>
dump一下,看看什么情况:
SYS@travel>oradebug setmypid
Statement processed.
SYS@travel> ALTER system dump datafile 4 block 129;
 
System altered.
 
SYS@travel>oradebug tracefile_name
/u01/app/oracle/diag/rdbms/travel/travel/trace/travel_ora_11492.trc
SYS@travel>
 
*** 2013-10-26 22:06:33.568
Start dump data blocks tsn: 4 file#:4 minblk 129 maxblk 129
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777345
BH (0x693e6418) file#: 4 rdba: 0x01000081 (4/129) class: 9 ba: 0x69182000
set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 110,28
dbwrid: 0 obj: 17946 objn: 17946 tsn: 4 afn: 4 hint: f
hash: [0x7448c5d0,0x7448c5d0] lru: [0x693e6630,0x693e63d0]
ckptq: [NULL] fileq: [NULL] objq: [0x697f40d8,0x693e63f8] objaq: [0x697f40e8,0x693e6408]
st: XCURRENT md: NULL fpin: 'ktspswh6: ktspInitScan' tch: 0
flags: auto_bmr_tried
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
Block dump from disk:
buffer tsn: 4 rdba: 0x01000081 (4/129)
scn: 0x0000.00057901 seq: 0x03 flg: 0x04 tail: 0x79012103
frmt: 0x02 chkval: 0xc13e type: 0x21=SECOND LEVEL BITMAP BLOCK ------这里看到是type: 0x21=SECOND LEVEL BITMAP BLOCK,event 10231等等手段是跳不过去的
Hex dump of corrupt header 3 = CHKVAL
尝试使用bbed修改:
BBED> verify
DBVERIFY - Verification starting
FILE = /stage/travel/users01.dbf
BLOCK = 129
 
Block 129 is corrupt
Corrupt block relative dba: 0x01000081 (file 0, block 129)
Bad check value found during verification
Data in bad block:
type: 33 format: 2 rdba: 0x01000081
last change scn: 0x0000.00057901 seq: 0x3 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x79012103
check value in block header: 0xc13e
computed block checksum: 0x41f0
DBVERIFY - Verification complete
 
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED>
BBED> sum apply
Check value for File 4, Block 129:
current = 0x80ce, required = 0x80ce
 
BBED> verify
DBVERIFY - Verification starting
FILE = /stage/travel/users01.dbf
BLOCK = 129
DBVERIFY - Verification complete
 
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED>

好了,已经不抱错了,再试试,dbv,这里理论上应该是只有1个坏块了,file 4 block 129已经修好了:

[oracle@lunar bbed]$ dbv file=/stage/travel/users01.dbf
 
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Oct 26 23:10:51 2013
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
 
DBVERIFY - Verification starting : FILE = /stage/travel/users01.dbf
Page 128 is influx - most likely media corrupt
Corrupt block relative dba: 0x01000080 (file 4, block 128)
Fractured block found during dbv:
Data in bad block:
type: 30 format: 2 rdba: 0x01000080
last change scn: 0x0000.00055d9a seq: 0x4 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x5d9a2004
check value in block header: 0xd9f8
computed block checksum: 0x0
 
 
 
DBVERIFY - Verification complete
 
Total Pages Examined : 32000
Total Pages Processed (Data) : 29565
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 389
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2045
Total Pages Marked Corrupt : 1
Total Pages Influx : 1
Total Pages Encrypted : 0
Highest block SCN : 539524 (0.539524)
[oracle@lunar bbed]$

记录一下:
bbed修改后的block: 有问题的block:

*** 2013-10-26 23:12:54.998 *** 2013-10-26 22:06:33.568
Start dump data blocks tsn: 4 file#:4 minblk 129 maxblk 129 Start dump data blocks tsn: 4 file#:4 minblk 129 maxblk 129
Block dump from cache: Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777345 Dump of buffer cache at level 4 for tsn=4 rdba=16777345
BH (0x693e6418) file#: 4 rdba: 0x01000081 (4/129) class: 9 ba: 0x69182000 BH (0x693e6418) file#: 4 rdba: 0x01000081 (4/129) class: 9 ba: 0x69182000
set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 116,28 set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 110,28
dbwrid: 0 obj: 17946 objn: 17946 tsn: 4 afn: 4 hint: f dbwrid: 0 obj: 17946 objn: 17946 tsn: 4 afn: 4 hint: f
hash: [0x7448c5d0,0x7448c5d0] lru: [0x693e6630,0x693e63d0] hash: [0x7448c5d0,0x7448c5d0] lru: [0x693e6630,0x693e63d0]
ckptq: [NULL] fileq: [NULL] objq: [0x697f40d8,0x693e63f8] objaq: [0x697f40e8,0x693e6408] ckptq: [NULL] fileq: [NULL] objq: [0x697f40d8,0x693e63f8] objaq: [0x697f40e8,0x693e6408]
st: XCURRENT md: NULL fpin: 'ktspswh6: ktspInitScan' tch: 0 st: XCURRENT md: NULL fpin: 'ktspswh6: ktspInitScan' tch: 0
flags: auto_bmr_tried flags: auto_bmr_tried
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535] LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
Block dump from disk: Block dump from disk:
buffer tsn: 4 rdba: 0x01000081 (4/129) buffer tsn: 4 rdba: 0x01000081 (4/129)
scn: 0x0000.00057901 seq: 0x03 flg: 0x04 tail: 0x79012103 scn: 0x0000.00057901 seq: 0x03 flg: 0x04 tail: 0x79012103
frmt: 0x02 chkval: 0x80ce type: 0x21=SECOND LEVEL BITMAP BLOCK frmt: 0x02 chkval: 0xc13e type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1 Hex dump of corrupt header 3 = CHKVAL
Dump of memory from 0x00007FFDE0FECA00 to 0x00007FFDE0FEEA00

对比修复之前的file 4 block129的dbv的信息:

Page 129 is marked corrupt
Corrupt block relative dba: 0x01000081 (file 4, block 129)
Bad check value found during dbv:
Data in bad block:
type: 33 format: 2 rdba: 0x01000081
last change scn: 0x0000.00057901 seq: 0x3 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x79012103
check value in block header: 0xc13e
computed block checksum: 0x41f0

再次尝试event 10231跳过,发现还是不行:

SYS@travel>conn lunar/lunar
Connected.
LUNAR@travel>alter session set events '10231 trace name context forever, level 10';
 
Session altered.
 
LUNAR@travel>select count(*) from ff;
select count(*) from ff
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 129)
ORA-01110: data file 4: '/stage/travel/users01.dbf'
LUNAR@travel>!

bbed再次检查,发现没有问题:

[oracle@lunar bbed]$ dbv file=/stage/travel/users01.dbf
 
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Oct 26 23:20:14 2013
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
 
DBVERIFY - Verification starting : FILE = /stage/travel/users01.dbf
Page 128 is influx - most likely media corrupt
Corrupt block relative dba: 0x01000080 (file 4, block 128)
Fractured block found during dbv:
Data in bad block:
type: 30 format: 2 rdba: 0x01000080
last change scn: 0x0000.00055d9a seq: 0x4 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x5d9a2004
check value in block header: 0xd9f8
computed block checksum: 0x0
 
 
 
DBVERIFY - Verification complete
 
Total Pages Examined : 32000
Total Pages Processed (Data) : 29565
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 389
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2045
Total Pages Marked Corrupt : 1
Total Pages Influx : 1
Total Pages Encrypted : 0
Highest block SCN : 539524 (0.539524)
[oracle@lunar bbed]$

怀疑是buffer中该块还是老的,因此试试刷新一下:

SYS@travel>alter system flush BUFFER_CACHE;
 
System altered.
 
SYS@travel>conn lunar/lunar
Connected.
LUNAR@travel>alter session set events '10231 trace name context forever, level 10';
 
Session altered.
 
LUNAR@travel>select count(*) from ff;
 
COUNT(*)
----------
141112
 
LUNAR@travel>

问题解决了,O(∩_∩)O哈哈~

 

此条目发表在 backup&recovery, ORA-XXXXX 分类目录,贴了 标签。将固定链接加入收藏夹。

一次ORA-1578的处理》有 1 条评论

  1. zbdba 说:

    bbed改的块哪个offset呢?

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注