asked on

Find difference between timestamps (obtained from two different SCNs)

Hello there,
I came up with a SQL to find the timestamps of current scn and applied scn on primary and standby 11gR2 DBs respectively. This is for checking standby LAG. I now need to find the "difference" between these two timestamps in minutes/hours.. Is there a way this can be accomplished? Will appreciate your help in getting this difference.
I tried some options but they didn't work (using to_date)..

select scn_to_timestamp(to_char(current_scn)) ,scn_to_timestamp(to_char(APPLIED_SCN)) "applied scn" from v$database,v$archive_dest where dest_id=2;

SCN_TO_TIMESTAMP(TO_CHAR(CURRENT_SCN))
---------------------------------------------------------------------------
applied scn
---------------------------------------------------------------------------
14-MAR-18 03.13.08.000000000 AM
14-MAR-18 03.12.59.000000000 AM

select to_date(scn_to_timestamp(to_char(current_scn)),'dd-mon-yy hh.mi.ss.ns AM') as first_date, to_date(scn_to_timestamp(to_char(APPLIED_SCN)),'dd-mon-yy hh.mi.ss.ns AM') as second_date from v$database,v$archive_dest where dest_id=2;
select to_date(scn_to_timestamp(to_char(current_scn)),'dd-mon-yy hh.mi.ss.ns AM') as first_date, to_date(scn_to_timestamp(to_char(APPLIED_SCN)),'dd-mon-yy hh.mi.ss.ns AM') as second_date from v$database,v$archive_dest where dest_id=2
*
ERROR at line 1:
ORA-01821: date format not recognized

select round((second_date - first_date) * (24),2) as time_in_hours from
( select to_date(to_char(scn_to_timestamp(15225393474101),'mon-dd-yyyy hh24:mi:ss')) as first_date, to_date(to_char(scn_to_timestamp(17225393474101),'mon-dd-yyyy hh24:mi:ss')) as second_date from dual);
( select to_date(to_char(scn_to_timestamp(15225393474101),'mon-dd-yyyy hh24:mi:ss')) as first_date, to_date(to_char(scn_to_timestamp(17225393474101),'mon-dd-yyyy hh24:mi:ss')) as second_date from dual)
*
ERROR at line 2:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

slightwv (䄆 Netminder)

If you subtract timestamps you get an interval. Visually it is pretty easy to read an interval.

For example:
SQL> select systimestamp-(systimestamp-10/14) from dual;
+000000000 17:08:34.116888

That is 17 hours, 8 minutes and 34 seconds and some fractional seconds different.

If you want it broken down more, there are MANY examples on the web on extracting individual pieces from an interval.

I'm also not sure what you are after. Everything I remember reading on SCN_TO_TIMESTAMP is that it is sort of an estimate.

awking00

What is it you want to do with the time difference? Do you just want to display it or use it somehow?

Geert G

you can use a date field also to find the difference between the standby database time and the active database time

the standby database is at this time:

select controlfile_time from v$database;

Open in new window

but, you also need to check v$recover_file

select min(time) from v$recover_file

Open in new window

actually it's the least of above 2 querries:

with rt as (select min(time) recover_time from v$recover_file)
select nvl(least(controlfile_time, recover_time), controlfile_time) standby_time from v$database, rt;

Open in new window

when files need recovery and are not all synched, v$recover_file contains entries
this is typically so during creation of the standby
the controlfile created with (alter database create standby controlfile) ... will be newer than the database file timestamps
that query should cover that phase too

and if you want the difference between the active database (which is at sysdate time) in minutes:

with rt as (select min(time) recover_time from v$recover_file)
select (sysdate - nvl(least(controlfile_time, recover_time), controlfile_time))*24*60 standby_time_lag_minutes from v$database, rt;

Open in new window

oops, that query doesn't run on a standby

ERROR at line 2:
ORA-01219: database not open: queries allowed on fixed tables/views only

Open in new window

A standby database doesn't like the WITH clause ... it only accepts query on fixed tables

But this query does work:

select (sysdate - nvl(least(controlfile_time, (select min(time) from v$recover_file)), controlfile_time))*24*60 standby_time_lag_minutes 
from v$database;

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.