[20231103]rename IDL_UB1$后使用bbed的恢复的后遗症.txt

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

热门相关:高人竟在我身边   宴先生缠得要命   性版17:男性的17种性幻想   一个朋友的妈妈   高甜预警:我家影后超猛的