有备份时,一定要以使用备份恢复为首选,下面仅在11.2.0.4版本测试验证,其他版本未做严格测试验证,希望所有库都永不宕机,远离bbed等特殊恢复工具,保平安。
文章结构:
- 修改前基础知识结构
- 段头块结构说明
- 字节序endian说明
- ORA-01190报错以及使用bbed修复完整过程
1. 修改前基础知识
1.1 段头块结构
bbed工具不支持查询Segment Header的物理结构,不过可以对照block dump与bbed二进制大致判断结构。
# 下面segment header来自于网络,经过11.2.0.4测试验证:
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x23
Block type
ub1 frmt_kcbh @1 0xa2
Block format a2=oracle 112
ub1 spare1_kcbh @2 0x00 # not used
ub1 spare2_kcbh @3 0x00 # not used
ub4 rdba_kcbh @4 0x01400a0a # RDBA-Relative Data Block Address
ub4 bas_kcbh @8 0x0029cbc7 # SCN Base
ub2 wrp_kcbh @12 0x0000 # SCN Wrap
ub1 seq_kcbh @14 0x02 # sequence number
ub1 flg_kcbh @15 0x04
# Flag:
# 0x01 New Block
# 0x02 Delayed Loging Change advanced SCN/seq
# 0x04 Check value saved-block XOR's to zero
# 0x08 Temporary block
ub2 chkval_kcbh @16 0xe395 # Optional block checksum(if DB_BLOCK_CHECKSUM=TRUE)
ub2 spare3_kcbh @18 0x0000 # not used
struct ech(Extent Control Header) @36 # Extent Control Header
# High High water marker
ub4 extents_ech @36 0x00000012 # extents
ub4 blocks_ech @40 0x00000180 # blocks
ub4 offset_ech @44 0x00000a9c # offset
ub4 @48 # ext#
ub4 blk_ech @52 0x00000080 # blk#
ub4 extsize_ech @56 0x00000080 # ext size
ub4 hw_ech @60 0x01401f80 # Highwater dba
# Low High water marker
ub4 lhwmext_ech @92 0x00000010 # ext# of Low HighWater Mark
ub4 lhwmblk_ech @96 0x00000010 # blk# of Low HighWater Mark
ub4 lhwmextsize_ech @100 0x00000080 # ext size of Low HighWater Mark
ub4 lhwm_ech @104 0x01401e90 # Low HighWater Mark dba
ub4 lhwmbmb1_ech @124 0x01401e80 # Level 1 BMB for Low HWM block
ub4 hhwmbmb1_ech @128 0x01401f01 # Level 1 BMB for High HWM block
ub4 segtype_ech @208 0x00000001 # Segment Type
ub4 blksz_ech @212 0x00002000 # blksz
ub4 fbsz_echo @216 0x00000000 # fbsz
ub4 l2asoffset_ech @220 0x00001434 # L2 Array start offset
ub4 firstbmb3_echo @224 0x00000000 # First Level 3 BMB
ub4 l2hfinsert_ech @228 0x01400a09 # L2 Hint for inserts
ub4 nl2_echo @232 0x00000001 # nl2
ub4 lastBMB1_echo @236 0x01401f01 # Last Level 1 BMB
ub4 lastBMB2_echo @240 0x01400a09 # Last Level 2 BMB
ub4 lastBMB3_echo @244 0x00000000 # Last Level 3 BMB
ub4 mhnext_echo @260 0x00000000 # next of Map Header
ub4 mhext_echo @264 0x00000012 # extents of Map Header
ub4 mhobj_ech @272 0x00015781 # obj# of Map Header
ub4 mhflag_ech @276 0x10000000 # flag of Map Header
struct em(Extent Map) @280 # Extent Map
ub4 dba_em[0] @280 0x01400a08 ub4 len_em[0] @284 0x00000008
ub4 dba_em[1] @288 0x01400a10 ub4 len_em[1] @292 0x00000008
...
struct am(Auxillary Map) @2736 # Auxillary Map
ub4 fdba_am[0] @2736 0x01400a08 ub4 datadba_am[0] @2740 0x01400a0b
ub4 fdba_am[1] @2744 0x01400a08 ub4 datadba_am[1] @2748 0x01400a10
...
ub4 slbbdba1_am @5192 0x01400a09 # Second Level Bitmap block DBAs
1.2 endian字节序
不同OS平台具有不同字节序,可以查询v$transportable_platform查看具体OS是big或little。
对于little endian,在存储时,低位在前,高位在后,例如,下面为Linux中:
# 可以看到,kcvfhccc值为0x0000001a,由于为little endian,所以存储时,低位在前,存储为1a000000。
BBED> p file 18 block 1 kcvfhccc
ub4 kcvfhccc @148 0x0000001a
BBED> d /v file 18 block 1 offset 148
File: /veridata/rman/test_resize.dbf (18)
Block: 1 Offsets: 148 to 157 Dba:0x04800001
-------------------------------------------------------
1a000000 00000000 0000 l ..........
2. ORA-01190使用bbed强制online数据文件
有了上面的基础知识,当我们有数据文件offline,或者创建控制文件时,遗漏了某个数据文件,最后resetlogs打开数据库后,无法online数据文件,可以通过bbed修改文件头欺骗Oracle,达到online数据文件目的
2.1 online数据文件报错
# 本次报错主要为下面操作
1.重建控制文件恢复数据库,在CREATE CONTROLFILE语句中遗漏了下面表空间数据文件。
2.遗漏之后,文件变成unnamed文件,通过ALTER DATABASE RENAME将文件可以修正,由于进行了ALTER DATABASE OPEN RESETLOGS,导致无法online数据文件。
# 报错
SYS@honor1 > alter tablespace test_resize online;
alter tablespace test_resize online
*
ERROR at line 1:
ORA-01190: control file or data file 18 is from before the last RESETLOGS
ORA-01110: data file 18: '+DATADG/honor/datafile/test_resize.270.1088865031'
2.2 需要修改的信息
# 使用bbed来修改文件头信息
# 有4个属性来判断datafile是否和其他的datafile 一致,如果都一致,可以正常online
kcvfhsta (at offset 138) - fuzzy status
kcvfhcpc (at offset 140) - Checkpoint count.
kcvfhccc (at offset 148) - Unknown, but is always 1 less than thecheckpoint point count.
kscnbas (at offset 484) - SCN of last change to the datafile.
kcvcptim (at offset 492) -Time of the last change to the datafile.
# 还有2个跟resetlog相关的属性:
kcvfhrlc - resetlogs count
kcvfhrls - resetlogs scn
2.3 检查状态以及将ASM文件拷贝到文件系统
# 1. 查看数据文件状态信息
SYS@honor1 > select file#,status,checkpoint_change#,CHECKPOINT_COUNT,CHECKPOINT_TIME,RESETLOGS_CHANGE#,RESETLOGS_TIME,fuzzy from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE# CHECKPOINT_COUNT CHECKPOINT_TIME RESETLOGS_CHANGE# RESETLOGS_TIME FUZ
----- ---------- ------------------ ---------------- ------------------- ----------------- ------------------- ---
1 ONLINE 21484872432 2867 2021-12-01 16:56:46 21484852712 2021-12-01 13:38:27 NO