Oracle RMAN timing details

18 Oct

RMAN - View session info with pid

SELECT s.sid, p.spid, s.client_info 
FROM v$process p, v$session s 
WHERE p.addr = s.paddr 
AND CLIENT_INFO LIKE 'rman%';

RMAN – View time remaining

SELECT sid, serial#, opname, time_remaining
FROM v$session_longops
WHERE sid in (SELECT sid FROM v$session
WHERE program like '%rman%');

SELECT
  sid,
  to_char(start_time,'ddmmyyyy hh24:mi:ss'),
  totalwork,
  sofar, 
 (sofar/totalwork) * 100 pct_done
FROM v$session_longops
WHERE totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%';

RMAN – View backup details

SELECT b.file#, d.name, to_char(b.creation_change#) creation, 
nvl(to_char(b.incremental_level),'FULL') Incr, 
to_char(b.incremental_change#) inc#,
to_char(b.checkpoint_change#) ckp#, 
to_char(b.completion_time,'dd-mm-yyyy hh24:mi:ss') 
FROM v$backup_datafile b JOIN v$datafile d 
ON d.FILE# = b.FILE# 
WHERE b.completion_time > sysdate-4
ORDER BY b.completion_time desc;

RMAN – backup durations

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,
round(elapsed_seconds/3600, 2) hrs from V$RMAN_BACKUP_JOB_DETAILS
WHERE input_type = 'DB FULL'
ORDER BY session_key desc, start_time desc;

Comments