To resize redolog file in oracle

To resize redolog file in oracle

We cannot resize the redo log files. We must drop the redolog file and recreate them .This is only method to resize the redo log files. A database requires atleast two groups of redo log files,regardless the number of the members. We cannot the drop the redo log file if its status is current or active . We have change the status to "inactive" then only we can drop it.

When a redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure. After dropping a redo log file, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped redo log file. In my case i have four redo log files and they are of 50MB in size .I will resize to 100 MB.  Below are steps to resize the redo log files.

Step 1 : Check the Status of Redo Logfile 
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

Here,we cannot drop the current and active redo log file .

Step  2 :  Forcing a Checkpoint  :

The 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          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 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

Step  4  : Create new redo log file 

If we don't delete the old redo logfile by OS command when creating the log file with same name then face the below error . Therefore to solve it delete the file by using OS command .

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

SQL> alter database add logfile group 1 'C:\app\neerajs\oradata\orcl\redo01.log' size 100m;
Database altered.

SQL> alter database add logfile group 2 'C:\app\neerajs\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

Step 5 :  Now drop the remaining two old redo log file 
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

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 
SQL> alter database add logfile group 3 'C:\app\neerajs\oradata\orcl\redo03.log' size 100m;
Database altered.

SQL> alter database add logfile group 4 'C:\app\neerajs\oradata\orcl\redo04.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