topic Re: Samples / Threshold / Oracle backup with status "FAILED" in Alerting

Samples / Threshold / Oracle backup with status "FAILED"

mn_24 — Fri, 04 Oct 2024 06:07:21 GMT

Hi,

I want to receive a problem when there is a FAILED Oracle database backup and to be closed only if the next one is successfully COMPLETED otherwise I want to investigate further why it is failing.

This is the metric/SQL statement behind/ which is used:

SELECT

backup_job.SESSION_RECID,

backup_job.SESSION_STAMP,

backup_job.START_TIME,

backup_job.END_TIME,

backup_job.STATUS,

backup_job.INPUT_TYPE,

backup_job.ELAPSED_SECONDS,

(backup_job.COMPRESSION_RATIO * 100) COMPRESSION_RATIO,

backup_job.INPUT_BYTES,

backup_job.OUTPUT_BYTES,

backup_job.INPUT_BYTES_PER_SEC,

backup_job.OUTPUT_BYTES_PER_SEC,

backup_job.AUTOBACKUP_DONE,

backup_job.OPTIMIZED,

backup_job.AUTOBACKUP_COUNT,

backup_set.DISTINCT_BACKUP_TYPES AS BACKUP_TYPES,

CASE

WHEN (

backup_job.INPUT_TYPE = 'ARCHIVELOG'

) THEN 'NO'

WHEN (

INSTR(backup_set.DISTINCT_BACKUP_TYPES, 'D') > 0 AND

COALESCE(MAX(backup_set.INCREMENTAL_LEVEL), 0) = 0

) THEN 'FULL'

WHEN (

INSTR(backup_set.DISTINCT_BACKUP_TYPES, 'D') > 0 AND

MAX(backup_set.INCREMENTAL_LEVEL) = 1

) THEN 'INCREMENTAL'

ELSE 'NO'

END AS DATAFILES_INCLUDED,

CASE

WHEN INSTR(backup_set.DISTINCT_BACKUP_TYPES, 'L') > 0 THEN 'YES'

ELSE 'NO'

END AS logs_included,

CASE

WHEN MAX(CASE WHEN backup_set.CONTROLFILE_INCLUDED = 'YES' THEN 1 ELSE 0 END) > 0 THEN 'YES'

ELSE 'NO'

END AS CONTROLFILE_INCLUDED,

CASE

WHEN MAX(CASE WHEN backup_set.INCREMENTAL_LEVEL = 0 THEN 1 WHEN backup_set.INCREMENTAL_LEVEL = 1 THEN 2 ELSE 0 END) = 1 THEN '0'

WHEN MAX(CASE WHEN backup_set.INCREMENTAL_LEVEL = 0 THEN 1 WHEN backup_set.INCREMENTAL_LEVEL = 1 THEN 2 ELSE 0 END) = 2 THEN '1'

ELSE 'N/A'

END AS INCREMENTAL_LEVEL

FROM V$RMAN_BACKUP_JOB_DETAILS backup_job

LEFT JOIN

(

SELECT

distinct_backup_set.DISTINCT_BACKUP_TYPES,

V$BACKUP_SET_DETAILS.SESSION_STAMP,

V$BACKUP_SET_DETAILS.SESSION_RECID,

V$BACKUP_SET_DETAILS.CONTROLFILE_INCLUDED,

V$BACKUP_SET_DETAILS.INCREMENTAL_LEVEL

FROM

(

SELECT

LISTAGG(BACKUP_TYPE, '+') WITHIN GROUP(ORDER BY BACKUP_TYPE) as DISTINCT_BACKUP_TYPES,

SESSION_STAMP,

SESSION_RECID

FROM

(

SELECT

DISTINCT BACKUP_TYPE,

SESSION_STAMP,

SESSION_RECID

FROM V$BACKUP_SET_DETAILS

)

GROUP BY

SESSION_STAMP,

SESSION_RECID

) distinct_backup_set

JOIN

V$BACKUP_SET_DETAILS

ON V$BACKUP_SET_DETAILS.SESSION_RECID = distinct_backup_set.SESSION_RECID AND V$BACKUP_SET_DETAILS.SESSION_STAMP = distinct_backup_set.SESSION_STAMP

) backup_set
ON backup_job.SESSION_RECID = backup_set.SESSION_RECID AND backup_job.SESSION_STAMP = backup_set.SESSION_STAMP
WHERE (
(
backup_job.END_TIME >= CURRENT_TIMESTAMP - NUMTODSINTERVAL(5, 'MINUTE')
OR
(backup_job.END_TIME IS NULL AND backup_job.START_TIME >= CURRENT_TIMESTAMP - NUMTODSINTERVAL(60, 'MINUTE'))
)
AND backup_job.STATUS NOT LIKE 'RUNNING%'
)
GROUP BY

backup_job.SESSION_RECID,

backup_job.SESSION_STAMP,

backup_job.START_TIME,

backup_job.END_TIME,

backup_job.STATUS,

backup_job.INPUT_TYPE,

backup_job.ELAPSED_SECONDS,

backup_job.COMPRESSION_RATIO,

backup_job.INPUT_BYTES,

backup_job.OUTPUT_BYTES,

backup_job.INPUT_BYTES_PER_SEC,

backup_job.OUTPUT_BYTES_PER_SEC,

backup_job.AUTOBACKUP_DONE,

backup_job.OPTIMIZED,

backup_job.AUTOBACKUP_COUNT,

backup_set.DISTINCT_BACKUP_TYPES

ORDER BY backup_job.SESSION_STAMP DESC;

The archive log backups of this database are scheduled to run on every 3 hours as I saw an auto-closed problem for FAILED backup , start digging and saw also that the problem was opened and closed in 2min - couldn't believe!

Why Davis has closed the problem before there is a real successfully completed backup or at least leave the problem open?

Is it because of the configuration with the samples, what about the threshold - should it be 0 or 1? OR it is because of the SQL statement which is fetching the data with this WHERE clause:

"....

WHERE (
(
backup_job.END_TIME >= CURRENT_TIMESTAMP - NUMTODSINTERVAL(5, 'MINUTE')
OR
(backup_job.END_TIME IS NULL AND backup_job.START_TIME >= CURRENT_TIMESTAMP - NUMTODSINTERVAL(60, 'MINUTE'))
)
AND backup_job.STATUS NOT LIKE 'RUNNING%'
)

....."

Here I am checking from the database the last two backups now and you can see that the LAST one is FAILED - a problem was generated for that but auto-closed after only 2min - WHY?!:

Re: Samples / Threshold / Oracle backup with status "FAILED"

kalle_lahtinen — Mon, 21 Oct 2024 12:05:13 GMT

I was just now struggling with a similar issue. I think the "dealerting samples" includes any 1-minute sample where the condition is not breached - even including times when there is no data at all.

So let's take a dummy example:

10:00 Condition breach

10:01 <no data>

10:02 <no data>

10:03 <no data>

10:04 <no data>

10:05 Condition breach

If your "dealerting samples" is 3, it means you have 10:01, 10:02 and 10:03 as 1-minute slots where the alert condition is not breached, and the alert is then closed (too early). The data is not indicating success either, which makes this a bit counter-intuitive for me at least. I guess the solution is simply to add a gigantic number in the "dealerting samples", to ensure that you'll likely get some further input by that time, to know if the issue is still ongoing.

Re: Samples / Threshold / Oracle backup with status "FAILED"

GosiaMurawska — Wed, 08 Jan 2025 16:15:44 GMT

Hi @mn_24, did you manage to solve your issue? I'm sure the rest of the Community wants to know the solution 🙂

Re: Samples / Threshold / Oracle backup with status "FAILED"

mn_24 — Mon, 13 Jan 2025 16:34:55 GMT

Hi,

well i am not using the two backup related metrics because don't want to overlap the work of the assigned Backup Teams.

But if you create a separate Alerting profile and than if you are using for example ServiceNow integrate it properly based on the following rule: a human must close the incident manually.

The data for this metric is fetched on every 1min so change the Violating Samples to 4 and the Window to 5 just to have enough time to process the ticket and to lock it in SNOW until someone close it manually.

The problem in Dynatrace will be auto-closed and it will continue to check based on the SQL.

This is the process how it will continue to monitor only the latest backups and not stuck on one FAILED but after it could have completed without any errors, right?

On top of that you can enable the second metric to monitor only the FULL backups:

com.dynatrace.extension.sql-oracle.backup.time_since:filter(and(not(eq("incremental.level","INCREMENTAL 1")),or(not(eq("incremental.level","N/A"))))):splitBy("incremental.level","input.type","dt.entity.sql:com_dynatrace_extension_sql-oracle_host","dt.entity.sql:com_dynatrace_extension_sql-oracle_instance"):sort(value(auto,descending))

don't forget to adjust the threshold according to your environmental needs - maybe 1 week would be fine.

The other option to monitor oracle database backups is and if you don't have a dedicated Backup team who is monitoring them via third party technology(could be Dynatrace as well) and if you are using rman backup scripts in crontab. Those backups should have logs. You can edit the backup scripts to check for any keywords like "RMAN-", "WARNRING", "ORA-" and if there are any to save them in a separate log. Than use the Log Monitoring in Dynatrace to monitor this log.

Re: Samples / Threshold / Oracle backup with status "FAILED"

mn_24 — Tue, 11 Feb 2025 15:26:28 GMT

Hi,

wanted to ask in the last version what do you mean by 5/3/5 alerting strategy - is it related with the violating samples /window or with anything else? Could you please clarify?

@TomásSeroteRoos

Re: Samples / Threshold / Oracle backup with status "FAILED"

TomásSeroteRoos — Wed, 12 Feb 2025 09:15:36 GMT

Hi @mn_24,

Yes exactly, it means that the default metric event for failed backups that comes packaged with the extension has adjusted sliding window settings: