1) 查看当前日志组状态，第2组日志为当前日志组，另外两组为非活动状态且已归档，还有，每组日志均为 2个成员
SQL> select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 YES INACTIVE
2 2 NO CURRENT
3 2 YES UNUSED
SQL> select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/ORA11GR2/redo01.log
1 /u01/app/oracle/oradata/ORA11GR2/redo01_a.log
2 /u01/app/oracle/oradata/ORA11GR2/redo02_a.log
2 /u01/app/oracle/oradata/ORA11GR2/redo02.log
3 /u01/app/oracle/oradata/ORA11GR2/redo03_a.log
3 /u01/app/oracle/oradata/ORA11GR2/redo03.log
6 rows selected.
2) 删除第3组日志文件
[oracle@wang ~]$ cd /u01/app/oracle/oradata/ORA11GR2/
[oracle@wang ORA11GR2]$ ls redo*
redo01_a.log redo02_a.log redo03_a.log
redo01.log redo02.log redo03.log
[oracle@wang ORA11GR2]$
[oracle@wang ORA11GR2]$ rm redo03_a.log
[oracle@wang ORA11GR2]$ rm redo03.log
[oracle@wang ORA11GR2]$
[oracle@wang ORA11GR2]$ ls redo*
redo01_a.log redo01.log redo02_a.log redo02.log
[oracle@wang ORA11GR2]$
3) 重启数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 503319672 bytes
Database Buffers 322961408 bytes
Redo Buffers 2392064 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 29933
Session ID: 1 Serial number: 9
4) 此时我们发现 alert 日志有如下错误：
[oracle@wang ORA11GR2]$ cd trace/
[oracle@wang trace]$ pwd
/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace
[oracle@wang
trace]$ tail
-100f alert_ORA11GR2.log
============================================================================
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
Fri Sep 23 13:29:02 2016
ARC0 started with pid=20, OS id=29945
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Errors in file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_lgwr_29821.trc:
ORA-00313: open failed for members of log group 1 of thread 1
Errors in file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_lgwr_29821.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/ORA11GR2/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_lgwr_29821.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/ORA11GR2/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_29933.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORA11GR2/redo03.log'
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORA11GR2/redo03_a.log'
Fri Sep 23 13:29:03 2016
ARC1 started with pid=21, OS id=29975
USER (ospid: 29933): terminating the instance due to error 313
System state dump requested by (instance=1, osid=29933), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_diag_29801_20160923132903.trc
Dumping
diagnostic data in directory=[cdmp_20160923132903], requested by (instance=1,
osid=29933), summary=[abnormal instance termination].
Instance terminated by USER, pid = 29933
============================================================================================
5) 启动数据库到 mount 状态
SQL> conn / as sysdba
Connected to an idle instance.
SQL>
SQL> startup mount;
(此步骤不需要数据文件，只需要控制文件就可以)
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 503319672 bytes
Database Buffers 322961408 bytes
Redo Buffers 2392064 bytes
Database mounted.
6) clear 修复日志文件
（clear的动作就是在操作系统redo日志对应路径下创建日志文件，只是创建的文件是新的，且是未使用过的）
SQL> alter database clear logfile group 3;
Database altered.
——验证：
[oracle@wang ORA11GR2]$ ls redo*
redo01_a.log redo02_a.log redo03_a.log
redo01.log redo02.log redo03.log
[oracle@wang ORA11GR2]$
7） 打开数据库
SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
——再次验证：
SQL> select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 YES INACTIVE
2 2 NO CURRENT
3 2 YES UNUSED
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORA11GR2/redo03.log
2 /u01/app/oracle/oradata/ORA11GR2/redo02.log
1 /u01/app/oracle/oradata/ORA11GR2/redo01.log
1 /u01/app/oracle/oradata/ORA11GR2/redo01_a.log
2 /u01/app/oracle/oradata/ORA11GR2/redo02_a.log
3 /u01/app/oracle/oradata/ORA11GR2/redo03_a.log
6 rows selected.
完成！！！！！！！！！！！！！！！！！！！