/********************************************************************
* OEM ALERT: "Who missed backup in last 3 days?"
* - Ignores Standbys if Primary is backed up
* - Uses mgmt$ha_backup (real jobs) + mgmt$ha_backup_config
* Run in Repository DB as SYSMAN or OEM user
********************************************************************/
WITH primaries AS (
SELECT DISTINCT
TRIM(UPPER(SUBSTR(t.target_name, 1, INSTR(t.target_name, '.') - 1))) AS db_name,
t.target_guid
FROM mgmt$target t
JOIN mgmt$target_properties p ON t.target_guid = p.target_guid
WHERE t.target_type IN ('oracle_database', 'rac_database')
AND p.property_name = 'DataGuardStatus'
AND p.property_value = 'Primary'
),
standby_map AS (
SELECT
TRIM(UPPER(SUBSTR(t.target_name, 1, INSTR(t.target_name, '.') - 1))) AS standby_name,
TRIM(UPPER(SUBSTR(p2.property_value, 1, INSTR(p2.property_value, '.') - 1))) AS primary_name
FROM mgmt$target t
JOIN mgmt$target_properties p ON t.target_guid = p.target_guid
JOIN mgmt$target_properties p2 ON t.target_guid = p2.target_guid
WHERE t.target_type IN ('oracle_database', 'rac_database')
AND p.property_name = 'DataGuardStatus'
AND p.property_value = 'Physical Standby'
AND p2.property_name = 'DBName' -- this holds the primary DB name
),
recent_backups AS (
SELECT
TRIM(SUBSTR(DATABASE_NAME, 1, INSTR(DATABASE_NAME, '.') - 1)) AS db_name,
MAX(end_time) AS last_backup_time
FROM mgmt$ha_backup
WHERE end_time >= TRUNC(SYSDATE) - 3
AND status = 'COMPLETED'
GROUP BY TRIM(SUBSTR(DATABASE_NAME, 1, INSTR(DATABASE_NAME, '.') - 1))
),
configured_dbs AS (
SELECT DISTINCT
TRIM(UPPER(SUBSTR(t.target_name, 1, INSTR(t.target_name, '.') - 1))) AS db_name
FROM mgmt$target t
JOIN mgmt$ha_backup_config c ON t.target_guid = c.target_guid
WHERE t.target_type = 'oracle_database'
)
-- MAIN REPORT
SELECT
d.db_name AS "Database",
d.HOST AS "Host",
NVL(d.DATAGUARD_STATUS, 'Single Instance') AS "DG Role",
NVL(TO_CHAR(r.last_backup_time, 'YYYY-MM-DD HH24:MI'), 'NEVER') AS "Last Backup",
ROUND(SYSDATE - NVL(r.last_backup_time, SYSDATE-100), 1) AS "Days Since Backup",
'ALERT: NO BACKUP IN 72 HOURS' AS "Status"
FROM (
SELECT
TRIM(UPPER(SUBSTR(t.target_name, 1, INSTR(t.target_name, '.') - 1))) AS db_name,
t.host_name AS HOST,
p.property_value AS DATAGUARD_STATUS,
t.target_guid
FROM mgmt$target t
LEFT JOIN mgmt$target_properties p
ON t.target_guid = p.target_guid
AND p.property_name = 'DataGuardStatus'
WHERE t.target_type IN ('oracle_database', 'rac_database')
AND EXISTS (SELECT 1 FROM configured_dbs c WHERE c.db_name = TRIM(UPPER(SUBSTR(t.target_name, 1, INSTR(t.target_name, '.') - 1))))
) d
LEFT JOIN recent_backups r
ON r.db_name = d.db_name
-- Exclude Standby if its Primary HAD a backup
LEFT JOIN standby_map sm ON sm.standby_name = d.db_name
LEFT JOIN recent_backups rp ON rp.db_name = sm.primary_name
WHERE r.last_backup_time IS NULL -- No backup on this DB
AND (sm.standby_name IS NULL -- Not a standby
OR rp.last_backup_time IS NULL) -- OR its primary also missed
AND d.DATAGUARD_STATUS <> 'Physical Standby' -- Final safety net
ORDER BY "Days Since Backup" DESC;
0 comments:
Post a Comment