Monday, 27 October 2008

Monitor the time an RMAN backup takes

To see what RMAN is doing now, and see what SID is doing what sort of work, and how much it has got left to do, use the following SQL. This script is good when you are trying to see how much work an RMAN Channels have got left to do. It is good to watch with the RMAN backup script log (tail -f) as the backup is hapenning. For both scripts you have to lonig as SYSDBA on the instance where the BACKUP
is or RESTORE hapenning.




select
sid,
start_time,
totalwork,
sofar,
(sofar/totalwork) * 100 pct_done
from
v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%'
/

SID START_TIM TOTALWORK SOFAR PCT_DONE
---------- --------- ---------- ---------- ----------
100 27-OCT-08 1554952 1364978 87.7826454





To watch the success or failure of an RMAN job in the past, or even when it is hapenning, you can use the dynamic v$ view v$rman_status. The following query will show you a history of your BACKUP and RESTORE operations. By changing the where start_time > sysdate -1 clause you control how much in the past you want to look at. I am using this on Oracle 10g, I don't know if it is available on Oracle 9i and before.



select to_char(start_time, 'dd-mon-yyyy@hh24:mi:ss') "Date",
status,
operation,
mbytes_processed
from v$rman_status vs
where start_time > sysdate -1
order by start_time
/

Date STATUS OPERATION MBYTES_PROCESSED
-------------------- ----------------------- --------------------------------- ----------------
27-oct-2008@11:40:11 FAILED RMAN 0
27-oct-2008@11:40:29 COMPLETED BACKUP 11812
27-oct-2008@12:06:30 COMPLETED BACKUP 23112
27-oct-2008@12:41:45 COMPLETED BACKUP 160
27-oct-2008@12:42:28 FAILED CONTROL FILE AUTOBACKUP 0
27-oct-2008@17:24:28 RUNNING RMAN 0
27-oct-2008@17:24:43 COMPLETED DELETE 0
27-oct-2008@17:24:51 COMPLETED CATALOG 0
27-oct-2008@17:25:16 RUNNING RESTORE 22082.875



No comments: