Resize Redo log files

Oracle Database did not allow to resize redo log files since we need to drop and re-create to overcome the situation.

Following points need to be considered when we drop the redo log files

1. Can not drop redo log files if it's in Current or Active status
2. Database requires at least two redo log groups regardless number of members
3. OS files are wont delete when we drop redo log files. So delete from OS to create redo log files with same name

  

   Steps

1. Check the redo log status

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

    GROUP#      BYTES ARC STATUS              MEMBERS
---------- ---------- --- ---------------- ----------
         1  209715200 YES INACTIVE                  2
         2  209715200 YES INACTIVE                  2
         3  209715200 NO  CURRENT                   2

In this case we are try to resize group#3, but we can not drop as the status is CURRENT


2. Switch CURRENT redo log group

SQL> alter system switch logfile;

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

    GROUP#      BYTES ARC STATUS              MEMBERS
---------- ---------- --- ---------------- ----------
         1  209715200 NO  CURRENT                   2
         2  209715200 YES INACTIVE                  2
         3  209715200 YES ACTIVE                    2

Need to change status from ACTIVE to INACTIVE for drop redo log file


3. Change the status of redo log group. checkpoint force to write all changes from the database buffers to data files.

SQL> alter system checkpoint global;

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

    GROUP#      BYTES ARC STATUS              MEMBERS
---------- ---------- --- ---------------- ----------
         1  209715200 NO  CURRENT                   2
         2  209715200 YES INACTIVE                  2
         3  209715200 YES INACTIVE                  2

4. Check the members of group and drop the redo log group

SQL> select member from v$logfile where group#=3;
MEMBER
--------------------------------
C:\ORADATA\DEVCDB\REDO030.LOG
C:\ORADATA\DEVCDB\REDO031.LOG 

SQL> alter database drop logfile group 3;

5. Delete specific redo log files from Operating system

6. Create the redo log files with changes

For Multiple File

SQL> alter database add logfile group 3 ('C:\ORADATA\DEVCDB\REDO030.LOG','C:\ORADATA\DEVCDB\REDO031.LOG') size 500M;

For Single File

SQL> alter database add logfile group 3 'C:\ORADATA\DEVCDB\REDO031.LOG' size 500M;

7. Check whether added redo log files are available

SQL> select member from v$logfile where group#=3;
MEMBER
--------------------------------
C:\ORADATA\DEVCDB\REDO030.LOG
C:\ORADATA\DEVCDB\REDO031.LOG 

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

    GROUP#      BYTES ARC STATUS              MEMBERS
---------- ---------- --- ---------------- ----------
         1  209715200 NO  CURRENT                   2
         2  209715200 YES INACTIVE                  2
         3  209715200 YES UNUSED                  2