How do you increase the size of the Oracle REDO logs?

book

Article ID: 38081

calendar_today

Updated On:

Products

EHEALTH CA eHealth

Issue/Introduction

Summary 

Normally the REDO log switches take place every 10-15 minutes. That means about 4 to 6 redo log files will be created in one hour. Sometimes it can create 1 tor more redo log files within one minute due to heavy database activity or a large database transaction. This rapid creation of redo log files can fill up the ArchiveLogs directory in less than one hour, and thus cause the eHealth to run out of disk space. This is because most eHealth installations only allocate 10 to 50 Gb for the ArchiveLogs directory.

The instructions below assume redo logs are currently set to 35 Mb and you want to change them to 165 Mb.

Instructions

  1. Stop eHealth

    nhServer stop

  2. Start sqlplus using the eHealth adminstrative account

    Windows:   sqlplus %NH_USER%/%NH_USER%@%NH_DB_CONNECT_STRING%
    Solaris/Linux:   sqlplus $NH_USER/[email protected]$NH_DB_CONNECT_STRING

  3. To find the location of your current redo logs, run:

    select * from v$logfile;

  4. There are four redo log groups; make sure that group 1 is not listed as CURRENT

    select group#,status from v$log;

    If it is listed as current, use the following command to set it to INACTIVE

    alter system switch logfile;

    Confirm group 1 is now INACTIVE

    select group#,status from v$log;

  5. Drop group 1

    alter database drop logfile group 1;

    If the command fails, run the checkpoint:

    alter system checkpoint global;

    Now try the command to drop group 1 again, it should complete successfully.


  6. Create a new group with with a larger size. Note that the path to your database location will be different, so edit this command appropriately.  For example (remember to use the locations from step 3)

    alter database add logfile group 1 ( '/u03/app/oradata/oradata/EHEALTH/redo_g01a.log',  '/u04/app/oradata/oradata/EHEALTH/redo_g01b.log')
    size 165m reuse; 

  7. Make the new group 1 CURRENT

    alter system switch logfile;

    To confirm group 1 is CURRENT, run:

    select group#, status from v$log;

    You may have to run the alter command up to three times to get group 1 to be CURRENT.

  8. Repeat steps 1 to 7 for group 2, group 3, and group 4.

  9. Confirm the size change to each group is now 165 Mb.

    select group#, bytes from v$log;

  10. Exit from sqlplus

    exit

  11. Start eHealth

    nhServer start


Additional Information

TEC1418206 - General guide for troubleshooting REDO log files that fill up the ArchiveLogs directory

Environment

Release: LHDEVC99000-6.3-eHealth-Live Health Upgrade Elements
Component: