[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.跳過壞塊.

[20181122]模擬ORA-08103錯誤.txt

相關文章