Tuesday, March 20, 2018

How to resize redolog files in oracle


step.1

First see the size of the current  redo logs.           
SQL> select group#, bytes, status from v$log;

step.2

Retrieve all the log member names for the groups.                                         
SQL> select group#, member from v$logfile;       

step.3

Create 3 new log groups and name them groups 4, 5, and 6, with different size.                         
SQL> alter database add logfile group 4 '/oracle/dbs/log4PROD.dbf' size 10M;                               
SQL> alter database add logfile group 5 '/oracle/dbs/log5PROD.dbf' size 10M;   
SQL> alter database add logfile group 6 '/oracle/dbs/log6PROD.dbf' size 10M; 

step.4

Run a query to view the v$log status.                                 
SQL> select group#, status from v$log;                                     

step.5

Drop the old redolog files, make sure the status should be INACTIVE.
SQL> alter database drop logfile group 1;                                   
SQL> alter database drop logfile group 2;                                   
SQL> alter database drop logfile group 3;   

step.6

Verify the groups were dropped, and the new groups' sizes are correct.
SQL> select group#, bytes, status from v$log; 

step.7

Take a backup of the database, and delete the files associated with old redolog groups as they are no longer needed.
rm /usr/oracle/dbs/log1PROD.dbf
rm /usr/oracle/dbs/log2PROD.dbf 
rm /usr/oracle/dbs/log3PROD.dbf       

step.8

Monitor the alert.log for the times of redo log switches. Due to increased redo log size, the groups should not switch as frequently under the same load conditions.

Note:

You cannot drop CURRENT and ACTIVE redolog files.

No comments:

Post a Comment