[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.
<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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20121011]global_name為空的恢復(new)-bbed學習.txt
- oracle10g 資料檔案頭data file header(file header)OracleHeader
- The Ultimate (DLL) Header File (轉)Header
- [20220128]Check the datapump file header information in Oracle.txtHeaderORMOracle
- PE教程3: File Header （檔案頭）Header
- [20201221]KTFB Bitmapped File Space Header的恢復.txtAPPHeader
- CXF 2.0 學習筆記-3 SOAP Header筆記Header
- Nginx的client_header_buffer_size和large_client_header_buffers學習NginxclientHeader
- Java學習之File類理解Java
- ORA-01251: Unknown File Header Version read for file number 7Header
- LMT更新file header bitmap不產生redo和undo ?Header
- [Shell] awk學習(4)-read input file
- gulp使用gulp-file-include將header/footer引入頁面Header
- 學習筆記：robots.txt檔案筆記
- Salesforce LWC學習(二十七) File UploadSalesforce
- 關於MYSQL INNODB index page header學習和實驗總結MySqlIndexHeader
- Golang 原始碼學習（一） —— os/file 檔案操作Golang原始碼
- redo log file 物理結構學習與測試
- clob utl_file 匯出成TXT檔案
- 20161110Bitmapped File Space Header恢復APPHeader
- Java I/O系統學習系列一：File和RandomAccessFileJavarandomMac
- System File1 File Header（資料庫System檔案1檔案頭）損壞情況的恢復Header資料庫
- [20130530]OS block header破壞以及恢復.txtBloCHeader
- [20150425]tmux學習心得2.txtUX
- JAVA程式設計學習記錄(File類檔案操作)Java程式設計
- Vi and Vim Autocommand: 3 Steps to Add Custom Header To Your File AutomaticallyHeader
- [20190422]學習linux fuser.txtLinux
- [20141225]學習使用tmux心得.txtUX
- [20120808]學習物化檢視.txt
- JavaScript 複習之 File物件JavaScript物件
- Warning: Cannot modify header information - headers already sent by (output started at file_path:line)HeaderORM
- [20130409]Data file init write等待事件.txt事件
- 【HTML5&CSS3進階學習02】Header的實現·CSS中的佈局HTMLCSSS3Header
- 傳智黑馬java基礎學習——day22（File、遞迴）Java遞迴
- [20141203]學習使用pidstat.txt
- jQuery :headerjQueryHeader
- HTTP headerHTTPHeader
- [20161228]奇怪log file sync等待事件.txt事件