敗家貓:oracle 發生壞塊還原流程

<< 從win copy檔案到linux時要注意的事情 | 回blog首頁 | nocarchive使用rman還原測試 >>

2006年08月4日 17:25

oracle 發生壞塊還原流程

os:win2000
oracle:10.0.2

發生壞塊還原流程

1、建立測試環境

SQL> create tablespace test datafile 'd:\oracle\product\10.2.0\binhu\test.dbf' size 5M;

已建立表格空間.

SQL> create table test2 as select * from v$tablespace;

已建立表格.

SQL> insert into test2 (select * from v$tablespace);

已建立 6 個資料列.

自已多加一些資料進去，因為我在資料量太小的情況下沒有辦法產生出錯誤環境

SQL> insert into test2 (select * from test2);

已建立 344064 個資料列.

SQL> insert into test2 (select * from test2);

已建立 688128 個資料列.

SQL> commit;

SQL> shutdown immediate;

2、建立備份

RMAN> run {
2> allocate channel t1 type disk;
3> backup full format 'd:/vm/%U' database include current controlfile;
4> release channel t1;
5> }

使用目標資料庫控制檔替代復原目錄
配置的通道: t1
通道 t1: sid=154 devtype=DISK

開始 backup, 於 2006-08-04 11:11:57
通道 t1: 啟動完整資料檔備份集
通道 t1: 正在指定備份集中的資料檔
輸入資料檔 fno=00001 名稱=D:\ORACLE\PRODUCT\10.2.0\BINHU\SYSTEM01.DBF
輸入資料檔 fno=00003 名稱=D:\ORACLE\PRODUCT\10.2.0\BINHU\SYSAUX01.DBF
輸入資料檔 fno=00002 名稱=D:\ORACLE\PRODUCT\10.2.0\BINHU\UNDOTBS01.DBF
輸入資料檔 fno=00004 名稱=D:\ORACLE\PRODUCT\10.2.0\BINHU\USERS01.DBF
輸入資料檔 fno=00005 名稱=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF
通道 t1: 啟動部份 1, 在 2006-08-04 11:11:58
通道 t1: 已完成部份 1, 在 2006-08-04 11:13:13
片段處理=D:\VM\03HPSORU_1_1 標記=TAG20060804T111158 註解=NONE
通道 t1: 備份集完成, 經歷時間: 00:01:15
通道 t1: 啟動完整資料檔備份集
通道 t1: 正在指定備份集中的資料檔
包括備份集中目前的控制檔
在備份集中包括目前的 SPFILE
通道 t1: 啟動部份 1, 在 2006-08-04 11:13:16
通道 t1: 已完成部份 1, 在 2006-08-04 11:13:17
片段處理=D:\VM\04HPSOUA_1_1 標記=TAG20060804T111158 註解=NONE
通道 t1: 備份集完成, 經歷時間: 00:00:03
完成 backup, 於 2006-08-04 11:13:17

3、使用編輯器破壞test.dbf裏面的內容

4、open oracle後，查看alter.log

Hex dump of (file 5, block 212) in trace file d:\oracle\product\10.2.0\admin\binhu\udump\binhu_ora_1308.trc
Corrupt block relative dba: 0x014000d4 (file 5, block 212)
Bad header found during backing up datafile
Data in bad block:
type: 2 format: 2 rdba: 0x22222222
last change scn: 0x2222.22222222 seq: 0x22 flg: 0x22
spare1: 0x22 spare2: 0x22 spare3: 0x2222
consistency value in tail: 0x00000001
check value in block header: 0x2222
block checksum disabled
Reread of blocknum=212, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=212, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=212, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=212, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=212, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Fri Aug 04 11:40:23 2006
Hex dump of (file 5, block 212) in trace file d:\oracle\product\10.2.0\admin\binhu\udump\binhu_ora_1308.trc
Corrupt block relative dba: 0x014000d4 (file 5, block 212)
Bad header found during backing up datafile
Data in bad block:
type: 2 format: 2 rdba: 0x22222222
last change scn: 0x2222.22222222 seq: 0x22 flg: 0x22
spare1: 0x22 spare2: 0x22 spare3: 0x2222
consistency value in tail: 0x00000001
check value in block header: 0x2222
block checksum disabled
Reread of blocknum=212, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=212, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=212, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=212, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=212, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Hex dump of (file 5, block 214) in trace file d:\oracle\product\10.2.0\admin\binhu\udump\binhu_ora_1308.trc
Corrupt block relative dba: 0x014000d6 (file 5, block 214)
Bad check value found during backing up datafile
Data in bad block:
type: 0 format: 2 rdba: 0x000000d6
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000001
check value in block header: 0xa7d6
computed block checksum: 0x660
Reread of blocknum=214, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=214, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=214, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=214, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=214, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Hex dump of (file 5, block 318) in trace file d:\oracle\product\10.2.0\admin\binhu\udump\binhu_ora_1308.trc
Corrupt block relative dba: 0x0140013e (file 5, block 318)
Bad header found during backing up datafile
Data in bad block:
type: 0 format: 2 rdba: 0x99999999
last change scn: 0x9999.99999999 seq: 0x99 flg: 0x99
spare1: 0x0 spare2: 0x99 spare3: 0x9999
consistency value in tail: 0x00000001
check value in block header: 0x9999
block checksum disabled
Reread of blocknum=318, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=318, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=318, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=318, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=318, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Hex dump of (file 5, block 595) in trace file d:\oracle\product\10.2.0\admin\binhu\udump\binhu_ora_1308.trc
Corrupt block relative dba: 0x01400253 (file 5, block 595)
Bad check value found during backing up datafile
Data in bad block:
type: 0 format: 2 rdba: 0x00000253
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000001
check value in block header: 0xa553
computed block checksum: 0xbbbb
Reread of blocknum=595, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=595, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=595, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=595, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data
Reread of blocknum=595, file=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF. found same corrupt data

5、進行修複

檢查一下有些損壞
RMAN> backup validate datafile 5;

開始 backup, 於 2006-08-04 11:40:22
配置的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=159 devtype=DISK
通道 ORA_DISK_1: 啟動完整資料檔備份集
通道 ORA_DISK_1: 正在指定備份集中的資料檔
輸入資料檔 fno=00005 名稱=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF
通道 ORA_DISK_1: 備份集完成, 經歷時間: 00:00:02
完成 backup, 於 2006-08-04 11:40:24

再進sql查看
SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ------------------
         5        212          1                  0 CORRUPT
         5        214          1                  0 CHECKSUM
         5        595          1                  0 CHECKSUM
         5        318          1                  0 CORRUPT

再進rman
RMAN> blockrecover datafile 5 block 595;

開始 blockrecover, 於 2006-08-04 11:49:37
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 回復區塊
通道 ORA_DISK_1: 指定要從備份集回復的區塊
正在回復資料檔 00005 的區塊
通道 ORA_DISK_1: 從備份 D:\VM\03HPSORU_1_1 讀取
通道 ORA_DISK_1: 從備份部份 1 回復的區塊
片斷處理=D:\VM \03HPSORU_1_1 標記=TAG20060804T111158
通道 ORA_DISK_1: 區塊回復完成, 經歷時間: 00:00:02

啟動媒體恢復
媒體復原完成, 經歷時間: 00:00:07

完成 blockrecover, 於 2006-08-04 11:49:49

RMAN> blockrecover datafile 5 block 318;

開始 blockrecover, 於 2006-08-04 11:51:02
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 回復區塊
通道 ORA_DISK_1: 指定要從備份集回復的區塊
正在回復資料檔 00005 的區塊
通道 ORA_DISK_1: 從備份 D:\VM\03HPSORU_1_1 讀取
通道 ORA_DISK_1: 從備份部份 1 回復的區塊
片斷處理=D:\VM \03HPSORU_1_1 標記=TAG20060804T111158
通道 ORA_DISK_1: 區塊回復完成, 經歷時間: 00:00:02

啟動媒體恢復
媒體復原完成, 經歷時間: 00:00:03

完成 blockrecover, 於 2006-08-04 11:51:09

RMAN> blockrecover datafile 5 block 214;

開始 blockrecover, 於 2006-08-04 11:51:41
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 回復區塊
通道 ORA_DISK_1: 指定要從備份集回復的區塊
正在回復資料檔 00005 的區塊
通道 ORA_DISK_1: 從備份 D:\VM\03HPSORU_1_1 讀取
通道 ORA_DISK_1: 從備份部份 1 回復的區塊
片斷處理=D:\VM \03HPSORU_1_1 標記=TAG20060804T111158
通道 ORA_DISK_1: 區塊回復完成, 經歷時間: 00:00:02

啟動媒體恢復
媒體復原完成, 經歷時間: 00:00:07

完成 blockrecover, 於 2006-08-04 11:51:52

RMAN> blockrecover datafile 5 block 212;

開始 blockrecover, 於 2006-08-04 11:51:55
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 回復區塊
通道 ORA_DISK_1: 指定要從備份集回復的區塊
正在回復資料檔 00005 的區塊
通道 ORA_DISK_1: 從備份 D:\VM\03HPSORU_1_1 讀取
通道 ORA_DISK_1: 從備份部份 1 回復的區塊
片斷處理=D:\VM \03HPSORU_1_1 標記=TAG20060804T111158
通道 ORA_DISK_1: 區塊回復完成, 經歷時間: 00:00:01

啟動媒體恢復
媒體復原完成, 經歷時間: 00:00:03

完成 blockrecover, 於 2006-08-04 11:52:00

全做完後再次檢查
RMAN> backup validate datafile 5;

開始 backup, 於 2006-08-04 11:52:49
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 啟動完整資料檔備份集
通道 ORA_DISK_1: 正在指定備份集中的資料檔
輸入資料檔 fno=00005 名稱=D:\ORACLE\PRODUCT\10.2.0\BINHU\TEST.DBF
通道 ORA_DISK_1: 備份集完成, 經歷時間: 00:00:01
完成 backup, 於 2006-08-04 11:52:51

進sql查看是否真的ok了
SQL> select * from v$database_block_corruption;

沒有任何資料列被選取

完成修複
附上d:\oracle\product\10.2.0\admin\binhu\udump\binhu_ora_1308.trc的內容

*** 2006-08-04 11:51:50.264
Media Recovery drop redo thread 1
Completed Block Media Recovery of file 5, block 214
*** ACTION NAME:(0000168 STARTED104) 2006-08-04 11:51:57.264
Begin Media Recovery of 1 blocks
*** 2006-08-04 11:51:57.389
Media Recovery add redo thread 1
Start recovery at thread 1 ckpt scn 1941109 logseq 41 block 59425
End recovery at scn 1947026
*** 2006-08-04 11:51:57.655
Recovery of Online Redo Log: Thread 1 Group 1 Seq 41 Reading mem 0
*** 2006-08-04 11:51:57.843
Recovery of Online Redo Log: Thread 1 Group 2 Seq 42 Reading mem 0
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 46887Kb in 1.82s => 25.16 Mb/sec
Total physical reads: 50317Kb
Longest record: 39Kb, moves: 0/36032 (0%)
Change moves: 13/133 (9%), moved: 0Mb
Longest LWN: 1424Kb, moves: 11/729 (1%), moved: 9Mb
Last redo scn: 0x0000.001db591 (1947025)
----------------------------------------------
*** 2006-08-04 11:51:59.202
Media Recovery drop redo thread 1
Completed Block Media Recovery of file 5, block 212

注意事項:

This command is available only in the Enterprise Edition.
The target database must be mounted or open. You do not have to take a datafile offline if you are performing block media recovery on it.
You can only perform complete media recovery of individual blocks. Point-in-time recovery of individual data blocks is not supported.
You can only perform block media recovery on corrupt blocks.
Blocks marked media corrupt are not accessible until recovery completes.
You cannot perform block media recovery when using a backup control file.
You cannot use proxy backups to perform block media recovery. If the only backups that you have are proxy backups, then you can restore them to a nondefault location on disk, which causes RMAN to view the restored files as datafile copies. You can then use the datafile copies for block media recovery.
You must have a full backup of the file containing the corrupt blocks: block media recovery cannot use incremental backups.
Block media recovery cannot survive a missing or inaccessible archived log, although it can sometimes survive missing or inaccessible records (refer to Oracle9i Recovery Manager User's Guide).
The datafile header block (block 1) cannot be recovered.
You cannot perform block media recovery in NOARCHIVELOG mode.

參考網址:
http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/server.920/a96565/rcmsynta10.htm

http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/server.920/a96566/rcmconc2.htm#459477

附記:

1. backup validate database;       --檢查數據庫的錯誤(包含物理和邏輯)但不備份
2. backup database force;       --強製備份只讀的數據文件
3. backup database not backed up [since time='sysdate-2'];   --備份最近[兩天]沒有備份過的數據文件
4. backup check logical database;       --檢查邏輯錯誤的同時備份數據庫
5. backup validate check logical database;      --檢查邏輯錯誤但不備份數據庫
6. backup database skip [readonly|offline|inaccessible];   --跳過只讀|不在線|不能讀取(實際不存在)的文件

您可能有興趣的文章：

11g logical standby的一個bug

有關autotrace出來的訊習說明

取得物件的ddl

oracle使用db-link連到mysql

還原data guard的primary(使用catalog)

<< 從win copy檔案到linux時要注意的事情 | 回blog首頁 | nocarchive使用rman還原測試 >>

my_work 發表於樂多 │回應(0) │引用(1) │DB │編輯本文

樂多分類：網路/3C │昨日人次：0 │累計人次：536

引用URL

http://cgi.blog.roodo.com/trackback/1973638

引用列表:

all about polomurinureon and top news

polomurinureon 89 post【polomurinureon blog】 at 2007年10月24日 18:24

姓名 :
Email :
URL :
回應狀態:	公開私密
	記住我的資訊顯示我的圖像 ( 登入或申請個人圖像 )
回應內容 :

回最上方