-- ==========================================================
-- OEM Report: Databases Not Protected by Backup (Fixed)
-- Handles Data Guard properly using canonical base_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,
-- 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,
-- Only consider successful backups
MAX(CASE WHEN UPPER(NVL(b.status,'UNKNOWN')) = 'SUCCESS' THEN b.end_time END) AS last_success_time
FROM sysman.mgmt$ha_backup b
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 'CONFIGURED' ELSE 'NOT CONFIGURED' END AS config_status,
rb.last_success_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
),
-- Group by canonical base_name so primary + standby treated as one family
grouped AS (
SELECT
base_name,
MAX(CASE WHEN config_status = 'CONFIGURED' THEN 1 ELSE 0 END) AS any_configured,
MAX(CASE WHEN last_success_time >= SYSDATE - 3 THEN 1 ELSE 0 END) AS recent_backup_done,
MAX(last_success_time) AS last_backup_time
FROM merged
GROUP BY base_name
)
SELECT
m.base_name,
m.db_name AS example_member_db_name,
m.host_name,
m.dg_role,
m.config_status,
NVL(TO_CHAR(m.last_success_time,'YYYY-MM-DD HH24:MI:SS'), 'N/A') AS last_success_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 SUCCESSFUL 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,
ROUND(CASE WHEN g.last_backup_time IS NOT NULL THEN (SYSDATE - g.last_backup_time) ELSE NULL END, 2) AS days_since_last_success
FROM merged m
JOIN grouped g ON m.base_name = g.base_name
ORDER BY backup_protection_status, m.base_name, m.host_name;
0 comments:
Post a Comment