Backup report oem with catalog

SELECT db_name AS "Database",
backup_type AS "Type",
backup_status AS "Status",
start_time AS "Start Time",
end_time AS "End Time",
elapsed AS "Elapsed (Min)",
backup_size_mb AS "Size (MB)"
FROM report_user.rman_report_xxx_dba


SELECT INITCAP(backup_status),
 count(1) AS Count
FROM report_user.rman_report_xxx_dba
WHERE (
 (
  backup_type IS NOT NULL
  AND backup_type IN ('ARCHIVELOG','DB INCR', 'DB FULL')
 )
 OR backup_status LIKE '%ERROR%'
 )
GROUP BY backup_status


DROP MATERIALIZED VIEW RMAN_REPORT_xxx_DBA;
CREATE MATERIALIZED VIEW RMAN_REPORT_xxx_DBA (CBU,DB_NAME,DESCRIPTION,BACKUP_TYPE,BACKUP_STATUS,START_TIME,END_TIME,ELAPSED,BACKUP_SIZE_MB)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
/* Formatted on 11/20/2014 10:55:10 AM (QP5 v5.267.14150.38599) */
SELECT p.department AS cbu,
       r.db_name AS db_name,
       p.user_comment AS description,
       r.object_type AS backup_type,
       r.status AS backup_status,
       INITCAP (TO_CHAR (r.start_time, 'MONTH DD HH24:MI:SS')) AS start_time,
       INITCAP (TO_CHAR (r.end_time, 'MONTH DD HH24:MI:SS')) AS end_time,
       ROUND ( ( (r.end_time - r.start_time) * 24 * 60), 2) AS elapsed,
       r.mbytes_processed AS backup_size_mb
  FROM rman01.rc_rman_status@catalog r
       JOIN sysman.em_targets t
          ON UPPER (t.target_name) =
                DECODE (r.db_name,
                        'PF', 'PF1',
                        'PF', 'PF.xxx.COM',
                        'POLAP', 'POLAP.xxx.COM',
      'STM', 'PSTM',
                        'NPB', 'APNPB',
                        'NPP', 'APNPP',
                        UPPER (r.db_name))
       LEFT OUTER JOIN sysman.em_global_target_properties p
          ON p.target_guid = t.target_guid
 WHERE     start_time > (SYSDATE - 1)
       AND (   (    r.object_type IS NOT NULL
                AND r.object_type IN ('ARCHIVELOG', 'DB INCR', 'DB FULL'))
            OR r.status LIKE '%ERROR%')
       AND p.department IN ('HHH UUU AA');


COMMENT ON MATERIALIZED VIEW RMAN_REPORT_xxx_DBA IS 'snapshot table for snapshot REPORT_USER.RMAN_REPORT_xxx_DBA';

GRANT SELECT ON RMAN_REPORT_xxx_DBA TO MGMT_VIEW;



BEGIN
  SYS.DBMS_SCHEDULER.DROP_JOB
    (job_name  => 'REFRESH_RMAN_REPORT_MVIEW');
END;
/

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'REFRESH_RMAN_REPORT_MVIEW'
      ,start_date      => TO_TIMESTAMP_TZ('2013/07/26 10:18:57.506873 America/Los_Angeles','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=DAILY;BYHOUR=7;BYMINUTE=50;BYSECOND=0'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'begin
  dbms_mview.refresh(''RMAN_REPORT'');
  dbms_mview.refresh(''RMAN_REPORT_HAEA_DBA'');
end;'
      ,comments        => 'Refresh the rman_report materialized view for Backup Report'
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'REFRESH_RMAN_REPORT_MVIEW'
     ,attribute => 'RESTARTABLE'
     ,value     => TRUE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'REFRESH_RMAN_REPORT_MVIEW'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'REFRESH_RMAN_REPORT_MVIEW'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'REFRESH_RMAN_REPORT_MVIEW'
     ,attribute => 'MAX_RUNS');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'REFRESH_RMAN_REPORT_MVIEW'
     ,attribute => 'STOP_ON_WINDOW_CLOSE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'REFRESH_RMAN_REPORT_MVIEW'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'REFRESH_RMAN_REPORT_MVIEW'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'REFRESH_RMAN_REPORT_MVIEW'
     ,attribute => 'AUTO_DROP'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'REFRESH_RMAN_REPORT_MVIEW'
     ,attribute => 'RAISE_EVENTS'
     ,value     => SYS.DBMS_SCHEDULER.JOB_FAILED);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'REFRESH_RMAN_REPORT_MVIEW');
END;
/




Modifying report :D

SELECT db_name AS db_name,
       status AS backup_status FROM rman01.rc_rman_status@xxx where DB_NAME='PDWH' and   start_time > (SYSDATE - 1);


delete from rman01.rc_rman_status@xxx where DB_NAME='PDWH' and   start_time > (SYSDATE - 1) and STATUS='COMPLETED WITH WARNINGS';