How to drop corrupt or missing logfiles

When logfiles are not present in the actual locations. may have been dropped by accident.

16:33:41 SYS@eqdd011g SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log'

I tried to add a new one, but it failed as controlfile had the information. Logs were UNUSED in v$log

Elapsed: 00:00:00.68
16:33:49 SYS@eqdd011g SQL> alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' size 5m;
alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' size 5m
*
ERROR at line 1:
ORA-01577: cannot add log file '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' - file already part of database


UNUSED logfiles can be dropped straight

Elapsed: 00:00:00.10
16:34:24 SYS@eqdd011g SQL> alter database drop logfile group 1;

Database altered.

Elapsed: 00:00:00.38

Then added those files again

16:34:41 SYS@eqdd011g SQL> alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' size 5m;

Database altered.

Elapsed: 00:00:00.66

One more attempt to open the database
16:34:46 SYS@eqdd011g SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo02.log'


Elapsed: 00:00:00.13

2nd loggroup this time

16:34:49 SYS@eqdd011g SQL> alter database drop logfile group 2;

Database altered.

Elapsed: 00:00:00.33
16:34:58 SYS@eqdd011g SQL> alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo02.log' size 5m;

Database altered.

Elapsed: 00:00:00.55
16:35:09 SYS@eqdd011g SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'


Elapsed: 00:00:00.17

Log was current and unable to drop, it was not even archived since it was current

16:35:13 SYS@eqdd011g SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance eqdd011g (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'


Elapsed: 00:00:00.05
16:35:20 SYS@eqdd011g SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open


Elapsed: 00:00:00.02

16:36:22 SYS@eqdd011g SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 3
*
ERROR at line 1:
ORA-00350: log 3 of instance eqdd011g (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'


Elapsed: 00:00:00.17
16:38:00 SYS@eqdd011g SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Database altered.

Elapsed: 00:00:02.69
16:38:13 SYS@eqdd011g SQL> alter database drop logfile group 3;

Database altered.

16:39:11 SYS@eqdd011g SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ------ ------------- --------------------
2 1 0 5242880 1 YES UNUSED 0
1 1 91 5242880 1 NO CURREN 3220382 24-SEP-2009 16:38:10

2 rows selected.

Elapsed: 00:00:00.06
16:39:28 SYS@eqdd011g SQL> alter database open;

Database altered.