-
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|