-- ==========================================================
-- OEM Report: Databases Not Protected by Backup
-- Author : Daidipya Upalekar
-- Purpose: Identify databases (Primary/Standby) without
-- backup configuration or recent backup activity
-- ==========================================================
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,
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 target_name
FROM sysman.mgmt$rman_config
),
recent_backup AS (
SELECT
b.target_guid,
MAX(b.end_time) AS last_backup_time
FROM sysman.mgmt$ha_backup b
GROUP BY b.target_guid
),
merged AS (
SELECT
t.target_guid,
t.target_name,
t.host_name,
p.db_name,
NVL(p.dg_role, 'NONE') AS dg_role,
CASE
WHEN EXISTS (
SELECT 1
FROM sysman.mgmt$rman_config r
WHERE LOWER(r.target_name) LIKE LOWER(p.db_name) || '%'
) THEN 'CONFIGURED'
ELSE 'NOT CONFIGURED'
END AS config_status,
r.last_backup_time
FROM db_targets t
JOIN db_properties p ON t.target_guid = p.target_guid
LEFT JOIN recent_backup r ON t.target_guid = r.target_guid
),
grouped AS (
SELECT
db_name,
MAX(CASE WHEN config_status = 'CONFIGURED' THEN 1 ELSE 0 END) AS any_configured,
MAX(CASE WHEN last_backup_time >= SYSDATE - 3 THEN 1 ELSE 0 END) AS recent_backup_done
FROM merged
GROUP BY db_name
)
SELECT
m.db_name,
m.host_name,
m.dg_role,
m.config_status,
NVL(TO_CHAR(m.last_backup_time, 'YYYY-MM-DD HH24:MI:SS'), 'N/A') AS last_backup_time,
CASE
WHEN g.any_configured = 0 AND g.recent_backup_done = 0 THEN '❌ NO BACKUP CONFIGURED OR RUN'
WHEN g.any_configured = 1 AND g.recent_backup_done = 0 THEN '⚠️ CONFIGURED BUT NO RECENT BACKUP'
WHEN g.any_configured = 0 AND g.recent_backup_done = 1 THEN '⚠️ BACKUP RUN BUT NO CONFIGURATION FOUND'
ELSE '✅ BACKUP OK (Primary or Standby Covered)'
END AS backup_protection_status
FROM merged m
JOIN grouped g ON m.db_name = g.db_name
ORDER BY backup_protection_status, m.db_name, m.host_name;
0 comments:
Post a Comment