Being an Oracle DBA in the initial year of our we work on the standalone database. In the world of backup, we work on multiplexing of the redo log. But as in single instance database is different from RAC environment. Multiplexing means to keep multiple copies of redo log in different diskgroup. It is similar to that of multiplexing in standalone, Only change is RAC will have threads for the node.
i.e a 2 node RAC will have 2 threads. So while adding redolog group, we need to mention the thread parameter. Basically, we need to add new redolog groups in required diskgroup and drop the old ones.Now let’s have look on steps which we are going to create multiplexing Redolog in Oracle RAC.
1. Current redo log status:
col member for a56 set pagesize 299 set lines 299 select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) "Size (MB)" from v$log l, v$logfile f where f.group# = l.group# order by 1,2; GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB) ------ ------- ---------------------------------------------------------------------- ---------- ---------- --------- 1 1 +DATA/PREPROD/redo01.log YES ACTIVE 50 2 1 +DATA/PREPROD/redo02.log NO CURRENT 50 3 2 +DATA/PREPROD/redo03.log YES INACTIVE 50 4 2 +DATA/PREPROD/redo04.log NO CURRENT 50
Currently, the redologs are under +DATA diskgroup, which we will multiplex to +REDOA,+REDOB diskgroup and resize to 5G each.
2. Add logfile groups for node 1 :(THREAD 1)
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('+REDOA','+REDOB') SIZE 5G;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ('+REDOA','+REDOB') SIZE 5G;
3. Add logfile groups for node 2 : (THREAD 2)
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 9 ('+REDOA','+REDOB') SIZE 5G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 10 ('+REDOA','+REDOB') SIZE 5G;
4. Drop the old redolog groups:
col member for a56 set pagesize 299 set lines 299 select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) "Size (MB)" from v$log l, v$logfile f where f.group# = l.group# order by 1,2; GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB) ------ ------- ---------------------------------------------------------------------- ---------- ---------- --------- 1 1 +DATA/PREPROD/redo01.log YES INACTIVE 50 ---- >>> 2 1 +DATA/PREPROD/redo02.log NO CURRENT 50 ----->>> 3 2 +DATA/PREPROD/redo03.log YES INACTIVE 50 ----->>> 4 2 +DATA/PREPROD/redo04.log NO CURRENT 50 ----->>> 5 1 +REDOA/PREPROD/ONLINELOG/group_5.256.943101611 YES UNUSED 5120 5 1 +REDOB/PREPROD/ONLINELOG/group_5.256.943101623 YES UNUSED 5120 6 1 +REDOA/PREPROD/ONLINELOG/group_6.257.943101633 YES UNUSED 5120 6 1 +REDOB/PREPROD/ONLINELOG/group_6.257.943101643 YES UNUSED 5120 9 2 +REDOA/PREPROD/ONLINELOG/group_9.258.943101671 YES UNUSED 5120 9 2 +REDOB/PREPROD/ONLINELOG/group_9.258.943101679 YES UNUSED 5120 10 2 +REDOA/PREPROD/ONLINELOG/group_10.259.943101687 YES UNUSED 5120 10 2 +REDOB/PREPROD/ONLINELOG/group_10.259.943101695 YES UNUSED 5120
The old redolog groups are 1,2,3,4. And We can drop only the redolog groups whose status is INACTIVE.
Currently group 1, 3 are INACTIVE. Let’s drop them first.
alter database drop logfile group 1; alter database drop logfile group 3;
col member for a56 set pagesize 299 set lines 299 select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) "Size (MB)" from v$log l, v$logfile f where f.group# = l.group# order by 1,2; GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB) ------ ------- ---------------------------------------------------------------------- ---------- ---------- --------- 2 1 +DATA/PREPROD/redo02.log NO CURRENT 50 ---->>> 4 2 +DATA/PREPROD/redo04.log NO CURRENT 50 ---->>> 5 1 +REDOA/PREPROD/ONLINELOG/group_5.256.943101611 YES UNUSED 5120 5 1 +REDOB/PREPROD/ONLINELOG/group_5.256.943101623 YES UNUSED 5120 6 1 +REDOA/PREPROD/ONLINELOG/group_6.257.943101633 YES UNUSED 5120 6 1 +REDOB/PREPROD/ONLINELOG/group_6.257.943101643 YES UNUSED 5120 9 2 +REDOA/PREPROD/ONLINELOG/group_9.258.943101671 YES UNUSED 5120 9 2 +REDOB/PREPROD/ONLINELOG/group_9.258.943101679 YES UNUSED 5120 10 2 +REDOA/PREPROD/ONLINELOG/group_10.259.943101687 YES UNUSED 5120 10 2 +REDOB/PREPROD/ONLINELOG/group_10.259.943101695 YES UNUSED 5120
Now we need to drop group 2, 4. But their status is CURRENT. So any attempt to drop these loggroups will result in below error.
ORA-01623: log 1 is current log for instance test (thread 1) – cannot drop
So to make them inactive, switch logfiles multiple time, till the status changes to INACTIVE.
alter system switch logfile; alter system switch logfile; alter system switch logfile;
col member for a56 set pagesize 299 set lines 299 select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) "Size (MB)" from v$log l, v$logfile f where f.group# = l.group# order by 1,2; GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB) ------ ------- ---------------------------------------------------------------------- ---------- ---------- --------- 2 1 +DATA/PREPROD/redo02.log YES INACTIVE 50 --->>> 4 2 +DATA/PREPROD/redo04.log YES INACTIVE 50 --->>> 5 1 +REDOA/PREPROD/ONLINELOG/group_5.256.943101611 NO CURRENT 5120 5 1 +REDOB/PREPROD/ONLINELOG/group_5.256.943101623 NO CURRENT 5120 6 1 +REDOA/PREPROD/ONLINELOG/group_6.257.943101633 YES INACTIVE 5120 6 1 +REDOB/PREPROD/ONLINELOG/group_6.257.943101643 YES INACTIVE 5120 9 2 +REDOA/PREPROD/ONLINELOG/group_9.258.943101671 YES ACTIVE 5120 9 2 +REDOB/PREPROD/ONLINELOG/group_9.258.943101679 YES ACTIVE 5120 10 2 +REDOA/PREPROD/ONLINELOG/group_10.259.943101687 NO CURRENT 5120 10 2 +REDOB/PREPROD/ONLINELOG/group_10.259.943101695 NO CURRENT 5120
As now both are in the INACTIVE state, Let’s drop them.
alter database drop logfile group 2; alter database drop logfile group 4;
col member for a56 set pagesize 299 set lines 299 select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) "Size (MB)" from v$log l, v$logfile f where f.group# = l.group# order by 1,2;
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB) ------ ------- ---------------------------------------------------------------------- ---------- ---------- --------- 5 1 +REDOA/PREPROD/ONLINELOG/group_5.256.943101611 NO CURRENT 5120 5 1 +REDOB/PREPROD/ONLINELOG/group_5.256.943101623 NO CURRENT 5120 6 1 +REDOA/PREPROD/ONLINELOG/group_6.257.943101633 YES INACTIVE 5120 6 1 +REDOB/PREPROD/ONLINELOG/group_6.257.943101643 YES INACTIVE 5120 9 2 +REDOA/PREPROD/ONLINELOG/group_9.258.943101671 YES ACTIVE 5120 9 2 +REDOB/PREPROD/ONLINELOG/group_9.258.943101679 YES ACTIVE 5120 10 2 +REDOA/PREPROD/ONLINELOG/group_10.259.943101687 NO CURRENT 5120 10 2 +REDOB/PREPROD/ONLINELOG/group_10.259.943101695 NO CURRENT 5120
Now all the old the redolog groups were dropped. With this multiplexing activity completed.
For resizing redolog groups, we can follow the same above steps.
