DBA Tips for Resizing Online Redo Logs!

 Author: Jeff

Being Oracle database administrator you must be aware that the current redo log file gets filled up so frequently and so oftten we have to resize the redo logs. Basically when an online redo log gets filled up then Oracle automatically performs a log switch and starts writing change vectors to the next redo log group.

Although it is a simple task to resize online redo logs on a RAC environment but it should be handled with great care because if your online redo logs are damaged then your whole database gets affected. Today we will discuss some great tips that will help you resize online redo logs while the database is running.

Get Current Online Redo Log Groups:

First of all we will run below command to get current online redo log groups and their sizes.

SELECT LF.GROUP#, LF.MEMBER, L.BYTES FROM V$LOGFILE LF, V$LOG L WHERE LF.GROUP# = L.GROUP#;

GROUP#                                        MEMBER                                                  BYTES
------------- -------------------------------------------------------------------            -------------------
    1             /U03/APP/ORADATA/ORA920/REDO_G01A.LOG            401,439,200
    1             /U04/APP/ORADATA/ORA920/REDO_G01B.LOG            401,439,200
    1             /U05/APP/ORADATA/ORA920/REDO_G01C.LOG            401,439,200
    1             /U06/APP/ORADATA/ORA920/REDO_G01D.LOG            401,439,200
    2             /U03/APP/ORADATA/ORA920/REDO_G02A.LOG            401,439,200
    2             /U04/APP/ORADATA/ORA920/REDO_G02B.LOG            401,439,200
    2             /U05/APP/ORADATA/ORA920/REDO_G02C.LOG            401,439,200
    2             /U06/APP/ORADATA/ORA920/REDO_G02D.LOG            401,439,200
    3             /U03/APP/ORADATA/ORA920/REDO_G03A.LOG            401,439,200
    3             /U04/APP/ORADATA/ORA920/REDO_G03B.LOG            401,439,200
    3             /U05/APP/ORADATA/ORA920/REDO_G03C.LOG            401,439,200
    3             /U06/APP/ORADATA/ORA920/REDO_G03D.LOG            401,439,200
    4             /U03/APP/ORADATA/ORA920/REDO_G04A.LOG            401,439,200
    4             /U04/APP/ORADATA/ORA920/REDO_G04B.LOG            401,439,200
    4             /U05/APP/ORADATA/ORA920/REDO_G04C.LOG            401,439,200
    4             /U06/APP/ORADATA/ORA920/REDO_G04D.LOG            401,439,200

Force a Log Switch:

Now we will force a log switch until the last redo log is marked as CURRENT. First we will view V$LOG to determine if our last redo log is marked CURRENT or not.

SELECT GROUP#, STATUS FROM V$LOG;

GROUP#       STATUS
-------------    -------------------
      1             CURRENT
      2             INACTIVE
      3             INACTIVE
      4             INACTIVE

As our last redo log is not marked CURRENT so we will keep on running below command until we get last redo log marked as CURRENT.

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;

SELECT GROUP#, STATUS FROM V$LOG;

GROUP#       STATUS
-------------    -------------------
      1             INACTIVE
      2             INACTIVE
      3             INACTIVE
      4             CURRENT

Drop First Online Redo Log:

In this step we will be dropping our first online redo log group.

ALTER DATABASE DROP LOGFILE GROUP 1;

If your logfile group has an active status then you can avoid errors by performing a checkpoint on the database.

ALTER SYSTEM CHECKPOINT GLOBAL;
ALTER DATABASE DROP LOGFILE GROUP 1;

Recreating Dropped Online Redo Log Group:

Now if required then you can recreate the dropped redo log group with different size.

ALTER DATABASE ADD LOGFILE GROUP 1
(
2 '/U03/APP/ORADATA/ORA920/REDO_G01A.LOG',
3 '/U04/APP/ORADATA/ORA920/REDO_G01B.LOG',
4 '/U05/APP/ORADATA/ORA920/REDO_G01C.LOG'
5 '/U06/APP/ORADATA/ORA920/REDO_G01D.LOG'
)
SIZE 250M REUSE;

Force log switch:

Now we will be forcing another log switch so as to make our newly created online redo log groups as CURRENT.

SELECT GROUP#, STATUS FROM V$LOG;

GROUP#       STATUS
-------------    -------------------
      1             UNUSED
      2             INACTIVE
      3             INACTIVE
      4             CURRENT

ALTER SYSTEM SWITCH LOGFILE;

SELECT GROUP#, STATUS FROM V$LOG;

GROUP#       STATUS
-------------    -------------------
      1             CURRENT
      2             INACTIVE
      3             ACTIVE
      4             ACTIVE

Step Back:

Now you will have to step back and follow below mentioned steps as we did them before. This will resize all online redo log groups until all of them are rebuilt.

1. Drop First Online Redo Log
2. Recreating Dropped Online Redo Log Group
3. Force log switch

Get Current Online Redo Log Groups:

Once all all online redo log groups are resized then we can get the snapshot of all physical files.

SELECT LF.GROUP#, LF.MEMBER, L.BYTES FROM V$LOGFILE LF, V$LOG L WHERE LF.GROUP# = L.GROUP#;

GROUP#                                        MEMBER                                                  BYTES
------------- -------------------------------------------------------------------            -------------------
      1             /U03/APP/ORADATA/ORA920/REDO_G01A.LOG            508,810,000
      1             /U04/APP/ORADATA/ORA920/REDO_G01B.LOG            508,810,000
      1             /U05/APP/ORADATA/ORA920/REDO_G01C.LOG            508,810,000
      1             /U06/APP/ORADATA/ORA920/REDO_G01D.LOG            508,810,000
      2             /U03/APP/ORADATA/ORA920/REDO_G02A.LOG            508,810,000
      2             /U04/APP/ORADATA/ORA920/REDO_G02B.LOG            508,810,000
      2             /U05/APP/ORADATA/ORA920/REDO_G02C.LOG            508,810,000
      2             /U06/APP/ORADATA/ORA920/REDO_G02D.LOG            508,810,000
      3             /U03/APP/ORADATA/ORA920/REDO_G03A.LOG            508,810,000
      3             /U04/APP/ORADATA/ORA920/REDO_G03B.LOG            508,810,000
      3             /U05/APP/ORADATA/ORA920/REDO_G03C.LOG            508,810,000
      3             /U06/APP/ORADATA/ORA920/REDO_G03D.LOG            508,810,000
      4             /U03/APP/ORADATA/ORA920/REDO_G04A.LOG            508,810,000
      4             /U04/APP/ORADATA/ORA920/REDO_G04B.LOG            508,810,000
      4             /U05/APP/ORADATA/ORA920/REDO_G04C.LOG            508,810,000
      4             /U06/APP/ORADATA/ORA920/REDO_G04D.LOG            508,810,000 Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   A Guide to Oracle Bitmap Index Techniques!!
   Beware of Oracle Outage with database growth!!
   Exciting Oracle 11g features you should not miss to know!!
   SQL in Action II – Multi-Table Queries