[20171114]恢復資料檔案塊頭2.txt
[20171114]恢復資料檔案塊頭2.txt
--//曾經寫過一篇[20161111]資料庫檔案頭的修復.txt,但是利用大小相似的資料檔案頭覆蓋來恢復,那是屬於特種恢復.
--//參考連結:http://blog.itpub.net/267265/viewspace-2128309/
--//不在正常操作範圍,完全是不得已而為之.基本寫那篇在一年之前,這次做一個帶引號"常規恢復"看看.
--//後記:純屬無聊,千萬不要把這當作常規的恢復.
1.環境:
SCOTT@book> @ &r/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
2.建立測試環境:
CREATE TABLESPACE TEA DATAFILE
'/mnt/ramdisk/book/tea01.dbf' SIZE 40M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table t1 tablespace tea as select rownum id ,lpad('A',32,'A') name from dual connect by level<=1e5;
--//建立rman備份:
backup database filesperset=1 format '/home/oracle/backup/20171114_%U';
--//注:這是我個人主張的備份方式設定filesperset=1,可惜我們團隊一直不建議使用.這樣備份檔案顯得太多了.實際上這樣做與我後面的恢復有關.
create table t2 tablespace tea as select rownum id ,lpad('B',32,'B') name from dual connect by level<=1e5;
--//建立大小5M的表。注:說明一點,我是先做資料庫備份,再建立t2表.
--//破壞資料檔案頭,注:資料檔案頭在資料檔案的第2塊.第1塊OS頭.
update t2 set name=lpad('C',32,'C') where id <=5;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
rollback ;
update t2 set name=lpad('D',32,'D') where id between 5 and 9;
commit ;
alter database datafile 6 offline ;
$ dd if=/dev/zero of=/mnt/ramdisk/book/tea01.dbf count=1 bs=8192 conv=notrunc seek=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 3.8338e-05 seconds, 214 MB/s
--//再次提醒不要忘記conv=notrunc引數,不然資料檔案會被截斷.我自己在以前測試中遇到這種情況!!
2.檢查破壞的資料檔案:
RMAN> backup validate datafile 6;
Starting backup at 2017-11-14 15:15:07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=144 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=158 device type=DISK
RMAN-06169: could not read file header for datafile 6 error reason 7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/14/2017 15:15:08
RMAN-06056: could not access datafile 6
$ dbv file=/mnt/ramdisk/book/tea01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Nov 14 15:15:46 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/tea01.dbf
Page 1 is marked corrupt
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Completely zero block found during dbv:
Page 2 is marked corrupt
Corrupt block relative dba: 0x00000002 (file 0, block 2)
Bad header found during dbv:
Data in bad block:
type: 29 format: 2 rdba: 0x01800002
last change scn: 0x0003.176e7555 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x75551d02
check value in block header: 0x68de
computed block checksum: 0x0
Page 3 is marked corrupt
Corrupt block relative dba: 0x00000003 (file 0, block 3)
Bad header found during dbv:
Data in bad block:
type: 30 format: 2 rdba: 0x01800003
last change scn: 0x0003.176e7555 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x75551e01
check value in block header: 0x59b6
computed block checksum: 0x0
.....
--//一旦檔案頭損壞,下面的檢查感覺全部是壞塊的.實際上檢查都是不對的.
SCOTT@book> select * from v$database_block_corruption;
no rows selected
SCOTT@book> alter database datafile 6 online ;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
ORA-01210: data file header is media corrupt
3.修復:
RMAN> recover datafile 6 block 1;
Starting recover at 2017-11-14 15:19:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=158 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=171 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=184 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-11-14 15:19:52
--//實際上什麼都沒做.換一種執行方式:
RMAN> blockrecover datafile 6 block 1;
Starting recover at 2017-11-14 15:20:22
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-11-14 15:20:22
--//你使用dbv檢查還是報一樣的錯誤.也就是這樣方式無法修復檔案頭.
--//如果執行以下步驟恢復,是一種常見的做法.但是如果如果檔案很大restore需要很長時間,而recover需要時間與應用歸檔日誌量有關.
restore datafile 6;
recover datafile 6;
--//我採用另外的方法恢復,透過特殊方式取出檔案頭,然後在執行recover看看是否可行.
4.恢復測試:
RMAN> list backup by file;
List of Datafile Backups
========================
File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Compressed Tag
---- ------- - -- - ---------- ------------------- ------- ------- ---------- ---
1 204 B F A 13278016597 2017-11-14 15:09:34 1 1 NO TAG20171114T150933
2 203 B F A 13278016596 2017-11-14 15:09:34 1 1 NO TAG20171114T150933
3 201 B F A 13278016595 2017-11-14 15:09:34 1 1 NO TAG20171114T150933
4 205 B F A 13278016602 2017-11-14 15:09:38 1 1 NO TAG20171114T150933
5 202 B F A 13278016599 2017-11-14 15:09:35 1 1 NO TAG20171114T150933
6 206 B F A 13278016603 2017-11-14 15:09:38 1 1 NO TAG20171114T150933
List of Control File Backups
============================
CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Compressed Tag
---------- ------------------- ------- - ------- ------- ---------- ---
13278016610 2017-11-14 15:09:39 207 A 1 1 NO TAG20171114T150939
List of SPFILE Backups
======================
Modification Time BS Key S #Pieces #Copies Compressed Tag
------------------- ------- - ------- ------- ---------- ---
2017-11-06 15:26:50 207 A 1 1 NO TAG20171114T150939
--//確定key=206
RMAN> list backupset 206;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
206 Full 5.69M DISK 00:00:00 2017-11-14 15:09:38
BP Key: 207 Status: AVAILABLE Compressed: NO Tag: TAG20171114T150933
Piece Name: /home/oracle/backup/20171114_fcsji7ti_1_1
List of Datafiles in backup set 206
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
6 Full 13278016603 2017-11-14 15:09:38 /mnt/ramdisk/book/tea01.dbf
--//確定備份片檔案是/home/oracle/backup/20171114_fcsji7ti_1_1.
--//我的資料庫名=BOOK.
$ strings -t d /home/oracle/backup/20171114_fcsji7ti_1_1 | grep BOOK
8223 OBOOK
5955615 OBOOK
--//8223/8192=1.0037841796875
--//5955615/8192=727.0037841796875
--//可以確定包含BOOK在備份檔案集的第1塊,第727塊.
BBED> set filename '/home/oracle/backup/20171114_fcsji7ti_1_1'
FILENAME /home/oracle/backup/20171114_fcsji7ti_1_1
BBED> set block 1
BLOCK# 1
BBED> map /v
File: /home/oracle/backup/20171114_fcsji7ti_1_1 (100)
Block: 1 Dba:0x19000001
------------------------------------------------------------
BBED-00400: invalid blocktype (19)
BBED> map
File: /home/oracle/backup/20171114_fcsji7ti_1_1 (100)
Block: 727 Dba:0x190002d7
------------------------------------------------------------
Data File Header
struct kcvfh, 860 bytes @0
ub4 tailchk @8188
--//可以確定檔案頭備份在727塊中.從這裡還可以看出檔案頭實際上最後寫入備份檔案集中的.
$ ls -l /home/oracle/backup/20171114_fcsji7ti_1_1
-rw-r----- 1 oracle oinstall 5971968 2017-11-14 15:09:38 /home/oracle/backup/20171114_fcsji7ti_1_1
--// 備份檔案佔 5971968/8192=729塊.基本上倒數第2塊就是檔案頭.
BBED> set filename '/home/oracle/backup/20171114_fcsji7ti_1_1'
FILENAME /home/oracle/backup/20171114_fcsji7ti_1_1
BBED> set block 729
BBED-00309: out of range block number (729)
BBED> set block 728
BLOCK# 728
5.直接使用備份集匯入資料檔案看看.
$ dd if=/home/oracle/backup/20171114_fcsji7ti_1_1 skip=727 of=/mnt/ramdisk/book/tea01.dbf seek=1 count=1 bs=8192 conv=notrunc
--//再次提醒輸入輸出千萬不要搞錯了.注意引數選擇
--// seek=BLOCKS skip BLOCKS obs-sized blocks at start of output
--// skip=BLOCKS skip BLOCKS ibs-sized blocks at start of input
$ dd if=/home/oracle/backup/20171114_fcsji7ti_1_1 skip=727 of=/mnt/ramdisk/book/tea01.dbf seek=1 count=1 bs=8192 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 4.4931e-05 seconds, 182 MB/s
$ dbv file=/mnt/ramdisk/book/tea01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Nov 14 15:37:34 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/tea01.dbf
Page 1 is marked corrupt
Corrupt block relative dba: 0x01800001 (file 6, block 1)
Bad header found during dbv:
Data in bad block:
type: 11 format: 2 rdba: 0x018000d7
~~~~~~~~~~~~~~~~~
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x1
~~~~~~~~~~~~
consistency value in tail: 0x00000b01
check value in block header: 0x63c4
computed block checksum: 0x0
--//rdba錯誤.很容易修正.關於這方面的內容可以參考我以前寫的:
--// http://blog.itpub.net/267265/viewspace-2128672/ => [20161118]rman備份的疑問2.txt
BBED> set dba 6,1
DBA 0x01800001 (25165825 6,1)
BBED> assign kcvfh.kcvfhbfh.rdba_kcbh=0x01800001
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub4 rdba_kcbh @4 0x01800001
BBED> sum apply;
Check value for File 6, Block 1:
current = 0x6312, required = 0x6312
BBED> p dba 6,1 kcvfh.kcvfhbfh.spare3_kcbh
ub2 spare3_kcbh @18 0x0001
BBED> p dba 5,1 kcvfh.kcvfhbfh.spare3_kcbh
ub2 spare3_kcbh @18 0x0000
BBED> assign dba 6,1 kcvfh.kcvfhbfh.spare3_kcbh=0x0000
ub2 spare3_kcbh @18 0x0000
BBED> sum apply dba 6,1;
Check value for File 6, Block 1:
current = 0x6313, required = 0x6313
--//記住修改2處:kcvfh.kcvfhbfh.rdba_kcbh kcvfh.kcvfhbfh.spare3_kcbh.
RMAN> recover datafile 6 ;
Starting recover at 2017-11-14 15:56:56
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=144 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=158 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-11-14 15:56:58
SCOTT@book> alter database datafile 6 online ;
Database altered.
SCOTT@book> column name format a40
SCOTT@book> select * from t2 where id between 1 and 9;
ID NAME
---------- ----------------------------------------
1 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
2 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
3 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
4 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
5 DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
6 DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
7 DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
8 DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
9 DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
9 rows selected.
--//OK現在恢復正常了.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2147252/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- rman恢復資料檔案-----塊折斷
- 恢復資料,資料塊恢復
- [20171122]恢復資料檔案塊頭5.txt
- 資料檔案壞塊的製造和恢復
- [20171115]恢復資料檔案塊頭3補充.txt
- [20171115]恢復資料檔案塊頭4補充.txt
- bbed 與檔案頭恢復
- 【資料庫資料恢復】Oracle資料庫檔案出現壞塊報錯的資料恢復案例資料庫資料恢復Oracle
- 資料恢復-電腦管家檔案恢復工具資料恢復
- rman恢復資料檔案 恢復表空間
- 恢復之重建資料檔案
- 【資料庫資料恢復】透過恢復NDF檔案修復資料庫的資料恢復過程資料庫資料恢復
- RMAN恢復案例:無恢復目錄，丟失全部資料檔案、控制檔案、日誌檔案恢復
- rman 恢復資料塊
- rman恢復資料塊
- RMAN恢復案例:丟失全部資料檔案恢復
- 使用blockrecover 對有壞塊的資料檔案進行恢復BloC
- 資料檔案誤刪--但有資料檔案的copy恢復
- 恢復之還原資料檔案
- 資料檔案丟失的恢復
- [ORACLE ASM] AMDU 恢復資料檔案OracleASM
- 新增資料檔案與恢復cf
- Bak檔案恢復到資料庫資料庫
- 資料檔案丟失如何恢復
- 【資料庫資料恢復】MongoDB資料庫檔案損壞的資料恢復案例資料庫資料恢復MongoDB
- rman備份恢復-rman恢復資料檔案測試
- rman恢復時跳過資料檔案，進行恢復
- oracle實驗記錄 (恢復,備份-含壞塊資料檔案)Oracle
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- 【伺服器資料恢復】ZFS檔案系統下RAIDZ多塊硬碟離線的資料恢復案例伺服器資料恢復AI硬碟
- 資料塊恢復例項
- 資料恢復新姿勢——通過ibd和frm檔案恢復資料資料恢復
- 恢復案例：歸檔模式下丟失全部資料檔案的恢復模式
- system資料檔案頭損壞修復
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- RMAN恢復案例:丟失非系統資料檔案恢復
- RM 刪除資料檔案恢復操作
- 【資料庫資料恢復】Sql Server資料庫檔案丟失的資料恢復過程資料庫資料恢復SQLServer