JAVA J2EE 4 U

 /********************************************************************

 *  FINAL BOSS QUERY – YOUR LOGIC, 100% CORRECT

 *  Rule: First 4 or 5 chars = FAMILY

 *  IF PRIMARY HAS BACKUP → STANDBY IS SAFE (never listed)

 *  IF STANDBY HAS BACKUP → PRIMARY IS SAFE (never listed)

 ********************************************************************/

WITH dbs AS (

  SELECT 

    TRIM(REGEXP_SUBSTR(t.target_name,'[^.]+'))           AS db_name,

    t.host_name                                          AS host,

    i.LOG_MODE,

    SUBSTR(TRIM(REGEXP_SUBSTR(t.target_name,'[^.]+')),1,

           CASE WHEN REGEXP_LIKE(SUBSTR(TRIM(REGEXP_SUBSTR(t.target_name,'[^.]+')),5,1),'[0-9]')

                THEN 5 ELSE 4 END)                     AS family

  FROM mgmt$target t

  JOIN MGMT$DB_DBNINSTANCEINFO i 

    ON i.DATABASE_NAME LIKE t.target_name || '%'

  WHERE t.target_type = 'oracle_database'

),

family_backup AS (

  SELECT DISTINCT

    SUBSTR(TRIM(REGEXP_SUBSTR(DATABASE_NAME,'[^.]+')),1,

           CASE WHEN REGEXP_LIKE(SUBSTR(TRIM(REGEXP_SUBSTR(DATABASE_NAME,'[^.]+')),5,1),'[0-9]')

                THEN 5 ELSE 4 END) AS family

  FROM mgmt$ha_backup

  WHERE end_time >= SYSDATE - 3

    AND status = 'COMPLETED'

),

family_blackout AS (

  SELECT DISTINCT family

  FROM mgmt$blackout_history b

  JOIN dbs d ON TRIM(REGEXP_SUBSTR(b.target_name,'[^.]+')) = d.db_name

  WHERE b.status IN ('Started','Low')

    AND SYSDATE BETWEEN b.start_time AND NVL(b.end_time,SYSDATE+1)

)

-- ONLY list DBs where:

--   1. Family has ZERO backup

--   2. Not in blackout

--   3. Is ARCHIVELOG

SELECT 

  db_name      AS "Database",

  host         AS "Host",

  family || 'xx' AS "Family",

  LOG_MODE     AS "Log Mode",

  'ALERT: NO BACKUP 72H' AS "Status"

FROM dbs

WHERE LOG_MODE = 'ARCHIVELOG'

  AND family NOT IN (SELECT family FROM family_backup)

  AND family NOT IN (SELECT family FROM family_blackout)

ORDER BY family, db_name;