oracle中一次redolog丢失的恢复

接到同事的电话，某省的一个用于监控的siteview数据库启动不了了，登录后检查alertlog发现：

Completed first pass scan
 9868 redo blocks read, 655 data blocks need recovery
Thu Apr 19 16:52:42 2007
Started recovery at
 Thread 1: logseq 700, block 194931, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 3 Seq 700 Reading mem 0
  Mem# 0 errs 0: C:ORACLEORADATAIMALLSVREDO03.LOG
Thu Apr 19 16:52:43 2007
Ended recovery at
 Thread 1: logseq 700, block 204799, scn 0.147914177
 655 data blocks read, 655 data blocks written, 9868 redo blocks read
Crash recovery completed successfully
Thu Apr 19 16:52:43 2007
Errors in file c:oracleadminimallsvbdumpimallsv_lgwr_4180.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:ORACLEORADATAIMALLSVREDO01.LOG'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
 
Thu Apr 19 16:52:43 2007
Errors in file c:oracleadminimallsvbdumpimallsv_lgwr_4180.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:ORACLEORADATAIMALLSVREDO01.LOG'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
 
ORA-313 signalled during: alter database open...
原来是redolog被干掉了，其实对于redolog的丢失，如果丢失的不是active或者current（这个在win中一般不会被删除，因为使用时被锁定），我们可以用以下的方式恢复：

C:Documents and SettingsAdministrator>sqlplus "/ as sysdba"
 
SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 4月 19 17:05:03 2007
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
 
SQL> shutdown immediate
ORA-01109: 数据库未打开
 
 
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
 
Total System Global Area  487660924 bytes
Fixed Size                   454012 bytes
Variable Size             209715200 bytes
Database Buffers          276824064 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
SQL> select * from v$log;
 
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
         1          1        699  104857600          1 NO  INACTIVE
    147462354 17-4月 -07
 
         2          1          0  104857600          1 NO  UNUSED
            0 16-4月 -07
 
         3          1        700  104857600          1 NO  INVALIDATED
    147678398 17-4月 -07
 
 
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR 位于第 1 行:
ORA-01139: RESETLOGS 选项仅在不完全数据库恢复后有效
 
 
SQL> recover database until cancel;
完成介质恢复。
SQL> alter database open resetlogs;
 
数据库已更改。
 
SQL>
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
 
Total System Global Area  487660924 bytes
Fixed Size                   454012 bytes
Variable Size             209715200 bytes
Database Buffers          276824064 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。
至此，恢复完成。^_^