René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Resizing Oracle's redo log groups

I have two groups of redo logs that I want to resize. The member's size is 50MB: 
select status, bytes/1024/1024, group# from v$log;

STATUS           BYTES/1024/1024     GROUP#
---------------- --------------- ----------
CURRENT                       50          1
ACTIVE                        50          2
I determine the current location for the files: 
select group#, substrb(member, 1, 90) from v$logfile;

    GROUP# SUBSTRB(MEMBER,1,90)
---------- -------------------------------------------------------------------------------
         2 E:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_2NQLFJ4W_.LOG
         1 E:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_2NQLFG7B_.LOG
         1 E:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\GROUP_1_MEMBER_2.LOG
         2 E:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\GROUP_2_MEMBER_2.LOG
Adding two new groups (with two members each). The group's log file size will be 250 megabytes: 
alter database add logfile group 3 (
  'E:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\GROUP_3_MEMBER_1.LOG',  
  'E:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\GROUP_3_MEMBER_2.LOG'
) 
size 250M;

alter database add logfile group 4 (
  'E:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\GROUP_4_MEMBER_1.LOG',  
  'E:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\GROUP_4_MEMBER_2.LOG'
) 
size 250M;
Dropping group 1 and 2. First, I have to drop group 2 because group one's status is CURRENT.
However.... 
alter database drop logfile group 2;
.. it turns out that group two is needed for a crash recovery: 
ORA-01624: log 2 needed for crash recovery of instance xe (thread 1)
So, I create a checkpoint. 
alter system checkpoint;
And then drop group two: 
alter database drop logfile group 2;
Group one's time has come as well: 
alter database drop logfile group 1;
However.... the group is still CURRENT: 
ORA-01623: log 1 is current log for instance xe (thread 1) - cannot drop
So, I switch the logfile group: 
alter system switch logfile;
Since group one will be used for crash recovery, I have to create a checkpoint again: 
alter system checkpoint;
And then drop the group: 
alter database drop logfile group 1;
The «old files» are still in the filesystem, so they should be deleted as well:

Links

See also Other redo log related administrative tasks.