Avatar of dbadm
dbadm
Flag for Italy asked on

difference between last END_TIME and SYSDATE

Hi,
I have the following query from Oracle View V$RMAN_BACKUP_JOB_DETAILS:


SELECT   session_key, input_type, status, start_time, end_time,
         output_bytes_display, time_taken_display
    FROM v$rman_backup_job_details
   WHERE input_type = 'ARCHIVELOG'
ORDER BY session_key DESC;


I have to raise these alerts:
WARNING: Last Archivelog type backup successfully completed more than 2 hours ago
CRITICAL: Last Archivelog type backup successfully completed more than 4 hours ago

I need have in output just one record that returns the difference between last END_TIME date and sysdate in hours.


How can I do this?


Thanks!

Oracle DatabaseSQL

Avatar of undefined
Last Comment
DrSQL - Scott Anderson

8/22/2022 - Mon
DrSQL - Scott Anderson

If you subtract one date from another, you get a the number of days between them. You can divide that by 24 to get the number of hours. If it’s two timestamps (use systimestamp), then you get an interval and you can use extract(hour from (t1 -t2)) to get the number of hours.
Peter Chan

Condition below means "Last Archivelog type backup successfully completed more than 2 hours ago".

sysdate-end_time>1/12

Open in new window


Condition below means "Last Archivelog type backup successfully completed more than 4 hours ago".

sysdate-end_time>1/6



Open in new window

dbadm

ASKER

alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss';


SESSION_KEY INPUT_TYPE    START_TIME          END_TIME
----------- ------------- ------------------- -------------------
       8946 ARCHIVELOG    11/11/2021 17:00:00 11/11/2021 18:00:00
       8943 ARCHIVELOG    11/11/2021 14:00:34 11/11/2021 14:00:50
       8939 ARCHIVELOG    11/11/2021 10:00:33 11/11/2021 10:00:48
       8936 ARCHIVELOG    11/11/2021 06:05:41 11/11/2021 06:06:01
       8930 ARCHIVELOG    11/11/2021 06:00:43 11/11/2021 06:00:57
       8927 ARCHIVELOG    11/11/2021 02:00:40 11/11/2021 02:00:54
       8924 ARCHIVELOG    11/10/2021 23:08:08 11/10/2021 23:08:21

Open in new window



SQL> select sysdate from dual;


SYSDATE
-------------------
11/11/2021 21:00:00

Open in new window


Below is the output I'd like to have (just one record):

SESSION_KEY INPUT_TYPE    START_TIME          END_TIME           HOURS
----------- ------------ ----------------- -----------------  --------------
       8946 ARCHIVELOG    11/11/2021 17:00:00 11/11/2021 18:00    3

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
DrSQL - Scott Anderson

I’m assuming that’s the only record you want because it is the only one old enough. So, the query would be:

SELECT   session_key, input_type, status, start_time, end_time,
         Round((sysdate - end-time)/24) hours
    FROM v$rman_backup_job_details
   WHERE input_type = 'ARCHIVELOG' and (sysdate - end-time)/24 >= 2
ORDER BY session_key DESC;

you could also use TRUNC instead of ROUND - your preference
ASKER CERTIFIED SOLUTION
DrSQL - Scott Anderson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
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.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question