Resizing online redo log

It seems an easy topic, I prefer being careful with the easy ones… so it is handy to have all you need, in one place and which will lead implementing things faster and safer.

So, here, You will find how to resize redolog files statements; exact building blocks, ie adding redo log files, grouping/mirroring them more than one disk location, specifying the file names with ASM auto generated way, making status inactive for dropping, dropping old ones, switching between log files, all in one place!

Resizing means, you need to create or add new redo log files with a desired size and drop the old ones. For example in this example I had 3 files having 50MBs, I had created 500MB redo log files first. And dropped the old 50MB ones, this way resized the redo files.

Recall that you should check for the space in disk group in ASM first, to create bigger or additional redo log group. For example I saw, 8519 and 8517 MB free for my diskgroups REDOLOGS and REDOLOGS1, so no problem to create additioal 3 redolog files having total of 1500MB files.

SQL> select name, state, free_mb from v$asm_diskgroup;

NAME                           STATE          FREE_MB
------------------------------ ----------- ----------
REDOLOGS                       CONNECTED         8519
REDOLOGS1                      CONNECTED         8517
DATA                           CONNECTED        87717
ARCHIVELOGS                    CONNECTED        40274

SQL>

Also, note the current redo log file sizes first below:

-- redolog files info
SELECT b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;
THREAD# GROUP# MEMBER BYTES
---------- ---------- ---------------------------------------------------------------------------------------------------- ----------
1 1 +REDOLOGS/IDRA/ONLINELOG/group_1.278.997880185 52428800
1 1 +REDOLOGS1/IDRA/ONLINELOG/group_1.289.998810187 52428800
1 2 +REDOLOGS/IDRA/ONLINELOG/group_2.297.998810193 52428800
1 2 +REDOLOGS1/IDRA/ONLINELOG/group_2.298.998810201 52428800
1 3 +REDOLOGS/IDRA/ONLINELOG/group_3.281.998810211 52428800
1 3 +REDOLOGS1/IDRA/ONLINELOG/group_3.280.998810219 52428800

Adding redolog file statement is fairly self descriptive. You may mirror the files to different disk groups as giving them in (‘(ONLINELOG)’,'(ONLINELOG)’) Here the ONLINELOG is the template used for creating the auto generated names. I am using new GROUP numbers 4,5,6, which will not overlap existing group numbers 1,2,3. If you use existing ones you will get ORA-01184 error as shown below. In RAC, in which multiple instance (THREAD) in place you will create additional redolog groups for each instance or thread.

--adding redolog file with mirroring to +REDOLOGS asm diskgroup, giving sizing as 500 MB.

ALTER DATABASE ADD LOGFILE THREAD 1 
GROUP 4 ('+REDOLOGS(ONLINELOG)','+REDOLOGS1(ONLINELOG)') size 500M REUSE, 
GROUP 5 ('+REDOLOGS(ONLINELOG)','+REDOLOGS1(ONLINELOG)') size 500M REUSE, 
GROUP 6 ('+REDOLOGS(ONLINELOG)', '+REDOLOGS1(ONLINELOG)') size 500M REUSE;

SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ( '+REDOLOGS1(ONLINELOG)') SIZE 500M REUSE;
ALTER DATABASE ADD LOGFILE GROUP 3 ( '+REDOLOGS1(ONLINELOG)') SIZE 500M REUSE
*
ERROR at line 1:
ORA-01184: logfile group 4 already exists

So, we finished with adding new redo log groups. New added ones are in status UNUSED

-- for status of log files
SQL> SELECT group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
4 UNUSED
5 UNUSED
6 UNUSED

It is straightforward to drop a log file, if it is in INACTIVE mode. In case it is in use having ACTIVE or CURRENT statuses, and you want to drop it, you will probably get following error:

-- for deleting/dropping old log files
ALTER DATABASE DROP LOGFILE GROUP 1;

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance idra (thread 1)
ORA-00312: online log 1 thread 1: '+REDOLOGS/IDRA/ONLINELOG/group_1.257.907425247'
ORA-00312: online log 1 thread 1: '+REDOLOGS1/IDRA/ONLINELOG/group_1.258.907425247'

In case, the log status is CURRENT, like above, for GROUP 3 and you want to drop it. Then you should switch to next group by below statement:

-- for switching among groups
alter system switch logfile;

Even you made switches to further groups, it does not mean that it will change the status to INACTIVE, for making ACTIVE to INACTIVE, a CHECKPOINT should be  in place, which does not switch log files but writes changes to datafiles, in which you will not need REDO for recovery which are not CURRENT.

-- for making active to inactive
alter system checkpoint;