5 Replies Latest reply: Mar 25, 2013 11:33 AM by mseberg RSS

    RMAN report to a table

    VinayMummadi
      Hello,
      is there a way to create a RMAN report for all the backups that are being done in different databases and that report should be directly writing to a sample table.

      the report should contain the colums like -
      DBNAME BKPTYPE DATE STATUS STARTTIME ENDTIME .. and so on..

      i've tried using some v$views like V$RMAN_BACKUP_JOB_DETAILS,V$RMAN_BACKUP_JOB_DETAILS,V$BACKUP_SET_DETAILS. etc... but i need a some sql or pl/sql that should give me the complete report.

      thanks,
      vinay
        • 1. Re: RMAN report to a table
          EdStevens
          Vinay Mummadi wrote:
          Hello,
          is there a way to create a RMAN report for all the backups that are being done in different databases and that report should be directly writing to a sample table.

          the report should contain the colums like -
          DBNAME BKPTYPE DATE STATUS STARTTIME ENDTIME .. and so on..

          i've tried using some v$views like V$RMAN_BACKUP_JOB_DETAILS,V$RMAN_BACKUP_JOB_DETAILS,V$BACKUP_SET_DETAILS. etc... but i need a some sql or pl/sql that should give me the complete report.

          thanks,
          vinay
          I doubt if anyone is going to have such a query in their back pocket, but it should be a simple matter of coding. Instead of the v$ views, you should be looking at the [url http://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcviews001.htm]rman catalog views. Of course, that presupposes you are using a catalog -- which you should be doing if you are trying to manage backups from multiple databases.
          • 2. Re: RMAN report to a table
            J19
            Hi Vinay,

            Do you have Grid control then you can easly generate the backup report from single view

            SQL> desc mgmt$ha_backup
            Name Null? Type
            ----------------------------------------- -------- -----------------
            HOST VARCHAR2(256)
            DATABASE_NAME NOT NULL VARCHAR2(256)
            TARGET_TYPE NOT NULL VARCHAR2(64)
            DISPLAY_NAME VARCHAR2(256)
            TARGET_GUID NOT NULL RAW(16)
            SESSION_KEY NUMBER
            SESSION_RECID NUMBER
            SESSION_STAMP NUMBER
            COMMAND_ID VARCHAR2(33)
            STATUS VARCHAR2(23)
            START_TIME DATE
            END_TIME DATE
            TIME_TAKEN_DISPLAY VARCHAR2(4000)
            INPUT_TYPE VARCHAR2(13)
            OUTPUT_DEVICE_TYPE VARCHAR2(17)
            INPUT_BYTES_DISPLAY VARCHAR2(4000)
            OUTPUT_BYTES_DISPLAY VARCHAR2(4000)
            OUTPUT_BYTES_PER_SEC_DISPLAY VARCHAR2(4000)


            Regards,
            Jey
            • 3. Re: RMAN report to a table
              J19
              Hi Vinay,

              are you using recovery catalog then you can generate the backup report for all databases using the following query.

              select
              session_key, db_name,
              min(r.start_time) start_rman,
              min(c.checkpoint_time) start_controlfile,
              min(d.checkpoint_time) start_datafile,
              min(a.first_time) start_archivelog,
              max(a.next_time) end_archivelog,
              min(b.start_time) start_set,
              max(b.completion_time)
              end_set, min(p.start_time)
              start_piece, max(p.completion_time) end_piece
              from
              rman.rc_backup_controlfile_details c
              join
              rman.rc_backup_datafile_details d
              on c.session_key = d.session_key
              join
              rman.rc_backup_archivelog_details a
              on c.session_key = a.session_key
              join
              rman.rc_backup_set_details b
              on c.session_key = b.session_key
              join
              rman.rc_backup_piece_details p
              on c.session_key = p.session_key
              join
              rman.rc_rman_backup_job_details r
              on c.session_key = r.session_key
              where
              db_key =
              (select db_key
              from rman.rc_database
              where name = 'alice1')
              and
              c.checkpoint_time >sysdate-14
              and
              d.checkpoint_time >sysdate-14
              and
              r.start_time >sysdate-14
              and
              a.first_time >sysdate-14
              and
              b.start_time >sysdate-14
              and
              p.start_time >sysdate-14
              and
              a.next_time >sysdate-14
              and
              b.completion_time >sysdate-14
              and
              p.completion_time >sysdate-14
              group by
              session_key,
              db_name;

              Regards,
              Jey
              • 4. Re: RMAN report to a table
                VinayMummadi
                Hi Jey,

                i tried your query mgmt$ha_backup, the result is closer but not exactly that I was looking for. It doesn't show up archivelogs status, just gives me input_type as DB INCR.

                for the second query- i did not tried it, as we don't have a catalog database. Wanted to know what are the advantages and disadvantages of having a catalog database?

                thanks
                Vinay
                • 5. Re: RMAN report to a table
                  mseberg
                  Hello;
                   The advantages and disadvantages of having a catalog database
                  The only disadvantage I can think of you have 1 more database to maintain.


                  The advantages are described here :


                  Benefits of Using the Recovery Catalog as the RMAN Repository

                  Oracle 9

                  http://docs.oracle.com/cd/B10501_01/server.920/a96566/rcmquick.htm#442214


                  Oracle 11

                  http://docs.oracle.com/cd/E25054_01/backup.1111/e10642/rcmcatdb.htm


                  Also

                  Tool to Present a Consolidated View of the Status of RMAN Backups

                  http://arup.blogspot.com/2010/11/tool-to-present-consolidated-view-of.html


                  Best Regards

                  mseberg