[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
- [20190428]恢復oraInventory.txtAI
- [20180627]truncate table的另類恢復.txt
- BMP（Bitmap Image File)解碼流程：
- [20190130]刪除tab$記錄的恢復.txt
- [20181212]truncate的另類恢復5.txt
- [20210803]刪除user$的恢復準備.txt
- [20210930]bbed恢復刪除的資料.txt
- [20201111]CURSOR_SPACE_FOR_TIME.txt
- [20180630]truncate table的另類恢復2.txt
- [20210225]控制檔案序列號滿的恢復.txt
- [20181031]truncate IDL_UB1$恢復.txt
- [20191213]不完全恢復疑問.txt
- [20190225]刪除tab$記錄的恢復5.txt
- [20190130]刪除tab$記錄的恢復2.txt
- [20190212]刪除tab$記錄的恢復3.txt
- [20231020]rename IDL_UB1$後使用bbed的恢復.txt
- [20190213]學習bbed-恢復刪除的資料.txt
- [20190111]執行計劃bitmap and.txt
- [20190718]12c rman新特性 表恢復.txt
- Oracle-真實環境的丟失current redo log file的故障恢復Oracle
- [20200309]資料庫異常關閉恢復的終點.txt資料庫
- [20220909]bbed關於刪除記錄恢復的問題.txt
- [20190124]bbed恢復資料遇到延遲塊清除的問題.txt
- [20231019]rename IDL_UB1$的恢復測試前準備.txt
- Author: ** not defined in users.txt file
- [20190918]shrink space與ORA-08102錯誤.txt
- [20200327]ORA-46267 Insufficient space in 'USERS' tablespace.txt
- [20181129]大量的control file sequential read.txt
- [20231103]rename IDL_UB1$後使用bbed的恢復的後遺症.txt
- [20181130]control file sequential read.txt
- [20190531]ORA-600 kokasgi1故障模擬與恢復.txt
- [20210401]使用bbed讀取資料塊恢復注意6.txt
- [20201113]測試CURSOR_SPACE_FOR_TIME(10g).txt
- [20190125]bbed恢復資料遇到延遲塊清除的問題3.txt
- [20190124]bbed恢復資料遇到延遲塊清除的問題2.txt
- Checkpoint log:invalid bitmap page錯誤修復
- zjs-my-diary-20201222JS