Managing standby redologs in policy managed RAC configuration

Hi There,
In a policy managed RAC configuration with dataguard also in RAC mode, sometimes it is getting hard to manage standby redo logs.
From time to time, after a relocate, you will not have the required standby redo thread on the standby and it cannot go to realtime apply but working the old way…
This is an example:
On primary
[code language=”sql”]
SQL select thread#, group#,status from v$log;
THREAD# GROUP# STATUS
———- ———- ————————————————
1 1 INACTIVE
1 2 INACTIVE
2 3 INACTIVE
2 4 INACTIVE
1 5 INACTIVE
2 6 INACTIVE
3 13 ACTIVE
3 14 CURRENT
3 15 INACTIVE
4 16 CURRENT
4 17 INACTIVE
4 18 ACTIVE
[/code]
[code language=”sql”]
SQL select thread#, group#,status from v$standby_log;
THREAD# GROUP# STATUS
———- ———- ——————————
0 7 UNASSIGNED
0 8 UNASSIGNED
0 9 UNASSIGNED
0 10 UNASSIGNED
0 11 UNASSIGNED
0 12 UNASSIGNED
[/code]
On the standby
[code language=”sql”]
SQL select thread#, group#, bytes from v$standby_log;
THREAD# GROUP# STATUS
———- ———- ——————————
2 7 UNASSIGNED
2 8 UNASSIGNED
1 9 UNASSIGNED
1 10 UNASSIGNED
1 11 UNASSIGNED
2 12 UNASSIGNED
[/code]
[code language=”sql”]
SQL select thread#, group#,status from v$log order by thread#;
THREAD# GROUP# STATUS
———- ———- ————————————————
1 5 CLEARING
1 2 CURRENT
1 1 CLEARING
2 4 CLEARING
2 3 CLEARING
2 6 CURRENT
3 13 CLEARING
3 14 CURRENT
3 15 CLEARING
4 16 CURRENT
4 17 CLEARING
4 18 CLEARING
[/code]
No way we can do realtime apply here.
Production is using thread 3 and 4 while standby redo logs on the standby are not used.
The following script drop old standby_logs and recreate everything to allign the current threads.
[code language=”sql”]
declare
lGrp Number;
lDbRole Varchar2(50);
begin
select database_role Into lDbRole from v$database;
if lDbRole = ‘PHYSICAL STANDBY’ Then
execute immediate ‘alter database recover managed standby database cancel’;
End If;
for lDel in (select group# from v$standby_log)
loop
dbms_output.put_line (‘Dropping standby redolog group ‘||lDel.group#);
execute immediate ‘alter database drop standby logfile group ‘||lDel.group#;
end loop;
select max (group#)+5 into lGrp from v$log;
for lRec in (select thread#, bytes, count (*) Cnt from v$log group by thread#, bytes order by 1)
loop
for lRedos in 1..lRec.Cnt + 1
loop
dbms_output.put_line (‘Creating standby redolog for thread ‘||lRec.thread#||’ group ‘||lGrp||’ size ‘||lRec.bytes);
execute immediate ‘alter database add standby logfile thread ‘||lRec.thread#||’ group ‘||lGrp||’ size ‘||lRec.bytes;
lGrp := lGrp + 1;
end loop;
end loop;
if lDbRole = ‘PHYSICAL STANDBY’ Then
execute immediate ‘alter database recover managed standby database using current logfile disconnect’;
End If;
end;
/
[/code]
And after that, here is the result.
On primary
[code language=”sql”]
SQL select thread#, group#,status from v$standby_log;
THREAD# GROUP# STATUS
———- ———- ——————————
1 23 UNASSIGNED
1 24 UNASSIGNED
1 25 UNASSIGNED
1 26 UNASSIGNED
2 27 UNASSIGNED
2 28 UNASSIGNED
2 29 UNASSIGNED
2 30 UNASSIGNED
3 31 UNASSIGNED
3 32 UNASSIGNED
3 33 UNASSIGNED
3 34 UNASSIGNED
4 35 UNASSIGNED
4 36 UNASSIGNED
4 37 UNASSIGNED
4 38 UNASSIGNED
[/code]
On standby
[code language=”sql”]
SQL select thread#, group#,status from v$standby_log;
THREAD# GROUP# STATUS
———- ———- ——————————
1 23 UNASSIGNED
1 24 UNASSIGNED
1 25 UNASSIGNED
1 26 UNASSIGNED
2 27 UNASSIGNED
2 28 UNASSIGNED
2 29 UNASSIGNED
2 30 UNASSIGNED
3 31 UNASSIGNED
3 32 UNASSIGNED
3 33 UNASSIGNED
3 34 UNASSIGNED
4 35 UNASSIGNED
4 36 ACTIVE
4 37 UNASSIGNED
4 38 UNASSIGNED
[/code]
Any comments are welcome.
Cheers
jko


     

Leave a Reply

Your email address will not be published. Required fields are marked *