[20201222]KTFB Bitmapped File Space Bitmap的恢復.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20201221]KTFB Bitmapped File Space Header的恢復.txtAPPHeader
- 20161110Bitmapped File Space Header恢復APPHeader
- 1104File Space Bitmap Block損壞能修復嗎BloC
- 1108File Space Bitmap Block損壞能修復嗎2BloC
- 1110File Space Bitmap Block損壞能修復嗎3BloC
- [20161111File Space Bitmap Block修復機制BloC
- 20161114File Space Bitmap Block修復機制2BloC
- Microsoft Windows Bitmap File Format SummaryROSWindowsORM
- BMP（Bitmap Image File)解碼流程：
- [20190428]恢復oraInventory.txtAI
- 透過Snapshot Control File 恢復控制檔案
- 通過Snapshot Control File 恢復控制檔案
- Oracle-真實環境的丟失current redo log file的故障恢復Oracle
- rsau/max_diskspace/local - Maximum space for security audit file
- [20170627]使用TSPITR恢復表空間.txt
- 【備份與恢復】控制檔案的恢復（不完全恢復）
- Redo Log File（inactive、active）損壞，處理恢復對策
- [20210930]bbed恢復刪除的資料.txt
- rman恢復--丟失控制檔案的恢復
- 恢復之非歸檔模式下的恢復模式
- System File1 File Header（資料庫System檔案1檔案頭）損壞情況的恢復Header資料庫
- [20210225]控制檔案序列號滿的恢復.txt
- [20171225]沒有備份資料檔案的恢復.txt
- [20141218]誤操作刪除dual表的恢復.txt
- [20130425]使用vim編輯沒有儲存的恢復.txt
- LMT更新file header bitmap不產生redo和undo ?Header
- 【恢復】Redo日誌檔案丟失的恢復
- [20190213]學習bbed-恢復刪除的資料.txt
- 特殊的恢復操作
- 恢復資料,資料塊恢復
- bitlocker如何恢復金鑰 bitlocker恢復金鑰的方法
- rman恢復--丟失聯機重做日誌的恢復
- 恢復之資料庫關閉時的完全恢復資料庫
- 【備份與恢復】恢復受損的口令檔案
- RMAN備份恢復之控制檔案的恢復（三）
- RMAN備份恢復之控制檔案的恢復（二）
- RMAN備份恢復之控制檔案的恢復（一）
- 如何恢復SSD NVME固態硬碟的資料恢復硬碟資料恢復