How to Resize redo log file

Re-sizing the Redo Log file in Oracle:

When people ask (especially in interview) "how to resize the redo log file in oracle", every one may get confused and people will start thinking about the command to resize the log file. before starting this blog, let me make it clear that WE CANNOT RESIZE THE REDO LOG FILE USING SINGLE COMMAND, WE NEED TO DROP THE EXISTING REDO LOG FILE AND RECREATE THE REDO LOG FILE. This is the only method to resize the redo log file in oracle.

Below are the steps :

Before dropping the redo log file we need to keep the below this in mind:

1. A database requires atleast two redo log file, regardless number of members
2. We cannot drop the active or current redo log file
3. We have to change the active and current redo log file to inactive and then we can drop the redo log file

In  below example i have 4 redo log files which is of 50MB and i will resizing it to 100MB

Step 1: Check the status of Redo log file

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

              GROUP#  SEQUENCE#      BYTES    ARC    STATUS
              ----------    ----------    ----------      -----       -------------
                    1          5   52428800      YES          INACTIVE
                    2          6   52428800      YES          ACTIVE
                    3          7   52428800      NO          CURRENT
                    4          4   52428800     YES          INACTIVE

So as i mentioned earlier that we cannot drop the active and current redo log file, we cannot drop the group # 2 and 3 


Step 2: Forcing the checkpoint

There are two SQL command to force the checkpoint :

1. alter system checkpoint;
2. alter system checkpoint global;

The first command will explicitly forces oracle to perform a checkpoint for either the current instance and all instances. Forcing the checkpoint ensured that all changes to the database buffers are written  to the data files. The 2nd sql command i.e., global checkpoint is not finished until all instance that requires 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          5       52428800     YES      INACTIVE
         2          6      52428800     YES       INACTIVE
         3          7      52428800     NO       CURRENT
         4          4      52428800    YES       INACTIVE

Since the status of group 1,2,4 are inactive .so we will drop the group 1 and group 2 redo log file.

Step 3: Drop the redo log file

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database drop logfile group 2;
Database altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES    ARC    STATUS
----------    ----------    ----------    ---     ----------------
         3          7               52428800      NO       CURRENT
         4          4             52428800       YES      INACTIVE

Make sure that you delete the redo log file  by OS command before creating the log file. else we will face the below error:

SQL> alter database add logfile group 1 '\u01\phil\oradata\orcl\redo01.log' size 100m;
alter database add logfile group 1 '\u01\phil\oradata\orcl\redo01.log' size 100m
*
ERROR at line 1:
ORA-00301: error in adding log file '\u01\phil\oradata\orcl\redo01.log' - file cannot be created
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

Step 4: Create a new log file

SQL> alter database add logfile group 1 '\u01\phil\oradata\orcl\redo01.log' size 100m;
Database altered.

SQL> alter database add logfile group 2 '\u01\phil\oradata\orcl\redo02.log' size 100m;
Database altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#      SEQUENCE#      BYTES     ARC       STATUS
----------    ----------     ----------       ---      ----------------
         1          0      104857600       YES     UNUSED
         2          0      104857600       YES     UNUSED
         3          7       52428800        NO      CURRENT
         4          4       52428800       YES      INACTIVE

SQL> alter system switch logfile ;
System altered.

SQL> alter system switch logfile ;
System altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1          8  104857600     YES     ACTIVE
         2          9  104857600     NO      CURRENT
         3          7   52428800     YES     ACTIVE
         4          4   52428800     YES     INACTIVE

SQL> alter system checkpoint global;
System altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1          8    104857600     YES     INACTIVE
         2          9    104857600     NO     CURRENT
         3          7     52428800     YES     INACTIVE
         4          4     52428800    YES      INACTIVE

Step 5 : Now drop the remaining two redo log file

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter database drop logfile group 4;
Database altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1          8  104857600      YES      INACTIVE
         2          9  104857600      NO       CURRENT

Step 6: Create the redo log file now

SQL> alter database add logfile group 3 '\u01\phil\oradata\orcl\redo03.log' size 100m;
Database altered.

SQL> alter database add logfile group 4 '\u01\phil\oradata\orcl\redo03.log' size 100m;
Database altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1          8        104857600      YES       INACTIVE
         2          9        104857600      NO        CURRENT
         3          0        104857600     YES        UNUSED
         4          0        104857600     YES        UNUSED

SQL> alter system switch logfile ;
System altered.

SQL> alter system switch logfile ;
System altered.

I think the below details also will be relevant for this post. i.e. status of redo log file. The log file has below status :

USED:  Indicates that the log file has been just added

CURRENT: Indicates the valid log which is currently in use.

ACTIVE: Indicates the valid log which is currently not in use.

INACTIVE: Indicates that the log is no longer required for recovery.

Hope above post helps. And always, any mistake or if i miss any point please let me know :)