20161110Bitmapped File Space Header恢復
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20201221]KTFB Bitmapped File Space Header的恢復.txtAPPHeader
- System File1 File Header（資料庫System檔案1檔案頭）損壞情況的恢復Header資料庫
- MYSQL Space id in fsp header,but in the page header錯誤MySqlHeader
- [ERROR]Space id in fsp header but in the page header一列ErrorHeader
- [20201222]KTFB Bitmapped File Space Bitmap的恢復.txtAPP
- oracle10g 資料檔案頭data file header(file header)OracleHeader
- The Ultimate (DLL) Header File (轉)Header
- 1128PAGETABLE SEGMENT HEADER損壞恢復Header
- 1104File Space Bitmap Block損壞能修復嗎BloC
- zt_Archivelog 模式下，datafile header損壞，如何恢復？Hive模式Header
- 1108File Space Bitmap Block損壞能修復嗎2BloC
- 1110File Space Bitmap Block損壞能修復嗎3BloC
- Oracle RAC ASM disk header 備份 恢復 與 重建 示例說明OracleASMHeader
- [20161111File Space Bitmap Block修復機制BloC
- 20161114File Space Bitmap Block修復機制2BloC
- PE教程3: File Header （檔案頭）Header
- 20161125PAGETABLE SEGMENT HEADER損壞恢復Header
- [20130530]OS block header破壞以及恢復.txtBloCHeader
- 透過Snapshot Control File 恢復控制檔案
- 通過Snapshot Control File 恢復控制檔案
- rsau/max_diskspace/local - Maximum space for security audit file
- Redo Log File（inactive、active）損壞，處理恢復對策
- ORA-01251: Unknown File Header Version read for file number 7Header
- LMT更新file header bitmap不產生redo和undo ?Header
- gulp使用gulp-file-include將header/footer引入頁面Header
- 恢復資料,資料塊恢復
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- 【備份與恢復】控制檔案的恢復（不完全恢復）
- v$datafile&v$datafile_header,oracle 備份恢復時，查詢資料檔案資訊HeaderOracle
- 訂單尚未完全出貨,但order header的 workflow被puges後的恢復處理Header
- Oracle-真實環境的丟失current redo log file的故障恢復Oracle
- rman恢復時ORA-01152: file 1 was not restored from a sufficiently old backupREST
- 資料恢復：AMDU資料抽取恢復資料恢復
- postgreSQL 恢復至故障點 精準恢復SQL
- 使用恢復建議恢復資料庫資料庫
- 【備份恢復】資料恢復指導資料恢復
- Oracle例項恢復和介質恢復Oracle
- rman恢復方案和oracle異機恢復Oracle