[20231019]rename IDL_UB1$的恢复测试前准备.txt
--//前几天看了https://www.anbob.com/archives/7545.html链接,对方rename IDL_UB1$表操作,导致无法建立表操作使用包的语句都有问题.
--//测试时遇到许多其他事情打断了恢复工作，最后我仅仅简单尝试了修改数据字典obj$的恢复方式。
--//我当时的执行命令是rename IDL_UB1$ to IDL_UB1X;,我开始以为这样恢复会很简单，因为我改名的长度与原来一样。
--//仅仅$ 换成 X。但我实际看到的情况不同，先为rename IDL_UB1$的恢复测试前做一些知识储备。
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.事前准备:
SYS@book> select rowid,owner#,name from obj$ where name='IDL_UB1$';
ROWID OWNER# NAME
------------------ ------ --------
AAAAASAABAAAADzAAX 0 IDL_UB1$
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 ;
$ strings -t d /mnt/ramdisk/book/system01.dbf | grep 'IDL_UB1\$'| awk '{print int($1/8192), " ",$1-int($1/8192)*8192, " " ,$2}'
243 6325 IDL_UB1$
351 5692 IDL_UB1$
375 5692 IDL_UB1$
17086 4802 IDL_UB1$
34025 5594 SELECT
34029 5278 COMMIT"SELECT
36154 5278 COMMIT"SELECT
95511 4482 IDL_UB1$l
--//注：显示的第一个字段对应块号，第一个字段对应相应数据块的偏移.
--//主要关注或者讲修改数据块243,351,375,17086,95511.
--//看看这些块属于那个对象。
SYS@book> @ find_objz 1 243 '' 1
SYS@book> @ pr
==============================
FILE_ID : 1
BLOCK_ID : 240
BLOCKS : 8
SEGMENT_TYPE : TABLE
OWNER : SYS
SEGMENT_NAME : OBJ$
PARTITION_NAME :
EXTENT_ID : 0
BYTES : 65536
TABLESPACE_NAME : SYSTEM
RELATIVE_FNO : 1
SEGTSN : 0
SEGRFN : 1
SEGBID : 240
PL/SQL procedure successfully completed.
SYS@book> @ find_objz 1 351 '' 1
SYS@book> @ pr
==============================
FILE_ID : 1
BLOCK_ID : 344
BLOCKS : 8
SEGMENT_TYPE : INDEX
OWNER : SYS
SEGMENT_NAME : I_OBJ2
PARTITION_NAME :
EXTENT_ID : 0
BYTES : 65536
TABLESPACE_NAME : SYSTEM
RELATIVE_FNO : 1
SEGTSN : 0
SEGRFN : 1
SEGBID : 344
PL/SQL procedure successfully completed.
SYS@book> @ find_objz 1 375 '' 1
SYS@book> @ pr
==============================
FILE_ID : 1
BLOCK_ID : 368
BLOCKS : 8
SEGMENT_TYPE : INDEX
OWNER : SYS
SEGMENT_NAME : I_OBJ5
PARTITION_NAME :
EXTENT_ID : 0
BYTES : 65536
TABLESPACE_NAME : SYSTEM
RELATIVE_FNO : 1
SEGTSN : 0
SEGRFN : 1
SEGBID : 368
PL/SQL procedure successfully completed.
SYS@book> @ find_objz 1 17086 '' 1
SYS@book> @ pr
==============================
FILE_ID : 1
BLOCK_ID : 17024
BLOCKS : 128
SEGMENT_TYPE : CLUSTER
OWNER : SYS
SEGMENT_NAME : C_TOID_VERSION#
PARTITION_NAME :
EXTENT_ID : 18
BYTES : 1048576
TABLESPACE_NAME : SYSTEM
RELATIVE_FNO : 1
SEGTSN : 0
SEGRFN : 1
SEGBID : 3464
PL/SQL procedure successfully completed.
SYS@book> @ find_objz 1 95511 '' 1
SYS@book> @ pr
==============================
FILE_ID : 1
BLOCK_ID : 95488
BLOCKS : 128
SEGMENT_TYPE : CLUSTER
OWNER : SYS
SEGMENT_NAME : C_OBJ#_INTCOL#
PARTITION_NAME :
EXTENT_ID : 6
BYTES : 1048576
TABLESPACE_NAME : SYSTEM
RELATIVE_FNO : 1
SEGTSN : 0
SEGRFN : 1
SEGBID : 2688
PL/SQL procedure successfully completed.
--//dba= 1,95511 属于对象C_OBJ#_INTCOL#是1个CLUSTER对象。
BBED> set width 200
WIDTH 200
BBED> p kdbt
struct kdbt[0], 4 bytes @106
sb2 kdbtoffs @106 0
sb2 kdbtnrow @108 1
struct kdbt[1], 4 bytes @110
sb2 kdbtoffs @110 1
sb2 kdbtnrow @112 207
--//C_OBJ#_INTCOL#下仅仅有1个表。
BBED> x /rnnnnc *kdbr[90]
rowdata[3800] @4459
-------------
flag@4459: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@4460: 0x02
cols@4461: 5
col 0[2] @4463: 7
col 1[1] @4466: 0
col 2[2] @4468: 19
col 3[9] @4471: 380422925370589000000000000000000000
col 4[8] @4481: IDL_UB1$
--//是cluster的第1个表.下一个字符0x6c,对应字符l.所以上面扫描看到IDL_UB1$l字符串是正常的.
SELECT ROWNUM -1 rn , a.*
FROM ( SELECT *
FROM dba_objects
WHERE owner = 'SYS' AND data_object_id = 444
ORDER BY object_id) a;
RN OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
---------- ------ -------------- -------------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
0 SYS C_OBJ#_INTCOL# 444 444 CLUSTER 2013-08-24 11:37:43 2013-08-24 11:37:43 2013-08-24:11:37:43 VALID N N N 5
1 SYS HISTGRM$ 446 444 TABLE 2013-08-24 11:37:43 2013-08-24 11:37:43 2013-08-24:11:37:43 VALID N N N 1
2 rows selected.
--//HISTGRM$ 应该是直方图相关的表
BBED> x /rnn *kdbr[0]
rowdata[7503] @8162
-------------
flag@8162: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8163: 0x00
cols@8164: 2
kref@8165: 181
mref@8167: 68
hrid@8169:0x00417518.0
nrid@8175:0x00417518.0
col 0[3] @8181: 6579
col 1[2] @8185: 7
SYS@book> select * from HISTGRM$ where obj#=6579 and intcol#=7 and col#=7 and row#=0 and bucket=19
2 @ pr
==============================
OBJ# : 6579
COL# : 7
ROW# : 0
BUCKET : 19
ENDPOINT : 380422925370589000000000000000000000
INTCOL# : 7
EPVALUE : IDL_UB1$
SPARE1 :
SPARE2 :
PL/SQL procedure successfully completed.
SYS@book> @ oid 6579
owner object_name object_type SUBOBJECT_NAME CREATED LAST_DDL_TIME status DATA_OBJECT_ID OBJECT_ID
----- ----------------- ------------------ -------------- ------------------- ------------------- --------- -------------- ----------
SYS WRH$_SEG_STAT_OBJ TABLE 2013-08-24 11:39:10 2013-08-24 11:39:10 VALID 6579 6579
1 row selected.
--//明显这个不重要.
--//dba = 1,17086.属于C_TOID_VERSION# cluster。
BBED> map dba 1,17086
File: /mnt/ramdisk/book/system01.dbf (1)
Block: 17086 Dba:0x004042be
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @92
struct kdbt[3], 12 bytes @106
sb2 kdbr[3] @118
ub1 freespace[7842] @124
ub1 rowdata[222] @7966
ub4 tailchk @8188
-//offset =4802 在freespace 区间,不用修改.
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 87018 87018 1158 2023-08-30 22:00:13 1 VISIBLE
OBJ$ I_OBJ2 NORMAL YES VALID NO N 3 876 87018 87018 64485 2023-08-30 22:00:13 1 VISIBLE
OBJ$ I_OBJ3 NORMAL NO VALID NO N 2 16 3421 3421 249 2023-08-30 22:00:13 1 VISIBLE
OBJ$ I_OBJ4 NORMAL NO VALID NO N 2 383 9358 87018 3237 2023-08-30 22:00:13 1 VISIBLE
OBJ$ I_OBJ5 NORMAL YES VALID NO N 3 876 87018 87018 64473 2023-08-30 22:00:13 1 VISIBLE
--//I_OBJ2,I_OBJ5都是obj#的索引,里面都包含name字段,换一句话讲如果使用bbed恢复.这3块(243,351,375)都需要恢复.
--//oracle建立这两个索引有点奇怪,都是包含相同字段,仅仅顺序有一些不同,并且都是唯一索引.
3.探究数据块243,351,375的一些细节：
BBED> x /rnnncncntttnccnxnnncct dba 1,243 *kdbr[23]
rowdata[5215] @6311
-------------
flag@6311: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@6312: 0x00
cols@6313: 18
col 0[3] @6314: 225
col 1[3] @6318: 225
col 2[1] @6322: 0
col 3[8] @6324: IDL_UB1$
col 4[2] @6333: 1
col 5[0] @6336: *NULL*
col 6[2] @6337: 2
col 7[7] @6340: 2013-08-24 11:37:39
col 8[7] @6348: 2013-08-24 11:37:39
col 9[7] @6356: 2013-08-24 11:37:39
col 10[2] @6364: 1
col 11[0] @6367: *NULL*
col 12[0] @6368: *NULL*
col 13[1] @6369: 0
col 14[0] @6371: *NULL*
col 15[1] @6372: 0
col 16[2] @6374: 1
col 17[1] @6377: 0
--//dba=1,351 , 索引i_obj2
BBED> p dba 1,351 kd_off
sb2 kd_off[0] @124 8032
sb2 kd_off[1] @126 0
sb2 kd_off[2] @128 4078
sb2 kd_off[3] @130 4120
sb2 kd_off[4] @132 4172
sb2 kd_off[5] @134 4214
..
sb2 kd_off[36] @196 5589
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sb2 kd_off[37] @198 5623
sb2 kd_off[38] @200 5657
sb2 kd_off[39] @202 5697
sb2 kd_off[40] @204 5731
sb2 kd_off[41] @206 5771
sb2 kd_off[42] @208 5809
sb2 kd_off[43] @210 5842
...
sb2 kd_off[99] @322 7861
sb2 kd_off[100] @324 7911
sb2 kd_off[101] @326 7952
--//前面记录的偏移是5692,BBED> p dba 1,351 kd_off输出的相对偏移.
--//5692-92 = 5600,前面还有一些flag,lock标识,而且i_obj2是唯一索引,记录的rowid在前面占6字节.对应的应该是kd_off[36]指向的内容。
BBED> x /rncncccnnn dba 1,351 *kd_off[36]
rowdata[1679] @5681
-------------
flag@5681: 0x00 (NONE)
lock@5682: 0x00
keydata[6]: 0x00 0x40 0x00 0xf3 0x00 0x17
data key:
col 0[1] @5690: 0
col 1[8] @5692: IDL_UB1$
--//偏移等于5692，能对上。
col 2[2] @5701: 1
col 3[0] @5704: *NULL*
col 4[0] @5705: *NULL*
col 5[0] @5706: *NULL*
col 6[2] @5707: 2
col 7[1] @5710: 0
col 8[3] @5712: 225
--//dba=1,375 , 索引i_obj5,也是唯一索引.方法类似.
BBED> x /rncnnnnccn dba 1,375 *kd_off[36]
rowdata[1679] @5681
-------------
flag@5681: 0x00 (NONE)
lock@5682: 0x00
keydata[6]: 0x00 0x40 0x00 0xf3 0x00 0x17
data key:
col 0[1] @5690: 0
col 1[8] @5692: IDL_UB1$
col 2[2] @5701: 1
col 3[2] @5704: 2
col 4[1] @5707: 0
col 5[0] @5709: *NULL*
col 6[0] @5710: *NULL*
col 7[0] @5711: *NULL*
col 8[3] @5712: 225
4.阶段总结：
--//如果需要使用bbed做这里rename恢复，仅仅需要修改这3块。
--//概括一下:
x /rnnncncntttnccnxnnncct dba 1,243 *kdbr[23]
x /rncncccnnn dba 1,351 *kd_off[36]
x /rncnnnnccn dba 1,375 *kd_off[36]
--//内容有点多，使用bbed的恢复另外写一篇blog。