Newsletters Archive - All

If you would like to know more about the Newsletter, please email me.

  Timing Oracle RMAN Operations — (21-April-2009)

Whether running an Oracle RMAN backup or a restore job, there are times when it 
becomes necessary to estimate the RMAN operations within that job. One quick 
solution, as demonstrated in this update, is to query the timing statistics 
found in the dynamic performance view v$session_longops.

The following query provides a simple example of how to use the timing data 
found in v$session_longops and join it to v$session to estimate currently 
running RMAN operations.


    sid                                             sid
  , serial#                                         serial_num
  , b.opname                                        opname
  , TO_CHAR(b.start_time, 'mm/dd/yy HH24:MI:SS')    start_time
  , b.totalwork                                     totalwork
  , b.sofar                                         sofar
  , ROUND( (b.sofar/DECODE(   b.totalwork
                            , 0
                            , 0.001
                            , b.totalwork)*100),2)  pct_done
  , b.elapsed_seconds                               elapsed_seconds
  , b.time_remaining                                time_remaining
  , DECODE(   b.time_remaining
            , 0
            , TO_CHAR((b.start_time + b.elapsed_seconds/3600/24), 'mm/dd/yy HH24:MI:SS')
            , TO_CHAR((SYSDATE + b.time_remaining/3600/24), 'mm/dd/yy HH24:MI:SS')
    ) done_at
       v$session         a
  JOIN v$session_longops b USING (sid,serial#)
      a.program LIKE 'rman%'
  AND b.opname LIKE 'RMAN%'
  AND b.opname NOT LIKE '%aggregate%'
  AND b.totalwork > 0


This script along with a collection of other useful database management scripts 
can be found at:

Jeffrey M. Hunter, OCP
Sr. Database Administrator