[20121011]file header reset--bbed學習.txt

lfree發表於2012-10-11
[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.

 <48 bytes per line>

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/

 <48 bytes per line>

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...

 <48 bytes per line>

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...

 <48 bytes per line>

--因為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

 <80 bytes per line>

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

 <80 bytes per line>

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

 <80 bytes per line>

BBED> modify /x  5707 offset 140
 File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
 Block: 1                                                                      Offsets:  140 to  143                                                                 Dba:0x02000001
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 57070000

 <80 bytes per line>

BBED> modify /x  5607 offset 148
 File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
 Block: 1                                                                      Offsets:  148 to  151                                                                 Dba:0x02000001
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 56070000

 <80 bytes per line>

--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點增加來測試.先暫時學到這裡!!累!!
--控制檔案有記錄嗎?


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-746222/,如需轉載,請註明出處,否則將追究法律責任。

相關文章