DB : Resizing online redo log files

Step 1 : Check the Status of Redo Logfile 

SQL> col MEMBER for a50
SQL> select group#,sequence#,bytes,archived,status from v$log;

    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1        119 1048576000 NO  CURRENT
         2        118 1048576000 YES INACTIVE


SQL> SELECT a.group#, a.member, b.bytes  FROM v$logfile a, v$log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                  BYTES
---------- -------------------------------------------------- ----------
         2 /d01/oracle/DEV01/db/apps_st/data/log02a.dbf       1048576000
         2 /d01/oracle/DEV01/db/apps_st/data/log02b.dbf       1048576000
         1 /d01/oracle/DEV01/db/apps_st/data/log01a.dbf       1048576000
         1 /d01/oracle/DEV01/db/apps_st/data/log01b.dbf       1048576000

Step 2: Since here only two groups are there, add two more redo groups(group 3 and Group 4) of size 300 MB

SQL> alter database add logfile group 3 ('/d01/oracle/DEV01/db/apps_st/data/log03a.log','/d01/oracle/DEV01/db/apps_st/data/log03b.log') size 300M;

Database altered.

SQL> alter database add logfile group 4 ('/d01/oracle/DEV01/db/apps_st/data/log04a.log','/d01/oracle/DEV01/db/apps_st/data/log04b.log') size 300M;

Database altered.

SQL> alter system switch logfile;

System altered.

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

    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1        119 1048576000 YES ACTIVE
         2        118 1048576000 YES INACTIVE
         3        120  314572800 YES ACTIVE
         4        121  314572800 NO  CURRENT

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

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

    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1        123 1048576000 NO  CURRENT
         2        122 1048576000 YES ACTIVE
         3        120  314572800 YES ACTIVE
         4        121  314572800 YES ACTIVE

SQL> alter system switch logfile;

System altered.

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

    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1        123 1048576000 YES ACTIVE
         2        122 1048576000 YES ACTIVE
         3        124  314572800 NO  CURRENT
         4        121  314572800 YES ACTIVE

SQL> alter system switch logfile;

System altered.

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

    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1        123 1048576000 YES ACTIVE
         2        122 1048576000 YES ACTIVE
         3        124  314572800 YES ACTIVE
         4        125  314572800 NO  CURRENT

Step  3:  Forcing a Checkpoint 
SQL statement alter system checkpoint explicitly forces Oracle to perform a checkpoint for either the current instance or all instances. Forcing a checkpoint ensures that all changes to the database buffers are written to the datafiles on disk .A global checkpoint is not finished until all instances that require recovery have been recovered.


SQL> alter system checkpoint global;

System altered.

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

    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1        123 1048576000 YES INACTIVE
         2        122 1048576000 YES INACTIVE
         3        124  314572800 YES INACTIVE
         4        125  314572800 NO  CURRENT

Step 4: Drop the redo logfile group 1 

SQL> alter database drop logfile group 1;

Database altered.

Step 5: Remove the group 1 redo logfile physically from the storage

Step 6: Create new redo log file of group 1

SQL> alter database add logfile group 1 ('/d01/oracle/DEV01/db/apps_st/data/log01a.log','/d01/oracle/DEV01/db/apps_st/data/log01b.log') size 300M;

Database altered.

Step 7: Drop the redo logfile group 2

SQL> alter database drop logfile group 2;

Database altered.

Step 8: Remove the group 2 redo logfile physically from the storage

Step 9: Create new redo log file of group 2

SQL> alter database add logfile group 2 ('/d01/oracle/DEV01/db/apps_st/data/log02a.log','/d01/oracle/DEV01/db/apps_st/data/log02b.log') size 300M;

Database altered.

Step 10: Check the Status of Redo Logfile

SQL> SELECT a.group#, a.member, b.bytes  FROM v$logfile a, v$log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                  BYTES
---------- -------------------------------------------------- ----------
         2 /d01/oracle/DEV01/db/apps_st/data/log02a.log       314572800
         2 /d01/oracle/DEV01/db/apps_st/data/log02b.log       314572800
         1 /d01/oracle/DEV01/db/apps_st/data/log01a.log       314572800
         1 /d01/oracle/DEV01/db/apps_st/data/log01b.log       314572800
         3 /d01/oracle/DEV01/db/apps_st/data/log03a.log       314572800
         3 /d01/oracle/DEV01/db/apps_st/data/log03b.log       314572800
         4 /d01/oracle/DEV01/db/apps_st/data/log04a.log       314572800
         4 /d01/oracle/DEV01/db/apps_st/data/log04b.log       314572800

8 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

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

    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1        130  314572800 NO  CURRENT
         2        127  314572800 YES INACTIVE
         3        128  314572800 YES INACTIVE
         4        129  314572800 YES ACTIVE


Error: 

SQL> alter database add logfile group 1 ('/d01/oracle/DEV01/db/apps_st/data/log01a.log','/d01/oracle/DEV01/db/apps_st/data/log01b.log') size 300M;
alter database add logfile group 1 ('/d01/oracle/DEV01/db/apps_st/data/log01a.log','/d01/oracle/DEV01/db/apps_st/data/log01b.log') size 300M
*
ERROR at line 1:
ORA-00301: error in adding log file '/d01/oracle/DEV01/db/apps_st/data/log01a.log' - file cannot be created
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

Solution: 
If the dropped redo logfile is not removed physically from OS level this error will arise, so after droping the redo log file group remove the dropped redo log file at OS level