玩转BBED应该是每个Oracle工程师渴望掌握的的技能,我遇见过很多逻辑坏块,RMAN恢复后数据文件头SCN不一致导致库open不起来的问题,如果掌握了BBED,这些问题都不叫问题了,下面的例子是bbed模拟文件头损坏,修复的案例:
#文件头损坏报错
1)模拟文件头损坏
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /u01/app/11g/oradata/testdb/system01.dbf 0
2 /u01/app/11g/oradata/testdb/sysaux01.dbf 0
3 /u01/app/11g/oradata/testdb/undotbs01.dbf 0
4 /u01/app/11g/oradata/testdb/users01.dbf 0
5 /u01/app/11g/oradata/testdb/tyy.dbf 0
BBED> copy file 4 block 5 to file 5 block 1
File: /u01/app/11g/oradata/testdb/tyy.dbf (5)
Block: 1 Offsets: 0 to 511 Dba:0x01400001
------------------------------------------------------------------------
1ea20000 05000001 163f0000 00000104 90c10000 04000000 80800f00 00000000
00000000 00f80000 00000000 00000000 00000000 00000000 00000000 00000000
....
<32 bytes per line>
段头坏了以后,我们想用纯手工方式修复的话肯定不现实,因为里面结构复杂,可以看下里面的结构
BBED> map /v
File: /u01/app/11g/oradata/testdb/users01.dbf (4)
Block: 1 Dba:0x01000001
------------------------------------------------------------
Data File Header
struct kcvfh, 860 bytes @0
struct kcvfhbfh, 20 bytes @0
struct kcvfhhdr, 76 bytes @20
ub4 kcvfhrdb @96
struct kcvfhcrs, 8 bytes @100
ub4 kcvfhcrt @108
ub4 kcvfhrlc @112
struct kcvfhrls, 8 bytes @116
ub4 kcvfhbti @124
struct kcvfhbsc, 8 bytes @128
ub2 kcvfhbth @136
ub2 kcvfhsta @138
struct kcvfhckp, 36 bytes @484
ub4 kcvfhcpc @140
ub4 kcvfhrts @144
ub4 kcvfhccc @148
struct kcvfhbcp, 36 bytes @152
ub4 kcvfhbhz @312
struct kcvfhxcd, 16 bytes @316
sword kcvfhtsn @332
ub2 kcvfhtln @336
text kcvfhtnm[30] @338
ub4 kcvfhrfn @368
struct kcvfhrfs, 8 bytes @372
ub4 kcvfhrft @380
struct kcvfhafs, 8 bytes @384
ub4 kcvfhbbc @392
ub4 kcvfhncb @396
ub4 kcvfhmcb @400
ub4 kcvfhlcb @404
ub4 kcvfhbcs @408
ub2 kcvfhofb @412
ub2 kcvfhnfb @414
ub4 kcvfhprc @416
struct kcvfhprs, 8 bytes @420
struct kcvfhprfs, 8 bytes @428
ub4 kcvfhtrt @444
ub4 tailchk @8188
2)此时,修复思路为copy其他好的datafile块头到损坏的datafile
BBED> copy file 4 block 1 to file 5 block 1
----下面开始修改
2.1 修改 block dba
BBED> p kcvfhbfh
struct kcvfhbfh, 20 bytes @0
ub1 type_kcbh @0 0x0b
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x01000001 --block dba地址 转换完为文件4 block 1,如下图
ub4 bas_kcbh @8 0x00000000
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xb658
ub2 spare3_kcbh @18 0x0000
###16进制转换
##我们应该修改为正确的值,1400001,注意,x86结构都是反的
BBED> modify /x 01004001 offset 4
###修改完成
2.2 修改文件大小
BBED> p kcvfhhdr
struct kcvfhhdr, 76 bytes @20
ub4 kccfhswv @20 0x00000000
ub4 kccfhcvn @24 0x0b200400
ub4 kccfhdbi @28 0xae00ac33
text kccfhdbn[0] @32 T
text kccfhdbn[1] @33 E
text kccfhdbn[2] @34 S
text kccfhdbn[3] @35 T
text kccfhdbn[4] @36 D
text kccfhdbn[5] @37 B
text kccfhdbn[6] @38
text kccfhdbn[7] @39
ub4 kccfhcsq @40 0x000002bf
ub4 kccfhfsz @44 0x00000280
s_blkz kccfhbsz @48 0x00
ub2 kccfhfno @52 0x0004
##我们可以通过ls -ltr去查看文件大小是多少
[oracle@dba ~]$ ls -ltr /u01/app/11g/oradata/testdb/tyy.dbf
-rw-r----- 1 11g oinstall 10493952 Nov 27 09:21 /u01/app/11g/oradata/testdb/tyy.dbf
select (10493952-8192)/8192 from dual;
(10493952-8192)/8192
--------------------
1280
1280转为16进制为500
0x00000500
BBED> modify /x 00050000 offset 44
修改成功
struct kcvfhhdr, 76 bytes @20
ub4 kccfhswv @20 0x00000000
ub4 kccfhcvn @24 0x0b200400
ub4 kccfhdbi @28 0xae00ac33
text kccfhdbn[0] @32 T
text kccfhdbn[1] @33 E
text kccfhdbn[2] @34 S
text kccfhdbn[3] @35 T
text kccfhdbn[4] @36 D
text kccfhdbn[5] @37 B
text kccfhdbn[6] @38
text kccfhdbn[7] @39
ub4 kccfhcsq @40 0x000002bf
ub4 kccfhfsz @44 0x00000500
s_blkz kccfhbsz @48 0x00
ub2 kccfhfno @52 0x0004
2.3 修改datafile文件号
SQL>select file#,name from v$datafile;
FILE# TS# NAME
---------- ---------- -----------------------------------
1 0 /u01/app/11g/oradata/testdb/system01.dbf
2 1 /u01/app/11g/oradata/testdb/sysaux01.dbf
3 2 /u01/app/11g/oradata/testdb/undotbs01.dbf
4 4 /u01/app/11g/oradata/testdb/users01.dbf
5 6 /u01/app/11g/oradata/testdb/tyy.dbf
BBED> modify /x 05 offset 52
BBED> p kcvfhhdr
ub4 kccfhfsz @44 0x00000500
s_blkz kccfhbsz @48 0x00
ub2 kccfhfno @52 0x0005
###
2.4 修改文件创建SCN
SQL> select file#,CREATION_CHANGE# from v$datafile;
FILE# CREATION_CHANGE#
---------- ----------------
1 7
2 1834
3 923328
4 16143
5 964467
964467 转为16进制为EB773
BBED> p kcvfhcrs
struct kcvfhcrs, 8 bytes @100
ub4 kscnbas @100 0x00003f0f
ub2 kscnwrp @104 0x0000
BBED> modify /x 73B70E00 offset 100
BBED> p kcvfhcrs
struct kcvfhcrs, 8 bytes @100
ub4 kscnbas @100 0x000eb773
ub2 kscnwrp @104 0x0000
2.5 修改文件创建时间
BBED> p kcvfh
ub4 kcvfhcrt @108 0x3121c98d
##使用此sql
select file#,to_char(creation_time,'yyyy-mm-dd hh24:mi:ss') creation_time_file,
(to_char(creation_time,'yyyy'）-1988)*12*31*24*3600+
(to_char(creation_time,'mm’)-1)*31*24*3600
+(to_char(creation_time,'dd')-1)*24*3600
+to_char(creation_time,'hh24')*3600
+to_char(creation_time,mi')*60
+to_char(creation_time,'ss') creation_name_scn,to_char((to_char(creation_time,'yyyy’)-1988)*12*31*24*3600+
(to_char(creation_time,'mm')-1)*31*24*3600
+(to_char(creation_time,'dd')-1)*24*3600
+to_char('creation_time,'hh24')3600
+to_char(creation_time,'mi')*60
+to_char(creation_time,'ss'),'xxxxxxxxxxxxxxxx')
from v$datafile order by 1;
FILE# CREATION_TIME_FILE CREATION_NAME_SCN TO_CHAR((TO_CHAR(
---------- ------------------- ----------------- -----------------
1 2013-08-24 11:37:33 824297853 3121c97d
2 2013-08-24 11:37:37 824297857 3121c981
3 2013-08-24 12:07:19 824299639 3121d077
4 2013-08-24 11:37:49 824297869 3121c98d
5 2023-11-24 11:19:19 1153739959 44c4acb7
modify /x b7 offset 108
modify /x ac offset 109
modify /x c4 offset 110
modify /x 44 offset 111
p kcvfh
ub4 kcvfhcrt @108 0x44c4acb7
BBED> sum apply
2.6 修改表空间号
select ts#,name from v$datafile;
TS# NAME
---------- ----------------------------------------------------------------------------------------------------
0 /u01/app/11g/oradata/testdb/system01.dbf
1 /u01/app/11g/oradata/testdb/sysaux01.dbf
2 /u01/app/11g/oradata/testdb/undotbs01.dbf
4 /u01/app/11g/oradata/testdb/users01.dbf
6 /u01/app/11g/oradata/testdb/tyy.dbf
BBED> p kcvfh
sword kcvfhtsn @332 4
BBED> modify /x 06 offset 332
sword kcvfhtsn @332 6
2.7 修改相对文件号
select file#,name from v$datafile;
FILE# NAME
---------- ----------------------------------------------------------------------------------------------------
1 /u01/app/11g/oradata/testdb/system01.dbf
2 /u01/app/11g/oradata/testdb/sysaux01.dbf
3 /u01/app/11g/oradata/testdb/undotbs01.dbf
4 /u01/app/11g/oradata/testdb/users01.dbf
5 /u01/app/11g/oradata/testdb/tyy.dbf
BBED> p kcvfh
ub4 kcvfhrfn @368 0x00000004
BBED> modify /x 05 offset 368
ub4 kcvfhrfn @368 0x00000005
2.8 修改表空间名称
BBED> p kcvfhtnm
text kcvfhtnm[0] @338 U
text kcvfhtnm[1] @339 S
text kcvfhtnm[2] @340 E
text kcvfhtnm[3] @341 R
text kcvfhtnm[4] @342 S
###当前的表空间为users,因为是从file4 copy过来的文件头，我们要对其修改,因为长度不一样,我们要对多余的字符offset置空
select dump('TYY',16) from dual;
DUMP('TYY',16)
----------------------
Typ=96 Len=3: 54,59,59
modify /x 54 offset 338
modify /x 59 offset 339
modify /x 59 offset 340
modify /x 00 offset 341
modify /x 00 offset 342
BBED> p kcvfhtnm
text kcvfhtnm[0] @338 T
text kcvfhtnm[1] @339 Y
text kcvfhtnm[2] @340 Y
text kcvfhtnm[3] @341
2.9 更改checkpoint SCN
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000eb018
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x43b53f2c
ub2 kcvcpthr @496 0x0001
SQL> select file#,to_char(checkpoint_change#,'xxxxxxxxxxxxxxxxxxxxxxx') from v$datafile;
FILE# TO_CHAR(CHECKPOINT_CHANG
---------- ------------------------
1 eb018
2 eb018
3 eb018
4 eb018
5 eb774
BBED> modify /x 74b70e00 offset 484
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000eb774
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x43b53f2c
2.10 更改checkpoint TIME
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000eb774
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x43b53f2c
ub2 kcvcpthr @496 0x0001
--scn模式
select file#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss') CHECKPOINT_TIME_file，
(to_char(CHECKPOINT_TIME,'yyyy’)-1988)*12*31*24*3600+
(to_char(CHECKPOINT_TIME,'mm')-1)*31*24*3600
+(to_char(CHECKPOINT_TIME,'dd')-1)*24*3600
+to_char（CHECKPOINT_TIME,'hh24')*3600
+to_char(CHECKPOINT_TIME,'mi')*60
+to_char（CHECKPOINT_TIME,'ss') CHECKPOINT_TIME_scn
from v$datafile order by 1;
--16进制模式
select file#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss')
CHECKPOINT_TIME_file，
to_char((to_char(CHECKPOINT_TIME,'yyyy')-1988)*12*31*24*3600
+(to_char(‘CHECKPOINT_TIME,'mm')-1)*31*24*3600
+(to_char(CHECKPOINT_TIME,'dd')-1)*24*3600
+to_char(CHECKPOINT_TIME’,'hh24')*3600
+to_char(CHECKPOINT_TIME,'mi')*60
+to_char(CHECKPOINT_TIME,‘ss'),'xxxxxxxxxxxxxxxx') CHECKPOINT_TIME_scn
from v$datafile order by 1;
FILE# CHECKPOINT_TIME_FIL CHECKPOINT_TIME_S
---------- ------------------- -----------------
1 2023-05-04 14:07:40 43b53f2c
2 2023-05-04 14:07:40 43b53f2c
3 2023-05-04 14:07:40 43b53f2c
4 2023-05-04 14:07:40 43b53f2c
5 2023-11-24 11:19:19 44c4acb7
modify /x b7 offset 492
modify /x ac offset 493
modify /x c4 offset 494
modify /x 44 offset 495
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000eb774
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x44c4acb7
BBED> sum apply
##检查块是否可认
###没报错后,尝试启动
###报错旧的控制文件,说明块已被认可,这时我们重建控制文件即可
SQL> alter database backup controlfile to trace;
SQL> shutdown immediate
###重建完控制文件,提示需要介质恢复,这说明,datafile 可以正常使用了,recover完以后完美恢复并启动
2321
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
