Managing Standby RedoLogs

      No Comments on Managing Standby RedoLogs

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

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

On the standby

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

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

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.

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;
/

And after that, here is the result.

On primary

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

On standby

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

Any comments are welcome.
Cheers
jko

The following two tabs change content below.

Jacques

I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

Leave a Reply

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