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

Products

• 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: