RMAN Performance

Monitoring RMAN Job Progress

select sid, serial#, sofar, totalwork, opname,
round(sofar/totalwork*100,2) "% Complete"
from v$session_longops
 where opname LIKE 'RMAN%'
and opname NOT LIKE '%aggregate%'
 and totalwork != 0
and sofar <> totalwork;

 set lines 300 
set operation format a10 
alter session set optimizer_mode=RULE; 
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss'; 
select distinct p.tag,r.object_type,r.SESSION_RECID,r.START_TIME,r.END_TIME,(r.END_TIME-r.START_TIME)*24 "ET(Hr)", 
r.OUTPUT_BYTES/1048576/1024 "SIZE (GB)",r.status,r.OUTPUT_DEVICE_TYPE,r.input_bytes/1048576 "input (MB)",INPUT_BYTES_PER_SEC/1048576 "read (MB/sec)",r.output_bytes/1048576 "output (MB)",OUTPUT_BYTES_PER_SEC/1048576 "output (MB/sec)" 
from v$backup_piece p,v$rman_status r, v$rman_backup_job_details d 
where p.RMAN_STATUS_RECID=r.RECID and p.RMAN_STATUS_STAMP=r.STAMP 
and r.status like '%COMPLETED%' and r.OPERATION like '%BACKUP%' 
and r.object_type like 'DB%' 
and d.SESSION_RECID=r.SESSION_RECID;



SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
select sid, totalwork, sofar, (sofar/totalwork) * 100 per_done,start_time, sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and lower(opname) not like ‘%aggregate%’
and lower(opname) like ‘rman%’;
For Export:

SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;

SQL>
select sid, totalwork, sofar, round((sofar/totalwork) * 100) done,start_time,sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and upper(message) like ‘%EXPORT%’;

For Import:
SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;

SQL>
select sid, totalwork, sofar, round((sofar/totalwork) * 100) per_done,start_time,sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and upper(message) like ‘%IMPORT%’;

scripts to check backup status and timings of database backups -
This script will be run in the database, not the catalog.

Login as sysdba -

This script will report on all backups – full, incremental and archivelog backups -
col STATUS format a9
col hrs format 999.99

select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

This script will report all on full and incremental backups, not archivelog backups -
col STATUS format a9
col hrs format 999.99

select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;