DBA Hub

📋Steps in this guide1/4

Multiplex redolog in Oracle RAC DBACLASS

This article explains the steps for multiplexing redologs in oracle RAC. 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 node. i.e a 2 node RAC will have 2 threads. So while adding redolog group, […]

oracle clusteringintermediate
by OracleDba
18 views
1

1. Current redo log status:

Currently, the redologs are under +DATA diskgroup, which we will multiplex to +REDOA,+REDOB diskgroup and resize to 5G each.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
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
2

2. Add logfile groups for node 1 :(THREAD 1)

Code/Command (click line numbers to comment):

1
2
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

3. Add logfile groups for node 2 : (THREAD 2)

Code/Command (click line numbers to comment):

1
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

4. Drop the old redolog groups:

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. 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. As now both are in the INACTIVE state, Let’s drop them. 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. For any queries, please post in our FORUM .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
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

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

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

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!