[20161110]Bitmapped File Space Header恢复.txt
--前面的测试可以可以KTFB Bitmapped File Space Bitmap的损坏很好修复。
--如果Bitmapped File Space Header呢？如何修复。通过测试来说明问题。
1.环境：
SYS@book> @ &r/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 SUGAR DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 40M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table t1 tablespace sugar as select rownum id ,lpad('A',32,'A') name from dual connect by level<=1e5;
--建立大小5M的表。
create table t2 tablespace sugar as select rownum id ,lpad('B',32,'B') name from dual connect by level<=2e5;
create table t3 tablespace sugar as select rownum id ,lpad('C',32,'C') name from dual connect by level<=2e5;
alter system checkpoint;
--无论如何数据文件第0块OS，第1块为文件头块，第2块为Bitmapped File Space Header，第3-127块为Bitmapped File Space Bitma。
--注意我不考虑小数据文件的情况，这样位图区可能没有这个大，但是第2块始终为Bitmapped File Space Header.
2.破坏Bitmapped File Space Header恢复。
$ cp /mnt/ramdisk/book/sugar01.dbf /u01/backup/sugar01.dbf_20161110
SYS@book> alter tablespace sugar offline ;
Tablespace altered.
--安全期间，做一个备份：
$ cp /mnt/ramdisk/book/sugar01.dbf /u01/backup/sugar01.dbf_20161110
--正常数据文件第0块OS。第1块是文件头，第2块是位图头块，第3-127块是位图区。
$ bvi -b 16384 -s 8192 /mnt/ramdisk/book/sugar01.dbf
--我的测试全部设置为0。
SCOTT@book> alter tablespace sugar online ;
Tablespace altered.
--居然也可以online。
SCOTT@book> select id from t1 where rownum=1;
ID
----------
1
SCOTT@book> select id from t2 where rownum=1;
ID
----------
1
SCOTT@book> select id from t3 where rownum=1;
ID
----------
1
--访问数据都正常。
3.建立新表看看。
SCOTT@book> create table t4 tablespace sugar as select rownum id ,lpad('D',32,'D') name from dual connect by level<=10;
create table t4 tablespace sugar as select rownum id ,lpad('D',32,'D') name from dual connect by level<=10
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace SUGAR
--报错。
--既然可以online，执行如下看看。
SYS@book> execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR');
BEGIN dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR'); END;
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 2)
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 120
ORA-06512: at line 1
3.最稳妥的方式是exp/expdp导出。
$ exp scott/book TABLESPACES=sugar
Export: Release 11.2.0.4.0 - Production on Thu Nov 10 15:42:24 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export selected tablespaces ...
For tablespace SUGAR ...
. exporting cluster definitions
. exporting table definitions
. . exporting table T1 100000 rows exported
. . exporting table T2 200000 rows exported
. . exporting table T3 200000 rows exported
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.
4.采用替换法：
建立一样的表空间数据文件：
CREATE TABLESPACE TAE DATAFILE
'/mnt/ramdisk/book/tea01.dbf' SIZE 40M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SCOTT@book> alter tablespace sugar offline ;
Tablespace altered.
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /mnt/ramdisk/book/system01.dbf 0
2 /mnt/ramdisk/book/sysaux01.dbf 0
3 /mnt/ramdisk/book/undotbs01.dbf 0
4 /mnt/ramdisk/book/users01.dbf 0
5 /mnt/ramdisk/book/example01.dbf 0
6 /mnt/ramdisk/book/sugar01.dbf 0
7 /mnt/ramdisk/book/tea01.dbf 0
BBED> copy dba 7,2 to dba 6,2
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 2 Offsets: 0 to 63 Dba:0x01800002
------------------------------------------------------------------------------------------------------------------------------------------------
1da20000 0200c001 d05a5f00 00000104 b0680000 07000000 08000000 00140000 09000000 00080000 feff3f00 7e000000 ff130000 00000000 70020000 08200e00
<64 bytes per line>
BBED> set dba 7,2
DBA 0x01c00002 (29360130 7,2)
BBED> set dba 6,2
DBA 0x01800002 (25165826 6,2)
$ bvi -b 16384 -s 8192 /mnt/ramdisk/book/sugar01.dbf
00004000 1D A2 00 00 02 00 C0 01 D0 5A 5F 00 00 00 01 04 .........Z_.....
~~~~~~~~~~~
00004010 B0 68 00 00 07 00 00 00 08 00 00 00 00 14 00 00 .h..............
~~~~~~~~~~~
00004020 09 00 00 00 00 08 00 00 FE FF 3F 00 7E 00 00 00 ..........?.~...
00004030 FF 13 00 00 00 00 00 00 70 02 00 00 08 20 0E 00 ........p.... ..
00004040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00004050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00004060 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
--修改2处,第1处dba地址，第2处数据文件号。
--注意大小头问题，dba修改为02 00 80 10.
--但是这样修改2者是不一致的，不知道这样可行吗？
BBED> set dba 6,2
DBA 0x01800002 (25165826 6,2)
BBED> sum apply dba 6,2
Check value for File 6, Block 2:
current = 0x68f1, required = 0x68f1
SYS@book> alter tablespace sugar online ;
Tablespace altered.
SYS@book> execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR');
PL/SQL procedure successfully completed.
SYS@book> alter system dump datafile 6 block 2;
System altered.
SCOTT@book> create table t4 tablespace sugar as select rownum id ,lpad('D',32,'D') name from dual connect by level<=10;
Table created.
--这样建立表成功了。
SYS@book> alter system dump datafile 6 block 2;
System altered.
buffer tsn: 7 rdba: 0x01800002 (6/2)
scn: 0x0000.005f5dc0 seq: 0x02 flg: 0x04 tail: 0x5dc01d02
frmt: 0x02 chkval: 0x66c7 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F59BC76BA00 to 0x00007F59BC76DA00
7F59BC76BA00 0000A21D 01800002 005F5DC0 04020000 [.........]_.....]
7F59BC76BA10 000066C7 00000006 00000008 00001400 [.f..............]
7F59BC76BA20 00000009 00000800 003FFFFE 0000007E [..........?.~...]
7F59BC76BA30 000013FF 00000191 000000DF 000E2008 [............. ..]
7F59BC76BA40 00000000 00000000 00000000 00000000 [................]
7F59BC76BA50 00000D00 00000008 00000000 00000000 [................]
7F59BC76BA60 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
7F59BC76D9F0 00000000 00000000 00000000 5DC01D02 [...............]]
File Space Header Block:
Header Control:
RelFno: 6, Unit: 8, Size: 5120, Flag: 9
AutoExtend: YES, Increment: 2048, MaxSize: 4194302
Initial Area: 126, Tail: 5119, First: 401, Free: 223
Deallocation scn: 925704.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 7 file#: 6 minblk 2 maxblk 2
SYS@book> alter system dump datafile '/u01/backup/sugar01.dbf_20161110' block 2;
System altered.
Dump all the blocks in range:
buffer tsn: 7 rdba: 0x01800002 (6/2)
scn: 0x0000.005f54b3 seq: 0x02 flg: 0x04 tail: 0x54b31d02
frmt: 0x02 chkval: 0x67f1 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F59BC76BA00 to 0x00007F59BC76DA00
7F59BC76BA00 0000A21D 01800002 005F54B3 04020000 [.........T_.....]
7F59BC76BA10 000067F1 00000006 00000008 00001400 [.g..............]
7F59BC76BA20 00000009 00000800 003FFFFE 0000007E [..........?.~...]
7F59BC76BA30 000013FF 00000190 000000E0 000E2008 [............. ..]
7F59BC76BA40 00000000 00000000 00000000 00000000 [................]
7F59BC76BA50 00000C80 00000080 00000000 00000000 [................]
7F59BC76BA60 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
7F59BC76D9F0 00000000 00000000 00000000 54B31D02 [...............T]
File Space Header Block:
Header Control:
RelFno: 6, Unit: 8, Size: 5120, Flag: 9
AutoExtend: YES, Increment: 2048, MaxSize: 4194302
Initial Area: 126, Tail: 5119, First: 400, Free: 224
Deallocation scn: 925704.0
Header Opcode:
Save: No Pending Op
End dump data block from file /u01/backup/sugar01.dbf_20161110 minblk 2 maxblk 2
--可以看出基本修复正确。first，free不同，是因为我多建立了一个表T4。我删除再对比：
SCOTT@book> drop table t4 purge ;
Table dropped.
SCOTT@book> alter system checkpoint;
System altered.
SCOTT@book> alter system checkpoint;
System altered.
File Space Header Block:
Header Control:
RelFno: 6, Unit: 8, Size: 5120, Flag: 9
AutoExtend: YES, Increment: 2048, MaxSize: 4194302
Initial Area: 126, Tail: 5119, First: 400, Free: 224
Deallocation scn: 6250071.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 7 file#: 6 minblk 2 maxblk 2
--从个性讲不主张这样恢复，选择稳妥的方式很重要。
5.以上存在一个问题，视乎里面记录了
Size: 5120, Flag: 9
AutoExtend: YES, Increment: 2048, MaxSize: 4194302
--不会定义信息记录在这里，如果没有表空间或者数据文件的定义，看看是否可行。
SYS@book> alter tablespace sugar offline ;
Tablespace altered.
$ bvi -b 16384 -s 8192 /mnt/ramdisk/book/sugar01.dbf
--我的测试全部设置为0。
SYS@book> alter tablespace sugar online ;
Tablespace altered.
SYS@book> select dbms_metadata.get_ddl( 'TABLESPACE', 'SUGAR') c100 from dual;
C100
----------------------------------------------------------------------------------------------------
CREATE TABLESPACE "SUGAR" DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 41943040
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
SCOTT@book> select dbms_metadata.get_ddl( 'TABLESPACE', 'TAE') c100 from dual;
C100
----------------------------------------------------------------------------------------------------
CREATE TABLESPACE "TAE" DATAFILE
'/mnt/ramdisk/book/tea01.dbf' SIZE 41943040
AUTOEXTEND ON NEXT 16777216 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
--两者对比，可以发现丢失了AUTOEXTEND ON NEXT 16777216 MAXSIZE 32767M ，估计问题不大。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2128266/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2128266/
扫一扫
3962
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
