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, 
       ( sofar / totalwork ) * 100 pct_done 
FROM   v$session_longops 
WHERE  totalwork > sofar 
       AND opname NOT LIKE '%aggregate%' 
       AND opname LIKE 'RMAN%' 


---------- --------- ---------- ---------- ----------
       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", 
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: