ITPub博客

首页 > 数据库 > Oracle > [20201222]KTFB Bitmapped File Space Bitmap的恢复.txt

[20201222]KTFB Bitmapped File Space Bitmap的恢复.txt

原创 Oracle 作者:lfree 时间:2020-12-22 09:37:49 0 删除 编辑

[20201222]KTFB Bitmapped File Space Bitmap的恢复.txt

--//上个星期测试了数据文件OS头的修复,链接:http://blog.itpub.net/267265/viewspace-2743415/
--// http://blog.itpub.net/267265/viewspace-2744047/=>[20201221]KTFB Bitmapped File Space Header的恢复.txt

--//数据文件第1块就是数据文件头,更新检查点就是写这块信息,比如执行alter system checkpoint;命令。
--//第2块为type: 0x1d=KTFB Bitmapped File Space Header
--//第3块为type: 0x1e=KTFB Bitmapped File Space Bitmap
...
--//第127块为type: 0x1e=KTFB Bitmapped File Space Bitmap

--//数据文件头的恢复我以前有链接如下:
--//链接http://blog.itpub.net/267265/viewspace-2128309/=>20161111]数据库文件头的修复.txt
--//里面有一点点问题,就是时间戳要按照链接计算http://blog.itpub.net/267265/viewspace-2222414/

--//本测试仅仅涉及第3块为type: 0x1d=KTFB Bitmapped File Space Bitmap.

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

--//删除前面建立的表空间,建立表空间脚本如下:
CREATE TABLESPACE TEA DATAFILE
  '/mnt/ramdisk/book/tea01.dbf' SIZE 40M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE TABLESPACE SUGAR DATAFILE
  '/mnt/ramdisk/book/sugar01.dbf' SIZE 40M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

2.对比第3块为type: 0x1d=KTFB Bitmapped File Space Bitmap(从0开始计数):

SCOTT@book> select * from v$dbfile order by 1;
     FILE# NAME
---------- ----------------------------------------
         1 /mnt/ramdisk/book/system01.dbf
         2 /mnt/ramdisk/book/sysaux01.dbf
         3 /mnt/ramdisk/book/undotbs01.dbf
         4 /mnt/ramdisk/book/users01.dbf
         5 /mnt/ramdisk/book/example01.dbf
         6 /mnt/ramdisk/book/tea01.dbf
         7 /mnt/ramdisk/book/sugar01.dbf
7 rows selected.

SCOTT@book> set verify off
SCOTT@book> @ bbvi 6 3
BVI_COMMAND
----------------------------------------------------------------------------------------------------
bvi -b 24576 -s 8192 /mnt/ramdisk/book/tea01.dbf
xxd -c16 -g 2 -s 24576 -l 8192 /mnt/ramdisk/book/tea01.dbf
dd if=/mnt/ramdisk/book/tea01.dbf bs=8192 skip=3 count=1 of=6_3.dd conv=notrunc 2>/dev/null
od -j 24576 -N 8192 -t x1 -v /mnt/ramdisk/book/tea01.dbf
hexdump -s 24576 -n 8192 -C -v /mnt/ramdisk/book/tea01.dbf
alter system dump datafile '/mnt/ramdisk/book/tea01.dbf' block 3;

alter session set events 'immediate trace name set_tsn_p1 level 8';
alter session set events 'immediate trace name buffer level 25165827';
9 rows selected.

$ od -j 24576 -N 8192 -t x1  /mnt/ramdisk/book/tea01.dbf
0060000 1e a2 00 00 03 00 80 01 0b 73 42 19 03 00 01 04
0060020 5a 58 00 00 06 00 00 00 80 00 00 00 00 00 00 00
0060040 00 00 00 00 00 f8 00 00 00 00 00 00 00 00 00 00
0060060 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
*
0077760 00 00 00 00 00 00 00 00 00 00 00 00 01 1e 0b 73
0100000

 $ od -j 24576 -N 8192 -t x1  /mnt/ramdisk/book/sugar01.dbf
0060000 1e a2 00 00 03 00 c0 01 20 74 42 19 03 00 01 04
0060020 1b 58 00 00 07 00 00 00 80 00 00 00 00 00 00 00
0060040 00 00 00 00 00 f8 00 00 00 00 00 00 00 00 00 00
0060060 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
*
0077760 00 00 00 00 00 00 00 00 00 00 00 00 01 1e 20 74
0100000

$ diff -Nur <(od -j 24576 -N 8192 -t x1 -w2 /mnt/ramdisk/book/tea01.dbf) <(od -j 24576 -N 8192 -t x1  -w2 /mnt/ramdisk/book/sugar01.dbf)
--- /dev/fd/63  2020-12-22 08:48:13.122258995 +0800
+++ /dev/fd/62  2020-12-22 08:48:13.123258995 +0800
@@ -1,14 +1,14 @@
 0060000 1e a2
 0060002 00 00
 0060004 03 00
-0060006 80 01 --> dba地址。
-0060010 0b 73 --> scn的一部分
+0060006 c0 01
+0060010 20 74
----------------------------------------------------
BBED> set dba 6,3
        DBA             0x01800003 (25165827 6,3)

BBED> set dba 7,3
        DBA             0x01c00003 (29360131 7,3)
----------------------------------------------------        
 0060012 42 19
 0060014 03 00
 0060016 01 04
-0060020 5a 58  -->检查和
+0060020 1b 58
 0060022 00 00
-0060024 06 00  -->文件号6
+0060024 07 00  -->文件号7
 0060026 00 00
 0060030 80 00
 0060032 00 00
@@ -17,5 +17,5 @@
 0060046 00 00
 *
 0077774 01 1e
-0077776 0b 73  ->tailchk
+0077776 20 74  
 0100000

--//你可以发现修复KTFB Bitmapped File Space Bitmap与KTFB Bitmapped File Space Header类似,就是覆盖修改dba地址(偏移在4-7),
--//以及文件号(偏移在20-21).

3.当然我上面的情况,数据文件上没有数据。
--//建立一些看看看:
SCOTT@book> create table tx tablespace tea as select * from all_objects;
Table created.

SCOTT@book> alter system checkpoint;
System altered.

SCOTT@book> alter system dump datafile '/mnt/ramdisk/book/tea01.dbf' block 3;
System altered.

Start dump data block from file /mnt/ramdisk/book/tea01.dbf minblk 3 maxblk 3
 V10 STYLE FILE HEADER:
    Compatibility Vsn = 186647552=0xb200400
    Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
    Activation ID=0=0x0
    Control Seq=315468=0x4d04c, File size=5120=0x1400
    File Number=6, Blksiz=8192, File Type=3 DATA
Dump all the blocks in range:
buffer tsn: 7 rdba: 0x01800003 (6/3)
scn: 0x0003.19427839 seq: 0x01 flg: 0x04 tail: 0x78391e01
frmt: 0x02 chkval: 0x579a type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F628082A800 to 0x00007F628082C800
7F628082A800 0000A21E 01800003 19427839 04010003  [........9xB.....]
7F628082A810 0000579A 00000006 00000080 00000000  [.W..............]
7F628082A820 000000A0 0000F760 00000000 00000000  [....`...........]
7F628082A830 00000000 00000000 FFFFFFFF FFFFFFFF  [................]
7F628082A840 FFFFFFFF FFFFFFFF FFFFFFFF 00000000  [................]
7F628082A850 00000000 00000000 00000000 00000000  [................]
        Repeat 505 times
7F628082C7F0 00000000 00000000 00000000 78391E01  [..............9x]
File Space Bitmap Block:
BitMap Control:
RelFno: 6, BeginBlock: 128, Flag: 0, First: 160, Free: 63328
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFF00000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
...
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
End dump data block from file /mnt/ramdisk/book/tea01.dbf minblk 3 maxblk 3

--//如果位图信息不能反应真实的情况,oracle提供一个包dbms_space_admin.执行如下一般情况下就可以修复。
--//它的修复机制我在链接http://blog.itpub.net/267265/viewspace-2128411/ => 20161114File Space Bitmap Block修复机制2
--//实际上就是填充FF,然后扫描我估计是类似sys.seg$表,确定那些块被使用。
SCOTT@book> select * from sys.seg$ where file#=6
  2  @ prxx
==============================
FILE#                         : 6
BLOCK#                        : 130
TYPE#                         : 5
TS#                           : 7
BLOCKS                        : 1280
EXTENTS                       : 25
INIEXTS                       : 8
MINEXTS                       : 1
MAXEXTS                       : 2147483645
EXTSIZE                       : 128
EXTPCT                        : 0
USER#                         : 83
LISTS                         : 0
GROUPS                        : 0
BITMAPRANGES                  : 2147483645
CACHEHINT                     : 0
SCANHINT                      : 0
HWMINCR                       : 95223
SPARE1                        : 4325633
SPARE2                        :
PL/SQL procedure successfully completed.

--//我再重复测试:
SYS@book> execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('TEA');
PL/SQL procedure successfully completed.

SCOTT@book> alter system dump datafile '/mnt/ramdisk/book/tea01.dbf' block 3;
System altered.

Start dump data block from file /mnt/ramdisk/book/tea01.dbf minblk 3 maxblk 3

*** 2020-12-22 09:06:27.938
 V10 STYLE FILE HEADER:
    Compatibility Vsn = 186647552=0xb200400
    Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
    Activation ID=0=0x0
    Control Seq=315468=0x4d04c, File size=5120=0x1400
    File Number=6, Blksiz=8192, File Type=3 DATA
Dump all the blocks in range:
buffer tsn: 7 rdba: 0x01800003 (6/3)
scn: 0x0003.19427bbf seq: 0x02 flg: 0x04 tail: 0x7bbf1e02
frmt: 0x02 chkval: 0x585a type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F628082A800 to 0x00007F628082C800
7F628082A800 0000A21E 01800003 19427BBF 04020003  [.........{B.....]
7F628082A810 0000585A 00000006 00000080 00000000  [ZX..............]
7F628082A820 0000F800 00000000 00000000 00000000  [................]
7F628082A830 00000000 00000000 FFFFFFFF FFFFFFFF  [................]
7F628082A840 FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF  [................]
        Repeat 494 times
7F628082C730 FFFFFFFF FFFFFFFF 00000000 00000000  [................]
7F628082C740 00000000 00000000 00000000 00000000  [................]
        Repeat 10 times
7F628082C7F0 00000000 00000000 00000000 7BBF1E02  [...............{]
File Space Bitmap Block:
BitMap Control:
RelFno: 6, BeginBlock: 128, Flag: 0, First: 63488, Free: 0
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
...
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
End dump data block from file /mnt/ramdisk/book/tea01.dbf minblk 3 maxblk 3

--//你可以发现就是全部填充FF,实际上最好执行完成后马上执行一个alter system checkpoint ;将脏块写盘。
--//避免意外数据库down后的一些奇怪现象。

SCOTT@book> alter system checkpoint;
System altered.

SCOTT@book> alter system dump datafile '/mnt/ramdisk/book/tea01.dbf' block 3;
System altered.

*** 2020-12-22 09:09:16.719
Start dump data block from file /mnt/ramdisk/book/tea01.dbf minblk 3 maxblk 3
 V10 STYLE FILE HEADER:
    Compatibility Vsn = 186647552=0xb200400
    Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
    Activation ID=0=0x0
    Control Seq=315473=0x4d051, File size=5120=0x1400
    File Number=6, Blksiz=8192, File Type=3 DATA
Dump all the blocks in range:
buffer tsn: 7 rdba: 0x01800003 (6/3)
scn: 0x0003.19427bc1 seq: 0x01 flg: 0x04 tail: 0x7bc11e01
frmt: 0x02 chkval: 0x579a type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F628082A800 to 0x00007F628082C800
7F628082A800 0000A21E 01800003 19427BC1 04010003  [.........{B.....]
7F628082A810 0000579A 00000006 00000080 00000000  [.W..............]
7F628082A820 000000A0 0000F760 00000000 00000000  [....`...........]
7F628082A830 00000000 00000000 FFFFFFFF FFFFFFFF  [................]
7F628082A840 FFFFFFFF FFFFFFFF FFFFFFFF 00000000  [................]
7F628082A850 00000000 00000000 00000000 00000000  [................]
        Repeat 505 times
7F628082C7F0 00000000 00000000 00000000 7BC11E01  [...............{]
File Space Bitmap Block:

--//你可以发现现在修正了位图区错误。

4.上面的修复是非常理想的情况:
--//如果破坏了整个结构,上面的命令就无效了。
$ dd if=/dev/zero of=/mnt/ramdisk/book/tea01.dbf bs=8192 seek=3 count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 3.747e-05 seconds, 219 MB/s

$ od -j 24576 -N 8192 -t x1  /mnt/ramdisk/book/tea01.dbf
0060000 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
*
0100000

SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> select count(*) from tx;
  COUNT(*)
----------
     84804

--//扫描表并不需要读取dba=6,3的位图区。

SCOTT@book> create table ty tablespace tea as select * from all_objects;
create table ty tablespace tea as select * from all_objects
                                                *
ERROR at line 1:
ORA-00600: internal error code, arguments: [KSLFDNG2], [0x08620F490], [0], [0x084E51B78], [0x000000000], [], [], [], [], [], [], []
--//建立新的对象报错。

5.修复测试:
BBED> set offset 0
        OFFSET          0

BBED> copy file 7 block 3 to file 6 block 3
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /mnt/ramdisk/book/tea01.dbf (6)
 Block: 3                                                    Offsets:    0 to   63                                               Dba:0x01800003
------------------------------------------------------------------------------------------------------------------------------------------------
 1ea20000 0300c001 20744219 03000104 1b580000 07000000 80000000 00000000 00000000 00f80000 00000000 00000000 00000000 00000000 00000000 00000000
<64 bytes per line>

BBED> set dba 6,3
        DBA             0x01800003 (25165827 6,3)

BBED> dump /v offset 4 count 4
 File: /mnt/ramdisk/book/tea01.dbf (6)
 Block: 3                                 Offsets:    4 to    7                            Dba:0x01800003
-----------------------------------------------------------------------------------------------------------
 0300c001                                                                l ....
<32 bytes per line>
--// 01800003 反转 03008001

BBED> modify /x 03008001
 File: /mnt/ramdisk/book/tea01.dbf (6)
 Block: 3                                                    Offsets:    4 to    7                                               Dba:0x01800003
------------------------------------------------------------------------------------------------------------------------------------------------
 03008001
<64 bytes per line>

BBED> dump /v offset 20 count 4
 File: /mnt/ramdisk/book/tea01.dbf (6)
 Block: 3                                 Offsets:   20 to   23                            Dba:0x01800003
-----------------------------------------------------------------------------------------------------------
 07000000                                                                l ....
<32 bytes per line>

BBED> modify /x 06
 File: /mnt/ramdisk/book/tea01.dbf (6)
 Block: 3                                                    Offsets:   20 to   23                                               Dba:0x01800003
------------------------------------------------------------------------------------------------------------------------------------------------
 06000000
 <64 bytes per line>


BBED> sum apply
Check value for File 6, Block 3:
current = 0x585a, required = 0x585a

BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/tea01.dbf
BLOCK = 3

DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
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> alter system flush buffer_cache;
System altered.

SCOTT@book> select count(*) from tx;
  COUNT(*)
----------
     84804

SCOTT@book> create table ty tablespace tea as select * from all_objects;
create table ty tablespace tea as select * from all_objects
                                                *
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktssinseg3], [7], [25165954], [], [], [], [], [], [], [], [], []

--//位图信息不对。

SYS@book> execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('TEA');
PL/SQL procedure successfully completed.

SYS@book> alter system checkpoint;
System altered.

SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> create table ty tablespace tea as select * from all_objects;
Table created.

SCOTT@book> select count(*) from ty;
  COUNT(*)
----------
     84805
--//OK,现在修复了。

6.总结:
--//1.建立类似的数据文件属性。使用它覆盖。至于建立的脚本可以通过alert获得。在toad下点击script获得,好像一些属性属于
--//表空间的属性,比如EXTENT MANAGEMENT LOCAL AUTOALLOCATE,BLOCKSIZE 8K,SEGMENT SPACE MANAGEMENT MANUAL,FLASHBACK ON;
--//2.修改2处,dba地址(偏移4-7),以及文件号(偏移20-21).
--//3.再次提醒使用dd命令要小心小心再小心。
--//4.以前学习有点乱,通过这次重复测试,加强理解与记忆。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2744216/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2855
  • 访问量
    6643791