Q
Problem solve Get help with specific problems with your technologies, process and projects.

13 steps to replacing small redo log files

The redo log files are too small. What are best practices for replacing them with larger ones?
The following note is from MetaLink note 30910.1 Recreating Database Objects:

Small redo logs can be a major bottleneck in a database. If log switches are taking place at a very fast rate, it is a good idea to recreate the redo log files with a larger size, and perhaps even add one or more redo log files. Since it is recommended that redo log files be all of the same size, you should recreate them all at once with the new, bigger size. At any given time, the database must have at least two redo log groups. Therefore, if you currently have only two redo log groups, you need to create a third one to be able to drop and recreate the log files.

To do that,
- Go into Server Manager and connect internal (if 9i, connect as SYSDBA).
- Create the third redo log group with the desired size and in the desired location.

  ALTER DATABASE ADD LOGFILE GROUP 
 
   'file1' SIZE 
  
   ;
  
 
where must be a non-existing group number and 'file1' is the full path name of the new redo log file. If you are mirroring your redo log files, the statement would be:
  ALTER DATABASE ADD LOGFILE GROUP 
  
    ('file1', ..., 'fileN') SIZE 
   
    ;  

   
  

Once you have at least three redo log groups, you are ready to recreate your redo logs. For simplicity, the procedure below assumes the database has a single redo thread. If you are using the Oracle Parallel Server and have more than one redo thread, you should apply this procedure to each one of the threads. Here are the steps:

1. Go into Server Manager and connect internal (if 9i, connect as SYSDBA).

2. Shut down the database (normal or immediate).

3. Mount the database in restricted mode.

     STARTUP RESTRICT MOUNT

4. If the database is in archivelog mode, force all filled redo log groups to be archived.

     ARCHIVE LOG ALL  

5. Find out which is the current redo log group.

           
     SELECT GROUP#, STATUS FROM V$LOG;

One of the groups will have status 'CURRENT'. That will be the last one to be recreated. At least one of the groups should have status 'INACTIVE'. If not, repeat the above query until that is the case.

6. Pick one of the inactive redo groups and drop it.

LTER DATABASE DROP LOGFILE GROUP 
  
   ;  
  

7. Recreate that redo log group with the desired size and in the desired location.

     ALTER DATABASE ADD LOGFILE GROUP 
  
    'file1' SIZE 
   
     REUSE;        

   
  

where 'file1' is the full path name of the redo log file. If you are mirroring your redo log files, the statement would be:
     ALTER DATABASE ADD LOGFILE GROUP 
  
    ('file1', ..., 'fileN') SIZE 
   
     REUSE;  
   
  

8. Repeat steps 6 and 7 for all inactive redo log groups.

9. Open the database.

     ALTER DATABASE OPEN;  

10. Force a log switch to make the current redo log group inactive.

     ALTER SYSTEM SWITCH LOGFILE;  

11. Check the status of that redo group to make sure it is inactive.

     SELECT STATUS FROM V$LOG
      WHERE GROUP# = 
  
   ; 
  

When the status shows as 'INACTIVE', move on to step 12. If the database is in archivelog mode, you should check both the redo group's status and whether it has already been archived:
     SELECT ARCHIVED, STATUS FROM V$LOG
      WHERE GROUP# = 
  
   ;
  

When both the status shows as 'INACTIVE' and archived is 'YES', move on to step 12.

12. Repeat steps 6 and 7 for this particular redo log group that just became inactive. If you originally had only two redo log groups and do not want a third one added, simply apply step 6 to drop this newly inactivated redo log group.

13. Shut down the database (normal or immediate) and take a full backup. When the database is restarted without RESTRICTed session, all users will be able to connect.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close