-- ==========================================================
-- OEM Report: Databases Not Protected by Backup (Enhanced)
-- Author : Daidipya Upalekar (enhanced by Nikhil & ChatGPT)
-- Purpose: Identify databases (Primary/Standby) without
-- backup configuration or recent backup activity.
-- Handles Data Guard by grouping on DB_NAME.
-- ==========================================================
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
),
-- Backup configuration at target level
rman_configured AS (
SELECT DISTINCT
r.target_guid,
r.target_name,
r.host_name
FROM sysman.mgmt$ha_rman_config r
),
-- Most recent backup time at target level
recent_backup AS (
SELECT
b.target_guid,
MAX(b.end_time) AS last_backup_time
FROM sysman.mgmt$ha_backup b
WHERE UPPER(b.status) = 'SUCCESS'
GROUP BY b.target_guid
),
-- Combine target, property, config, and backup info
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 rc.target_guid IS NOT NULL THEN 'CONFIGURED'
ELSE 'NOT CONFIGURED'
END AS config_status,
rb.last_backup_time
FROM db_targets t
JOIN db_properties p ON t.target_guid = p.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
),
-- Group at DB_NAME level (so DG primary & standby are treated as one logical DB)
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,
MAX(last_backup_time) AS last_backup_time
FROM merged
GROUP BY db_name
)
-- Final output
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