Newsletters Archive - All

If you would like to know more about the iDevelopment.info 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.

-------------------------------------------------------------------------------

SELECT
    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
FROM
       v$session         a
  JOIN v$session_longops b USING (sid,serial#)
WHERE
      a.program LIKE 'rman%'
  AND b.opname LIKE 'RMAN%'
  AND b.opname NOT LIKE '%aggregate%'
  AND b.totalwork > 0
ORDER BY
    b.start_time
/

-------------------------------------------------------------------------------

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

http://www.idevelopment.info/cgi/ORACLE_dba_scripts.cgi

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------