RMAN SQL TO FIND BACKUP DETAILS ..

RMAN SQL TO FIND BACKUP DETAILS ..


Rman View in 11g ..
select table_name from dict where table_name like 'V$%RMAN%';



SQL> select name from v$fixed_table where name like '%RMAN%';

NAME
------------------------------
GV$RMAN_CONFIGURATION
V$RMAN_CONFIGURATION
GV$RMAN_STATUS_CURRENT
V$RMAN_STATUS
GV$RMAN_OUTPUT
V$RMAN_OUTPUT
V$RMAN_BACKUP_SUBJOB_DETAILS
V$RMAN_BACKUP_JOB_DETAILS
V$RMAN_BACKUP_TYPE
GV$RMAN_ENCRYPTION_ALGORITHMS
V$RMAN_ENCRYPTION_ALGORITHMS
GV$RMAN_COMPRESSION_ALGORITHM
V$RMAN_COMPRESSION_ALGORITHM

13 rows selected.


V$RMAN_COMPRESSION_ALGORITHM provides descriptions of supported compression algorithms. It is used by the RMAN client.
V$RMAN_CONFIGURATION         Information about RMAN persistent configuration settings.
V$RMAN_ENCRYPTION_ALGORITHMS displays supported encryption algorithms. It is used by the RMAN client to validate user-requested algorithms. 

set linesize 300 pagesize 300
CLEAR COLUMNS BREAKS COMPUTES
COLUMN "value" 	FORMAT a100		
select name ||' = '|| value "value" FROM v$rman_configuration ORDER BY name;

value
----------------------------------------------------------------------------------------------------
CONTROLFILE AUTOBACKUP = ON
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE = DISK TO '/dumps/DGBDR/cf_%F'
RETENTION POLICY = TO REDUNDANCY 1

set linesize 200 pagesize 200 
col username  form a10
col kill        form a20
col os_id  form a6
col client_info form a24
col action  form a21
SELECT
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill
-- ,a.sid || ',' || a.serial# AS kill_string, b.spid AS OS_ID
,a.username
,(CASE WHEN a.client_info IS NULL AND a.action IS NOT NULL THEN 'First Default'
 WHEN a.client_info IS NULL AND a.action IS NULL THEN 'Polling'
 ELSE a.client_info
 END) client_info
,a.action
FROM gv$session a ,gv$process b
WHERE a.program like '%rman%'
and a.inst_id = b.inst_id 
AND a.paddr = b.addr;




set lines 200
column sid format 9999
column spid format 99999
column client_info format a25
column event format a30
column secs format 9999
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,sid, spid, client_info, event, seconds_in_wait secs, p1, p2, p3 from gv$process p, gv$session s 
where p.addr = s.paddr
and p.inst_id=s.inst_id 
and client_info like 'rman channel=%';



set pagesize 200
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;


select ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill, start_time,totalwork, sofar, (sofar/totalwork) * 100 done,sysdate + time_remaining/3600/24 end_at from gv$session_longops 
where totalwork > sofar 
AND opname NOT LIKE '%aggregate%' 
AND opname like 'RMAN%';


select recid,set_stamp,sequence#,first_change#,next_change#   from  v$backup_redolog;


gv$rman_output

select * from v$rman_status 
where session_recid = (select max(session_recid) from v$rman_status) 
order by recid;


alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
set lines 200
set numf 999999999999
col status for a25
select start_time, end_time, operation, object_type, status, output_bytes from v$rman_status where session_recid = (select max(session_recid) from v$rman_status) 
order by recid;


column elapsed format a25
select to_char(start_time,'dd-mm-yyyy:hh24:mi:ss') start_time, to_char(end_time,'dd-mm-yyyy:hh24:mi:ss') end_time, 
       lpad(round(floor((end_time-start_time)*24*60*60)/3600),2,'0')
       || ':' ||
       lpad(floor((((end_time-start_time)*24*60*60) -
       floor(((end_time-start_time)*24*60*60)/3600)*3600)/60),2,'0')
       || ':' ||
       lpad(round((((end_time-start_time)*24*60*60) -
       floor(((end_time-start_time)*24*60*60)/3600)*3600 -
       (floor((((end_time-start_time)*24*60*60) -
       floor(((end_time-start_time)*24*60*60)/3600)*3600)/60)*60))),2,'0') elapsed, output_bytes  
from v$rman_backup_job_details 
order by start_time;


select to_char(start_time,'dd-mm-yyyy:hh24:mi:ss') start_time, to_char(end_time,'dd-mm-yyyy:hh24:mi:ss') end_time, output_bytes  
from v$rman_backup_job_details 
order by start_time;



select session_recid,to_char(start_time,'yyyy-mm-dd:hh24:mi:ss') start_time, to_char(end_time,'yyyy-mm-dd:hh24:mi:ss') end_time, output_bytes  
from v$rman_backup_job_details a
where session_recid = (select max(session_recid) from v$rman_backup_job_details b );

select trunc(START_TIME),sum(OUTPUT_BYTES)/1000000  PROCESSED_IN_MB from v$rman_status where STATUS ='COMPLETED'
group by trunc(START_TIME)
order by 1 desc


TRUNC(START_TIME)   PROCESSED_IN_MB
------------------- ---------------
03-05-2017 00:00:00              59
02-05-2017 00:00:00          277230
01-05-2017 00:00:00          120287
30-04-2017 00:00:00          116449


set lines 200 pages 200 
col "Start Time" for a20;
col "End Time" for a20;
col STATUS for a15;
select  to_char(START_TIME, 'DD-MON-YY HH24:MI:SS') "Start Time", 
 to_char(END_TIME, 'DD-MON-YY HH24:MI:SS') "End Time", 
 STATUS "Status", 
 INPUT_BYTES/1024/1024/1024 "Input Bytes (in GB)", 
 OUTPUT_BYTES/1024/1024/1024 "Output Bytes (in GB)", 
 ELAPSED_SECONDS "Time in Seconds"
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;


set pages 200
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 like 'DB%'
order by session_key;


SESSION_KEY INPUT_TYPE    STATUS    START_TIME     END_TIME           HRS
----------- ------------- --------- -------------- -------------- -------
      12283 DB INCR       COMPLETED 04/21/17 00:00 04/21/17 07:52    7.88
      12295 DB INCR       COMPLETED 04/22/17 00:00 04/23/17 09:54   33.91
      12302 DB INCR       COMPLETED 04/23/17 00:00 04/23/17 10:39   10.65
      12317 DB INCR       COMPLETED 04/24/17 00:00 04/24/17 07:33    7.55
      12329 DB INCR       COMPLETED 04/25/17 00:00 04/25/17 07:58    7.97
      12341 DB INCR       COMPLETED 04/26/17 00:00 04/26/17 15:44   15.75
      12353 DB INCR       COMPLETED 04/27/17 00:00 04/27/17 06:58    6.97
      12365 DB INCR       COMPLETED 04/28/17 00:00 04/28/17 07:55    7.92
      12377 DB INCR       FAILED    04/29/17 00:00 04/30/17 09:06   33.11
      12384 DB INCR       COMPLETED 04/30/17 00:00 04/30/17 08:22    8.38
      12397 DB INCR       COMPLETED 05/01/17 00:00 05/01/17 07:52    7.87
      12409 DB INCR       RUNNING   05/02/17 00:00 05/03/17 10:38   34.65

12 rows selected.

SELECT vbd.file#, vrbjd.start_time, vrbjd.end_time, vbd.incremental_level, vrbjd.input_type, vrbjd.status FROM v$rman_backup_job_details vrbjd, v$backup_datafile vbd
WHERE vbd.completion_time BETWEEN vrbjd.start_time AND vrbjd.end_time
AND vrbjd.input_type <> 'ARCHIVELOG'
ORDER BY 2,1;



select to_char(START_TIME,'DD MON YY HH24:Mi') START_TIME ,to_char(END_TIME,'DD MON YY HH24:Mi') END_TIME ,OUTPUT_BYTES/1000000  PROCESSED_IN_MB,STATUS
from v$rman_status where trunc(START_TIME)= trunc(sysdate);


START_TIME      END_TIME        PROCESSED_IN_MB STATUS
--------------- --------------- --------------- -------------------------
03 MAY 17 07:56 03 MAY 17 09:29            3049 RUNNING
03 MAY 17 00:00 03 MAY 17 04:30           13746 COMPLETED WITH ERRORS
03 MAY 17 06:18 03 MAY 17 06:18              30 COMPLETED
03 MAY 17 07:56 03 MAY 17 07:56              30 COMPLETED
03 MAY 17 06:18 03 MAY 17 06:18               0 COMPLETED
03 MAY 17 00:00 03 MAY 17 04:30           13746 FAILED

6 rows selected.

select ctime "Date",       decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type,    bsize "Size MB"
 from (select trunc(bp.completion_time) ctime  , backup_type  , round(sum(bp.bytes/1024/1024),2) bsize
       from v$backup_set bs, v$backup_piece bp
       where bs.set_stamp = bp.set_stamp
       and bs.set_count  = bp.set_count
       and bp.status = 'A'
       group by trunc(bp.completion_time), backup_type)
order by 1, 2;

select max(START_TIME) "START_TIME" ,END_TIME,ELAPSED_SECONDS/60 "ELAPSED_MINS", OUTPUT_BYTES/1024/1024/1024 "OUTPUT_BYTES_GB",STATUS from v$rman_backup_job_details 
group by start_time,END_TIME,ELAPSED_SECONDS,OUTPUT_BYTES, STATUS having max(START_TIME) > sysdate-1;


START_TIME          END_TIME             ELAPSED_MINS OUTPUT_BYTES_GB STATUS
------------------- ------------------- ------------- --------------- ----------
02-05-2017 12:00:02 03-05-2017 06:18:30          1098              68 COMPLETED
03-05-2017 00:00:03 03-05-2017 04:30:06           270              13 FAILED


select /*+ rule */ NVL(min(r.status),'NO BACKUP') as status 
from V$RMAN_BACKUP_JOB_DETAILS r inner join (select distinct session_stamp, incremental_level from v$backup_set_details) b 
on r.session_stamp = b.session_stamp where incremental_level is not null 
and r.start_time > sysdate - 7 
and b.incremental_level = 0;

STATUS
----------
FAILED


TAG Details
-----------

set linesize 200 pagesize 200
col HANDLE for a60
select bp.TAG, bp.handle, bp.STAMP, decode(bs.backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type,bp.SET_STAMP, to_char(bp.completion_time + 30/1440,'yyyy/mm/dd HH24:MI:SS') end_time 
from v$backup_set bs, v$backup_piece bp 
where bs.set_stamp = bp.set_stamp
and bs.set_count  = bp.set_count ; 
-- and tag='&TAG';


TAG                              HANDLE                                                            STAMP BACKUP_TYPE  SET_STAMP END_TIME
-------------------------------- ------------------------------------------------------------ ---------- ----------- ---------- -------------------
TAG20170429T120003               /backup/VIHAAN/20170429_VIHAAN_13567_1_942587273             942587275 Archive Log  942587273 2017/04/29 15:11:44


col dbsize_mbytes      for 99,999,990.00 justify right head "DBSIZE_MB"
col input_mbytes       for 99,999,990.00 justify right head "READ_MB"
col output_mbytes      for 99,999,990.00 justify right head "WRITTEN_MB"
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|% ORIG"
col est_complete       for a20           head "ESTIMATED COMPLETION"
col recid              for 9999999       head "ID"

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 to_char(START_TIME,'DD MON YY HH24:Mi') START_TIME ,STATUS,OPERATION from v$rman_status where STATUS like '%ERROR%'
and  START_TIME > trunc(sysdate) -8
order by 1 desc


START_TIME      STATUS                    OPERATION
--------------- ------------------------- ---------------------------------
29 APR 17 00:00 COMPLETED WITH ERRORS     RMAN
03 MAY 17 00:00 COMPLETED WITH ERRORS     RMAN

set linesize 300 pagesize 300
col x1 format a20 heading "Begin Date/Time"
col x2 format a20 heading "Completion Date/Time"
col x3 format a30 heading "Status"
col x4 format a20 heading "Backup Actual Size"
col x5 format a20 heading "RMAN Compressed Size"
col x6 format a30 heading "Backup Total Time (HH:MI:SS)"
col x7 format a29 heading "Backup Type-Incremental/Full"
col HOST for a16
BREAK ON HOST SKIP 1 ON database_name SKIP 1 
col database_name for a15
select SYS_CONTEXT('USERENV', 'SERVER_HOST') HOST ,SYS_CONTEXT('USERENV','DB_NAME') database_name,to_char(start_time,'dd-mon-yyyy:hh24:mi:ss') x1,to_char(end_time,'dd-mon-yyyy:hh24:mi:ss') x2,status x3,input_bytes_display x4, output_bytes_display x5, time_taken_display x6, input_type x7 
from v$rman_backup_job_details 
where 1=1 
and start_time > sysdate-15
-- and input_type!='ARCHIVELOG'
;

***************************************************

define NUMBER_OF_DAYS=15
set lines 300 pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 999,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 999999999 heading "OUT|INST"
col START_TIME  for a20
col END_TIME for a20
col Sday for a4
col eday for a4
select
  j.session_recid, j.session_stamp,
 (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  to_char(j.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time,
  to_char(j.end_time, 'dd-mm-yyyy hh24:mi:ss') end_time
  ,decode(to_char(j.start_time, 'd'), 1, 'Mon', 2, 'Tue',3, 'Wed', 4, 'Thu',5, 'Fri', 6, 'Sat',7, 'Sun') Sday 
  ,decode(to_char(j.end_time, 'd'),   1, 'Mon', 2, 'Tue', 3, 'Wed', 4, 'Thu',5, 'Fri', 6, 'Sat',7,  'Sun') Eday 								 
 --, j.elapsed_seconds
 , j.time_taken_display
  ,x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
--and j.input_type='DB FULL'  ------------<<<<<<<<<<<<<<<<<
order by j.start_time;

**********

 
 set lines 750 pages 9999
col TIME_TAKEN_DISPLAY for a10
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time,
  to_char(j.end_time, 'dd-mm-yyyy hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
order by j.start_time desc;

==========

SET PAGESIZE 300
COL time_taken_display FORM A15 HEAD "Time|Taken|HH:MM:SS"
COL rman_end_time FORM A17
COL i_size_gig FORM 999999.99 HEAD "Input|Gig"
COL o_size_gig FORM 999999.99 HEAD "Output|Gig"
COL compression_ratio FORM 99999.99 HEAD "Comp.|Ratio"
COL status FORM A25
COL input_type FORM A14
--
SELECT
time_taken_display
,TO_CHAR(end_time,'dd-mon-rrrr hh24:mi') AS rman_end_time
,input_bytes/1024/1024/1024 i_size_gig
,output_bytes/1024/1024/1024 o_size_gig
,compression_ratio
,status
,input_type
FROM v$rman_backup_job_details
where 1=1
and status = 'FAILED'
ORDER BY end_time;


set pages 9999 lines 500
set numformat 999999999.99
set trim on
set trims on
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
col INSTANCE for a9
col status for a22
col COMMAND_ID for a20
col INPUT_TYPE for a10
col OUTPUT_DEVICE_TYPE for a10
col OUTPUT_BYTES_PER_SEC_DISPLAY for a9
col status for a25 heading "BACKUP|STATUS"
col COMMAND_ID heading "BACKUP NAME"
col STARTED_TIME heading "START TIME"
COL END_TIME heading "END TIME"
col ELAPSED_TIME heading "MINUTES | TAKEN"
col INPUT_TYPE heading "INPUT|TYPE"
col OUTPUT_DEVICE_TYPE heading "OUTPUT|DEVICES"
col INPUT_SIZE heading  "INPUT SIZE|GB"
col OUTPUT_SIZE heading  "OUTPUT SIZE|GB"
col OUTPUT_BYTES_PER_SEC_DISPLAY heading "OUTPUT | RATE|(PER SEC)"
SELECT (SELECT instance_name FROM v$instance) || ' ' || (SELECT instance_number FROM v$instance) instance,rs.sid,
rj.COMMAND_ID,
rj.STATUS,
max(rj.START_TIME) STARTED_TIME,
rj.END_TIME,
rj.ELAPSED_SECONDS/60 ELAPSED_TIME,
rj.INPUT_TYPE,
rj.OUTPUT_DEVICE_TYPE,
rj.INPUT_BYTES/1024/1024/1024 INPUT_SIZE,
rj.OUTPUT_BYTES/1024/1024/1024 OUTPUT_SIZE,
rj.OUTPUT_BYTES_PER_SEC_DISPLAY
from v$rman_backup_job_details rj, v$rman_status rs
where rj.COMMAND_ID=rs.COMMAND_ID
group by rs.sid,rj.COMMAND_ID,rj.STATUS,rj.START_TIME,rj.END_TIME,rj.ELAPSED_SECONDS,rj.INPUT_TYPE,rj.OUTPUT_DEVICE_TYPE,rj.INPUT_BYTES,rj.OUTPUT_BYTES,rj.OUTPUT_BYTES_PER_SEC_DISPLAY
having max(rj.START_TIME) > sysdate -15 
order by rj.START_TIME desc
/

===

set lines 300 pages 300
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time,
to_char(j.end_time, 'dd-mm-yyyy hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;


set lines 300 pages 300
col backup_type for a4 heading "TYPE"
col controlfile_included heading "CF?"
col incremental_level heading "INCR LVL"
col pieces for 999 heading "PCS"
col elapsed_seconds heading "ELAPSED|SECONDS"
col device_type for a10 trunc heading "DEVICE|TYPE"
col compressed for a4 heading "ZIP?"
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col input_file_scan_only for a4 heading "SCAN|ONLY"
select
d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,
to_char(d.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time,
to_char(d.completion_time, 'dd-mm-yyyy hh24:mi:ss') completion_time,
d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only
from V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where session_recid = &SESSION_RECID
and session_stamp = &SESSION_STAMP
order by d.start_time;

set lines 200 pages 2000
col STATUS format a25
col dev format a10
col START_TIME format a16
col END_TIME format a16
col hrs format 99.99
col INBYTES format a10
col OUTBYTES format a10
select SESSION_KEY, INPUT_TYPE, STATUS, output_device_type DEV,
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,
input_bytes_display inbytes, output_bytes_display outbytes
from V$RMAN_BACKUP_JOB_DETAILS
where status='RUNNING'
order by session_key;


set lines 300 pages 300
col backup_type for a4 heading "TYPE"
col controlfile_included heading "CF?"
col incremental_level heading "INCR LVL"
col pieces for 999 heading "PCS"
col elapsed_seconds heading "ELAPSED|SECONDS"
col device_type for a10 trunc heading "DEVICE|TYPE"
col compressed for a4 heading "ZIP?"
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col input_file_scan_only for a4 heading "SCAN|ONLY"
col START_TIME for a27
select
d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,
to_char(d.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time,
to_char(d.completion_time, 'dd-mm-yyyy hh24:mi:ss') completion_time,
d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only,d.status
from V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where session_recid in ( select SESSION_KEY from V$RMAN_BACKUP_JOB_DETAILS where status='RUNNING' )
--and session_stamp = SESSION_STAMP
order by d.start_time;



set lines 200  pages 1000
select output
from GV$RMAN_OUTPUT
where session_recid in ( select SESSION_KEY from V$RMAN_BACKUP_JOB_DETAILS where status='RUNNING' )
--and session_stamp = SESSION_STAMP
order by recid;

===



set linesize 500 pagesize 300
col errors 		for a20 
col backup_type         for a15
col time_frame 	        for a30 
col status 		for a15
col device_type         for a15
col recid 		for a12
with rman_status as (
select distinct substr(backup_type,1,instr(backup_type||' datafile','
datafile')) backup_type
    , round(mbytes_processed/1024,1) total_gb, output_device_type device_type
    , round((mbytes_processed/1024)/((end_time-start_time))/24,1) gb_hour
    , start_time, end_time, status, session_stamp, recid
    , to_char(mod(floor((end_time-start_time)*24),24),'00')||'h'   || to_char(mod(floor((end_time-start_time)*24*60),60),'00')||'m'
   || to_char(mod(floor((end_time-start_time)*24*60*60),60),'00')||'s'
elapsed_time
    , case when status='FAILED' then
        case when (select count(1) from v$rman_output
                   where session_stamp = x.session_stamp
                     and rman_status_recid = x.recid) = 0
             then 'RMAN Output Not Found'
             else (select
nvl(rtrim(xmlagg(xmlelement(e,substr(output,1,instr(output,':')-1)||',')).extract('//text()'),','),'No Errors ~ Session Hung')
                   from v$rman_output
                   where session_stamp = x.session_stamp
                     and rman_status_recid = x.recid
                     and output like 'ORA-%')
             end
      else null
      end errors
from (select nvl(substr(y.output,instr(y.output,'starting ')+9),object_type)
backup_type
           , mbytes_processed, object_type, output_device_type
           , start_time, end_time, status, x.session_stamp, x.recid
      from v$rman_status x, v$rman_output y
      where x.session_stamp = y.session_stamp(+)
        and x.recid = y.rman_status_recid(+)
        and object_type in ('DB INCR','DB FULL')
        and status != 'RUNNING'
        and operation = 'BACKUP'
        and ((lower(y.output) like '%startingÚtafileºckup%'
          and lower(y.output) not like '%validation%')
           or y.output is null)) x
order by session_stamp desc, recid desc)
select session_stamp, recid||'' recid
    , substr(replace(initcap(backup_type),'Db ','DB '),1,25) backup_type
    , substr(device_type,1,10) device_type
    , substr(status,1,11) status
    , to_char(start_time,'dd-Mon-yyyy  hh24:mi')||' ~ '||to_char(end_time,'hh24:mi')||decode(trunc(end_time-start_time),0,null,'(+1Day)') time_frame
    , elapsed_time, total_gb, gb_hour, substr(errors,1,30) errors
from rman_status;



SESSION_STAMP RECID        BACKUP_TYPE     DEVICE_TYPE     STATUS          TIME_FRAME                     ELAPSED_TIME   TOTAL_GB    GB_HOUR ERRORS
------------- ------------ --------------- --------------- --------------- ------------------------------ ------------ ---------- ---------- --------------------
   1092272403 6185                         DISK            COMPLETED       26-Dec-2021  01:00 ~ 01:10      00h 10m 02s       33.8      202.2
   1091667603 6155                         DISK            COMPLETED       19-Dec-2021  01:00 ~ 01:10      00h 10m 11s       33.8      199.2
   1091062802 6125                         DISK            COMPLETED       12-Dec-2021  01:00 ~ 01:10      00h 10m 12s       33.7        198
   1090458003 6095                         DISK            COMPLETED       05-Dec-2021  01:00 ~ 01:10      00h 10m 19s       33.6      195.6
   1089766803 6065                         DISK            COMPLETED       28-Nov-2021  01:00 ~ 01:10      00h 10m 11s       33.7      198.5
   1089162003 6035                         DISK            COMPLETED       21-Nov-2021  01:00 ~ 01:10      00h 10m 03s       33.6      200.5
   1088557202 6005                         DISK            COMPLETED       14-Nov-2021  01:00 ~ 01:10      00h 10m 20s       33.8      196.2
   1087952403 5975                         DISK            COMPLETED       07-Nov-2021  01:00 ~ 01:10      00h 10m 11s       33.6        198
   1087347602 5946                         DISK            COMPLETED       31-Oct-2021  01:00 ~ 01:10      00h 10m 13s       33.4      196.4

9 rows selected.



 
set linesize 300 pagesize 300
col x1 format a20 heading "Begin Date/Time"
col x2 format a20 heading "Completion Date/Time"
col x3 format a10 heading "Status"
col x4 format a20 heading "Backup Actual Size"
col x5 format a20 heading "RMAN Compressed Size"
col x6 format a30 heading "Backup Total Time (HH:MI:SS)"
col x7 format a29 heading "Backup Type-Incremental/Full"
col HOST for a24
BREAK ON HOST SKIP 1 ON database_name SKIP 1 
col database_name for a10
select SYS_CONTEXT('USERENV', 'SERVER_HOST') HOST ,SYS_CONTEXT('USERENV','DB_NAME') database_name,to_char(start_time,'dd-mon-yyyy:hh24:mi:ss') x1,to_char(end_time,'dd-mon-yyyy:hh24:mi:ss') x2,status x3,input_bytes_display x4, output_bytes_display x5, time_taken_display x6, input_type x7 
from v$rman_backup_job_details 
where 1=1 
and start_time > sysdate -7
-- and input_type!='ARCHIVELOG'
-- and input_type='DB INCR'
-- and status in ('COMPLETED','RUNNING')
order  by 3
;








define NUMBER_OF_DAYS=30
set pages 1000 linesize 300
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a20 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
col START_TIME for a24
col END_TIME for a24
PROMPT Enter Number of Days Back to look
PROMPT
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;


---

from catalog

set linesize 500
define db_name=''
define NUMBER_OF_DAYS=30
col "Input size" format a14
col "output size" format a14
col "time_taken_display" format a10
col "output/sec" format a14
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a20 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"

set pages 35
select * from(
select
  J.DB_NAME,j.session_recid, j.session_stamp, j.session_key,
  j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
   j.input_bytes_display as "Input Size", j.output_bytes_display "Output Size", j.output_bytes_per_sec_display as "Output/Sec", j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds,
 to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time
 -- x.cf, x.df, x.i0, x.i1, x.l
--  ro.inst_id output_instance
from RC_RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'BACKUP' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     RC_BACKUP_SET_DETAILS d
                     join RC_BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
 /* left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from RC_RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp */
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time )
--where db_name=upper('&db_name')
;

====

set linesize 300 pagesize 300
col kill for a60
col kill1 for a16
COL opname FORM A35
COL pct_complete FORM 99.99 HEAD "% Comp."
COL start_time FORM A15 HEAD "Start|Time"
COL hours_running FORM 9999.99 HEAD "Hours|Running"
COL minutes_left FORM 999999 HEAD "Minutes|Left"
COL est_comp_time FORM A15 HEAD "Est. Comp.|Time"
col MESSAGE for a50 trunc
--
SELECT 
--sid, serial#, 
''''||sid ||','|| serial#||',@'||inst_id ||'''' kill1,
opname,
ROUND(sofar/totalwork*100,2) AS pct_complete,
TO_CHAR(start_time,'dd-mon-yy hh24:mi') start_time,
(sysdate-start_time)*24 hours_running,
((sysdate-start_time)*24*60)/(sofar/totalwork)-(sysdate-start_time)
*24*60 minutes_left,
TO_CHAR((sysdate-start_time)/(sofar/totalwork)+start_time,'dd-mon-yy hh24:mi')
est_comp_time,MESSAGE
,       'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||inst_ID||''' IMMEDIATE;' kill
FROM gv$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <> totalwork;

===========

alter session set nls_date_format='dd-mon-yy hh24:mi:ss';
set lines 1500
set pages 100
col CLI_INFO format a10
col spid format a5
col ch format a20
col seconds format 999999.99
col filename format a65
col bfc  format 9
col "% Complete" format 999.99
col event format a40
set numwidth 10

col kill for a17
select sysdate from dual;

REM gv$session_longops (channel level)



define 1='18-MAR-15 11:00:00'



prompt
prompt Channel progress - gv$session_longops:
prompt
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, CLIENT_INFO ch, context, sofar, totalwork,  round(sofar/totalwork*100,2) "% Complete"    
FROM gv$session_longops o, gv$session s
     WHERE opname LIKE 'RMAN%'
     AND opname NOT LIKE '%aggregate%'
     AND o.sid=s.sid
     AND totalwork != 0
     AND sofar <> totalwork;




prompt
prompt Session progess - CURRENT wait events and time in wait so far:
prompt


select ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill, CLIENT_INFO ch, seq#, event, state, wait_time_micro/1000000 seconds 
from gv$session where program like '%rman%' 
and wait_time = 0 
and not action is null;

REM use the following for 10G
--select  inst_id, sid, CLIENT_INFO ch, seq#, event, state, seconds_in_wait secs
--from gv$session where program like '%rman%' and
--wait_time = 0 and
--not action is null;



REM gv$backup_async_io

restore info !!!

select round(sum(bytes)/1024/1024/1024,2) Size_GB from V$BACKUP_ASYNC_IO where type='AGGREGATE';

select round(sum(bytes)/1024/1024/1024/1000,2) Size_TB from V$BACKUP_ASYNC_IO where type='AGGREGATE';
   

alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 400
col kill for a17
col filename for a40
col ch for a18
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, CLIENT_INFO Ch, a.STATUS,open_time, round(BYTES/1024/1024,2) "SOFAR Mb" , round(total_bytes/1024/1024,2) TotMb, io_count,
round(BYTES/TOTAL_BYTES*100,2) "% Complete" , a.type
--, filename
from gv$backup_async_io a,  gv$session s
where not a.STATUS in ('UNKNOWN','FINISHED')
and a.sid=s.sid 
and open_time > sysdate -1
order by 2,7;

alter session set nls_date_format='dd-mm-yyyy hh24:mi';

set linesize 400 pagesize 300

col FILENAME for a40

col ch for a18

select s.inst_id, a.sid, CLIENT_INFO Ch, a.STATUS,open_time, round(BYTES/1024/1024/1024,2) "SOFAR gb" , round(total_bytes/1024/1024/1024,2) "Tot gb", io_count,

round(BYTES/TOTAL_BYTES*100,2) "% Complete" , a.type

--, filename

from gv$backup_async_io a, gv$session s

where not a.STATUS in ('UNKNOWN') and s.status='ACTIVE' and a.STATUS <> 'FINISHED'

and a.sid=s.sid order by 6 desc,7;

prompt
prompt Disk (file and backuppiece) progress - includes tape backuppiece 
prompt if backup_tape_io_slaves=TRUE:
prompt
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, CLIENT_INFO Ch, a.STATUS,open_time, round(BYTES/1024/1024,2) "SOFAR Mb" , round(total_bytes/1024/1024,2) TotMb, io_count,
round(BYTES/TOTAL_BYTES*100,2) "% Complete" , a.type, filename
from gv$backup_async_io a,  gv$session s
where not a.STATUS in ('UNKNOWN')
and a.sid=s.sid 
and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') 
order by 2,7;

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set pages 2222 long 6666
select sl.sid, sl.opname,
to_char(100*(sofar/totalwork), '990.9')||'%' pct_done,
sysdate+(TIME_REMAINING/60/60/24) done_by
from v$session_longops sl, v$session s
where sl.sid = s.sid
and sl.serial# = s.serial#
and sl.sid in (select sid from v$session where module like 'backup%' or module like 'restore%' or module like 'rman%')
and sofar != totalwork
and totalwork > 0

/

SET HEAD OFF
SELECT 'RMAN Throughput : '||
ROUND(SUM(v.value/(power(2,30))),1) || ' GB so far ---> Per Second Throughput = ' ||
ROUND(SUM(v.value /(power(2,30)))/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 /(power(2,30)))),2) || ' GB'
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





REM gv$backup_sync_io
prompt
prompt Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):
prompt
select s.inst_id, a.sid, CLIENT_INFO Ch, filename, a.type, a.status, buffer_size bsz, buffer_count bfc,open_time open, io_count from gv$backup_sync_io a, gv$session s
where 1=1
and a.sid=s.sid and
open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') ;

select file#,
avg(datafile_blocks),
avg(blocks_read),
avg(blocks_read/datafile_blocks) * 100 as "% read for backup"
from v$backup_datafile
where incremental_level > 0
and used_change_tracking = 'YES'
group by file#
order by file#;

define NUMBER_OF_DAYS=16
set lines 300 pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds for a10 heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 999,999,999,999,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 999999999 heading "OUT|INST"
col START_TIME  for a24
col END_TIME for a24
col Sday for a16
col eday for a16
col output_mbytes for a10
select
j.session_recid, j.session_stamp,
 upper(dbms_xplan.format_size2(j.output_bytes)) output_mbytes, j.status, j.input_type,
to_char(j.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time,
to_char(j.end_time, 'dd-mm-yyyy hh24:mi:ss') end_time
,to_char(j.start_time,'DAY','nls_date_language=ENGLISH') Sday
,to_char(j.end_time,'DAY','nls_date_language=ENGLISH') Eday
 , dbms_xplan.format_time_s(j.elapsed_seconds) elapsed_seconds
 --, j.time_taken_display
 , x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
--and j.input_type='DB FULL'  ------------<<<<<<<<<<<<<<<<<
--and j.output_bytes/1024/1024 > 19000000   --- for Big backup 
--and j.output_bytes/1024/1024 > 566579
--and j.output_bytes > 19000000
order by j.start_time;