一次日誌檔案損壞的恢復

出差回來,開發人員給我說,35資料庫不能啟動了[@more@]

redo log file 損壞的處理過程
一 檢視日誌

Thu Mar 29 09:15:21 2007
Errors in file d:oracleadmings12366bdumpgs12366_lgwr_2052.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:ORACLEORADATAGS12366REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系統找不到指定的檔案。

Thu Mar 29 09:15:21 2007
Errors in file d:oracleadmings12366bdumpgs12366_lgwr_2052.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:ORACLEORADATAGS12366REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系統找不到指定的檔案。

Thu Mar 29 09:15:21 2007
ORA-313 signalled during: alter database open
...
Thu Mar 29 09:16:38 2007
alter database open
Thu Mar 29 09:16:38 2007
Errors in file d:oracleadmings12366bdumpgs12366_lgwr_2052.trc:
ORA-00341: log 1 of thread 1, wrong log # 2 in header
ORA-00312: online log 1 thread 1: 'D:ORACLEORADATAGS12366REDO01.LOG'

Thu Mar 29 09:16:38 2007
Errors in file d:oracleadmings12366bdumpgs12366_lgwr_2052.trc:
ORA-00341: log 1 of thread 1, wrong log # 2 in header
ORA-00312: online log 1 thread 1: 'D:ORACLEORADATAGS12366REDO01.LOG'

Thu Mar 29 09:16:38 2007
ORA-341 signalled during: alter database open...

二 檢視日誌當前狀態
C:Documents and Settingsgs12366>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Mar 30 09:39:42 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination d:oracleora92RDBMS
Oldest online log sequence 0
Current log sequence 531
SQL> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
------------------------------------------------------------------------

1 ONLINE
D:ORACLEORADATAGS12366REDO01.LOG

2 ONLINE
D:ORACLEORADATAGS12366REDO02.LOG

3 STALE ONLINE
D:ORACLEORADATAGS12366REDO03.LOG

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- -------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 104857600 1 NO UNUSED
0 27-MAR-07

2 1 530 104857600 1 NO INACTIVE
47709153 27-MAR-07

3 1 531 104857600 1 NO INVALIDATED
48070450 28-MAR-07

SQL>

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

-- 確定 丟失了D:ORACLEORADATAGS12366REDO01.LOG
-- 解決過程
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1670457484 bytes
Fixed Size 456844 bytes
Variable Size 595591168 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> select group#,archived,status from v$log;

GROUP# ARC STATUS
---------- --- ----------------
1 NO UNUSED
2 NO INACTIVE
3 NO INVALIDATED

SQL> alter database drop logfile group 1;

Database altered.

SQL> select group#,archived,status from v$log;

GROUP# ARC STATUS
---------- --- ----------------
2 NO INACTIVE
3 NO INVALIDATED

SQL> alter database open;

Database altered.

SQL> select group#,archived,status from v$log;

GROUP# ARC STATUS
---------- --- ----------------
2 NO CURRENT
3 NO INACTIVE

SQL> alter database add logfile group 1 'd:oracleoradatags12366redo01.log' s
ize 104857600 reuse;

Database altered.

SQL> select group#,archived,status from v$log;

GROUP# ARC STATUS
---------- --- ----------------
1 YES UNUSED
2 NO CURRENT
3 NO INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#,archived,status from v$log;

GROUP# ARC STATUS
---------- --- ----------------
1 NO INACTIVE
2 NO INACTIVE
3 NO CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select group#,archived,status from v$log;

GROUP# ARC STATUS
---------- --- ----------------
1 NO INACTIVE
2 NO CURRENT
3 NO ACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#,archived,status from v$log;

GROUP# ARC STATUS
---------- --- ----------------
1 NO CURRENT
2 NO ACTIVE
3 NO INACTIVE