Today, due to a network issue the host (Solaries) lost contact with the NetApp filer and it corrupted some datafiles and redo log files.
The solution was to recreate all indexes from the corrupted datafile in a new tablespace and drop the corrupted tablespace.
The archived log process hung because it could not archive the log 464704 which got corrupted, the solution was to clear the redo log and manually switch the logs to test the fix.
1- Archived log hung because the online redo log was corrupted and could not be archived.

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
1 1 464713 104857600 2 NO CURRENT
2 1 464711 104857600 2 NO INACTIVE
3 1 464709 104857600 2 NO INACTIVE
4 1 464704 104857600 2 NO INACTIVE
5 1 464712 104857600 2 NO INACTIVE
6 1 464710 104857600 2 NO INACTIVE

6 rows selected.

2- When tried to clear the online redo log it wouldn’t let us do it because of the datafile corrupted as well.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4
*
ERROR at line 1:
ORA-00393: log 4 of thread 1 is needed for recovery of offline datafiles
ORA-00312: online log 4 thread 1: ‘/oracle/EPB/WISPRD/redo/redo2/redo4b.log’
ORA-00312: online log 4 thread 1: ‘/oracle/EPB/WISPRD/redo/redo1/redo4a.log’
ORA-01110: data file 281: ‘/oracle/EPB/WISPRD/data/cust_index_jul2007.dbf’

3- Found an additional clause to force the CLEAR.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4 UNRECOVERABLE DATAFILE ;

Database altered.

4- Online redo log group is now clear but keep in mind that we don’t have that archive log (464704) and recoverability is compromised at this point. We need to take a full snap backup.

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
1 1 464713 104857600 2 NO INACTIVE
2 1 464711 104857600 2 YES INACTIVE
3 1 464715 104857600 2 NO CURRENT
4 1 0 104857600 2 YES UNUSED
5 1 464712 104857600 2 YES INACTIVE
6 1 464714 104857600 2 NO INACTIVE
6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
1 1 464719 104857600 2 YES INACTIVE
2 1 464717 104857600 2 YES INACTIVE
3 1 464715 104857600 2 YES INACTIVE
4 1 464720 104857600 2 YES ACTIVE
5 1 464718 104857600 2 YES INACTIVE
6 1 464721 104857600 2 NO CURRENT

6 rows selected.
SQL> DROP TABLESPACE CUST_INDEX_JUL2007 INCLUDING CONTENTS;

Tablespace dropped.
SQL> select distinct status from v$datafile;

STATUS
——-
ONLINE
SYSTEM

After all this a full backup was taken.

A hope that helps…