5 Replies Latest reply: Oct 17, 2013 9:29 AM by DK2010 RSS

    extract the backup report from rman catalog sorted by hostname

    user12019529

      Hi All,

       

      i have a catalog database .

       

      i have many database running on many servers.

       

      i need to extract the backup report from the catalog database sorted by hostname.

       

      please share any of the reporting scripts you have.

       

      Below is the script i have to extract backup report

      col TIME heading "TIME_SINCE_LAST_BACKUP(HOURS)" form 99999.99

      col BACKUP_SIZE heading "BACKUP_SIZE(GB)"

      col BACKUP_TYPE for a12

      col DURATION heading "BACKUP|DURATION|(MIN)" form 999.99

      col TIME heading "TIME_SINCE|LAST_BACKUP|(HOURS)" form 99999.99

      col BACKUP_SIZE heading "BACKUP_SIZE|(GB)"

       

      SELECT A.DB_NAME

           ,A.OBJECT_TYPE "BACKUP_TYPE"

           ,TO_CHAR(A.START_TIME,'dd/mon/yyyy hh24:mi:ss') START_TIME

      ,TO_CHAR(A.END_TIME,'dd/mon/yyyy hh24:mi:ss') END_TIME

      ,ROUND((A.END_TIME-A.START_TIME)*24*60,2) DURATION

           ,ROUND((SYSDATE-A.END_TIME)*24,2) TIME

      ,ROUND(OUTPUT_BYTES/1024/1024/1024,2) BACKUP_SIZE

      FROM rman.RC_RMAN_STATUS A,

           (SELECT DB_NAME,OBJECT_TYPE

           ,MAX(END_TIME) END_TIME

           FROM rman.RC_RMAN_STATUS

           WHERE

           OBJECT_TYPE IN ('DB FULL','DB INCR')

           AND

           STATUS like 'COMPLETED%'

           AND

           OPERATION in ('BACKUP','BACKUP COPYROLLFORWARD')

           GROUP BY DB_NAME,OBJECT_TYPE) B

      WHERE A.OBJECT_TYPE IN ('DB FULL','DB INCR','ARCHIVELOG')

           AND

           STATUS like 'COMPLETED%'

           AND

           OPERATION in ('BACKUP','BACKUP COPYROLLFORWARD')

           AND

           A.DB_NAME=B.DB_NAME

           AND

           A.END_TIME=B.END_TIME

           AND A.OBJECT_TYPE=B.OBJECT_TYPE and

           A.end_time > sysdate- 1.5

      ORDER BY 2,1 desc;