/*
 * Title  : rmn_job_prg.sql
 * Author : EXADIME LLC
 * Created: 30-Apr-2018
 * Updated: 31-Oct-2021
 * Release: 1.0.0
 * Purpose: Shows RMAN job progress.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on
set termout on

set linesize 200
set pagesize 0
set pause off
set long 80

set heading on
set headsep on
set underline on
set colsep " "

set define on
set verify off
set escape off
set embedded on

col recid              for 9999999			head "Id"
col dbsize_mbytes      for 99,999,990.00  justify right head "DB Size"
col input_mbytes       for 99,999,990.00  justify right head "Read"
col output_mbytes      for 99,999,990.00  justify right head "MBytes|Written"
col output_device_type for a10            justify left  head "Device"
col complete           for 990.00         justify right head "%|Complete" 
col compression        for 990.00         justify right head "%|Compress"
col est_complete       for a20		  justify left  head "Completion"

col sno		       for 999	  head '#'
col instance_name      for a10    head 'Instance'
col sid                           head 'SID'
col serial#                       head 'Serial#'
col client_info        for a30    head 'Channel'
col opname             for a30    head 'Operation'
col start_time         for a18    head 'Start Time'
col totalwork                     head 'Total Work'
col sofar                         head 'So Far'
col pct_complete                  head '%|Complete'
col elapsed_seconds               head 'Seconds|Elapsed'
col time_remaining                head 'Seconds|Remaining'
col done_at            for a18    head 'Estimate|Complete'

set termout off;
col current_instance new_value current_instance noprint;
select rpad(instance_name, 17) current_instance from v$instance;
set termout on;

spool &&rpt_dir/rmn_job_prg.rpt

select 
	recid,
	output_device_type,
	dbsize_mbytes,
	input_bytes/1024/1024 input_mbytes,
	output_bytes/1024/1024 output_mbytes,
	(output_bytes/input_bytes*100) compression,
	(mbytes_processed/dbsize_mbytes*100) complete,
	to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
from 
	v$rman_status rs,
	(select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile)
where 
	status='RUNNING'
	and output_device_type is not null;

select
	row_number() over(order by 10)    sno,
	instance_name,
	sid,
	serial#,
	client_info,
	opname,
	start_time,
	totalwork,
	sofar,
	pct_complete,
	elapsed_seconds,
	time_remaining,
	done_at
from (
	select
		i.instance_name,
		sid,
		serial#,
		a.client_info,
		substr(b.opname,1,30)						opname,
		to_char(b.start_time, 'mm/dd/yy hh24:mi:ss')			start_time,
		b.totalwork							totalwork,
		b.sofar								sofar,
		round((b.sofar/decode(b.totalwork,0,0.001,b.totalwork)*100),2)  pct_complete,
		b.elapsed_seconds						elapsed_seconds,
		b.time_remaining						time_remaining,
		decode(b.time_remaining,0,
		to_char((b.start_time + b.elapsed_seconds/3600/24), 'mm/dd/yy hh24:mi:ss'),
		to_char((sysdate + b.time_remaining/3600/24), 'mm/dd/yy hh24:mi:ss')
		) done_at
	from
		gv$session         a
	join	gv$session_longops b using (sid,serial#)
	join	gv$instance        i on (i.inst_id = a.inst_id and i.inst_id = b.inst_id)
	where
		a.program like 'rman%'
	and	b.opname like 'RMAN%'
	and	b.opname not like '%aggregate%'
	and	b.totalwork > 0
) where
	pct_complete<>100
order by
	10;

spool off

clear columns

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80
set long 80

set termout on
set serverout off
set feedback on
set echo off
