[20180228]手工模拟ORA-00600:[13013] [5001]3.txt
Oracle中表的数据块由块头、事务槽、行字典、行数据等多种结构组成。 行数据(rowdata)实际是由许多row piece 行片组成的,每一条
row piece的头部都有flag、locks、cols(cc)三个标志位。
其中flag标记了该row piece的类型,该flag位占用一个字节,其不同的bit位代表不同的含义,见下表:
ROW_CLUSTER_KEY = 0x80; KDRHFK
ROW_CTABLE_NUMBER = 0x40; KDRHFC
ROW_HEAD_PIECE = 0x20; KDRHFH
ROW_DELETED_ROW = 0x10; KDRHFD
ROW_FIRST_PIECE = 0x08; KDRHFF
ROW_LAST_PIECE = 0x04; KDRHFL
ROW_FROM_PREVIOUS = 0x02; KDRHFP
ROW_CONTINUE_NEXT = 0x01; KDRHFN
--//我们最常见0x2c 由如下组成:(如果没有出现行迁移)
ROW_HEAD_PIECE = 0x20; KDRHFH
ROW_FIRST_PIECE = 0x08; KDRHFF
ROW_LAST_PIECE = 0x04; KDRHFL
--//而如果0x3c 在上面的基础上增加:
ROW_DELETED_ROW = 0x10; KDRHFD
出现ORA-00600:[13013], [5001]且Arg [f] Code =3 代表这一row piece的flag >0xc0,
也就是该行片同时被标记为key和clustered(row is marked as both a Key and Clustered), 其检验代码为check code 6251。
当flag >= 0xc0 时 会出现kdrchk: row is marked as both a Key and Clustered Block 12 failed with check code 6251
当 0xac >flag >= 0xa0 时 会 kdrchk: row is Key and Not only piece of key Block 12 failed with check code 6255
当 flag = 0x43 是 会出现 kdrchk: C and neither of H or F Block 12 failed with check code 6263
当 flag = 0x83 时 会出现 kdrchk: row is marked both as a Key and being continued Block 12 failed with check code 6254
当Oracle进程访问数据块时首先会校验block的sum值并与block中的CHECKSUM值进行对比,若一致则说明该block没有物理讹误。但是光这
一项检查是不够的,不足以保证block无误。所以Oracle引入了一些列的逻辑检验,每一种逻辑检验对应一个检测代码(check code),这
些检测包括row piece的flag、cols(cc)状态是否正确等。
实际负责这类逻辑检验的函数包括:kdbchk、kddummy_blkchk、kco_blkchk、kdBlkCheckError、kdrchk等等。
这里当服务进程访问到问题数据块,检测代码发现其flag为0xff(KCHDFLPN),该flag从逻辑上讲是冲突的,所以检测代码认为该row
piece存在异常,进而会引发update的ORA-00600:[13013], [5001]或查询的ORA-600 [qertbFetchByRowID]内部错误。
这里需要说明一下的是,很多人认为dbv工具时无法检测出逻辑讹误的,实际上dbv、rman、validate structure和bbed-verify均可以检
测出一定程度的逻辑讹误,但是最可靠的还是db_block_checksum=true情况下的validate structure [online]验证命令。从另一个角度
来说,普通的dbv只能做单一的检测,而无法做到交叉地检验,从而了解表和索引上的不一致问题,但是validate structure online却可
以做到。
--//我自己也手工模拟看看.
1.环境:
SCOTT@book> @ &r/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
SCOTT@book> create table empx as select * from emp;
Table created.
SCOTT@book> create unique index pk_empx on empx(empno);
Index created.
--//注我开始没有建立索引,无法模拟,无法显示错误记录,自然不会update.
SCOTT@book> select rowid,empx.* from empx where empno=7900;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAWktAAEAAAAIrAAL 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
SCOTT@book> @ &r/rowid AAAWktAAEAAAAIrAAL
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
92461 4 555 11 0x100022B 4,555 alter system dump datafile 4 block 555 ;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
alter system flush buffer_cache;
2.使用bbed修改行记录flag:
BBED> x /rnccntnnn dba 4,555 *kdbr[11]
rowdata[78] @7699
-----------
flag@7699: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7700: 0x00
cols@7701: 8
--//flag=0x2c
col 0[2] @7702: 7900
col 1[5] @7705: JAMES
col 2[5] @7711: CLERK
col 3[3] @7717: 7698
col 4[7] @7721: 1981-12-03 00:00:00
col 5[3] @7729: 950
col 6[0] @7733: *NULL*
col 7[2] @7734: 30
BBED> modify /x 0xff dba 4,555 offset 7699
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 555 Offsets: 7699 to 7762 Dba:0x0100022b
------------------------------------------------------------------------------------------------------------------------------------------------
ff000802 c250054a 414d4553 05434c45 524b03c2 4d630777 b50c0301 010103c2 0a33ff02 c11f2c00 0803c24f 4d054144 414d5305 434c4552 4b03c24e 590777bb
<64 bytes per line>
BBED> dump /v dba 4,555 offset 7699 count 1
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 555 Offsets: 7699 to 7699 Dba:0x0100022b
-----------------------------------------------------------------------------------------------------------
ff l .
<32 bytes per line>
BBED> sum apply dba 4,555
Check value for File 4, Block 555:
current = 0x479e, required = 0x479e
3.测试:
SCOTT@book> select rowid,empx.* from empx where empno=7900;
ERROR:
ORA-01801: date format is too long for internal buffer
no rows selected
--//与作者提示不同.
$ oerr ora 01801
01801, 00000, "date format is too long for internal buffer"
// *Cause:
// *Action:
SCOTT@book> update (select rowid,empx.* from empx where empno=7900) set sal=940;
update (select rowid,empx.* from empx where empno=7900) set sal=940
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [13013], [5001], [92461], [16777771], [11], [16777771], [3], [], [], [], [], []
--//参数说明:
Arg [a] Passcount
Arg [b] Data Object number
Arg [c] Tablespace Relative DBA of block containing the row to be updated
Arg [d] Row Slot number
Arg [e] Relative DBA of block being updated (should be same as [c])
Arg [f] Code
SCOTT@book> select object_id,data_object_id,object_name from dba_objects where owner=user and object_name='EMPX';
OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- --------------------
92461 92461 EMPX
SCOTT@book> @ &r/dfb10 16777771
RFILE# BLOCK#
---------- ----------
4 555
TEXT
----------------------------------------
alter system dump datafile 4 block 555 ;
--//11 对应行号.
--//通过提示full,无法查询信息.
SCOTT@book> select /*+ full(empx) */ rowid,empx.* from empx where empno=7900;
no rows selected
--//解决很简单,修改正确flag,步骤如下:
SCOTT@book> alter system flush buffer_cache;
System altered.
BBED> modify /x 0x2c dba 4,555 offset 7699
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 555 Offsets: 7699 to 7762 Dba:0x0100022b
------------------------------------------------------------------------------------------------------------------------------------------------
2c000802 c250054a 414d4553 05434c45 524b03c2 4d630777 b50c0301 010103c2 0a33ff02 c11f2c00 0803c24f 4d054144 414d5305 434c4552 4b03c24e 590777bb
<64 bytes per line>
BBED> sum apply dba 4,555
Check value for File 4, Block 555:
current = 0x949e, required = 0x949e
BBED> verify dba 4,555
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 555
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
SCOTT@book> select rowid,empx.* from empx where empno=7900;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAWktAAEAAAAIrAAL 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
SCOTT@book> update (select rowid,empx.* from empx where empno=7900) set sal=940;
1 row updated.
SCOTT@book> commit ;
Commit complete.