Find delay between Primary and Standby Database

Hi,

This is just a tip and a ready reckoner for me

To find Delay if both Standby and Primary in same timezone

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') WHEN,': standby is ' || trim(to_char(1440 * (sysdate - max (next_time)),99999.99) ||' minutes behind') LAG from v$archived_log where applied ='YES';

To find Delay if Standby and Primary in different timezone

1. On Primary get time at OS level

$ date

Tue Feb 14 15:25:08 CET 2012 ------------------------- A

2. On Standby

sqlplus / as sysdba

SQL> alter session set nls_date_format='DD-MON-YY HH24:MI';

SQL> select dbtimezone from dual; - gives timezone for database

SQL> select max(next_time) from v$archived_log where applied='YES';

MAX(NEXT_TIME)

---------------

14-FEB-12 14:22 --------------------------------------- B

The delay is A-B

In this case around 1 hour.

Regards,

Tanveer