trying to create an RMAN report for 9i, 10g, and 11g repositories

I am working on setting up RMAN to run in our environment. We have Oracle Financials running 9i, and a mix of DSS, OLTP and datawarehouse aplications on 10g SR2 and 11g. We are putting the 9i metadata in the 10g repository.

My goal is to create one report that runs every day, and tells me what databases were backed up, start time, end time, and the size of the backup. If it weren't for 9i this would not be that hard. But somehow I have not gotten what I want out of the report yet. I created two triggers so that I could include the server name in the report. The main issue seems to be that the amount of data seems to be way off. Not to mention that I can't get the size of the backup for 9i. But everything else seems accurate.

Any help in reworking my report would be very appreciated.

thanks,

Brian

Code:

---

SERVER_NAME        DB_NAME CATALOG START_TIME        COMPLETION_TIME        MB
------------------ ------  ------- ------------------ ------------------ ----------
servera.domain.com SID_A    10G    15-JUL-2009:01:00  15-JUL-2009:01:03      257.6
serverb.domain.com SID_B    10G    15-JUL-2009:01:00  15-JUL-2009:01:01        N/A
serverc.domain.com SID_C    10G    15-JUL-2009:01:30  15-JUL-2009:01:31        N/A
serverd.domain.com SID_D    11G    14-JUL-2009:21:00  14-JUL-2009:21:05      405.0

---

Code:

---

CREATE OR REPLACE TRIGGER "RMAN10G"."BP_BI_TAG"
  BEFORE INSERT ON rman10g.bp
      FOR EACH ROW
BEGIN
  :NEW.comments        := SYS_CONTEXT('USERENV','HOST');
END bp_bi_tag;
/


CREATE OR REPLACE TRIGGER "RMAN11G"."BP_BI_TAG"
  BEFORE INSERT ON rman11g.bp
      FOR EACH ROW
BEGIN
  :NEW.comments        := SYS_CONTEXT('USERENV','HOST');
END bp_bi_tag;
/



COLUMN server_name    FORMAT A30
COLUMN db_name        FORMAT A8
COLUMN catalog        FORMAT A10
COLUMN start_time      FORMAT A20
COLUMN completion_time FORMAT A20
COLUMN MB              FORMAT A10

SELECT server_name, db_name, catalog,
      TO_CHAR(start_time,      'DD-MON-YYYY:HH24:MI') start_time,
      TO_CHAR(completion_time, 'DD-MON-YYYY:HH24:MI') completion_time,
      CASE MB
      WHEN 0 THEN LPAD('N/A', 10)
      ELSE TO_CHAR(MB, '999,990.0')
      END CASE
  FROM ( SELECT bp.comments server_name, db.db_name, '10G' catalog,
                MIN(start_time)      start_time,
                MAX(completion_time) completion_time,
                ROUND(SUM(NVL(bp.bytes, 0))/1024/1024, 1) MB
          FROM rman10g.bp
          INNER JOIN rman10g.dbinc DB
            ON bp.db_key = db.db_key
          WHERE bp.start_time > SYSDATE-1
          GROUP BY bp.comments, db.db_name, status
          UNION ALL
        SELECT bp.comments server_name, db.db_name,  '11G' catalog,
                MIN(start_time)      start_time,
                MAX(completion_time) completion_time,
                ROUND(SUM(NVL(bp.bytes, 0))/1024/1024, 1) MB
          FROM rman11g.bp
          INNER JOIN rman11g.dbinc DB
            ON bp.db_key = db.db_key
          WHERE bp.start_time > SYSDATE-1
          GROUP BY bp.comments, db.db_name, status )
 ORDER BY start_time, server_name, db_name;

---