[20121011]file header reset--bbed学习.txt

[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点增加来测试.先暂时学到这里!!累!!
--控制文件有记录吗?


上一篇:《HTML5开发手册》——2.4 初学者“菜谱”:使用address元素提供通信信息


下一篇:使用sublime text2如何新建文件快速生成HTML头部信息?