當前聯(lián)機日志損壞恢復(fù)

我忒忙

環(huán)境模擬
刪除狀態(tài)為active的聯(lián)機日志，然后強行關(guān)閉數(shù)據(jù)庫
處理過程
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1260720 bytes
Variable Size 142607184 bytes
Database Buffers 16777216 bytes
Redo Buffers 7127040 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Alert.log 日志中錯誤
Wed Aug 24 00:26:33 2011
Errors in file /u01/admin/xienfei/udump/xff_ora_9186.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
xff_ora_9186.trc文件中錯誤
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
查詢當前日志組狀態(tài)
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# STATUS MEMBER
———- —————- ———————————————
1 CURRENT /u01/oradata/xienfei/redo01.log
3 INACTIVE /u01/oradata/xienfei/redo03.log
2 ACTIVE /u01/oradata/xienfei/redo02.log
嘗試刪除redo日志
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance xff (thread 1) – cannot drop
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’
發(fā)現(xiàn)是當前日志不能被刪除，嘗試切換日志
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open
在數(shù)據(jù)庫未打開狀態(tài)，不能切換日志，只能嘗試清空日志
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance xff (thread 1)
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’
因為數(shù)據(jù)庫恢復(fù)需要使用，不能被清空，嘗試不完成恢復(fù)
SQL> recover database until cancel;
ORA-00279: change 1272687 generated at 08/24/2011 00:20:05 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_27_756841839.arc
ORA-00280: change 1272687 for thread 1 is in sequence #27
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1272903 generated at 08/24/2011 00:25:17 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_28_756841839.arc
ORA-00280: change 1272903 for thread 1 is in sequence #28
ORA-00278: log file ‘/u01/archive/1_27_756841839.arc’ no longer needed for this
recovery
ORA-00308: cannot open archived log ‘/u01/archive/1_28_756841839.arc’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u01/oradata/xienfei/system01.dbf’
已經(jīng)提示數(shù)據(jù)不一致，嘗試著打開數(shù)據(jù)庫
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u01/oradata/xienfei/system01.dbf’
創(chuàng)建pfile文件，添加隱含參數(shù)，使之不進行檢查點一致性校驗
SQL> create pfile=’/tmp/pfile’ from spfile;
File created.
修改pfile ，添加以下參數(shù)
*._allow_resetlogs_corruption=TRUE
*._allow_error_simulation=TRUE
SQL> shutdown abort
ORACLE instance shut down.
使用pfile打開數(shù)據(jù)庫
SQL> startup pfile=’/tmp/pfile’
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1260720 bytes
Variable Size 150995792 bytes
Database Buffers 8388608 bytes
Redo Buffers 7127040 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database
發(fā)現(xiàn)flashback導致數(shù)據(jù)庫不能被正常打開，嘗試關(guān)閉它
SQL> alter database flashback off;
Database altered.
嘗試直接open數(shù)據(jù)庫
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
根據(jù)提示，使用resetlogs打開數(shù)據(jù)庫
SQL> alter database open resetlogs;
Database altered.
查詢?nèi)罩緺顟B(tài)
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# STATUS MEMBER
———- —————- ———————————————
3 UNUSED /u01/oradata/xienfei/redo03.log
2 UNUSED /u01/oradata/xienfei/redo02.log
1 CURRENT /u01/oradata/xienfei/redo01.log
因為group1錯誤，而當前日志組在group 1上，所以切換日志組
SQL> alter system switch logfile;
System altered.
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# STATUS MEMBER
———- —————- ———————————————
3 UNUSED /u01/oradata/xienfei/redo03.log
2 CURRENT /u01/oradata/xienfei/redo02.log
1 ACTIVE /u01/oradata/xienfei/redo01.log
SQL> alter system checkpoint;
System altered.
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# STATUS MEMBER
———- —————- ———————————————
3 UNUSED /u01/oradata/xienfei/redo03.log
2 CURRENT /u01/oradata/xienfei/redo02.log
1 INACTIVE /u01/oradata/xienfei/redo01.log
刪除有問題的group 1日志組
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter system switch logfile;
System altered.
添加日志組并檢查是否正確
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# STATUS MEMBER
———- —————- ———————————————
3 CURRENT /u01/oradata/xienfei/redo03.log
2 ACTIVE /u01/oradata/xienfei/redo02.log
SQL> alter database add logfile group 1 ‘/u01/oradata/xienfei/redo01.log’ size 50m reuse;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# STATUS MEMBER
———- —————- ———————————————
3 ACTIVE /u01/oradata/xienfei/redo03.log
2 ACTIVE /u01/oradata/xienfei/redo02.log
1 CURRENT /u01/oradata/xienfei/redo01.log
注意：根據(jù)oracle官方建議，使用oracle隱含參數(shù)運行數(shù)據(jù)庫可能存在很多不穩(wěn)定因素，建議立即導出數(shù)據(jù)庫數(shù)據(jù)，然后新建庫，重新導入數(shù)據(jù)

更多精彩Oracle  內(nèi)容請關(guān)注我:

我忒忙