[20180614][20180614]删除bootstrap$记录无法启动3(补充).txt
--//昨天测试删除bootstrap$记录,导致数据库重启出现问题的修复方法:
--//链接:
http://blog.itpub.net/267265/viewspace-2156144/=>[20180612]删除bootstrap$记录无法启动.txt
http://blog.itpub.net/267265/viewspace-2156149/=>[20180614]删除bootstrap$记录无法启动2.txt
--//有网友问的问题,就是使用bbed修复记录后,修改flag从0x3c=>0x2c后,如果执行verify会报错.
--//从我个人认为如果select正常,这些问题可以忽略.真要修复实际对于不经常使用bbed的用户还是难度的.
--//实际上这个问题可以参考链接:
http://blog.itpub.net/267265/viewspace-2137082/=>[20170412]bbed恢复修改记录(不等长).txt.
--//我喜欢通过例子讲解问题:
1.环境:
SCOTT@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
SCOTT@book> create table t as select * from dept ;
Table created.
SCOTT@book> select rowid,t.* from t ;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAWEIAAEAAAAIjAAA 10 ACCOUNTING NEW YORK
AAAWEIAAEAAAAIjAAB 20 RESEARCH DALLAS
AAAWEIAAEAAAAIjAAC 30 SALES CHICAGO
AAAWEIAAEAAAAIjAAD 40 OPERATIONS BOSTON
SCOTT@book> @ &r/rowid AAAWEIAAEAAAAIjAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90376 4 547 0 0x1000223 4,547 alter system dump datafile 4 block 547 ;
SCOTT@book> delete from t where deptno in (10,30);
2 rows deleted.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint;
System altered.
2.使用bbed修复:
BBED> set dba 4,547
DBA 0x01000223 (16777763 4,547)
BBED> set count 10
COUNT 10
--//注意在使用find前设置count不要太大,否则查询字符0x3c可能跳过.
--//补充一点: 0x3c对应asicc码是'<',0x2c对应asicc码是','.
BBED> map
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Dba:0x01000223
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[4] @142
ub1 freespace[7946] @150
ub1 rowdata[92] @8096
ub4 tailchk @8188
BBED> set offset 8096
OFFSET 8096
BBED> find /x 0x3c curr
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Offsets: 8120 to 8129 Dba:0x01000223
----------------------------------------------------------------
3c020302 c11f0553 414c
<64 bytes per line>
BBED> x /rncc offset 8120
rowdata[24] @8120
-----------
flag@8120: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8121: 0x02
cols@8122: 0
BBED> find
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Offsets: 8162 to 8171 Dba:0x01000223
-----------------------------------------------------------------
3c020302 c10b0a41 4343
<64 bytes per line>
--//可以发现删除记录偏移在8120,8162.执行如下:
assign dba 4,547 offset 8120 = 0x2c
assign dba 4,547 offset 8162 = 0x2c
--//检查记录情况.
BBED> x /4rncc *kdbr[3]
rowdata[0] @8096
----------
flag@8096: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8097: 0x00
cols@8098: 3
col 0[2] @8099: 40
col 1[10] @8102: OPERATIONS
col 2[6] @8113: BOSTON
rowdata[24] @8120
-----------
flag@8120: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8121: 0x02
cols@8122: 3
col 0[2] @8123: 30
col 1[5] @8126: SALES
col 2[7] @8132: CHICAGO
rowdata[44] @8140
-----------
flag@8140: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8141: 0x00
cols@8142: 3
col 0[2] @8143: 20
col 1[8] @8146: RESEARCH
col 2[6] @8155: DALLAS
rowdata[66] @8162
-----------
flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8163: 0x02
cols@8164: 3
col 0[2] @8165: 10
col 1[10] @8168: ACCOUNTING
col 2[8] @8179: NEW YORK
--//检查:
BBED> sum apply dba 4,547
Check value for File 4, Block 547:
current = 0xdc32, required = 0xdc32
BBED> verify dba 4,547
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
Block Checking: DBA = 16777763, Block Type = KTB-managed data block
data header at 0x7ff1765ed27c
kdbchk: the amount of space used is not equal to block size
used=118 fsc=42 avsp=7946 dtl=8064
Block 547 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
--//可以发现verify报错,实际上fsc保存了删除记录时保存的长度.
SCOTT@book> select sum(length(dname)+length(deptno)+length(loc)) n10 from dept where deptno in (10,30);
N10
---------------------
34
--//加上删除时每个记录前的长度指示器占1个字节(3个字段),3个字节.以及cols字段占1个字节(注意计算不包括flag,lock的长度)
--//34+4*2 = 42.也就是fsc记录删除记录时回收的长度.
3.检查:
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> select rowid,t.* from t ;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAWEIAAEAAAAIjAAA 10 ACCOUNTING NEW YORK
AAAWEIAAEAAAAIjAAB 20 RESEARCH DALLAS
AAAWEIAAEAAAAIjAAC 30 SALES CHICAGO
AAAWEIAAEAAAAIjAAD 40 OPERATIONS BOSTON
--//可以发现即使verify错误,问题不大,显示已经正常.不必计较这个问题.
--//如果在该块以后有事务发生,itl槽被覆盖.可以执行多次.
select * from t where rownum=1 for update;
commit ;
alter system flush buffer_cache;
--//错误变成如下:
BBED> verify dba 4,547
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
Block Checking: DBA = 16777763, Block Type = KTB-managed data block
data header at 0x21d8e7c
kdbchk: the amount of space used is not equal to block size
used=118 fsc=0 avsp=7988 dtl=8064
Block 547 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
--//当然显示没有任何问题:
SCOTT@book> select rowid,t.* from t ;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAWEIAAEAAAAIjAAA 10 ACCOUNTING NEW YORK
AAAWEIAAEAAAAIjAAB 20 RESEARCH DALLAS
AAAWEIAAEAAAAIjAAC 30 SALES CHICAGO
AAAWEIAAEAAAAIjAAD 40 OPERATIONS BOSTON
--//如果真要修复,其实是很繁琐的.
--//参考http://blog.itpub.net/267265/viewspace-2137082/=>[20170412]bbed恢复修改记录(不等长).txt.
--//公式: dtl-used+fsc=avsp.
--//8064-118+0 = 7946,修改avsp=7946就ok了.
BBED> p dba 4,547 kdbh
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
sb1 kdbhntab @125 1
sb2 kdbhnrow @126 4
sb2 kdbhfrre @128 -1
sb2 kdbhfsbo @130 26
sb2 kdbhfseo @132 7972
sb2 kdbhavsp @134 7988
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sb2 kdbhtosp @136 7992
BBED> assign dba 4,547 kdbh.kdbhavsp=7946
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
sb2 kdbhavsp @134 7946
BBED> sum apply dba 4,547
Check value for File 4, Block 547:
current = 0x6371, required = 0x6371
BBED> verify dba 4,547
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
Block Checking: DBA = 16777763, Block Type = KTB-managed data block
data header at 0x7f60bb6a527c
kdbchk: space available on commit is incorrect
tosp=7992 fsc=0 stb=0 avsp=7946
Block 547 failed with check code 6111
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
--//avsp+fsc+stb=tops,7946+0+0=7975,fsc=0.要修改kdbh.kdbhtosp=kdbh.kdbhavsp。
BBED> assign dba 4,547 kdbh.kdbhtosp= dba 4,547 kdbh.kdbhavsp
sb2 kdbhtosp @136 7946
BBED> p dba 4,547 kdbh
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
sb1 kdbhntab @125 1
sb2 kdbhnrow @126 4
sb2 kdbhfrre @128 -1
sb2 kdbhfsbo @130 26
sb2 kdbhfseo @132 7972
sb2 kdbhavsp @134 7946
sb2 kdbhtosp @136 7946
BBED> sum apply dba 4,547
Check value for File 4, Block 547:
current = 0x6343, required = 0x6343
BBED> verify dba 4,547
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
--//总之,剩下的修复很烦,自己不经常做也会搞晕.而且在修改前一定要做好备份,特别是生产系统!!
--//实际上就是2步:(在itl槽覆盖后,不覆盖要修改itl槽的对于信息设置fsc=0)
1.dtl-used+fsc=avsp.
used=118 fsc=0 avsp=7988 dtl=8064
8064-118+0 = 7946
assign dba 4,547 kdbh.kdbhavsp=7946
2.assign dba 4,547 kdbh.kdbhtosp= dba 4,547 kdbh.kdbhavsp
