JAVA J2EE 4 U

 WITH all_dbs AS (

    SELECT target_guid, target_name, host_name

    FROM sysman.mgmt$target

    WHERE target_type IN ('oracle_database', 'rac_database')

),

rman_configured AS (

    SELECT DISTINCT target_name FROM sysman.mgmt$rman_config

),

backup_runs AS (

    SELECT DISTINCT database_name FROM sysman.mgmt$ha_backup

)

SELECT

    TRIM(SUBSTR(a.target_name, 1, INSTR(a.target_name, '.') - 1)) AS database_name,

    a.host_name,

    CASE

        WHEN r.target_name IS NOT NULL THEN 'OEM RMAN CONFIGURED'

        WHEN b.database_name IS NOT NULL THEN 'BACKUP RUN FOUND (Manual/External)'

        ELSE 'NO BACKUP CONFIGURED'

    END AS backup_status

FROM all_dbs a

LEFT JOIN rman_configured r

    ON TRIM(SUBSTR(a.target_name, 1, INSTR(a.target_name, '.') - 1)) =

       TRIM(SUBSTR(r.target_name, 1, INSTR(r.target_name, '.') - 1))

LEFT JOIN backup_runs b

    ON TRIM(SUBSTR(a.target_name, 1, INSTR(a.target_name, '.') - 1)) =

       TRIM(SUBSTR(b.database_name, 1, INSTR(b.database_name, '.') - 1))

ORDER BY backup_status, a.host_name;