******************************************************
* Description: SQL for checking RMAN restore progress
* Compatiablity: RDBMS 11g, 12c
* Date: 03:16 PM EST, 04/06/2017
******************************************************

<1> Checking RMAN restore progress:
     |
     |__ SQL> set linesize 126
              column Pct_Complete format 999.99
              column client_info format a25
              column sid format 999
              column MB_PER_S format 999.99
              select s.client_info,
              l.sid,
              l.serial#,
              to_char(l.START_TIME,'dd-mm-yy hh:mm:ss') started_at,
              l.sofar,
              l.totalwork,
              round (l.sofar / l.totalwork*100,2) "Pct_Complete",
              aio.MB_PER_S,
              to_char((sysdate + l.TIME_REMAINING/3600/24),'dd-mm-yy hh:mm:ss') end_at
              from v$session_longops l,
              v$session s,
              (select sid,
              serial,
              100* sum (long_waits) / sum (io_count) as "LONG_WAIT_PCT",
              sum (effective_bytes_per_second)/1024/1024 as "MB_PER_S"
              from v$backup_async_io
              group by sid, serial) aio
              where aio.sid = s.sid
              and aio.serial = s.serial#
              and l.opname like 'RMAN%'
              and l.opname not like '%aggregate%'
              and l.totalwork != 0
              and l.sofar <> l.totalwork
              and s.sid = l.sid
              and s.serial# = l.serial#
              order by 1;

			  
             CLIENT_INFO                SID    SERIAL# STARTED_AT             SOFAR  TOTALWORK Pct_Complete MB_PER_S END_AT
             ------------------------- ---- ---------- ----------------- ---------- ---------- ------------ -------- -----------------
             rman channel=c1            222          7 06-04-17 06:04:31    2495576   12819072        19.47   336.62 06-04-17 07:04:02
             rman channel=c2             34          3 06-04-17 06:04:31    1619308   12825792        12.63   100.70 06-04-17 07:04:43
             rman channel=c3             66          3 06-04-17 06:04:31    1866846   12936704        14.43   224.71 06-04-17 07:04:34
             rman channel=c4             99          3 06-04-17 06:04:31    2141115   12851712        16.66   249.58 06-04-17 07:04:45