無備份歸檔的db意外斷電處理-2662和4193

公司生產庫崩潰，沒有備份和歸檔，想碰碰運氣在mount下利用沒有覆蓋的redo來recover database，其實還算運氣好剛好可以recover然後順利open db，下面的警告日誌中提到了我們經常說的db如何會恢復，db找到corrupted block然後不停的利用redo來恢復，從下面的recover記錄中可以看出來Media Recovery Start parallel recovery started with 7 processes

並行recovery以及利用redo和archivelog具體恢復資訊。

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

警告日誌記錄：

Thu Apr 12 08:54:18 2012

ALTER DATABASE RECOVER database

Media Recovery Start

parallel recovery started with 7 processes

Thu Apr 12 08:54:19 2012

Recovery of Online Redo Log: Thread 1 Group 3 Seq 42178 Reading mem 0

Mem# 0 errs 0: /db/oracle10g/oradata/benguo/redo03.log

Thu Apr 12 08:54:32 2012

Hex dump of (file 2, block 8337) in trace file /db/oracle10g/admin/benguo/bdump/benguo_p002_30981.trc

Corrupt block relative dba: 0x00802091 (file 2, block 8337)

Fractured block found during media recovery

Data in bad block:

type: 2 format: 2 rdba: 0x00802091

last change scn: 0x0000.615fa3f6 seq: 0x6 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0xe0020202

check value in block header: 0xb288

computed block checksum: 0x5553

Reread of rdba: 0x00802091 (file 2, block 8337) found same corrupted data

Hex dump of (file 2, block 8345) in trace file /db/oracle10g/admin/benguo/bdump/benguo_p002_30981.trc

Corrupt block relative dba: 0x00802099 (file 2, block 8345)

Fractured block found during media recovery

Data in bad block:

type: 2 format: 2 rdba: 0x00802099

last change scn: 0x0000.615fe005 seq: 0x16 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0xb1b20201

check value in block header: 0x492b

computed block checksum: 0x1f3f

Reread of rdba: 0x00802099 (file 2, block 8345) found same corrupted data

Hex dump of (file 2, block 8329) in trace file /db/oracle10g/admin/benguo/bdump/benguo_p002_30981.trc

Corrupt block relative dba: 0x00802089 (file 2, block 8329)

Fractured block found during media recovery

Data in bad block:

type: 2 format: 2 rdba: 0x00802089

last change scn: 0x0000.615fdffd seq: 0x2 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x97870233

check value in block header: 0x7a22

computed block checksum: 0xdeee

Reread of rdba: 0x00802089 (file 2, block 8329) found same corrupted data

……省略了中間部分block的介質恢復

Thu Apr 12 08:54:34 2012

Hex dump of (file 2, block 9069) in trace file /db/oracle10g/admin/benguo/bdump/benguo_p001_30979.trc

Corrupt block relative dba: 0x0080236d (file 2, block 9069)

Fractured block found during media recovery

Data in bad block:

type: 2 format: 2 rdba: 0x0080236d

last change scn: 0x0000.615fe2f8 seq: 0x3 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x00320209

check value in block header: 0xfad

computed block checksum: 0xad65

Reread of rdba: 0x0080236d (file 2, block 9069) found same corrupted data

Thu Apr 12 08:54:34 2012

Media Recovery Complete (benguo)

Thu Apr 12 08:54:37 2012

Completed: ALTER DATABASE RECOVER database

其實自己想展示的是如何用下列辦法來恢復db：

生產庫伺服器意外斷電，導致db無法open，一般意外斷電，redo和undo都可能損壞，如果沒有備份此時自己想到是什麼allow_resetlogs_corruption和corrupted_rollback_segment隱含引數來開啟db，注意用allow_resetlogs_corruption開啟時最可能出現的ora-000600的2662

的錯誤，根據第三個引數和第五個引數差別來推進scn。

此時可以使用oracle內部事件10015來推進scn

alter session set events ‘10015 trace name adjust_scn,level x’, 推進scn

不過對於10g下有個錯誤就是使用10015事件推進scn時需要手動設定隱含引數_allow_error_simulation,該引數在10g下預設是false，也就是推進scn沒有實際效果，需要注意的是即使不設定_allow_error_simulation為true，該命令依然顯示執行，其實oracle內部的scn並沒有推薦。

此時又可能出現新的錯誤，ora-00600 internal error code argument [4193]…

600中如果出現錯誤4193很可能是回滾段的問題，此時需要我們設定corrupted_rollback_segment該引數來使錯誤的回滾段不檢查，修改undo_management=manual手動管理undo，然後就是重複幾次recover database until cancel然後open resetlogs，這種辦法一般是可以開啟大多數db的，如果實在不行可能需要額外的辦法和工具來處理。上次一個實際的db解決了但是沒有記錄相應的恢復步驟，後續碰見合適機會的補一個blog的恢復流程。

[@more@]