實驗環境:
OEL5.6 Oracle11.2.0.1
實驗開始:
數據庫運行狀態,刪除所有日志:
[oracle@ITAA TEST]$ ls
control01.ctl redo01.log sysaux01.dbf undotbs01.dbf
data_ol01.dbf redo02.log system01.dbf users01.dbf
example01.dbf redo03.log temp01.dbf
[oracle@ITAA TEST]$ rm -rf *.log
在數據庫中切換日志組:
SYS@TEST>alter system switch logfile;
System altered.
SYS@TEST>/
System altered.
SYS@TEST>/
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SYS@TEST>insert into t select * from dba_objects;
72504 rows created.
SYS@TEST>/
72504 rows created.
SYS@TEST>/
72504 rows created.
SYS@TEST>/
72504 rows created.
SYS@TEST>/
72504 rows created.
SYS@TEST>/
此時數據庫hung住,所有業務都暫停,alert日志報錯如下:
Mon Jul 13 17:03:28 2015
Errors in file /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_m000_23367.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/TEST/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_m000_23367.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/TEST/redo02.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_m000_23367.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/TEST/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
暫時關閉監聽,防止新業務占滿session,此時新連接可以正常連入數據庫。
這時候不要停庫,因為數據庫已經無法正常關閉,一旦數據庫down掉,必然會丟失數據。
此時使用sysdba連入數據庫,直接clear日志組,數據庫即可恢復正常運行。
SYS@TEST>alter database clear unarchived logfile group 1;
Database altered.
SYS@TEST>alter database clear unarchived logfile group 2;
Database altered.
SYS@TEST>alter database clear unarchived logfile group 3;
Database altered.
數據庫運行恢復正常。