segment header损坏如何修复
段头损坏,dbms_repair包,dbms_rowid,以及event 10231 都是没有作用的(存在非空索引的情况除外)。
但是基于rowid的操作方式可以的。 关于这点,oracle mos文档中也有提及.
This article describe a procedure that can be used to skip corrupted rows from a table when event 10231, dbms_repair or skipping the rows through an index cannot be used. This method builds the rowid’s for a table based on the information from dba_extents.
测试环境准备
SQL> conn / as sysdba
Connected.
SQL> create table zhuo.zhuo as select * from dba_objects where object_id is not null;
Table created.
SQL> select count(*) from zhuo.zhuo;
COUNT(*)
----------
80066
SQL> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,blocks,extents from DBA_SEGMENTS
2 WHERE OWNER='ZHUO' AND SEGMENT_NAME='ZHUO';
SQL> col SEGMENT_NAME for a10
SEGMENT_NA HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
---------- ----------- ------------ ---------- ----------
ZHUO 5 1410 1280 25
dump block
SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 5 block 1410;
System altered.
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/zhuo/zhuo/trace/zhuo_ora_1584.trc
部分trace:
Block dump from disk:
buffer tsn: 5 rdba: 0x01400582 (5/1410)
scn: 0x0000.000ba855 seq: 0x01 flg: 0x04 tail: 0xa8552301
frmt: 0x02 chkval: 0x94ed type: 0x23=PAGETABLE SEGMENT HEADER
通过header_block和dump block确定block 1410即为PAGETABLE SEGMENT HEADER
损坏前查询
SQL> alter session set current_schema=ZHUO;
Session altered.
SQL> set autot trace
SQL> select count(*) from zhuo.zhuo;
Execution Plan
----------------------------------------------------------
Plan hash value: 1214160148
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 320 (0)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| ZHUO | 49886 | 320 (0)| 00:00:04 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1149 consistent gets
1146 physical reads
0 redo size
528 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
修复
存在非空索引-- 使用索引获取ROWID
- 在11.2版本中,段头损坏的话,仍然是可以访问的(有主键索引的情况下)
SQL> alter table zhuo.zhuo add constraint PK_zhuo primary key (object_id);
Table altered.
破坏块,出现坏块:
BBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1410
Block 1410 is corrupt
Corrupt block relative dba: 0x01400582 (file 0, block 1410)
Fractured block found during verification
Data in bad block:
type: 35 format: 2 rdba: 0x01400582
last change scn: 0x0000.000ba855 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xa9552301 ---根据tail的计算方式:23应该为type,跟前面的35对应不上。原因:35=0x23,type以10进制显示。后面的tail以16进制显示。
check value in block header: 0x95ed
computed block checksum: 0x0
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 : 2
Message 531 not found; product=RDBMS; facility=BBED
查询该表
SQL> alter system checkpoint;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from zhuo.zhuo;
COUNT(*)
----------
80066
SQL> set autot trace
SQL> alter session set current_schema='ZHUO'
2 ;
alter session set current_schema='ZHUO'
*
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier
SQL> alter session set current_schema=ZHUO;
Session altered.
SQL> select count(*) from zhuo;
Execution Plan
----------------------------------------------------------
Plan hash value: 2127640271
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| PK_ZHUO | 95893 | 50 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
21 recursive calls
0 db block gets
187 consistent gets
6 physical reads
0 redo size
528 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+full(t) */ count(*) from zhuo.zhuo t;
select /*+full(t) */ count(*) from zhuo.zhuo t
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'
SQL> select /*+index(t pk_zhuo)*/ count(rowid) from zhuo.zhuo t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1209007207
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 180 (0)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | INDEX FULL SCAN| PK_ZHUO | 8168 | 98016 | 180 (0)| 00:00:03 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
29 recursive calls
12 db block gets
197 consistent gets
1 physical reads
0 redo size
532 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from zhuo.zhuo;
select * from zhuo.zhuo
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'
发现不报错,根据前面的测试,当segment block出现坏块的时候,select和dml都是不能进行的,但是在此处却不报错。**在11.2版本中,段头损坏的话,仍然是可以访问的(有主键索引的情况下).**自动走索引访问数据.但是,不能通过索引获得的仍然报错。
1.1说明
针对段头损坏,如果你使用我们常规的查询data block 的sql,是查询不到的,如下:
SQL> SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
2 3 WHERE file_id =5
4 and 1410 between block_id and block_id + blocks -1;
no rows selected
通常来讲,如果上面sql查询不到,那么该block很可能就是segment header或位图block:
SQL> SELECT owner, segment_name, segment_type, partition_name FROM dba_segments WHERE header_file =5
2 and header_block=1410;
OWNER SEGMENT_NA SEGMENT_TYPE PARTITION_NAME
---------- ---------- ------------------ ------------------------------
ZHUO ZHUO TABLE
我们可以通过alert log或trace文件信息去识别。
alert部分信息:
Corrupt Block Found
TSN = 5, TSNAME = ZHUO
RFN = 5, BLK = 1410, RDBA = 20972930
OBJN = 80622, OBJD = 80622, OBJECT = ZHUO, SUBOBJECT =
SEGMENT OWNER = ZHUO, SEGMENT TYPE = Table Segment
- 抢救数据
SQL> create table zhuo_new as select * from zhuo.zhuo where 1=0;
Table created.
SQL> create table bad_rows (row_id rowid,oracle_error_code number);
Table created.
SQL> set serveroutput on;
set timing on;
DECLARE
TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
CURSOR c1 IS select /*+ index(t pk_zhuo) */ rowid
from zhuo.zhuo t;
SQL> SQL> 2 3 4 5 r RowIDTab;
6 rows NATURAL := 20000;
7 bad_rows number := 0 ;
8 errors number;
9 error_code number;
10 myrowid rowid;
11 BEGIN
12 OPEN c1;
13 LOOP
14 FETCH c1 BULK COLLECT INTO r LIMIT rows;
15 EXIT WHEN r.count=0;
16 BEGIN
17 FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
18 insert into zhuo_new
19 select /*+ ROWID(A) */ *
20 from zhuo.zhuo A where rowid = r(i);
21 EXCEPTION
22 when OTHERS then
23 BEGIN
24 errors := SQL%BULK_EXCEPTIONS.COUNT;
25 FOR err1 IN 1..errors LOOP
26 error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
27 if error_code in (1410, 8103) then
28 myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
29 bad_rows := bad_rows + 1;
30 insert into bad_rows values(myrowid, error_code);
31 else
32 raise;
33 end if;
34 END LOOP;
35 END;
36 END;
37 commit;
38 END LOOP;
39 commit;
40 CLOSE c1;
41 dbms_output.put_line('Total Bad Rows: '||bad_rows);
42 END;
43 /
Total Bad Rows: 0
PL/SQL procedure successfully completed.
Elapsed: 00:00:19.44
SQL> select count(*) from zhuo_new;
COUNT(*)
----------
80066
Elapsed: 00:00:00.05
可以看到,可以把数据全部抢救出来。没有损失。
具体脚本如下:
set serveroutput on;
set timing on;
DECLARE
TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
CURSOR c1 IS select /*+ index(t pk_zhuo) / rowid
from zhuo.zhuo t;
r RowIDTab;
rows NATURAL := 20000;
bad_rows number := 0 ;
errors number;
error_code number;
myrowid rowid;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO r LIMIT rows;
EXIT WHEN r.count=0;
BEGIN
FORALL i IN r.FIRST…r.LAST SAVE EXCEPTIONS
insert into zhuo_new
select /+ ROWID(A) */ *
from zhuo.zhuo A where rowid = r(i);
EXCEPTION
when OTHERS then
BEGIN
errors := SQL%BULK_EXCEPTIONS.COUNT;
FOR err1 IN 1…errors LOOP
error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
if error_code in (1410, 8103) then
myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
bad_rows := bad_rows + 1;
insert into bad_rows values(myrowid, error_code);
else
raise;
end if;
END LOOP;
END;
END;
commit;
END LOOP;
commit;
CLOSE c1;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/
注意source table和目标表的修改。
不存在非空索引–dbms_rowid.ROWID_CREATE来构造ROWID
重新创建表,没有非空索引:
SQL> drop table zhuo.zhuo purge;
Table dropped.
SQL> create table zhuo.zhuo
2 as
3 select * from dba_objects where object_id is not null;
Table created.
SQL> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,blocks,extents from DBA_SEGMENTS
2 WHERE OWNER='ZHUO' AND SEGMENT_NAME='ZHUO';
SEGMENT_NAME
--------------------------------------------------------------------------------
HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
----------- ------------ ---------- ----------
ZHUO
5 1410 1280 25
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from zhuo.zhuo;
COUNT(*)
----------
80069
制造坏块
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from zhuo.zhuo;
select count(*) from zhuo.zhuo
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'
抽取数据:
SQL> create table bad_rows (row_id rowid,oracle_error_code varchar2(50));
SQL> create table zhuo_new as select * from zhuo.zhuo where 1=2;
SQL> set serveroutput on;
set timing on;
SQL> SQL>
SQL> DECLARE
2 TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
3 CURSOR Crowid_info IS
4 select Do.DATA_OBJECT_ID dataid,
5 DE.FILE_ID fid,
6 DE.BLOCK_ID blkid,
7 DE.BLOCKS blkcnt
8 from dba_objects DO, dba_extents DE
9 where DO.OBJECT_NAME = 'ZHUO'
10 and nvl(DO.SUBOBJECT_NAME,'-1') = nvl(DE.PARTITION_NAME,'-1')
11 and DO.OBJECT_NAME = DE.SEGMENT_NAME
12 and DO.owner = 'ZHUO'
13 order by 1, 2, 3 asc;
14 bad_rows number := 0;
15 errors varchar2(500);
16 error_code varchar2(500);
17 myrowid rowid;
18 BEGIN
19 execute immediate 'alter session set commit_write=''batch,nowait'' ';
20 for i in Crowid_info loop
21 for j in 0 .. i.blkcnt - 1 loop
22 for z in 0 .. 2000 loop
23 begin
24 myrowid := dbms_rowid.ROWID_CREATE(1,i.dataid,i.fid,i.blkid + j,z);
25 insert into zhuo_new select /*+ ROWID(A) */ * from zhuo.zhuo A where rowid = myrowid;
26 EXCEPTION
27 when OTHERS then
28 BEGIN
29 errors := SQLERRM;
30 error_code := SQLCODE;
31 if (error_code like '%1410%' or error_code like '%8103%' or error_code like '%1578%') then
32 bad_rows := bad_rows + 1;
33 insert into bad_rows values (myrowid, error_code);
34 commit;
35 else
36 raise;
37 end if;
38 END;
39 commit;
40 end;
41 end loop;
42 end loop;
43 end loop;
44 dbms_output.put_line('Total Bad Rows: ' || bad_rows);
45 commit;
46 END;
47 /
Total Bad Rows: 0
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.13
SQL> select count(*) from zhuo_new;
COUNT(*)
----------
0
按照mos上面的文档操作,发现,居然没有数据被抽取出来。
手动构造的方式:
SQL> select object_id from dba_objects where object_name='ZHUO';
OBJECT_ID
----------
80630
SQL> select distinct dbms_rowid.rowid_block_number(rowid) blk# from ZHUO;
select distinct dbms_rowid.rowid_block_number(rowid) blk# from ZHUO
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select distinct dbms_rowid.rowid_block_number(rowid) blk# from ZHUO.ZHUO;
select distinct dbms_rowid.rowid_block_number(rowid) blk# from ZHUO.ZHUO
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'
SQL> select dbms_rowid.rowid_create(1,80630,5,1411,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAATr2AAFAAAAWDAAA
SQL> insert into zhuo_new select * from zhuo.zhuo where rowid >= CHARTOROWID('AAATr2AAFAAAAWDAAA');
insert into zhuo_new select * from zhuo.zhuo where rowid >= CHARTOROWID('AAATr2AAFAAAAWDAAA')
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'
大家可以看到,针对没有非空索引或主键存在的情况下,段头损坏,基于rowid的方式是没有办法处理的。
总结:
针对11gR2版本,segment header损坏的情况下,可以通过如下PL/SQL 构造ROWID的方式挽救绝大多数非坏块的数据, 一般分成 2种情况 :
1、如果存在非空索引,可以使用索引查询出rowid,全部抽取出数据。
2、对于没有索引或者索引损坏的情况,基于rowid的方法没有办法处理。
参考:
http://www.askmaclean.com/archives/build-rowid-workaround-1578-1410-8103.html
SCRIPT to skip ORA-8103 ORA-1410 ORA-1578 ORA-600 [kdsgrp1] when reading a TABLE (Doc ID 1527738.1)