$ 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.跳过坏块.
