[20181122]模拟ORA-08103错误.txt

中国策划协会,成都三圣乡,www.lolo.cc

[20181122]模拟ora-08103错误.txt

$ oerr ora 8103
08103, 00000, "object no longer exists"
// *cause:  the object has been deleted by another user since the operation
//          began, or a prior incomplete recovery restored the database to
//          a point in time during the deletion of the object.
// *action: delete the object if this is the result of an incomplete
//          recovery.

在 master note for handling oracle database corruption issues (文档 id 1088018.1)中对ora-8103错误的描述如下：

the object has been deleted by another user since the operation began.
if the error is reproducible, following may be the reasons:-
a.) the header block has an invalid block type.
b.) the data_object_id (seg/obj) stored in the block is different than the data_object_id stored in the segment header.

see dba_objects.data_object_id and compare it to the decimal value stored in the block (field seg/obj).

--//以前做过的测试.链接:http://blog.itpub.net/267265/viewspace-2131848/
--//听别人提起高水位下的块出现了未格式化的块.自己模拟测试看看.

1.环境:
scott@book> @ ver1
port_string                    version        banner
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/linux 2.4.xx            11.2.0.4.0     oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production

--//建立表空间:
create tablespace tea datafile
  '/mnt/ramdisk/book/tea01.dbf' size 40m autoextend on next 1m maxsize unlimited
logging
online
extent management local autoallocate
blocksize 8k
segment space management manual
flashback on;

--//顺便做1个备份.
rman> backup as copy datafile 6 format '/home/oracle/backup/%b' ;
starting backup at 2018-11-22 16:22:12
using target database control file instead of recovery catalog
allocated channel: ora_disk_1
channel ora_disk_1: sid=67 device type=disk
allocated channel: ora_disk_2
channel ora_disk_2: sid=94 device type=disk
allocated channel: ora_disk_3
channel ora_disk_3: sid=106 device type=disk
channel ora_disk_1: starting datafile copy
input datafile file number=00006 name=/mnt/ramdisk/book/tea01.dbf
output file name=/home/oracle/backup/tea01.dbf tag=tag20181122t162213 recid=13 stamp=992881334
channel ora_disk_1: datafile copy complete, elapsed time: 00:00:01
finished backup at 2018-11-22 16:22:15

starting control file and spfile autobackup at 2018-11-22 16:22:15
piece handle=/u01/app/oracle/fast_recovery_area/book/autobackup/2018_11_22/o1_mf_s_992881335_fzdssq4g_.bkp comment=none
finished control file and spfile autobackup at 2018-11-22 16:22:16

2.建立测试环境:
scott@book> create table t tablespace tea as select * from all_objects where rownum<=1e4;
table created.

scott@book> select object_id,data_object_id from dba_objects where object_name = 't' and owner = user;
 object_id data_object_id
---------- --------------
     90463          90463

scott@book> select segment_type, header_file, header_block from dba_segments where owner = user and segment_name = 't';
segment_type       header_file header_block
------------------ ----------- ------------
table                        6          128

scott@book> select file_id, block_id, blocks from dba_extents where owner = user and segment_name = 't';
   file_id   block_id     blocks
---------- ---------- ----------
         6        128          8
         6        136          8
         6        144          8
         6        152          8
         6        160          8
         6        168          8
         6        176          8
         6        184          8
         6        192          8
         6        200          8
         6        208          8
         6        216          8
         6        224          8
         6        232          8
         6        240          8
         6        248          8
         6        256        128
17 rows selected.

2.破坏数据块看看:
scott@book> alter system flush buffer_cache;
system altered.

scott@book> select max(rowid) from t ;
max(rowid)
------------------
aaawffaagaaaaebabj

scott@book> @ rowid aaawffaagaaaaebabj
    object       file      block        row rowid_dba            dba                  text
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90463          6        257         73  0x1800101           6,257                alter system dump datafile 6 block 257 ;

--//dba=6,257块上有数据.

scott@book> insert into t  select * from all_objects where rownum<=1;
1 row created.

scott@book> commit ;
commit complete.

scott@book> alter system flush buffer_cache;
system altered.

scott@book> @ rowid aaawffaagaaaaecaaa
    object       file      block        row rowid_dba            dba                  text
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90463          6        258          0  0x1800102           6,258                alter system dump datafile 6 block 258 ;

--//dba=6,258块上有数据.通过bbed观察dba=6,259
bbed> map dba 6,259
 file: /mnt/ramdisk/book/tea01.dbf (6)
 block: 259                                   dba:0x01800103
------------------------------------------------------------
 ktb data block (table/cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @92
 struct kdbt[0], 0 bytes                    @106
 sb2 kdbr[0]                                @106
 ub1 freespace[8082]                        @106
 ub1 rowdata[0]                             @8188
 ub4 tailchk                                @8188

--//可以发现dba=6,259已经格式化.但是没有数据.
scott@book> select count(*) from t;
  count(*)
----------
     10001
--//ok一切正常.现在破坏dba=6,259

bbed> set offset 0
        offset          0
--//注意一定要设置offset 0,不然可能copy仅仅剩下的部分.

bbed> copy filename '/home/oracle/backup/tea01.dbf' block 259 to filename '/mnt/ramdisk/book/tea01.dbf' block 259
warning: contents of previous bifile will be lost. proceed? (y/n) y
 file: /mnt/ramdisk/book/tea01.dbf (6)
 block: 259                                                  offsets:    0 to   63                                               dba:0x01800103
------------------------------------------------------------------------------------------------------------------------------------------------
 00a20000 03018001 00000000 00000105 83a70000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<64 bytes per line>

--//现在已经未格式化块.

4.继续测试:
scott@book> alter system flush buffer_cache;
system altered.

scott@book> select count(*) from t;
select count(*) from t
*
error at line 1:
ora-08103: object no longer exists
--//再线ora-08103.
--//而执行如下不会报错:
scott@book> select count(*) from t where rownum<=10001;
  count(*)
----------
     10001
scott@book> select count(*) from t where rownum<=10002;
select count(*) from t where rownum<=10002
                     *
error at line 1:
ora-08103: object no longer exists
    
--//检查跟踪文件:
kcbzibmlt: dump suspect buffer, err=8103
buffer tsn: 7 rdba: 0x01800103 (6/259)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0xa783 type: 0x00=unknown
hex dump of corrupt header 4 = corrupt
dump of memory from 0x000000006a4ea000 to 0x000000006a4ea014
06a4ea000 0000a200 01800103 00000000 05010000  [................]
06a4ea010 0000a783                             [....]
hex dump of block: st=4, typ_found=0
dump of memory from 0x000000006a4ea000 to 0x000000006a4ec000
06a4ea000 0000a200 01800103 00000000 05010000  [................]
06a4ea010 0000a783 00000000 00000000 00000000  [................]
06a4ea020 00000000 00000000 00000000 00000000  [................]
        repeat 508 times
06a4ebff0 00000000 00000000 00000000 00000001  [................]
dump of buffer cache at level 8 for tsn=7 rdba=25166080

--//可以发现dba =6,259报错.

5.如何跳过呢?
--//我测试仅仅设置坏块.如果真有数据可以通过一些特殊的方式读出来,这个给看运气.通过10231事件跳过坏块

$ oerr ora 10231
10231, 00000, "skip corrupted blocks on _table_scans_"
// *cause:
// *action: such blocks are skipped in table scans, and listed in trace files
--//alter session set db_file_multiblock_read_count=1 ;
alter session set events '10231 trace name context forever, level 10';

scott@book> alter session set events '10231 trace name context forever, level 10';
session altered.

scott@book> select count(*) from t ;
select count(*) from t
*
error at line 1:
ora-08103: object no longer exists

--//不行.在bbed设置坏块看看.
bbed> set dba 6,259
        dba             0x01800103 (25166083 6,259)

bbed> corrupt
block marked media corrupt.

bbed> sum apply ;
check value for file 6, block 259:
current = 0xa683, required = 0xa683


scott@book> select count(*) from t ;
  count(*)
----------
     10001

--//ok.跳过坏块.

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复