[20231019]rename IDL_UB1$的恢复测试前准备.txt

[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建立这两个索引有点奇怪,都是包含相同字段,仅仅顺序有一些不同,并且都是 unique索引. 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是unique索引,记录的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,也是unique索引.方法类似. 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。