Resize Oracle 11g redo log files

Check logfile groups and status:

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TI NEXT_CHANGE# NEXT_TIM
---------------- ------------- -------- ------------ --------
         1          1       4441   52428800        512          1 NO
INACTIVE              36466120 06.09.14     36478293 06.09.14

         2          1       4442   52428800        512          1 NO
CURRENT               36478293 06.09.14   2,8147E+14

         3          1       4440   52428800        512          1 NO
INACTIVE              36431037 06.09.14     36466120 06.09.14


SQL> select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------

IS_
---
         3         ONLINE
E:\APP\ORACLE\ORADATA\NSM\REDO03.LOG
NO

         2         ONLINE
E:\APP\ORACLE\ORADATA\NSM\REDO02.LOG
NO

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------

IS_
---

         1         ONLINE
E:\APP\ORACLE\ORADATA\NSM\REDO01.LOG
NO


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

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE
 
Add an additional logfile group (this is optional, but the database should have at least 2 groups during resize activity):

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 'E:\APP\ORACLE\ORADATA\NSM\REDO04.LOG' S IZE 200M;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1         50 INACTIVE
         2         50 CURRENT
         3         50 INACTIVE
         4        200 UNUSED

Recreate logfile groups:

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         2         50 CURRENT
         3         50 INACTIVE
         4        200 UNUSED
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 'E:\APP\ORACLE\ORADATA\NSM\REDO01.LOG' SIZE 200M REUSE;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 UNUSED
         2         50 CURRENT
         3         50 INACTIVE
         4        200 UNUSED

Make a logswitch and a checkpoint to advance with the CURRENT and ACTIVE logfile:

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 CURRENT
         2         50 ACTIVE
         3         50 INACTIVE
         4        200 UNUSED

SQL> ALTER SYSTEM CHECKPOINT;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 CURRENT
         2         50 INACTIVE
         3         50 INACTIVE
         4        200 UNUSED

Recreate other logfile groups reusing exiting redo log files:

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 CURRENT
         3         50 INACTIVE
         4        200 UNUSED

SQL> ALTER DATABASE ADD LOGFILE GROUP 2 'E:\APP\ORACLE\ORADATA\NSM\REDO02.LOG' SIZE 200M REUSE;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 CURRENT
         2        200 UNUSED
         3         50 INACTIVE
         4        200 UNUSED

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 'E:\APP\ORACLE\ORADATA\NSM\REDO03.LOG' SIZE 200M REUSE;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 CURRENT
         2        200 UNUSED
         3        200 UNUSED
         4        200 UNUSED

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 ACTIVE
         2        200 CURRENT
         3        200 UNUSED
         4        200 UNUSED

Remove additional logfile group:

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 ACTIVE
         2        200 CURRENT
         3        200 UNUSED

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 ACTIVE
         2        200 ACTIVE
         3        200 CURRENT

SQL> ALTER SYSTEM CHECKPOINT;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 INACTIVE
         2        200 INACTIVE
         3        200 CURRENT

SQL> SELECT * FROM V$LOGFILE;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------

IS_
---
         3         ONLINE
E:\APP\ORACLE\ORADATA\NSM\REDO03.LOG
NO

         2         ONLINE
E:\APP\ORACLE\ORADATA\NSM\REDO02.LOG
NO

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------

IS_
---

         1         ONLINE
E:\APP\ORACLE\ORADATA\NSM\REDO01.LOG
NO


SQL>EXIT

DELETE REDOLOG4.LOG FILE FROM OS