[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_LAST_PIECE = 0x04; KDRHFL
--//而如果0x3c 在上面的基礎上增加:
ROW_DELETED_ROW = 0x10; KDRHFD
http://www.askmaclean.com/archives/%E6%89%8B%E5%B7%A5%E6%A8%A1%E6%8B%9Foracle%E6%95%B0%E6%8D%AE%E5%9D%97%E9%80%BB%E8%BE%91%E8%AE%B9%E8%AF%AF%E5%BC%95%E5%8F%91ora-0060013013-5001%E4%B8%80%E4%BE%8B.html出現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 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
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
2c000802 c250054a 414d4553 05434c45 524b03c2 4d630777 b50c0301 010103c2 0a33ff02 c11f2c00 0803c24f 4d054144 414d5305 434c4552 4b03c24e 590777bb
<64 bytes per line>
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
1 row updated.
SCOTT@book> commit ;
Commit complete.