[20231020]rename IDL_UB1$后使用bbed的恢复.txt
发布时间 2023-10-23 21:52:10作者: lfree
--//继续前面的测试：
--//参考链接： [20231019]rename IDL_UB1$的恢复测试前准备.txt
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> rename IDL_UB1$ to IDL_UB1X;
Table renamed.
SYS@book> alter system checkpoint;
System altered.
alter system checkpoint;
alter system checkpoint;
$ strings -t d /mnt/ramdisk/book/system01.dbf | grep 'IDL_UB1X'| awk '{print int($1/8192), " ",$1-int($1/8192)*8192, " " ,$2}'
351 3983 IDL_UB1X
375 3983 IDL_UB1X
86182 3867 IDL_UB1X
--//为了测试方便,我贴上前面的测试结果(删除多余部分),可以发现偏移都不对.
--//其中的86182对应一定是obj$.居然没有就地修改,位置发生了变动,包括索引.
--//如果关闭数据库,数据库可能无法启动.(注：我后面的测试说明可以正常启动，仅仅一些涉及包的命令无法正常使用)
--//原来的结果如下.
$ 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$
3.对比分析:
--//dba 1,243
BBED> x /rnnncncntttnccnxnnncct dba 1,243 *kdbr[23]
rowdata[5215] @6311
-------------
flag@6311: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@6312: 0x01
cols@6313: 0
--//可以发现记录已经做了删除标识，KDRHFD表示删除标识。
BBED> x /rnnncncntttnccnxnnncct dba 1,86182 *kdbr[6]
rowdata[0] @3853
----------
flag@3853: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@3854: 0x01
cols@3855: 18
col 0[3] @3856: 225
col 1[3] @3860: 225
col 2[1] @3864: 0
col 3[8] @3866: IDL_UB1X
col 4[2] @3875: 1
col 5[0] @3878: *NULL*
col 6[2] @3879: 2
col 7[7] @3882: 2013-08-24 11:37:39
col 8[7] @3890: 2023-10-20 10:48:44
col 9[7] @3898: 2023-10-20 10:48:44
col 10[2] @3906: 1
col 11[0] @3909: *NULL*
col 12[0] @3910: *NULL*
col 13[1] @3911: 0
col 14[0] @3913: *NULL*
col 15[1] @3914: 0
col 16[2] @3916: 2
~~~~~~~~~~~~~~~~~~~
col 17[1] @3919: 0
--//正常应该是0x2c。
--//原来的dba =1,243的样子:
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
--//长度并没有变化,col 16值不同(事后检查spare1),可以猜测rename实际上先delete原来记录,然后在插入一条.并不是就地修改.导致
--//索引的位置也发生了不再原来位置,索引我仅仅改动最后1个字符,应该还在原来的块中.
BBED> x /rncncccnnn dba 1,351 *kd_off[36]
rowdata[1713] @5681
-------------
flag@5681: 0x01 (KDXRDEL)
lock@5682: 0x02
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[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
--//现在flag@5681: 0x01 (KDXRDEL),标识删除.
BBED> x /rncncccnnn dba 1,351 *kd_off[37]
rowdata[4] @3972
----------
flag@3972: 0x00 (NONE)
lock@3973: 0x02
keydata[6]: 0x00 0x41 0x50 0xa6 0x00 0x06
data key:
col 0[1] @3981: 0
col 1[8] @3983: IDL_UB1X
col 2[2] @3992: 1
col 3[0] @3995: *NULL*
col 4[0] @3996: *NULL*
col 5[0] @3997: *NULL*
col 6[2] @3998: 2
col 7[1] @4001: 0
col 8[3] @4003: 225
--//索引的恢复很简单设置flag对调就ok了.
BBED> x /rncnnnnccn dba 1,375 *kd_off[36]
rowdata[1713] @5681
-------------
flag@5681: 0x01 (KDXRDEL)
lock@5682: 0x02
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
--//现在flag@5681: 0x01 (KDXRDEL),标识删除.
BBED> x /rncnnnnccn dba 1,375 *kd_off[37]
rowdata[4] @3972
----------
flag@3972: 0x00 (NONE)
lock@3973: 0x02
keydata[6]: 0x00 0x41 0x50 0xa6 0x00 0x06
data key:
col 0[1] @3981: 0
col 1[8] @3983: IDL_UB1X
col 2[2] @3992: 1
col 3[2] @3995: 2
col 4[1] @3998: 0
col 5[0] @4000: *NULL*
col 6[0] @4001: *NULL*
col 7[0] @4002: *NULL*
col 8[3] @4003: 225
--//有了以上相关信息恢复就很简单了.
--//恢复对应数据块243块号,在86182块做删除标识，恢复原来的索引指向(块号351,375).
4.关闭数据库看看,开始bbed恢复测试:
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.
--//实际上数据库open一点问题都没有.
SYS@book> @ ddl scott.dept
BEGIN dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY',true); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
--//实际一些包会报错.也就是实际上 select * from obj$ where name ='IDL_UB1X';输出正常.
--//但是如果你调用一些包就出现问题.
SYS@book> @ rowid AAAYD0AAIAAMqfEAAF
|| DBMS_ROWID.ROWID_BLOCK_NUMBER ('AAAYD0AAIAAMqfEAAF')
*
ERROR at line 13:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
--//实际上恢复就变得很简单了.直接修改数据字段obj$字段就ok了.但是我的目的是使用bbed完成恢复，继续操作。
--//在bbed下执行执行如下操作。
assign dba 1,243 offset 6311 = 0x2c
assign dba 1,86182 offset 3853 = 0x3c
assign dba 1,351 offset 5681 =0x0
assign dba 1,351 offset 3972 =0x1
assign dba 1,375 offset 5681 =0x0
assign dba 1,375 offset 3972 =0x1
5.修复数据块的一致性.
--//然后开始修复数据块的一致性.
--//dba 1,243
BBED> set dba 1,243
DBA 0x004000f3 (4194547 1,243)
BBED> sum apply
Check value for File 1, Block 243:
current = 0x73b6, required = 0x73b6
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 243
Block Checking: DBA = 4194547, Block Type = KTB-managed data block
data header at 0x8ec644
kdbchk: the amount of space used is not equal to block size
used=7298 fsc=66 avsp=822 dtl=8120
Block 243 failed with check code 6110
--//简单公式:
--//code 6110
--//avsp = dtl-used-fsc
--//code 6111
--//tosp = avsp+stb+fsc
--//avsp = dtl-used-fsc
--//avsp= 8120-7298-66 = 756
BBED> assign kdbh.kdbhavsp=756
sb2 kdbhavsp @78 756
BBED> sum apply
Check value for File 1, Block 243:
current = 0x7274, required = 0x7274
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 243
Block Checking: DBA = 4194547, Block Type = KTB-managed data block
data header at 0x8ec644
kdbchk: space available on commit is incorrect
tosp=890 fsc=66 stb=0 avsp=756
Block 243 failed with check code 6111
--//tosp = avsp+stb+fsc
--//tosp= 756+0+66 = 822
BBED> assign kdbh.kdbhtosp=822
sb2 kdbhtosp @80 822
BBED> sum apply
Check value for File 1, Block 243:
current = 0x7238, required = 0x7238
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 243
--//其它块基本类似.不再具体说明.
--//dba 1,86182
BBED> set dba 1,86182
DBA 0x004150a6 (4280486 1,86182)
BBED> sum apply
Check value for File 1, Block 86182:
current = 0x84ea, required = 0x84ea
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 86182
Block Checking: DBA = 4280486, Block Type = KTB-managed data block
data header at 0x8ec644
kdbchk: the amount of space used is not equal to block size
used=3098 fsc=0 avsp=4956 dtl=8120
Block 86182 failed with check code 6110
--//avsp =8120-3098-0 = 5022
BBED> assign kdbh.kdbhavsp=5022
sb2 kdbhavsp @78 5022
BBED> sum apply
Check value for File 1, Block 86182:
current = 0x8428, required = 0x8428
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 86182
Block Checking: DBA = 4280486, Block Type = KTB-managed data block
data header at 0x8ec644
kdbchk: avsp(5022) > tosp(4956)
Block 86182 failed with check code 6128
BBED> assign kdbh.kdbhtosp=5022
sb2 kdbhtosp @80 5022
BBED> sum apply
Check value for File 1, Block 86182:
current = 0x84ea, required = 0x84ea
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 86182
Block Checking: DBA = 4280486, Block Type = KTB-managed data block
data header at 0x8ec644
kdbchk: space available on commit is incorrect
tosp=5022 fsc=0 stb=2 avsp=5022
Block 86182 failed with check code 6111
--//tosp=5022+0+2=5024.
BBED> assign kdbh.kdbhtosp=5024
sb2 kdbhtosp @80 5024
BBED> sum apply
Check value for File 1, Block 86182:
current = 0x84d4, required = 0x84d4
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 86182
--//dba 1,351
BBED> set dba 1,351
DBA 0x0040015f (4194655 1,351)
BBED> sum apply
Check value for File 1, Block 351:
current = 0xa100, required = 0xa100
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 351
--//dba 1,375
BBED> set dba 1,375
DBA 0x00400177 (4194679 1,375)
BBED> sum apply
Check value for File 1, Block 375:
current = 0x1923, required = 0x1923
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 375
--//索引不需要恢复.很好理解长度都没有变化。
6.看看恢复情况:
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> @ ddl scott.dept
C300
------------------------------------------------------------------------
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
SYS@book> @ rowid AAAYD0AAIAAMqfEAAF
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
98548 8 3319748 5 0x232A7C4 8,3319748 alter system dump datafile 8 block 3319748 ;
--//没有任何问题.检查看看:
SYS@book> validate index i_obj2;
Index analyzed.
SYS@book> validate index i_obj5;
Index analyzed.
SYS@book> select * from obj$ where name ='IDL_UB1$'
2 @ pr
==============================
OBJ# : 225
DATAOBJ# : 225
OWNER# : 0
NAME : IDL_UB1$
NAMESPACE : 1
SUBNAME :
TYPE# : 2
CTIME : 2013-08-24 11:37:39
MTIME : 2013-08-24 11:37:39
STIME : 2013-08-24 11:37:39
STATUS : 1
REMOTEOWNER :
LINKNAME :
FLAGS : 0
OID$ :
SPARE1 : 0
SPARE2 : 1
SPARE3 : 0
SPARE4 :
SPARE5 :
SPARE6 :
PL/SQL procedure successfully completed.
SYS@book> analyze table IDL_UB1$ validate structure cascade;
Table analyzed.
--//OK.实际上最简单还是直接修改数据字典obj$,我的测试可以关闭数据库重启在修改数据字段应该没有任何问题,bbed恢复纯粹为了练
--//习.
$ dbv file=/mnt/ramdisk/book/system01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Fri Oct 20 11:52:54 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 97280
Total Pages Processed (Data) : 64260
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 13456
Total Pages Failing (Index): 0
Total Pages Processed (Other): 4206
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 15358
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 392188896 (3.392188896)
7.总结：
--//如果rename后修改长度不等长，情况要复杂一点，不过鉴于数据库能正常重启并open，修改数据字典也许更加简单。
--//后续rename后修改长度不等长的情况测试就不作了。