ORA-08103错误在数据库日程运维和异常恢复中都可能遇到,出现该错误的原因很多,有很多情况下(模拟普通ORA-08103并解决),直接通过修改块使其在数据库查询的时候表标志为坏块,然后使用event或者dbms_repair包来标志该块,然后跳过就可以解决该问题,但是有些时候,遇到极端情况,该方法会失效,需要借助极端工具来处理该极端问题.
分析表相关EXTENT
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table t_xifenfei as
2 select * from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
COUNT(*)
----------
74504
SQL> select data_object_id,object_id from dba_objects where object_name='T_XIFENFEI';
DATA_OBJECT_ID OBJECT_ID
-------------- ----------
75592 75592
SQL> select file_id,block_id,block_id+blocks-1
2 from dba_extents
3 where segment_name ='T_XIFENFEI' AND owner='CHF';
FILE_ID BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
4 680 687
4 688 695
4 696 703
4 704 711 <---注意
4 712 719
4 720 727
4 728 735
…………
24 rows selected.
SQL> Select segment_name,header_file,header_blocK
2 from dba_segments where
3 segment_name in ('T_XIFENFEI') and owner='CHF'
4 ;
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
T_XIFENFEI 4 682
SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 4 block 682;
System altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28933.trc
--trace文件
Dump of memory from 0xB6CFD600 to 0xB6CFF600
B6CFD600 0000A223 010002AA 000DB4EA 04010000 [#...............]
B6CFD610 00008ECF 00000000 00000000 00000000 [................]
B6CFD620 00000000 00000018 00000480 00000A9C [................]
B6CFD630 00000017 0000003F 00000080 010007BF [....?...........]
B6CFD640 00000000 00000017 00000000 0000043F [............?...]
B6CFD650 00000000 00000000 00000000 00000017 [................]
B6CFD660 0000003F 00000080 010007BF 00000000 [?...............]
B6CFD670 00000017 00000000 0000043F 01000780 [........?.......]
B6CFD680 01000780 00000000 00000000 00000000 [................]
B6CFD690 00000000 00000000 00000000 00000000 [................]
Repeat 3 times
B6CFD6D0 00000001 00002000 00000000 00001434 [..... ......4...]
B6CFD6E0 00000000 010002A9 00000001 01000781 [................]
B6CFD6F0 010002A9 00000000 00000000 00000000 [................]
B6CFD700 00000000 00000000 00000018 00000000 [................]
B6CFD710 00012748 10000000 010002A8 00000008 [H'..............]
B6CFD720 010002B0 00000008 010002B8 00000008 [................]
B6CFD730 010002C0 00000008 010002C8 00000008 [................] <----dump中找到下面值
……
Extent Map
-----------------------------------------------------------------
0x010002a8 length: 8
0x010002b0 length: 8
0x010002b8 length: 8
0x010002c0 length: 8 <-----选择第四个exent
0x010002c8 length: 8
…………
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x010002a8 Data dba: 0x010002ab
Extent 1 : L1 dba: 0x010002a8 Data dba: 0x010002b0
Extent 2 : L1 dba: 0x010002b8 Data dba: 0x010002b9
Extent 3 : L1 dba: 0x010002b8 Data dba: 0x010002c0 <---同上
Extent 4 : L1 dba: 0x010002c8 Data dba: 0x010002c9
Extent 5 : L1 dba: 0x010002c8 Data dba: 0x010002d0
…………
--------------------------------------------------------
--确定Extent 3的记录在file 4 block 682 的偏移量为304
------省略了相同部分B6CFD
SQL> SELECT TO_NUMBER('730','XXXXX') FROM DUAL;
TO_NUMBER('730','XXXXX')
------------------------
1840
SQL> SELECT TO_NUMBER('600','XXX') FROM DUAL;
TO_NUMBER('600','XXX')
----------------------
1536
SQL> SELECT 1840-1536 FROM DUAL;
1840-1536
----------
304
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
这里我们假设修改SEGMENT HEADER中关于EXTENT 3(从0开始计数)的映射地址,从而来使得该表在查询的时候出现ORA-08103错误
bbed修改相关值
[oracle@xifenfei ~]$ bbed parfile=bbed.par
BBED> SET MODE EDIT
MODE Edit
BBED> INFO
File# Name Size(blks)
----- ---- ----------
4 /u01/oracle/oradata/ora11g/users01.dbf 0
BBED> SET FILE 4 BLOCK 682
FILE# 4
BLOCK# 682
BBED> D
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 682 Offsets: 0 to 511 Dba:0x010002aa
------------------------------------------------------------------------
23a20000 aa020001 eab40d00 00000104 cf8e0000 …………
<32 bytes per line>
BBED> m /x 1100
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 682 Offsets: 0 to 511 Dba:0x010002aa
------------------------------------------------------------------------
11000000 aa020001 eab40d00 00000104 cf8e0000 …………
<32 bytes per line>
BBED> d
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 682 Offsets: 0 to 511 Dba:0x010002aa
------------------------------------------------------------------------
11000000 aa020001 eab40d00 00000104 cf8e0000 …………
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 682:
current = 0x2cfd, required = 0x2cfd
重现ORA-08103
SQL> startup
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1344652 bytes
Variable Size 239078260 bytes
Database Buffers 67108864 bytes
Redo Buffers 6328320 bytes
Database mounted.
Database opened.
SQL> conn chf/xifenfei
Connected.
SQL> select count(*) from t_xifenfei;
select count(*) from t_xifenfei
*
ERROR at line 1:
ORA-08103: object no longer exists
定位坏块位置
SQL> alter session set max_dump_file_size=unlimited;
Session altered.
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events 'immediate trace name trace_buffer_on level 1048576';
Session altered.
SQL> alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> alter session set events '8103 trace name errorstack level 3';
Session altered.
SQL> alter session set events '10236 trace name context forever, level 1';
Session altered.
SQL> alter session set tracefile_identifier='ORA8103';
Session altered.
SQL> select * from chf.t_xifenfei;
select * from chf.t_xifenfei
*
ERROR at line 1:
ORA-08103: object no longer exists
--trace文件关键内容
block_row_dump:
tab 0, row 0, @0x1f70
tl: 48 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 9] 44 49 43 54 2e 42 41 53 45
col 1: [ 1] 32
col 2: [32]
…………
end_of_block_dump
The buffer with tsn: 0 rdba: 0x00400321 has already been dumped
The buffer with tsn: 4 rdba: 0x010002aa was pinned,
but could not be dumped
SQL> Select to_number('010002aa','xxxxxxxxxxxxxxxxxx') from dual;
TO_NUMBER('010002AA','XXXXXXXXXXXXXXXXXX')
------------------------------------------
16777898
SQL> select
2 dbms_utility.data_block_address_file(16777898) FILE_NO,
3 dbms_utility.data_block_address_block(16777898) BLOCK_NO
4 from dual;
FILE_NO BLOCK_NO
---------- ----------
4 682
检查坏块
[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/ora11g/users01.dbf
DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jan 13 18:03:13 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/users01.dbf
Block Checking: DBA = 16777898, Block Type = Unlimited data segment header with flg blks
Incorrect total map count: 24
Page 682 failed with check code 17006
DBVERIFY - Verification complete
Total Pages Examined : 2240
Total Pages Processed (Data) : 1421
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 199
Total Pages Failing (Index): 0
Total Pages Processed (Other): 229
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 391
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 898278 (0.898278)
--对应块
SQL> select
2 dbms_utility.data_block_address_file(16777898) FILE_NO,
3 dbms_utility.data_block_address_block(16777898) BLOCK_NO
4 from dual;
FILE_NO BLOCK_NO
---------- ----------
4 682
通过这里dbv检查发现,该数据库已经是坏块了,也就是说,网上流传的人工标志为坏块的方法在此处行不通,这里我们考虑使用dul等类此工具挖取数据,来拯救数据
dul和odu常规操作
DUL> unload table chf.t_xifenfei; . unloading table T_XIFENFEI DUL: Warning: Using data objno from segment header( 16779199) instead of expected id (75592) from dictionary DUL: Error: No entry in control file for block: ts# = 4 rfile# = 0 block# = 1087 DUL: Error: While processing unknown file block# 1087 DUL: Error: Could not read/parse data block ODU> unload table chf.t_xifenfei Unloading table: T_XIFENFEI,object ID: 75592 Unloading segment,storage(Obj#=75592 DataObj#=75592 TS#=4 File#=4 Block#=682 Cluster=0) corrupted block 0 rows unloaded
通过上面的测试证明,在该中情况下(SEGMENT HEADER)出现异常时,dul/odu均不能使用最常规的方法挖的数据
扫描数据文件方式挖
因为odu在这个方面的操作人性化于dul,所以只用odu进行相关测试,dul肯定能过实现相同功能
ODU> SCAN EXTENT TABLESPACE 4 PARALLEL 2 scan extent start: 2012-9-23 2:47:51 scanning extent... scanning extent finished. scan extent completed: 2012-9-23 2:47:51 ODU> unload table chf.t_xifenfei object 75592 Unloading table: T_XIFENFEI,object ID: 75592 Unloading segment,storage(Obj#=75592 DataObj#=75592 TS#=4 File#=4 Block#=682 Cluster=0) 74504 rows unloaded
试验证明通过odu扫描表空间/数据文件找回来所有的数据,然后truncate掉问题表,导入该数据库,问题可以得意顺利解决
