WITH db AS (
SELECT
TRIM(REGEXP_SUBSTR(t.target_name, '[^.]+')) AS db_name,
t.host_name AS host,
NVL(p_dg.property_value,'Single Instance') AS dg_role,
i.LOG_MODE,
t.target_guid
FROM mgmt$target t
LEFT JOIN mgmt$target_properties p_dg
ON p_dg.target_guid = t.target_guid AND p_dg.property_name = 'DataGuardStatus'
JOIN MGMT$DB_DBNINSTANCEINFO i
ON i.DATABASE_NAME LIKE t.target_name || '%'
WHERE t.target_type = 'oracle_database'
),
blackout AS (
SELECT DISTINCT TRIM(REGEXP_SUBSTR(b1.target_name, '[^.]+')) AS db_name
FROM mgmt$blackout_history b1
WHERE b1.status IN ('Started','Low')
AND SYSDATE BETWEEN b1.start_time AND NVL(b1.end_time,SYSDATE+1)
),
primary_backup AS (
SELECT
TRIM(REGEXP_SUBSTR(b.DATABASE_NAME, '[^.]+')) AS db_name,
MAX(b.end_time) AS last_backup
FROM mgmt$ha_backup b
WHERE b.end_time >= SYSDATE - 3 AND b.status = 'COMPLETED'
GROUP BY TRIM(REGEXP_SUBSTR(b.DATABASE_NAME, '[^.]+'))
),
standby_map AS (
SELECT
s.db_name AS standby,
p.db_name AS primary
FROM db s
JOIN db p ON p.dg_role = 'Primary' AND s.dg_role = 'Physical Standby'
WHERE TRIM(REGEXP_SUBSTR(s.LOG_MODE, '[^.]+')) = p.db_name
)
SELECT
d.db_name AS "Database",
d.host AS "Host",
d.dg_role AS "DG Role",
d.LOG_MODE AS "Log Mode",
NVL(b.db_name,'NO') AS "Blackout?",
NVL(TO_CHAR(p.last_backup,'YYYY-MM-DD HH24:MI'),'NEVER') AS "Last Backup",
CASE
WHEN d.LOG_MODE = 'NOARCHIVELOG' THEN 'OK (NoArch)'
WHEN b.db_name IS NOT NULL THEN 'OK (Blackout)'
WHEN d.dg_role = 'Physical Standby' AND sp.last_backup IS NOT NULL
THEN 'OK (Standby, Primary OK)'
ELSE 'ALERT: NO BACKUP 72H'
END AS "Status"
FROM db d
LEFT JOIN blackout b ON b.db_name = d.db_name
LEFT JOIN primary_backup p ON p.db_name = d.db_name
LEFT JOIN standby_map sm ON sm.standby = d.db_name
LEFT JOIN primary_backup sp ON sp.db_name = sm.primary
WHERE d.LOG_MODE = 'ARCHIVELOG'
AND b.db_name IS NULL
AND (d.dg_role IN ('Primary','Single Instance') OR sp.last_backup IS NULL)
AND p.last_backup IS NULL
ORDER BY p.last_backup NULLS FIRST;
0 comments:
Post a Comment