Monitor RMAN restore

Here are some queries you can play with while waiting for an RMAN restore to complete. These queries are useful to monitor the restore, tracks progress (percentage completed and throughput) and to identify potential problems.

TTITLE LEFT '% Completed. Aggregate is the overall progress:'
SET LINE 132
SELECT opname, round(sofar/totalwork*100) "% Complete"
  FROM gv$session_longops
 WHERE opname LIKE 'RMAN%'
   AND totalwork != 0
   AND sofar <> totalwork
 ORDER BY 1;

TTITLE LEFT 'Channels waiting:'
COL client_info FORMAT A15 TRUNC
COL event FORMAT A20 TRUNC
COL state FORMAT A7
COL wait FORMAT 999.90 HEAD "Min waiting"
SELECT s.sid, p.spid, s.client_info, status, event, state, seconds_in_wait/60 wait
  FROM gv$process p, gv$session s
 WHERE p.addr = s.paddr
   AND client_info LIKE 'rman%';

TTITLE LEFT 'Files currently being written to:'
COL filename FORMAT a50
SELECT filename, bytes, io_count
  FROM v$backup_async_io
 WHERE status='IN PROGRESS'
/

TTITLE OFF
SET HEAD OFF
SELECT 'Throughput: '||
       ROUND(SUM(v.value/1024/1024),1) || ' Meg so far @ ' ||
       ROUND(SUM(v.value     /1024/1024)/NVL((SELECT MIN(elapsed_seconds)
            FROM v$session_longops
            WHERE opname          LIKE 'RMAN: aggregate input'
              AND sofar           != TOTALWORK
              AND elapsed_seconds IS NOT NULL
       ),SUM(v.value     /1024/1024)),2) || ' Meg/sec'
 FROM gv$sesstat v, v$statname n, gv$session s
WHERE v.statistic# = n.statistic#
  AND n.name       = 'physical write total bytes'
  AND v.sid        = s.sid
  AND v.inst_id    = s.inst_id
  AND s.program LIKE 'rman@%'
GROUP BY n.name
/
SET HEAD ON

Example output

% Completed. Aggregate is the overall progress:
OPNAME                                                          % Complete
--------------------------------------------------------------- ----------
RMAN: aggregate input                                                   80
RMAN: full datafile restore                                             75

Channels waiting
SID SPID CLIENT_INFO     STATUS   EVENT                STATE   Min waiting
--- ---- --------------- -------- -------------------- ------- -----------
883 924  rman channel=t1 ACTIVE   Backup: sbtrestore   WAITING      122.57
882 930  rman channel=t2 ACTIVE   Backup: sbtread2     WAITING         .15
881 937  rman channel=t3 INACTIVE SQL*Net message from WAITING       67.72
424 939  rman channel=t4 INACTIVE SQL*Net message from WAITING       23.42

Files currently being written to
FILENAME                                                  BYTES   IO_COUNT
---------------------------------------------------- ---------- ----------
/dev/data/orcl_system_d1                             6251741184      47698
/dev/data/orcl_users_d7                              6251610112      47697

Throughput: 94414.4 Meg so far @ 7.48 Meg/sec