-- ==========================================================
-- OEM Report: Databases Without Recent Backup (Family-Level)
-- Uses backup activity (begin/end time) instead of status
-- Author: Daidipya Upalekar + Nikhil
-- ==========================================================
WITH db_targets AS (
SELECT t.target_guid, t.target_name, t.host_name
FROM sysman.mgmt$target t
WHERE t.target_type IN ('oracle_database','rac_database')
),
db_properties AS (
SELECT
p.target_guid,
MAX(CASE WHEN p.property_name = 'DBName' THEN p.property_value END) AS db_name,
-- canonical base name: remove trailing letters (A-Z, a-z)
MAX(CASE WHEN p.property_name = 'DBName' THEN REGEXP_REPLACE(p.property_value,'[A-Za-z]+$','') END) AS base_name,
MAX(CASE WHEN p.property_name IN ('DatabaseRole','DBRole','DataGuardStatus')
THEN p.property_value END) AS dg_role
FROM sysman.mgmt$target_properties p
GROUP BY p.target_guid
),
rman_configured AS (
SELECT DISTINCT r.target_guid
FROM sysman.mgmt$ha_rman_config r
),
recent_backup AS (
SELECT
b.target_guid,
MAX(NVL(b.end_time, b.begin_time)) AS last_backup_time -- take whichever timestamp exists
FROM sysman.mgmt$ha_backup b
WHERE NVL(b.end_time, b.begin_time) IS NOT NULL
GROUP BY b.target_guid
),
merged AS (
SELECT
t.target_guid,
t.target_name,
t.host_name,
dp.db_name,
dp.base_name,
NVL(dp.dg_role,'NONE') AS dg_role,
CASE WHEN rc.target_guid IS NOT NULL THEN 1 ELSE 0 END AS config_flag,
rb.last_backup_time
FROM db_targets t
JOIN db_properties dp ON t.target_guid = dp.target_guid
LEFT JOIN rman_configured rc ON t.target_guid = rc.target_guid
LEFT JOIN recent_backup rb ON t.target_guid = rb.target_guid
),
-- Aggregate at family level
family_agg AS (
SELECT
base_name,
LISTAGG(db_name || '(' || dg_role || ')', ', ') WITHIN GROUP (ORDER BY db_name) AS members,
MAX(config_flag) AS any_configured,
MAX(last_backup_time) AS last_backup_time,
MAX(CASE WHEN last_backup_time >= SYSDATE - 3 THEN 1 ELSE 0 END) AS recent_backup_done
FROM merged
GROUP BY base_name
),
-- Identify which member ran the last backup
last_backup_member AS (
SELECT
f.base_name,
LISTAGG(m.db_name || '(' || m.dg_role || ')', ', ') WITHIN GROUP (ORDER BY m.db_name) AS backup_members
FROM family_agg f
JOIN merged m ON f.base_name = m.base_name
WHERE m.last_backup_time = f.last_backup_time
GROUP BY f.base_name
)
SELECT
f.base_name,
f.members AS all_members,
NVL(TO_CHAR(f.last_backup_time, 'YYYY-MM-DD HH24:MI:SS'), 'N/A') AS last_backup_time,
NVL(lb.backup_members, 'N/A') AS member_with_last_backup,
CASE
WHEN f.any_configured = 0 AND f.recent_backup_done = 0 THEN '❌ NO BACKUP CONFIGURED OR RUN'
WHEN f.any_configured = 1 AND f.recent_backup_done = 0 THEN '⚠️ CONFIGURED BUT NO BACKUP ACTIVITY IN LAST 3 DAYS'
WHEN f.any_configured = 0 AND f.recent_backup_done = 1 THEN '⚠️ BACKUP RUN BUT NO CONFIGURATION FOUND'
ELSE '✅ BACKUP OK (Primary or Standby Covered)'
END AS backup_protection_status,
ROUND(CASE WHEN f.last_backup_time IS NOT NULL THEN SYSDATE - f.last_backup_time ELSE NULL END, 2) AS days_since_last_backup
FROM family_agg f
LEFT JOIN last_backup_member lb ON f.base_name = lb.base_name
WHERE f.recent_backup_done = 0 -- Only show families without a backup in last 3 days
ORDER BY backup_protection_status, f.base_name;
0 comments:
Post a Comment