Resize Redo Log


This step I use to resize redo log,
preparation :

select a.*, a.bytes/1024/1024 BM from v$log a;  
GROUP# STATUS SIZEMB      
1 INACTIVE 256      
2 INACTIVE 256      
3 INACTIVE 256      
4 CURRENT 256      
           
select group#, member from v$logfile order by group#;
GROUP# MEMBER        
1 E:\REDO\REDO01_01.ILG    
1 H:\REDO\REDO01_02.ILG    
2 F:\REDO\REDO02_01.ILG    
2 E:\REDO\REDO02_02.ILG    
3 G:\REDO\REDO03_01.ILG    
3 F:\REDO\REDO03_02.ILG    
4 H:\REDO\REDO04_01.ILG    
4 G:\REDO\REDO04_02.ILG    

or you can combine the query

SELECT   a.group#, a.status, b.MEMBER, a.BYTES / 1024 / 1024 sizemb
    FROM v$log a, v$logfile b        
   WHERE a.group# = b.group#        
ORDER BY group#          
GROUP# STATUS MEMBER SIZEMB      
1 CURRENT E:\REDO\REDO01_01.ILG 256      
1 CURRENT H:\REDO\REDO01_02.ILG 256      
2 INACTIVE F:\REDO\REDO02_01.ILG 256      
2 INACTIVE E:\REDO\REDO02_02.ILG 256      
3 INACTIVE G:\REDO\REDO03_01.ILG 256      
3 INACTIVE F:\REDO\REDO03_02.ILG 256      
4 ACTIVE H:\REDO\REDO04_01.ILG 256      
4 ACTIVE G:\REDO\REDO04_02.ILG 256      

To resize redo log file, we must drop the old redo log file, and then create new redo log file.
To drop the redo log file, we must make the redo log file status to “INACTIVE”.
If the status is “ACTIVE” and we try to drop redo log file, you will get these errors:

ORA-01624: log 1 needed for crash recovery of instance xxx (thread 1)
ORA-00312: online log 1 thread 1

To change the status, use “ALTER SYSTEM CHECKPOINT” to database
and then you can use “ALTER DATABASE DROP LOGFILE GROUP 1….”

So, this is my script to resize redo log from 128MB to 256MB,
ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM CHECKPOINT;

ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE ADD LOGFILE GROUP 1 (
‘E:\REDO\REDO01_01.ILG’,
‘H:\REDO\REDO01_02.ILG’) SIZE 256 m REUSE;

ALTER SYSTEM SWITCH LOGFILE;

ALTER DATABASE DROP LOGFILE GROUP 2;

ALTER DATABASE ADD LOGFILE GROUP 2 (
‘E:\REDO\REDO02_02.ILG’,
‘F:\REDO\REDO02_01.ILG’) SIZE 256 m REUSE;

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM CHECKPOINT;

ALTER DATABASE DROP LOGFILE GROUP 3;

ALTER DATABASE ADD LOGFILE GROUP 3 (
‘G:\REDO\REDO03_01.ILG’,
‘F:\REDO\REDO03_02.ILG’) SIZE 256 m REUSE;

ALTER SYSTEM SWITCH LOGFILE;

ALTER DATABASE DROP LOGFILE GROUP 4;

ALTER DATABASE ADD LOGFILE GROUP 4 (
‘G:\REDO\REDO04_02.ILG’,
‘H:\REDO\REDO04_01.ILG’) SIZE 256 m REUSE;

ALTER SYSTEM SWITCH LOGFILE;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: