Resizing/Recreating Online Redolog file

Step 1 :- Check your online redo log file group and members

sql> select  a.group#, a.member, b.bytes
  2  FROM v$logfile a, v$log b WHERE a.group# = b.group#;

group#                  member                                                                          bytes

1           D:\oracle\product\10.2.0\oradata\orcl\redo01.log           52428800

2          D:\oracle\product\10.2.0\oradata\orcl\redo02.log          52428800

3          D:\oracle\product\10.2.0\oradata\orcl\redo03.log          52428800

Step 2:

Forcing a log switch logfile

step 3:

sql> select group#, status from v$log;

group#       status

1                     inactive

2                   current

3                    inactive

sql> alter system switch logfile;

sql> alter system switch logfile;

sql> select group#, status from v$log;

group#       status

1                   active

2                   active

3                 current

Now we try to drop the group 1, we can face errors

 
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance ORA920 (thread 1)
ORA-00312: online log 1 thread 1: ‘file_name’

sql>alter system checkpoint global;

system altered.

sql> alter database drop logfile group 1;

Database altered.

Step 4: Recreate the redolog file

SQL> alter database add logfile group 1 (‘D:\oracle\product\10.2.0\oradata\orcl\redo01.log’) size 100m reuse;

Database altered.

sql> select group#, status from v$log;

group#       status

1                     unused

2                  in active

3                    current

V$LOG

• UNUSED – Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
• CURRENT – Current redo log. This implies that the redo log is active. The redo log could be open or closed.
• ACTIVE – Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
• CLEARING – Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
• CLEARING_CURRENT – Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
• INACTIVE – Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
• INVALIDATED – Archived the current redo log without a log switch.

Why we are not altering Resize logfile

Refer :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:55812348055