阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

ORA-08103错误情景再现及解决

208次阅读
没有评论

共计 7244 个字符,预计需要花费 19 分钟才能阅读完成。

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

更多 Oracle 相关信息见 Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计7244字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中