[20201221]KTFB Bitmapped File Space Header的恢复.txt
--//上个星期测试了数据文件OS头的修复，链接：http://blog.itpub.net/267265/viewspace-2743415/
--//数据文件第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/
--//本测试仅仅涉及第2块为type: 0x1d=KTFB Bitmapped File Space Header。
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 MANUAL
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 MANUAL
FLASHBACK ON;
2.对比第2块为type: 0x1d=KTFB Bitmapped File Space Header(从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> @ bbvi 6 2
BVI_COMMAND
----------------------------------------------------------------------------------------------------
bvi -b 16384 -s 8192 /mnt/ramdisk/book/tea01.dbf
xxd -c16 -g 2 -s 16384 -l 8192 /mnt/ramdisk/book/tea01.dbf
dd if=/mnt/ramdisk/book/tea01.dbf bs=8192 skip=2 count=1 of=6_2.dd conv=notrunc 2>/dev/null
od -j 16384 -N 8192 -t x1 -v /mnt/ramdisk/book/tea01.dbf
hexdump -s 16384 -n 8192 -C -v /mnt/ramdisk/book/tea01.dbf
alter system dump datafile '/mnt/ramdisk/book/tea01.dbf' block 2;
alter session set events 'immediate trace name set_tsn_p1 level 8';
alter session set events 'immediate trace name buffer level 25165826';
9 rows selected.
$ od -j 16384 -N 8192 -t x1 /mnt/ramdisk/book/tea01.dbf
0040000 1d a2 00 00 02 00 80 01 fa 00 55 17 03 00 02 04
~~~~~~~~~~~
0040020 59 40 00 00 06 00 00 00 08 00 00 00 00 14 00 00
~~~~~
0040040 09 00 00 00 80 00 00 00 fe ff 3f 00 7e 00 00 00
0040060 ff 13 00 00 00 00 00 00 70 02 00 00 f9 00 55 17
0040100 03 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
0040120 80 00 00 00 08 00 00 00 00 00 00 00 00 00 00 00
0040140 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
*
0057760 00 00 00 00 00 00 00 00 00 00 00 00 02 1d fa 00
0060000
$ od -j 16384 -N 8192 -t x1 /mnt/ramdisk/book/sugar01.dbf
0040000 1d a2 00 00 02 00 c0 01 e7 58 40 19 03 00 01 04
~~~~~~~~~~~
0040020 80 9b 00 00 07 00 00 00 08 00 00 00 00 14 00 00
~~~~~
0040040 09 00 00 00 80 00 00 00 fe ff 3f 00 7e 00 00 00
0040060 ff 13 00 00 00 00 00 00 70 02 00 00 3f d5 96 17
0040100 03 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
0040120 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
*
0057760 00 00 00 00 00 00 00 00 00 00 00 00 01 1d e7 58
0060000
$ diff -Nur <(od -j 16384 -N 8192 -t x1 -w2 /mnt/ramdisk/book/tea01.dbf) <(od -j 16384 -N 8192 -t x1 -w2 /mnt/ramdisk/book/sugar01.dbf)
--- /dev/fd/63 2020-12-21 09:14:21.111743334 +0800
+++ /dev/fd/62 2020-12-21 09:14:21.111743334 +0800
@@ -1,14 +1,14 @@
0040000 1d a2
0040002 00 00
0040004 02 00
-0040006 80 01 =>dba地址的一部分
-0040010 fa 00 =>scn的一部分,可以看下面的转储。
-0040012 55 17 =>scn的一部分,可以看下面的转储。
+0040006 c0 01 =>dba地址的一部分
-------------------------------------------------
BBED> set dba 6,2
DBA 0x01800002 (25165826 6,2)
BBED> set dba 7,2
DBA 0x01c00002 (29360130 7,2)
----------------------------------------------
+0040010 e7 58
+0040012 40 19
0040014 03 00
-0040016 02 04
-0040020 59 40 => 检查和。
+0040016 01 04
+0040020 80 9b
0040022 00 00
-0040024 06 00 =>文件号6
+0040024 07 00 =>文件号7
0040026 00 00
0040030 08 00
0040032 00 00
@@ -27,16 +27,11 @@
*
0040070 70 02
0040072 00 00
-0040074 f9 00 =>scn的一部分,可以看下面的转储。
-0040076 55 17 =>scn的一部分,可以看下面的转储。
+0040074 3f d5
+0040076 96 17
0040100 03 00
0040102 00 00
*
-0040120 80 00
-0040122 00 00
-0040124 08 00
-0040126 00 00
-*
-0057774 02 1d ->tailchk
-0057776 fa 00 ->tailchk
+0057774 01 1d
+0057776 e7 58
0060000
SCOTT@book> alter system dump datafile '/mnt/ramdisk/book/tea01.dbf' block 2;
System altered.
Start dump data block from file /mnt/ramdisk/book/tea01.dbf minblk 2 maxblk 2
V10 STYLE FILE HEADER:
Compatibility Vsn = 186647552=0xb200400
Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
Activation ID=0=0x0
Control Seq=314109=0x4cafd, File size=5120=0x1400
File Number=6, Blksiz=8192, File Type=3 DATA
Dump all the blocks in range:
buffer tsn: 7 rdba: 0x01800002 (6/2)
scn: 0x0003.175500fa seq: 0x02 flg: 0x04 tail: 0x00fa1d02
frmt: 0x02 chkval: 0x4059 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F77DBB8FA00 to 0x00007F77DBB91A00
7F77DBB8FA00 0000A21D 01800002 175500FA 04020003 [..........U.....]
7F77DBB8FA10 00004059 00000006 00000008 00001400 [Y@..............]
7F77DBB8FA20 00000009 00000080 003FFFFE 0000007E [..........?.~...]
7F77DBB8FA30 000013FF 00000000 00000270 175500F9 [........p.....U.]
7F77DBB8FA40 00000003 00000000 00000000 00000000 [................]
7F77DBB8FA50 00000080 00000008 00000000 00000000 [................]
7F77DBB8FA60 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
7F77DBB919F0 00000000 00000000 00000000 00FA1D02 [................]
File Space Header Block:
Header Control:
RelFno: 6, Unit: 8, Size: 5120, Flag: 9
AutoExtend: YES, Increment: 128, MaxSize: 4194302
Initial Area: 126, Tail: 5119, First: 0, Free: 624
Deallocation scn: 391446777.3
Header Opcode:
Save: No Pending Op
End dump data block from file /mnt/ramdisk/book/tea01.dbf minblk 2 maxblk 2
--//对比标准数据块的map
BBED> map /v dba 4,135
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 135 Dba:0x01000087
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
...
ub4 tailchk @8188
--//通过上面的比较可以看出不同之处在dba地址(偏移4-7)在以及文件号偏移(20).
3.通过上面比较可以推断，只要建立属性一样的数据文件替换几处就ok了。
SCOTT@book> column SPARE3 format a20
SCOTT@book> select * from sys.file$;
FILE# STATUS$ BLOCKS TS# RELFILE# MAXEXTEND INC CRSCNWRP CRSCNBAS OWNERINSTANCE SPARE1
----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------- ----------
1 2 64000 0 1 4194302 1280 0 7 4194306
2 2 51200 1 2 4194302 1280 0 1834 8388610
3 2 3200 2 3 4194302 640 0 923328 12582914
4 2 640 4 4 4194302 160 0 16143 16777218
5 2 12800 6 5 4194302 80 0 952916 20971522
6 2 5120 7 6 4194302 128 3 391355879 25165826
7 2 5120 8 7 4194302 128 3 423647208 29360130
7 rows selected.
--//25165826 = 0x1800002
--//29360130 = 0x1c00002
--//spare1保存的是dba地址。
3,391355879 = scn(10): 13276257767 = scn(16): 0x317539de7
3,423647208 = scn(10): 13308549096 = scn(16): 0x3194057e8
--//这里的scn对应的是CREATION_CHANGE#。
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- ------------------------------
1 13308581647 2020-12-21 10:38:16 7 925702 ONLINE 2169 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13308581647 2020-12-21 10:38:16 1834 925702 ONLINE 2158 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13308581647 2020-12-21 10:38:16 923328 925702 ONLINE 2079 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13308581647 2020-12-21 10:38:16 16143 925702 ONLINE 2165 YES /mnt/ramdisk/book/users01.dbf USERS
5 13308581647 2020-12-21 10:38:16 952916 925702 ONLINE 2073 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13308581647 2020-12-21 10:38:16 13276257767 925702 ONLINE 1546 YES /mnt/ramdisk/book/tea01.dbf TEA
7 13308581647 2020-12-21 10:38:16 13308549096 925702 ONLINE 8 YES /mnt/ramdisk/book/sugar01.dbf SUGAR
7 rows selected.
4.开始测试：
--//首先备份一下。
SCOTT@book> alter tablespace tea offline ;
Tablespace altered.
$ cp /mnt/ramdisk/book/tea01.dbf ~/tea01.dbf
SCOTT@book> alter tablespace tea online ;
Tablespace altered.
$ dd if=/dev/zero of=/mnt/ramdisk/book/tea01.dbf bs=8192 seek=2 count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.3777e-05 seconds, 152 MB/s
--//使用dd注意。conv=notrunc，输出文件对于的动作是seek。
SYS@book> shutdown abort
ORACLE instance shut down.
SYS@book> startup
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
Database opened.
--//启动正常。
$ od -j 16384 -N 8192 -t x1 /mnt/ramdisk/book/tea01.dbf
0040000 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
*
0060000
--//还是0的状态。
SYS@book> select count(*) from scott.tx;
COUNT(*)
----------
87038
--//你可以发现即使我清零，读取里面数据正常。
$ dbv file=/mnt/ramdisk/book/tea01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Mon Dec 21 10:04:32 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/tea01.dbf
Page 2 is marked corrupt
Corrupt block relative dba: 0x01800002 (file 6, block 2)
Completely zero block found during dbv:
DBVERIFY - Verification complete
Total Pages Examined : 5120
Total Pages Processed (Data) : 2484
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 128
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2507
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 423651790 (3.423651790)
SYS@book> select * from sys.file$;
FILE# STATUS$ BLOCKS TS# RELFILE# MAXEXTEND INC CRSCNWRP CRSCNBAS OWNERINSTANCE SPARE1
----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ----------
1 2 64000 0 1 4194302 1280 0 7 4194306
2 2 51200 1 2 4194302 1280 0 1834 8388610
3 2 3200 2 3 4194302 640 0 923328 12582914
4 2 640 4 4 4194302 160 0 16143 16777218
5 2 12800 6 5 4194302 80 0 952916 20971522
6 2 5120 7 6 4194302 128 3 391355879 25165826
7 2 5120 8 7 4194302 128 3 423647208 29360130
7 rows selected.
--//我通过toad访问，点击表空间报错：
Select MAX(d.bytes) total_bytes,
nvl(SUM(f.Bytes), 0) free_bytes,
d.file_name,
MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
ROUND(SQRT(MAX(f.BLOCKS)/SUM(f.BLOCKS))*(100/SQRT(SQRT(COUNT(f.BLOCKS)) )), 2) frag_idx
from DBA_FREE_SPACE f , DBA_DATA_FILES d
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and d.tablespace_name = 'TEA'
group by d.file_name
Error occurred: [600] (ORA-00600: internal error code, arguments: [KSLFDNG2], [0x0862242F0], [0], [0x084C98158], [0x000000000], [], [], [], [], [], [], []
)
//点击script，给出脚本如下：
CREATE TABLESPACE TEA DATAFILE
'/mnt/ramdisk/book/tea01.dbf' AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
--//执行修改看看是否可以通过：
SYS@book> alter database datafile '/mnt/ramdisk/book/tea01.dbf' autoextend on next 1m maxsize unlimited;
alter database datafile '/mnt/ramdisk/book/tea01.dbf' autoextend on next 1m maxsize unlimited
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 2)
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
--//不行。
SYS@book> create table ty tablespace tea as select * from dba_objects ;
--//hang住。
SYS@book> insert into scott.tx select * from dba_objects ;
--//hang住。
SYS@book> select count(*) from scott.tx;
COUNT(*)
----------
87038
SYS@book> update scott.tx set owner=upper(owner);
87038 rows updated.
SYS@book> rollback ;
Rollback complete.
--//但是如果修改等导致空间扩张，就hang。
5.尝试恢复：
SYS@book> alter database datafile 6 offline ;
Database altered.
SYS@book> recover datafile 6;
Media recovery complete.
--// dd if=/mnt/ramdisk/book/sugar01.dbf of=/mnt/ramdisk/book/tea01.dbf bs=8192 skip=2 seek=2 count=1 conv=notrun
--//注意dd参数 skip=2 seek=2 count=1 conv=notrun。
--//这里使用bbed要安全可靠一些。
BBED> set offset 0
OFFSET 0
--//注意一定要先设置offset 0，避免从某个偏移开始copy。
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/tea01.dbf (6)
Block: 2 Offsets: 0 to 63 Dba:0x01800002
------------------------------------------------------------------------------------------------------------------------------------------------
1da20000 0200c001 e7584019 03000104 809b0000 07000000 08000000 00140000 09000000 80000000 feff3f00 7e000000 ff130000 00000000 70020000 3fd59617
<64 bytes per line>
BBED> set dba 6,2
DBA 0x01800002 (25165826 6,2)
BBED> set offset 4
OFFSET 4
BBED> dump /v count 4
File: /mnt/ramdisk/book/tea01.dbf (6)
Block: 2 Offsets: 4 to 7 Dba:0x01800002
-----------------------------------------------------------------------------------------------------------
0200c001 l ....
<32 bytes per line>
--//修改注意大小头问题。 01800002 反转 02008001
BBED> modify /x 02008001
File: /mnt/ramdisk/book/tea01.dbf (6)
Block: 2 Offsets: 4 to 7 Dba:0x01800002
------------------------------------------------------------------------------------------------------------------------------------------------
02008001
<64 bytes per line>
BBED> set offset 20
OFFSET 20
BBED> dump /v count 4
File: /mnt/ramdisk/book/tea01.dbf (6)
Block: 2 Offsets: 20 to 23 Dba:0x01800002
-----------------------------------------------------------------------------------------------------------
07000000 l ....
<32 bytes per line>
BBED> modify /x 06
File: /mnt/ramdisk/book/tea01.dbf (6)
Block: 2 Offsets: 20 to 23 Dba:0x01800002
------------------------------------------------------------------------------------------------------------------------------------------------
06000000
<64 bytes per line>
BBED> dump /v count 4
File: /mnt/ramdisk/book/tea01.dbf (6)
Block: 2 Offsets: 20 to 23 Dba:0x01800002
-----------------------------------------------------------------------------------------------------------
06000000 l ....
<32 bytes per line>
BBED> sum apply
Check value for File 6, Block 2:
current = 0x9bc1, required = 0x9bc1
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/tea01.dbf
BLOCK = 2
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
SYS@book> alter system flush buffer_cache;
System altered.
SYS@book> alter database datafile 6 online ;
Database altered.
SYS@book> update scott.tx set object_name=lpad(object_name,128,' ');
87038 rows updated.
SYS@book> rollback ;
Rollback complete.
--//OK，现在扩张没有问题了。
SYS@book> create table ty tablespace tea as select * from dba_objects ;
Table created.
SYS@book> select count(*) from ty;
COUNT(*)
----------
87039
Select MAX(d.bytes) total_bytes,
nvl(SUM(f.Bytes), 0) free_bytes,
d.file_name,
MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
ROUND(SQRT(MAX(f.BLOCKS)/SUM(f.BLOCKS))*(100/SQRT(SQRT(COUNT(f.BLOCKS)) )), 2) frag_idx
from DBA_FREE_SPACE f , DBA_DATA_FILES d
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and d.tablespace_name = 'TEA'
group by d.file_name;
TOTAL_BYTES FREE_BYTES FILE_NAME USED_BYTES FRAG_IDX
----------- ---------- ------------------------------------------------------------ ---------- ----------
41943040 8388608 /mnt/ramdisk/book/tea01.dbf 33554432 100
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命令要小心再小心。我再次犯错。
--//在恢复使用dd时少写了参数seek。导致覆盖了tea01.dbf的第0块。最后我选择改用bbed要安全可靠一些。
--// dd if=/mnt/ramdisk/book/sugar01.dbf of=/mnt/ramdisk/book/tea01.dbf bs=8192 skip=2 seek=2 count=1 conv=notrun
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2744047/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
