Backup Report from Enterprise Manager

How do we report on databases that haven’t been backed up? We create a report from Enterprise manager and see what has failed and what backups are potentially missing

Run this in Enterprise Manager

/* mark young 23-August 2011 */
SELECT  a.HOST_NAME,
       a.DISPLAY_NAME,
       CASE
          WHEN b.end_time < SYSDATE - 2 THEN '3. BACKUP OLDER THAN 2 DAYS'
          WHEN b.status='FAILED' THEN '1.FAILED'  
          WHEN b.status='COMPLETED WITH WARNINGS' THEN '4. COMPLETED WITH WARNINGS' 
          WHEN b.status='COMPLETED' THEN '5. COMPLETED' 
          ELSE NVL (b.status, '2. NO RMAN BACKUP')
       END
       status,
       c.database_ROLE,
       a.target_type,
       b.start_time,
       b.end_time,
       b.input_type,
       b.output_device_type,
       b.output_bytes_display
  FROM (  SELECT T.HOST_NAME,
                 T.DISPLAY_NAME,
                 T.target_guid,
                 T.target_type
            FROM mgmt$target t
           WHERE target_type = ('rac_database')
        GROUP BY T.HOST_NAME,
                 t.target_type,
                 t.target_guid,
                 T.DISPLAY_NAME) a, mgmt$ha_backup b, mgmt$ha_info c
 WHERE     a.target_guid = b.target_guid(+)
       AND a.target_guid = c.target_guid
       AND c.database_role = 'PRIMARY'
UNION
SELECT a.HOST_NAME,
       a.DISPLAY_NAME,
       CASE
          WHEN b.end_time < SYSDATE - 2 THEN '3. BACKUP OLDER THAN 2 DAYS'
          WHEN b.status='FAILED' THEN '1.FAILED'  
          WHEN b.status='COMPLETED WITH WARNINGS' THEN '4. COMPLETED WITH WARNINGS' 
          WHEN b.status='COMPLETED' THEN '5. COMPLETED' 
          ELSE NVL (b.status, '2. NO RMAN BACKUP')
       END
          status,
       c.database_ROLE,
       a.target_type,
       b.start_time,
       b.end_time,
       b.input_type,
       b.output_device_type,
       b.output_bytes_display
  FROM (  SELECT T.HOST_NAME,
                 T.DISPLAY_NAME,
                 T.target_guid,
                 T.target_type
            FROM mgmt$target t
           WHERE target_type = ('oracle_database')
                 AND t.type_qualifier3 <> 'RACINST'
        GROUP BY T.HOST_NAME,
                 t.target_type,
                 t.target_guid,
                 T.DISPLAY_NAME) a,
       mgmt$ha_backup b,
       mgmt$ha_info c
 WHERE     a.target_guid = b.target_guid(+)
       AND a.target_guid = c.target_guid
       AND c.database_role = 'PRIMARY'
ORDER BY 3