[20121011]file header reset--bbed学习.txt
如果数据库数据文件损坏,并且archivelog损坏,这样无法完全恢复,如果仅仅某个数据文件的scn与其他文件不同步,导致该数据文件无法mount.
正常可以像odu之类的工具恢复.但是在实际上如果修改数据文件的scn保持同步,这样数据库可以正常打开,选择常规的方法imp/exp以及expdp/impdp
方式恢复,这样虽然丢失一部分数据,至少一定程度减少损失.
自己做一个试验测试看看:
1.首先做冷备份test01.dbf数据文件:
$ cd /u01/app/oracle11g/oradata/test/
$ cp test01.dbf /data/testtest/
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 1728054120 bytes
Database Buffers 402653184 bytes
Redo Buffers 4964352 bytes
Database mounted.
Database opened.
SQL> select rowid ,dbms_rowid.rowid_relative_fno(rowid) rfile#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,dbms_rowid.ROWID_ROW_NUMBER(rowid) row#,dept1.* from scott.dept1;
ROWID RFILE# BLOCK# ROW# DEPTNO DNAME LOC
------------------ ---------- ---------- ---------- ---------- -------------- -------------
AAAcC1AAIAAAACDAAA 8 131 0 50 TEST AAAA
AAAcC1AAIAAAACDAAB 8 131 1 10 ACCOUNTING NEW YORK
AAAcC1AAIAAAACDAAC 8 131 2 20 RESEARCH DALLAS
AAAcC1AAIAAAACDAAD 8 131 3 30 SALES CHICAGO
AAAcC1AAIAAAACDAAE 8 131 4 40 OPERATIONS BOSTON
SQL> insert into scott.dept1 values(60,'AAAA','BBBB');
1 row created.
SQL> commit ;
Commit complete.
SQL> alter system archive log current ;
System altered.
SQL> alter system archive log current ;
System altered.
SQL> alter system archive log current ;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--移动archivelog到另外的目录:
$ mv /u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_10_11 /data/testtest/
$ cd /u01/app/oracle11g/oradata/test
$ mv test01.dbf test01.dbf.ORG
$ cp /data/testtest/test01.dbf .
--这样test01.dbf数据文件与其他数据文件的scn不一致.看看如何恢复.
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 1728054120 bytes
Database Buffers 402653184 bytes
Redo Buffers 4964352 bytes
Database mounted.
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/u01/app/oracle11g/oradata/test/test01.dbf'
2.检查各个scn的状态:
在关闭数据库前看看各个scn的状态:
SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#
FROM v$database
UNION
SELECT 'file in controlfile', NAME, checkpoint_change#
FROM v$datafile
WHERE NAME LIKE '%test01%'
UNION
SELECT 'file header', NAME, checkpoint_change#
FROM v$datafile_header
WHERE NAME LIKE '%test01%';
SCN location NAME CHECKPOINT_CHANGE#
------------------- ------------------------------------------------------------ ------------------
controlfile SYSTEM checkpoint 3010130713
file header /u01/app/oracle11g/oradata/test/test01.dbf 3010121681
file in controlfile /u01/app/oracle11g/oradata/test/test01.dbf 3010130713
SQL> select * from v$recover_file ;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- -------------------
8 ONLINE ONLINE 3010121681 2012-10-10 18:01:12
--可以发现file header记录的是3010121681,file in controlfile记录的是3010130713.
--只要恢复一致,就可以骗过oracle,正常加载数据文件.
3.bbed修复操作:
摘抄一段:
The file header is stored in the first block cf the data file. We can use bbed tc examine the blcck and show
the block map. The header blocks contain a single data structure — kcvfh. Oracle considers four attributes
of this data structure when determining if a data file is sync with the other data files of the database:
kscnbas (at offset 484) -- SCN of last change to the datafile.
kcvcptim (at offset 492) -- Time of the last change to the datafile.
kcvfhcpc (at offset 140) -- Checkpoint count.
kcvfhccc (at offset 148) -- Unknown, but is always l less than the checkpoint point count.
The first two attributes are stored in the kcvfhckp sub-structure. The second two are attributes in their own
right. We can use the print command to display them all for the file that requires recovery:
=====================
BBED> set file 1
FILE# 1
BBED> map /v
File: /u01/app/oracle11g/oradata/test/system01.dbf (1)
Block: 1 Dba:0x00400001
------------------------------------------------------------
Data File Header
struct kcvfh, 860 bytes @0
struct kcvfhbfh, 20 bytes @0
struct kcvfhhdr, 76 bytes @20
ub4 kcvfhrdb @96
struct kcvfhcrs, 8 bytes @100
ub4 kcvfhcrt @108
ub4 kcvfhrlc @112
struct kcvfhrls, 8 bytes @116
ub4 kcvfhbti @124
struct kcvfhbsc, 8 bytes @128
ub2 kcvfhbth @136
ub2 kcvfhsta @138
struct kcvfhckp, 36 bytes @484
ub4 kcvfhcpc @140
ub4 kcvfhrts @144
ub4 kcvfhccc @148
struct kcvfhbcp, 36 bytes @152
ub4 kcvfhbhz @312
struct kcvfhxcd, 16 bytes @316
sword kcvfhtsn @332
ub2 kcvfhtln @336
text kcvfhtnm[30] @338
ub4 kcvfhrfn @368
struct kcvfhrfs, 8 bytes @372
ub4 kcvfhrft @380
struct kcvfhafs, 8 bytes @384
ub4 kcvfhbbc @392
ub4 kcvfhncb @396
ub4 kcvfhmcb @400
ub4 kcvfhlcb @404
ub4 kcvfhbcs @408
ub2 kcvfhofb @412
ub2 kcvfhnfb @414
ub4 kcvfhprc @416
struct kcvfhprs, 8 bytes @420
struct kcvfhprfs, 8 bytes @428
ub4 kcvfhtrt @444
ub4 tailchk @8188
-------------------------------------------------------------------
BBED> p kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0xb36af319
BBED> p kcvfhckp.kcvcptim
ub4 kcvcptim @492 0x2f782495
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000757
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000756
--注意位置与文档讲的不同,以实际为主.
BBED> set dba 8,1
DBA 0x02000001 (33554433 8,1)
BBED> show
FILE# 8
BLOCK# 1
OFFSET 492
DBA 0x02000001 (33554433 8,1)
FILENAME /u01/app/oracle11g/oradata/test/test01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle11g/bbed/filelist.txt
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 210
COUNT 8192
LOGFILE log.bbd
SPOOL No
BBED> p kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0xb36acfd1
BBED> p kcvfhckp.kcvcptim
ub4 kcvcptim @492 0x2f76fd68
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000326
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000325
BBED> host bc
bc 1.06
Copyright 1991-1994, 1997, 1998, 2000 Free Software Foundation, Inc.
This is free software with ABSOLUTELY NO WARRANTY.
For details type `warranty'.
ibase=16
b36af319
0
B36AF319
3010130713
B36ACFD1
3010121681
quit
--可以发现与视图看到的基本一致.
BBED> set dba 1,1
DBA 0x00400001 (4194305 1,1)
BBED> dump /v offset 484
File: /u01/app/oracle11g/oradata/test/system01.dbf (1)
Block: 1 Offsets: 484 to 487 Dba:0x00400001
---------------------------------------------------------------------------------------------------------------------------------------------------------------
19f36ab3 l ..j.
BBED> dump /v offset 492
File: /u01/app/oracle11g/oradata/test/system01.dbf (1)
Block: 1 Offsets: 492 to 495 Dba:0x00400001
---------------------------------------------------------------------------------------------------------------------------------------------------------------
9524782f l .$x/
BBED> dump /v offset 140
File: /u01/app/oracle11g/oradata/test/system01.dbf (1)
Block: 1 Offsets: 140 to 143 Dba:0x00400001
---------------------------------------------------------------------------------------------------------------------------------------------------------------
57070000 l W...
BBED> dump /v offset 148
File: /u01/app/oracle11g/oradata/test/system01.dbf (1)
Block: 1 Offsets: 148 to 151 Dba:0x00400001
---------------------------------------------------------------------------------------------------------------------------------------------------------------
56070000 l V...
--因为intel cpu采用little在前,显示正确的再修改方便一些.
BBED> set dba 8,1
DBA 0x02000001 (33554433 8,1)
BBED> modify /x 19f36ab3 offset 484
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 1 Offsets: 484 to 487 Dba:0x02000001
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
19f36ab3
BBED> modify /x 9524782f offset 492
BBED-00209: invalid number (9524782f)
--奇怪!不行,有时候可以,不知道为什么?分成2段修改
BBED> modify /x 9524 offset 492
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 1 Offsets: 492 to 495 Dba:0x02000001
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9524762f
BBED> set offset +2
OFFSET 494
BBED> modify /x 782f
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 1 Offsets: 494 to 497 Dba:0x02000001
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
782f0100
BBED> modify /x 5707 offset 140
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 1 Offsets: 140 to 143 Dba:0x02000001
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
57070000
BBED> modify /x 5607 offset 148
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 1 Offsets: 148 to 151 Dba:0x02000001
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
56070000
--OK,修改完成!现在一致了.
BBED> sum
Check value for File 8, Block 1:
current = 0x9caf, required = 0x7996
BBED> sum apply
Check value for File 8, Block 1:
current = 0x7996, required = 0x7996
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle11g/oradata/test/test01.dbf
BLOCK = 1
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
--正常!
4.启动数据库检查:
--不行!
SQL> startup open read only ;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 1728054120 bytes
Database Buffers 402653184 bytes
Redo Buffers 4964352 bytes
Database mounted.
ORA-01122: database file 8 failed verification check
ORA-01110: data file 8: '/u01/app/oracle11g/oradata/test/test01.dbf'
ORA-01207: file is more recent than control file - old control file
--不知道有遗漏了什么,做一个对比看看,跳过第1块OS header.
$ dd if=test01.dbf.ORG count=1 bs=8192 skip=1 | xxd -c 16 > /tmp/aa.good
$ dd if=test01.dbf count=1 bs=8192 skip=1 | xxd -c 16 > /tmp/aa.bad
$ diff -Nur aa.bad aa.good
--- aa.bad 2012-10-11 15:28:37.000000000 +0800
+++ aa.good 2012-10-11 15:28:24.000000000 +0800
@@ -1,13 +1,13 @@
0000000: 0ba2 0000 0100 0002 0000 0000 0000 0104 ................
-0000010: 9679 0000 0000 0000 0000 200b d258 7f7b .y........ .襒.{
-0000020: 5445 5354 0000 0000 3fb4 0200 0020 0000 TEST....?.... ..
+0000010: 432e 0000 0000 0000 0000 200b d258 7f7b C......... .襒.{
+0000020: 5445 5354 0000 0000 d1b4 0200 0020 0000 TEST....?... ..
====> 位置0x10,0x11(十进制16,17) 检查和,先可以不管它. 0x28,0x29(十进制40,41) 不同
====> 查询对应 ub4 kccfhcsq @40 0x0002b43f
0000030: 0020 0000 0800 0300 0000 0000 0000 0000 . ..............
0000040: 5441 4732 3031 3230 3931 3654 3039 3335 TAG20120916T0935
0000050: 3338 0000 0000 0000 0000 0000 0000 0000 38..............
0000060: 0000 0000 d4f2 c800 0000 0000 2081 e82e ....则?.... .?
0000070: a7de 242f ad36 f100 0000 0000 0000 0000 .?/.6?........
-0000080: 0000 0000 0000 0000 0000 0000 5707 0000 ............W...
-0000090: 2cdf 762f 5607 0000 0000 0000 0000 0000 ,遶/V...........
+0000080: 0000 0000 0000 0000 0000 0000 2c03 0000 ............,...
+0000090: 2cdf 762f 2b03 0000 0000 0000 0000 0000 ,遶/+...........
====> 位置0x8b,0x8c(140). 0x94,0x95(148)
====> ub4 kcvfhcpc @140 0x00000757
====> ub4 kcvfhccc @148 0x00000756
00000a0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
00000b0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
00000c0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
@@ -29,7 +29,7 @@
00001c0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
00001d0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
00001e0: 0000 0000 19f3 6ab3 0000 0000 9524 782f .....骿......$x/
-00001f0: 0100 0000 9001 0000 041c 0000 1000 21d7 ..............!.
+00001f0: 0100 0000 9301 0000 3a4b 0000 1000 21d7 ........:K....!.
====> 位置0x1f4,0x1f5 ,0x1f8,0x1f9
====> 十进制500,501,504,505 (map命令也没有说明)
0000200: 0200 0000 0000 0000 0000 0000 0000 0000 ................
0000210: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000220: 0000 0000 0000 0000 0000 0000 0000 0000 ................
--经过多次测试,发现
ub4 kcvfhcpc @140,ub4 kcvfhccc @148 两处修改,原来如下:
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000326
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000325
修改为这样就OK了.
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x0000032c
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x0000032b
--这个增长并不是各个一样的,如果数据文件建立出现有先后,数量应该不会各个相等的.
BBED> set file 7
FILE# 7
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000629
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000628
0x32C-0x326=6
--难道要1点1点增加来测试.先暂时学到这里!!累!!
--控制文件有记录吗?
