[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已经变了.