這裡簡單介紹一下如何處理解決Linux平臺下Oracle 19c啟動時，告警日誌出現ORA-00800錯誤的問題，詳情介紹請見下麵內容： 環境描述： 操作系統：Red Hat Enterprise Linux release 8.8 (Ootpa) 資料庫 ：19.16.0.0.0 企業版 問題描述 ...
[20231103]rename IDL_UB1$後使用bbed的恢復的後遺症.txt
--//前段時間遇到的問題,測試使用rename IDL_UB1$後使用bbed的恢復,當時沒有發現這個問題,今天才意識到問題.
--//我只能繼續前面的恢復操作.
1.環境:
SYS@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
2.問題提出:
--//我本來想再嘗試rename修改後長度不一致的情況,發現問題.
SYS@book> rename IDL_UB1$ to IDL_UB1Y_BAK;
rename IDL_UB1$ to IDL_UB1Y_BAK
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [13011], [18], [4280486], [6], [4280486], [17], [], [], [], [], [], []
--//4280486 = set dba 1,86182 = alter system dump datafile 1 block 86182 = 0x4150a6,正好是當時insert obj$的塊號.
--//很明顯索引不一致.當時我犯了一個小錯誤,我僅僅analyze table IDL_UB1$,分析對象錯了.
SYS@book> analyze table IDL_UB1$ validate structure cascade;
Table analyzed.
--//實際上應該分析obj$表.
SYS@book> analyze table obj$ validate structure cascade;
analyze table obj$ validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
--//檢查跟蹤文件發現如下:
row not found in index tsn: 0 rdba: 0x00400168
env [0x7fff55680a40]: (scn: 0x0003.1775a1d0 xid: 0x007b.020.00000008 uba: 0x00000000.0000.00 statement num=0 parent
xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0003.1775a11a flg: 0x00000060)
col 0; len 3; (3): c2 03 1a
col 1; len 2; (2): c1 03
col 2; len 1; (1): 80
col 3; len 6; (6): 00 40 00 f3 00 17
Block header dump: 0x004000f3
Object id on Block? Y
seg/obj: 0x12 csc: 0x03.176048bd itc: 1 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.008.00004d7f 0x00c03342.0f06.13 --U- 1 fsc 0x0000.176048c2
--//0x00400168 = set dba 1,360 = alter system dump datafile 1 block 360 = 4194664
SYS@book> @ find_objz 1 360 '' 1
SYS@book> @ pr
==============================
FILE_ID : 1
BLOCK_ID : 360
BLOCKS : 8
SEGMENT_TYPE : INDEX
OWNER : SYS
SEGMENT_NAME : I_OBJ4
PARTITION_NAME :
EXTENT_ID : 0
BYTES : 65536
TABLESPACE_NAME : SYSTEM
RELATIVE_FNO : 1
SEGTSN : 0
SEGRFN : 1
SEGBID : 360
PL/SQL procedure successfully completed.
--//一看到這裡我馬上反應過來我前面的恢復有問題,因為rename 對象的操作實際上delete然後insert.
--//這樣其它obj$涉及到的索引都要修改,我犯了教條主義的錯誤,有點想當然了.
--//0x004000f3 = set dba 1,243 = alter system dump datafile 1 block 243 = 4194547
--//很明顯索引i_obj4找不到這個鍵值.
--//另外註意這樣執行沒有問題,看來oracle的索引檢查存在問題的.
SYS@book> validate index i_obj4;
Index analyzed.
SYS@book> analyze index i_obj4 validate structure;
Index analyzed.
3.嘗試恢復前看看obj$有那些索引：
SYS@book> @ ind2 obj$
Display indexes where table or index name matches obj$...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
----------- ---------- ---------- ---- ------------- ----
SYS OBJ$ I_OBJ1 1 OBJ#
2 OWNER#
3 TYPE#
I_OBJ2 1 OWNER#
2 NAME
3 NAMESPACE
4 REMOTEOWNER
5 LINKNAME
6 SUBNAME
7 TYPE#
8 SPARE3
9 OBJ#
I_OBJ3 1 OID$
I_OBJ4 1 DATAOBJ#
2 TYPE#
3 OWNER#
I_OBJ5 1 SPARE3
2 NAME
3 NAMESPACE
4 TYPE#
5 OWNER#
6 REMOTEOWNER
7 LINKNAME
8 SUBNAME
9 OBJ#
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
----------- ---------- ---------- ------- ---- ------ ---- ---- -- ------ ----- -------- ----- ------------------- ------ ---------
SYS OBJ$ I_OBJ1 NORMAL YES VALID NO N 2 250 86998 86998 1156 2017-02-04 06:00:11 1 VISIBLE
OBJ$ I_OBJ2 NORMAL YES VALID NO N 3 875 86998 86998 64478 2017-02-04 06:00:11 1 VISIBLE
OBJ$ I_OBJ3 NORMAL NO VALID NO N 2 16 3421 3421 249 2017-02-04 06:00:11 1 VISIBLE
OBJ$ I_OBJ4 NORMAL NO VALID NO N 2 382 9342 86998 3259 2017-02-04 06:00:11 1 VISIBLE
OBJ$ I_OBJ5 NORMAL YES VALID NO N 3 875 86998 86998 64466 2017-02-04 06:00:11 1 VISIBLE
4.嘗試i_obj4索引恢復:
SYS@book> select /*+ full(obj$) */ rowid,DATAOBJ#,TYPE#,OWNER# from obj$ where name='IDL_UB1$' ;
ROWID DATAOBJ# TYPE# OWNER#
------------------ ---------- ---------- ----------
AAAAASAABAAAADzAAX 225 2 0
SYS@book> @ rowid AAAAASAABAAAADzAAX
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------ ---- ----- --- ---------- ----- ----------------------------------------
18 1 243 23 0x4000F3 1,243 alter system dump datafile 1 block 243 ;
SYS@book> select /*+ index(obj$ i_obj4) */ rowid,DATAOBJ#,TYPE#,OWNER# from obj$ where name='IDL_UB1$' ;
no rows selected
--//有點不理解,使用提示index(obj$ i_obj4)至少應該看到錯誤的rowid.
BBED> x /rnnnx dba 1,363 *kd_off[10]
rowdata[4] @4436
----------
flag@4436: 0x00 (NONE)
lock@4437: 0x00
data key:
col 0[3] @4439: 225
col 1[2] @4443: 2
col 2[1] @4446: 0
col 3[6] @4448: 0x00 0x41 0x50 0xa6 0x00 0x06
--//004150a6 = set dba 1,86182 = alter system dump datafile 1 block 86182 = 4280486,還是指向原來的位置.
--//噢明白了前面使用索引的查詢不對,應該寫成,實際上看上面的執行計劃很容易明白,走的是 INDEX FULL SCAN:
SYS@book> select /*+ index(obj$ i_obj4) */ rowid,DATAOBJ#,TYPE#,OWNER# from obj$ where DATAOBJ#=225 and TYPE#=2 and OWNER#=0 ;
ROWID DATAOBJ# TYPE# OWNER#
------------------ ---------- ---------- ----------
AAAAASAABAAAVCmAAG 225 2 0
SYS@book> @ rowid AAAAASAABAAAVCmAAG
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------ ---- ----- --- ---------- ------- ----------------------------------------
18 1 86182 6 0x4150A6 1,86182 alter system dump datafile 1 block 86182
--//很明顯指向rename後的對象.
--//修複就很簡單就是col 3[6] 現在的0x00 0x41 0x50 0xa6 0x00 0x06 換成 00 40 00 f3 00 17.
BBED> dump dba 1,363 offset 4448 count 6
File: /mnt/ramdisk/book/system
------------------------------------
004150a6 0006
<16 bytes per line>
assign dba 1,363 offset 4448 = 0x00;
assign dba 1,363 offset 4449 = 0x40;
assign dba 1,363 offset 4450 = 0x00;
assign dba 1,363 offset 4451 = 0xf3;
assign dba 1,363 offset 4452 = 0x00;
assign dba 1,363 offset 4453 = 0x17;
BBED> sum apply
Check value for File 1, Block 363:
current = 0xc26a, required = 0xc26a
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 363
SYS@book> alter system flush buffer_cache;
System altered.
SYS@book> select /*+ index(obj$ i_obj4) */ rowid,DATAOBJ#,TYPE#,OWNER#,name from obj$ where DATAOBJ#=225 and TYPE#=2 and OWNER#=0 ;
ROWID DATAOBJ# TYPE# OWNER# NAME
------------------ -------- ----- ------ ---------
AAAAASAABAAAADzAAX 225 2 0 IDL_UB1$
--//剩下i_obj1,i_obj3索引也是類似.
5.嘗試i_obj1索引恢復:
SYS@book> @ treedump i_obj1
OBJECT_ID
----------
36
*** 2023-11-02 11:26:14.695
----- begin tree dump
branch: 0x400151 4194641 (0: nrow: 250, level: 1)
leaf: 0x400152 4194642 (-1: nrow: 421 rrow: 421)
leaf: 0x400153 4194644 (0: nrow: 420 rrow: 420)
....
SYS@book> select /*+ index(obj$ i_obj1) */ rowid,obj#,OWNER#,type# from obj$ where OBJ#=225 and OWNER#=0 ;
ROWID OBJ# OWNER# TYPE#
------------------ ---------- ---------- ----------
AAAAASAABAAAVCmAAG 225 0 2
--//4194642 = set dba 1,338 = alter system dump datafile 1 block 338 = 0x400152
BBED> x /rnnn dba 1,338 *kd_off[226]
rowdata[3711] @4688
-------------
flag@4688: 0x00 (NONE)
lock@4689: 0x02
keydata[6]: 0x00 0x41 0x50 0xa6 0x00 0x06
data key:
col 0[3] @4697: 225
col 1[1] @4701: 0
col 2[2] @4703: 2
--//i_OBJ1是唯 一索引 ,rowid在date key前面.
BBED> dump dba 1,338 offset 4690 count 6
File: /mnt/ramdisk/book/system
------------------------------------
004150a6 0006
<16 bytes per line>
assign dba 1,338 offset 4690 = 0x00;
assign dba 1,338 offset 4691 = 0x40;
assign dba 1,338 offset 4692 = 0x00;
assign dba 1,338 offset 4693 = 0xf3;
assign dba 1,338 offset 4694 = 0x00;
assign dba 1,338 offset 4695 = 0x17;
BBED> sum apply
Check value for File 1, Block 338:
current = 0x6f2b, required = 0x6f2b
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 338
SYS@book> alter system flush buffer_cache;
System altered.
SYS@book> select /*+ index(obj$ i_obj1) */ rowid,obj#,OWNER#,type#,name from obj$ where OBJ#=225 and OWNER#=0 and type#=2;
ROWID OBJ# OWNER# TYPE# NAME
------------------ ---- ------ ----- --------
AAAAASAABAAAADzAAX 225 0 2 IDL_UB1$
6.嘗試i_obj3索引恢復:
--//剩下i_obj3索引也是類似.
SYS@book> set null null
SYS@book> select /*+ full(obj$ ) */ rowid,oid$ from obj$ where name='IDL_UB1$';
ROWID OID$
------------------ -----
AAAAASAABAAAADzAAX null
--//很幸運name='IDL_UB1$'的OID$是NULL,i_obj3不會存在這樣的鍵值.也就是不需要修複操作.
SYS@book> analyze table obj$ validate structure cascade;
Table analyzed.
--//這樣才算徹底恢復.
--//我不再做不等長rename的bbed測試了,這樣工作量有點大,直接修改obj$數據字典的方法還是簡單許多.
6.總結:
--//主要問題在於分析對象錯誤,實際上如果意識到rename操作是delete然後insert,就應該意識到自己的恢覆沒有完成.
--//另外不建議選擇bbed的恢復模式.
7.補充相關ora-00600 13011錯誤信息：
ORA-00600: internal error code, arguments: [13011], [18], [4280486], [6], [4280486], [17], [], [], [], [], [], []
--//4280486 = set dba 1,86182 = alter system dump datafile 1 block 86182 = 0x4150a6
--//1,243 = file#,block# dba(10): 4194547 = file#,block# dba(16): 0x4000f3
--//僅僅是我的猜測：
參數1 : obj# 對象號,obj$的obj#=18.
參數2 : 塊號 dba 1,86182
參數3 : 行號 索引上記錄的行號 0x6
參數4 : 塊號 dba 1,86182
參數5 : 行號 表上記錄的行號 0x17=23
--//這裡塊號一樣我不是很清楚。總感覺參數4應該記錄的是4194547。
8.後記:
--//為了檢驗自己恢復正確,繼續做rename測試,小心不要在生產系統做這樣的測試!!
SYS@book> rename IDL_UB1$ to IDL_UB1Y_BAK;
Table renamed.
SYS@book> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
Database opened.
--//可以正常啟動.
SYS@book> @ rowid AAAAASAABAAAADzAAX
|| DBMS_ROWID.ROWID_BLOCK_NUMBER ('AAAAASAABAAAADzAAX')
*
ERROR at line 13:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
SYS@book> update obj$ set name='IDL_UB1$' where name='IDL_UB1Y_BAK';
1 row updated.
SYS@book> commit ;
Commit complete.
SYS@book> alter system flush buffer_cache;
System altered.
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> @ rowid AAAAASAABAAAADzAAX
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------ ---- ----- --- ---------- ----- ----------------------------------------
18 1 243 23 0x4000F3 1,243 alter system dump datafile 1 block 243 ;
--//OK,現在恢復正常.
SYS@book> select /*+ index(obj$ i_obj4) */ rowid,DATAOBJ#,TYPE#,OWNER# from obj$ where name='IDL_UB1$' ;
ROWID DATAOBJ# TYPE# OWNER#
------------------ -------- ----- ------
AAAAASAABAAAVCmAAt 225 2 0
SYS@book> @ rowid AAAAASAABAAAVCmAAt
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------ ---- ----- --- ---------- ------- ----------------------------------------
18 1 86182 45 0x4150A6 1,86182 alter system dump datafile 1 block 86182
--//rowid已經變了.
