Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, yildiray.

Asked: May 13, 2020 - 11:06 am UTC

Answered by: Connor McDonald - Last updated: May 19, 2020 - 11:06 am UTC

Category: Database Administration - Version: 11.2.0.4

Viewed 100+ times

You Asked

Hi expert
We are using dataguard on 11.2.0.4 oracle database. Now i want to switchover to physical standby but there is a problem. When i check status of dataguard with

DGMGRL> show database sby2;

Database - sby2

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   14 minutes 10 seconds (computed 6 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      424.00 KByte/s
  Real Time Query: OFF
  Instance(s):
    sby2

Database Status:
SUCCESS

 


there is always transport lag. Lag time changes 5 min to 50 min. Aplly lag always 0(zero). Database status: SUCCESS

When i check with sqlplus

SQL> select name, value, unit, time_computed from v$dataguard_stats where name in ('transport lag', 'apply lag');

NAME          VALUE         UNIT                           TIME_COMPUTED
------------- ------------- ------------------------------ ------------------------------
transport lag +00 00:00:00  day(2) to second(0) interval   05/13/2020 14:01:41
apply lag     +00 00:00:00  day(2) to second(0) interval   05/13/2020 14:01:41

SQL>


there is no lag. Always.

When i check this statement
 

Primary---

SQL>  SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG
 WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG)
 GROUP BY THREAD#;  2    3

MAX(SEQUENCE#)    THREAD#
-------------- ----------
        366800          1




Standby---
SQL>  SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG
 WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG)
 GROUP BY THREAD#;  2    3

MAX(SEQUENCE#)    THREAD#
-------------- ----------
        366800          1



both sides primary and standby gives same results. I think my dataguard works fine, but dgmgrl results confuses me.
In this case what can i do, is making switchover safe?

Thanks.

and we said...

Have you got *standby* redo logs configured? Without them, we are going to send *archived* redo logs to the standby, and thus you have a latency in changes being applied.

When standby redo logs are present, we'll be shipping redo to the standby in "real time".

More details here

https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/oracle-data-guard-redo-transport-services.html#GUID-E6EC6104-3C38-482D-B807-A0E84ECFB937

and you rated our response

  (2 ratings)

Reviews

May 15, 2020 - 4:24 pm UTC

Reviewer: A reader

Yes i have standby logs in standby database exacts same size and count in primary database.
primary---
SQL> select thread#,group#,bytes from v$log;

   THREAD#     GROUP#      BYTES
---------- ---------- ----------
         1         24  536870912
         1         25  536870912
         1         26  536870912
         1         27  536870912
         1         28  536870912
         1         29  536870912
         1         30  536870912

7 rows selected.

standby---
SQL> select thread#,group#,bytes from v$standby_log;

   THREAD#     GROUP#      BYTES
---------- ---------- ----------
         1          1  536870912
         1          2  536870912
         1          3  536870912
         1          4  536870912
         1          5  536870912
         1          6  536870912
         1          7  536870912

7 rows selected.

and i try one more standby logs, it's not change anything.
Connor McDonald

Followup  

May 18, 2020 - 3:11 am UTC

So let me get this right... *you* don't tell us that you already have standby redo logs, and then *you* give us one star for us suggesting them ..... yeah, that seems fair.........sigh

Other things to look at:

1) rapid redo generation. Lets say you have filled log 100 (and we are transporting). If by the time we have transported 100 we come back and see that you have already filled 101 and have moved onto 102, then we will pause and wait for 101 to be archived so we can send the whole thing.

2) network capacity. If your network can't cope, then transport lag will rise, especially during busy periods. You could use OS tools like qperf/iperf, or you can liaise with Support to do tracing using event 16421.




May 18, 2020 - 2:50 pm UTC

Reviewer: Graham from Newcastle upon Tyne, UK

This might sound silly but it's something to rule out. Are you actually using the standby redo logs?

When you enabled media recovery on your standby, did you execute:

alter database recover managed standby database using current logfile disconnect from session;


or

alter database recover managed standby database disconnect from session;

Connor McDonald

Followup  

May 19, 2020 - 11:06 am UTC

Good catch.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database