異常斷電導(dǎo)致current redo損壞處理

我忒忙

異常斷電導(dǎo)致current redo損壞處理
一、數(shù)據(jù)庫啟動異常
網(wǎng)友因異常斷電導(dǎo)致數(shù)據(jù)庫不能正常啟動，處理過程記錄
1、sqlplus中提示
C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
 
SQL*Plus: Release 11.1.0.6.0 - Production on 星期三 12月 7 12:57:02 2011
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
已連接到空閑例程。
 
SQL> startup
ORACLE 例程已經(jīng)啟動。
 
Total System Global Area 1581916160 bytes
Fixed Size                  1336060 bytes
Variable Size             964693252 bytes
Database Buffers          603979776 bytes
Redo Buffers               11907072 bytes
數(shù)據(jù)庫裝載完畢。
ORA-00368: 重做日志塊中的校驗和錯誤
ORA-00353: 日志損壞接近塊 12014 更改 9743799889 時間 12/05/2011 09:21:11
ORA-00312: 聯(lián)機日志 3 線程 1: 'R:\ORADATA\HZYL\REDO03.LOG'
2、alert日志文件
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_2960.trc  (incident=214262):
ORA-00353: 日志損壞接近塊 12014 更改 9743799889 時間 12/05/2011 09:21:11
ORA-00312: 聯(lián)機日志 3 線程 1: 'R:\ORADATA\HZYL\REDO03.LOG'
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_214262\hzyl_ora_2960_i214262.trc
Aborting crash recovery due to error 368
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_2960.trc:
ORA-00368: 重做日志塊中的校驗和錯誤
ORA-00353: 日志損壞接近塊 12014 更改 9743799889 時間 12/05/2011 09:21:11
ORA-00312: 聯(lián)機日志 3 線程 1: 'R:\ORADATA\HZYL\REDO03.LOG'
ORA-368 signalled during: ALTER DATABASE OPEN...
Wed Dec 07 12:57:17 2011
Trace dumping is performing id=[cdmp_20111207125717]
Wed Dec 07 12:57:17 2011
Sweep Incident[214262]: completed
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_m000_2400.trc  (incident=214326):
ORA-00353: log corruption near block 12014 change 9743799889 time 12/05/2011 09:21:11
ORA-00312: online log 3 thread 1: 'R:\ORADATA\HZYL\REDO03.LOG'
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_214326\hzyl_m000_2400_i214326.trc
Trace dumping is performing id=[cdmp_20111207125718]
二、查詢是損壞日志組
SQL> col member for a35
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b
   2 where a.group#=b.group#;
 
    GROUP# STATUS           MEMBER
---------- ---------------- -----------------------------------
         3 CURRENT          R:\ORADATA\HZYL\REDO03.LOG
         2 INACTIVE         R:\ORADATA\HZYL\REDO02.LOG
         1 INACTIVE         R:\ORADATA\HZYL\REDO01.LOG
確定是因為斷電導(dǎo)致當(dāng)前redo 損壞，需要使用隱含參數(shù)+不完全恢復(fù)處理(經(jīng)驗)

三、不完全恢復(fù)
SQL> recover database until cancel;
ORA-00279: 更改 9743796718 (在 12/05/2011 09:07:40 生成) 對于線程 1 是必需的
ORA-00289: 建議: S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC
ORA-00280: 更改 9743796718 (用于線程 1) 在序列 #15114 中
 
 
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: 無法打開歸檔日志
'S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC'
ORA-27041: 無法打開文件
OSD-04002: 無法打開文件
O/S-Error: (OS 2) 系統(tǒng)找不到指定的文件。
 
 
ORA-00308: 無法打開歸檔日志
'S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC'
ORA-27041: 無法打開文件
OSD-04002: 無法打開文件
O/S-Error: (OS 2) 系統(tǒng)找不到指定的文件。
 
 
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 將出現(xiàn)如下錯誤
ORA-01194: 文件 1 需要更多的恢復(fù)來保持一致性
ORA-01110: 數(shù)據(jù)文件 1: 'R:\ORADATA\HZYL\SYSTEM01.DBF'
四、啟用隱含參數(shù)，嘗試打開數(shù)據(jù)庫
SQL> create pfile='d:/1.txt' from spfile;
 
文件已創(chuàng)建。
 
#############添加隱含參數(shù)##################
*._allow_resetlogs_corruption=TRUE
*._allow_error_simulation=TRUE
##########################################
 
SQL> shutdown immediate
ORA-01109: 數(shù)據(jù)庫未打開
 
 
已經(jīng)卸載數(shù)據(jù)庫。
ORACLE 例程已經(jīng)關(guān)閉。
SQL> startup pfile ='d:\1.txt';
ORACLE 例程已經(jīng)啟動。
 
Total System Global Area 1581916160 bytes
Fixed Size                  1336060 bytes
Variable Size             964693252 bytes
Database Buffers          603979776 bytes
Redo Buffers               11907072 bytes
數(shù)據(jù)庫裝載完畢。
ORA-01589: 要打開數(shù)據(jù)庫則必須使用 RESETLOGS 或 NORESETLOGS 選項
 
 
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出現(xiàn)錯誤:
ORA-00603: ORACLE 服務(wù)器會話因致命錯誤而終止
進(jìn)程 ID: 3388
會話 ID: 335 序列號: 5
五、查看alert日志
Wed Dec 07 13:02:49 2011
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216664):
ORA-00600: 內(nèi)部錯誤代碼, 參數(shù): [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216664\hzyl_ora_3388_i216664.trc
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc:
ORA-00600: 內(nèi)部錯誤代碼, 參數(shù): [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 338: terminating the instance due to error 600
Wed Dec 07 13:02:50 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mmnl_3884.trc  (incident=216640):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216640\hzyl_mmnl_3884_i216640.trc
Wed Dec 07 13:02:50 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_diag_4020.trc  (incident=216552):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216552\hzyl_diag_4020_i216552.trc
Wed Dec 07 13:02:50 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dia0_2896.trc  (incident=216584):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216584\hzyl_dia0_2896_i216584.trc
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_pmon_2964.trc  (incident=216536):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216536\hzyl_pmon_2964_i216536.trc
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mmon_3764.trc  (incident=216632):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216632\hzyl_mmon_3764_i216632.trc
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dbrm_2060.trc  (incident=216560):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_psp0_1476.trc  (incident=21656:
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Wed Dec 07 13:02:52 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mman_3040.trc  (incident=216576):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Wed Dec 07 13:02:52 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dbw0_2116.trc  (incident=216592):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_lgwr_2624.trc  (incident=216600):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_smon_592.trc  (incident=216616):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ckpt_3052.trc  (incident=21660:
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Instance terminated by USER, pid = 3388
ORA-1092 signalled during: alter database open resetlogs...
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216665):
ORA-00600: 內(nèi)部錯誤代碼, 參數(shù): [], [], [], [], [], [], [], []
ORA-01092: ORACLE 實例終止。強制斷開連接
ORA-00600: 內(nèi)部錯誤代碼, 參數(shù): [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216666):
ORA-00600: 內(nèi)部錯誤代碼, 參數(shù): [], [], [], [], [], [], [], []
ORA-00600: 內(nèi)部錯誤代碼, 參數(shù): [], [], [], [], [], [], [], []
ORA-01092: ORACLE 實例終止。強制斷開連接
ORA-00600: 內(nèi)部錯誤代碼, 參數(shù): [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc:
ORA-00600: 內(nèi)部錯誤代碼, 參數(shù): [], [], [], [], [], [], [], []
ORA-00600: 內(nèi)部錯誤代碼, 參數(shù): [], [], [], [], [], [], [], []
ORA-01092: ORACLE 實例終止。強制斷開連接
ORA-00600: 內(nèi)部錯誤代碼, 參數(shù): [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Wed Dec 07 13:02:53 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=218920):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_218920\hzyl_ora_3388_i218920.trc
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_218920\hzyl_ora_3388_i218920.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
這里看到熟悉的ora-600 [2662]，處理方法增加scn，參考ORA-00600 [2662]

六、增加SCN，打開數(shù)據(jù)庫
SQL> exit
從 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 斷
開
 
C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
 
SQL*Plus: Release 11.1.0.6.0 - Production on 星期三 12月 7 13:04:51 2011
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
已連接到空閑例程。
 
SQL> startup pfile ='d:\1.txt' mount;
ORACLE 例程已經(jīng)啟動。
 
Total System Global Area 1581916160 bytes
Fixed Size                  1336060 bytes
Variable Size             964693252 bytes
Database Buffers          603979776 bytes
Redo Buffers               11907072 bytes
數(shù)據(jù)庫裝載完畢。
SQL> alter session set events '10015 trace name adjust_scn level 1';
 
會話已更改。
 
 
SQL> alter database open;
 
數(shù)據(jù)庫已更改。
 
SQL> select status from v$instance;
 
STATUS
------------
OPEN
七、啟動過程中，alert日志現(xiàn)ORA-00600[2256]
Completed: ALTER DATABASE   MOUNT
Wed Dec 07 13:06:07 2011
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Completed redo scan
 1 redo blocks read, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 1, block 2, scn 9743796722
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: R:\ORADATA\HZYL\REDO01.LOG
Completed redo application
Completed crash recovery at
 Thread 1: logseq 1, block 3, scn 9743816724
 0 data blocks read, 0 data blocks written, 1 redo blocks read
Wed Dec 07 13:06:08 2011
Thread 1 advanced to log sequence 2
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: R:\ORADATA\HZYL\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Dec 07 13:06:08 2011
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3096.trc  (incident=219072):
ORA-00600: 內(nèi)部錯誤代碼, 參數(shù): [2256], [0], [1073741824], [2], [1153882138], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_219072\hzyl_ora_3096_i219072.trc
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Wed Dec 07 13:06:09 2011
Trace dumping is performing id=[cdmp_20111207130609]
Opening with internal Resource Manager plan
Wed Dec 07 13:06:10 2011
Sweep Incident[219072]: completed
Starting background process FBDA
Starting background process SMCO
Wed Dec 07 13:06:10 2011
FBDA started with pid=26, OS id=2436
Wed Dec 07 13:06:10 2011
SMCO started with pid=27, OS id=2876
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Dec 07 13:06:11 2011
QMNC started with pid=28, OS id=2812
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Wed Dec 07 13:06:15 2011
db_recovery_file_dest_size of 10000 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Dec 07 13:06:25 2011
Completed: alter database open
打開過程中，出現(xiàn)ORA-00600[2256]，但是數(shù)據(jù)庫還是正常打開，具體原因待研究

更多Oracle學(xué)習(xí)：