Search This Blog

Wednesday 17 October 2012

Change the REDO Log size in RAC.


There is no any ALTER... command to resize the REDO logs.
So if you want to resize your REDO logs you will need to create a new group with a new size and then drop the old one.

Let's say you have this situation in your RAC for two nodes:

SQL> select GROUP#,MEMBERS,BYTES/1024/1024 MB_Size,status from v$log;
    GROUP#    THREAD#    MEMBERS    MB_SIZE STATUS
   ----------      ----------         ----------      ---------- ----------------
             1                      1                     2                 50 CURRENT
             2                      1                     2                 50 INACTIVE
             3                      2                     2                 50 INACTIVE
             4                      2                     2                 50 CURRENT

and you want to resize all your groups. Lets say you want to set 100M instead of 50M.

Action plan:
1. Add new REDO groups with a new size.

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ( '+DATA','+FLASH') SIZE 100M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ( '+DATA','+FLASH') SIZE 100M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 ( '+DATA','+FLASH') SIZE 100M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ( '+DATA','+FLASH') SIZE 100M;

Mentioned commands will create 4 new groups with two members in each.

2. Now you should wait till the Group 1/2/3/4 will start to be INACTIVE so you would be able to drop them.
Also you can speed up this process by executing:

alter system switch logfile;
alter system checkpoint;

3. To DROP the old groups.

ALTER DATABASE DROP LOGFILE GROUP 1;

In busy DB it is possible to see something like this during drop operation:
ORA-01623: log 1 is current log for instance RPTDB (thread 1) - cannot drop

in that case you should execute the following again:
alter system switch logfile;
or
alter system checkpoint;
or
just continue your tries to drop it.

Once all your old GROUPs will be dropped your output would be look like this:

SQL> select GROUP#,MEMBERS,BYTES/1024/1024 MB_Size from v$log;

 GROUP#    THREAD#    MEMBERS    MB_SIZE
 ----------     ----------         ----------     ----------
             5                    1                     2         100
             6                    1                     2         100
             7                    2                     2         100
             8                    2                     2         100

That is all!

No comments:

Post a Comment