There is no any ALTER... command to resize the REDO logs in orace. 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: ( say rac1 and rac2) .From rac1 node do the following ,
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.
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ( '+DATA','+FLASH') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ( '+DATA','+FLASH') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 ( '+DATA','+FLASH') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ( '+DATA','+FLASH') SIZE 100M;
Mentioned commands will create 4 new groups with two members in each. Change the volume groups (+DATA and +FLASH) according to your environment .
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:
SQL> alter system switch logfile;
SQL> alter system checkpoint;
3. To DROP the old groups.
SQL> 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:
SQL> alter system switch logfile;
or
SQL> alter system checkpoint;
or
just continue your tries to drop it.
Note: - I tried to drop a redo log group but it is always CURRENT or ACTIVE. I done alter system switch logfile and alter system checkpoint repeatedly but it does not become inactive . I overcome this issue by executing alter system switch logfile on the other node (rac2).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
Let's say you have this situation in your RAC for two nodes: ( say rac1 and rac2) .From rac1 node do the following ,
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.
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ( '+DATA','+FLASH') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ( '+DATA','+FLASH') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 ( '+DATA','+FLASH') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ( '+DATA','+FLASH') SIZE 100M;
Mentioned commands will create 4 new groups with two members in each. Change the volume groups (+DATA and +FLASH) according to your environment .
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:
SQL> alter system switch logfile;
SQL> alter system checkpoint;
3. To DROP the old groups.
SQL> 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:
SQL> alter system switch logfile;
or
SQL> alter system checkpoint;
or
just continue your tries to drop it.
Note: - I tried to drop a redo log group but it is always CURRENT or ACTIVE. I done alter system switch logfile and alter system checkpoint repeatedly but it does not become inactive . I overcome this issue by executing alter system switch logfile on the other node (rac2).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
