Rman backup status from sqlplus> ..

Rman backup status 

Rman backup output via sql  

v$rman_output to review  backups

for status ... 

http://anuj-singh.blogspot.com/2017/05/rman-sql-to-find-backup-details.html?m=1

-- v$rman_output goes away with a restart

set linesize 200  pagesize 200

select output

from v$rman_output where session_recid = (select max(session_recid) from v$rman_status)

order by recid ;

OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

connected to target database: GRAINUAT (DBID=2803380037, not open)

using target database control file instead of recovery catalog

old RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored

allocated channel: c1

channel c1: SID=134 device type=DISK

or 

set linesize 200  pagesize 200

select output

from v$rman_output where session_recid in (select session_recid from v$rman_status WHERE operation = 'BACKUP' AND status != 'COMPLETED' AND status != 'RUNNING')

order by recid ;

====

set linesize 200  pagesize 200

select output

from v$rman_output where session_recid in (select session_recid from v$rman_status

where start_time > sysdate -7)

order by recid ;

set linesize 200  pagesize 200

select output

from v$rman_output where session_recid in (select session_recid from v$rman_status

where start_time > sysdate -7)

order by recid ;

OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

connected to target database: GRAINUAT (DBID=2803380037, not open)

using target database control file instead of recovery catalog

old RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored

allocated channel: c1

channel c1: SID=134 device type=DISK

allocated channel: c2

channel c2: SID=167 device type=DISK

alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss';

set  linesize 200

select object_type,mbytes_processed, start_time, end_time,status

from v$rman_status

where session_recid = (select max(session_recid) from v$rman_status) and operation !='RMAN'

order by recid

OBJECT_TYPE     MBYTES_PROCESSED START_TIME           END_TIME             STATUS

--------------- ---------------- -------------------- -------------------- -----------------------

ARCHIVELOG                    15 03-SEP-2017 14:54:43 03-SEP-2017 14:54:48 COMPLETED

DB INCR               199589.594 03-SEP-2017 14:54:48 03-SEP-2017 18:17:23 RUNNING

=============

set linesize 300 pagesize 300

-- TTITLE LEFT '% Completed. Aggregate is the overall progress:'

SET LINE 300

col SKILL for a15

SELECT 

''''||sid ||','|| serial#||',@'||inst_id ||'''' Skill,opname, round(sofar/totalwork*100) "% Complete"   FROM gv$session_longops

WHERE opname LIKE 'RMAN%'

AND totalwork != 0

AND sofar <> totalwork

 ORDER BY 1;

set linesize 300 pagesize 300

col info for a120

select  'Session info  '||skill||info Rman_Session_info  from (SELECT ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' Skill,'Throughput: '||  ROUND(SUM(v.value/1024/1024),1) || ' Meg so far @ ' || ROUND(SUM(v.value /1024/1024)/NVL((SELECT MIN(elapsed_seconds) info

                FROM gv$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' info

     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 ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''', n.name)

;

-- TTITLE LEFT 'Channels waiting...'

COL client_info FORMAT A15 TRUNC

COL event FORMAT A30 

COL state FORMAT A30

COL wait FORMAT 999.90 HEAD "Min waiting"

col SKILL for a15

SELECT ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' Skill, 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...'

set numf 9999999999999

COL filename FORMAT a80

SELECT filename, bytes, io_count FROM gv$backup_async_io

 WHERE status='IN PROGRESS'

/

=====

define _start prompt='sysdate-1'

define _errors_only='y'

col command_id  for a19

col start_time  for a10

col end_time    for a10

COL RECID           NEW_VAL P_RECID       NOPRINT

COL COMMAND_ID      NEW_VAL P_COMMAND_ID  NOPRINT

COL START_TIME      NEW_VAL P_START_TIME  NOPRINT

COL END_TIME        NEW_VAL P_END_TIME    NOPRINT

COL OPERATION       NEW_VAL P_OPERATION   NOPRINT

COL STATUS          NEW_VAL P_STATUS      NOPRINT

COL LINE#       NOPRINT;

break on recid on command_id on start_time on end_time on operation on status skip page;

set pause off;

ttitle left -

     '###############################################################################################################' skip 1-

     '# RECID      : ' P_RECID       skip 1-

     '# COMMAND_ID : ' P_COMMAND_ID  skip 1-

     '# START_TIME : ' P_START_TIME  skip 1-

     '# END_TIME   : ' P_END_TIME    skip 1-

     '# OPERATION  : ' P_OPERATION   skip 1-

     '# STATUS     : ' P_STATUS      skip 2;

with 

 st as (

   select/*+ materialize */

     --s.sid,

     --s.parent_recid,

     s.recid,command_id

    ,to_char(start_time,'hh24:mi:ss') start_time

    ,to_char(end_time  ,'hh24:mi:ss') end_time

    ,operation||' '||object_type      operation

    ,status                      status

   from v$rman_status s 

   where start_time >= case 

                            when '&_start' is null then trunc(sysdate-3) 

                            when regexp_like('&_start','\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d') then to_date('&_start','yyyy-mm-dd hh24:mi:ss')

                            when regexp_like('&_start','\d\d\d\d-\d\d-\d\d') then to_date('&_start','yyyy-mm-dd')

                            when regexp_like('&_start','^\d+$') then sysdate-to_number('&_start')

                            else trunc(sysdate)

                        end

   and s.status not in ('RUNNING','COMPLETED') 

 )

   select --distinct command_id||' '||operation||' '||object_type||' '||status  errors 

     st.*

    --,o.recid as line#

    ,o.output

   from st

       ,v$rman_output o

   where 

     o.RMAN_STATUS_RECID=st.recid

     and (  o.output not like 'Backup Set%'

        and o.output not like '  Backup Piece%'

        and o.output not like 'input%'

        and o.output not like 'channel%'

        and o.output not like 'piece%'

     )

     and (

           lower('&_errors_only')!='y' 

            or 

          (o.output like 'RMAN-%' 

            and substr(o.output,1,10) not in (

                                        'RMAN-06210', 

                                        'RMAN-06211', 

                                        'RMAN-06212', 

                                        'RMAN-06213', 

                                        'RMAN-06214')

         ))

   order by --command_id,start_time,

     st.recid,o.recid;

undef _start

clear break

====

define _start=''

select recid, parent_recid, row_type, command_id, operation, object_type, status

from v$rman_status 

where start_time >= case 

                        when '&_start' is null then trunc(sysdate-3) 

                        when regexp_like('&_start','\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d') then to_date('&_start','yyyy-mm-dd hh24:mi:ss')

                        when regexp_like('&_start','\d\d\d\d-\d\d-\d\d') then to_date('&_start','yyyy-mm-dd')

                        when regexp_like('&_start','^\d+$') then sysdate-to_number('&_start')

                        else trunc(sysdate)

                    end

order by start_time desc;

undef _start

###############################################################################

# RECID      :      48091

# COMMAND_ID : 2022-04-11T21:02:29

# START_TIME : 21:19:28

# END_TIME   : 21:21:19

# OPERATION  : RMAN

# STATUS     : COMPLETED

PARENT_RECID ROW_TYPE            OBJECT_TYPE

------------ ------------------- -------------

             SESSION

===

define _start=''

col command_id  for a19

col start_time  for a10

col end_time    for a10

COL RECID           NEW_VAL P_RECID       NOPRINT

COL COMMAND_ID      NEW_VAL P_COMMAND_ID  NOPRINT

COL START_TIME      NEW_VAL P_START_TIME  NOPRINT

COL END_TIME        NEW_VAL P_END_TIME    NOPRINT

COL OPERATION       NEW_VAL P_OPERATION   NOPRINT

COL STATUS          NEW_VAL P_STATUS      NOPRINT

COL LINE#       NOPRINT

break on recid on command_id on start_time on end_time on operation on status skip page;

set pause on;

ttitle left -

     '###############################################################################################################' skip 1-

     '# RECID      : ' P_RECID       skip 1-

     '# COMMAND_ID : ' P_COMMAND_ID  skip 1-

     '# START_TIME : ' P_START_TIME  skip 1-

     '# END_TIME   : ' P_END_TIME    skip 1-

     '# OPERATION  : ' P_OPERATION   skip 1-

     '# STATUS     : ' P_STATUS      skip 2;

with 

 st as (

   select/*+ materialize */

     --s.sid,

     --s.parent_recid,

     s.recid,command_id

    ,to_char(start_time,'hh24:mi:ss') start_time

    ,to_char(end_time  ,'hh24:mi:ss') end_time

    ,operation||' '||object_type      operation

    ,status                      status

   from v$rman_status s 

   where start_time >= case 

                            when '&_start' is null then trunc(sysdate-1) 

                            when regexp_like('&_start','\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d') then to_date('&_start','yyyy-mm-dd hh24:mi:ss')

                            when regexp_like('&_start','\d\d\d\d-\d\d-\d\d') then to_date('&_start','yyyy-mm-dd')

                            when regexp_like('&_start','^\d+$') then sysdate-to_number('&_start')

                            else trunc(sysdate)

                        end

   and s.status not in ('RUNNING','COMPLETED') 

 )

   select --distinct command_id||' '||operation||' '||object_type||' '||status  errors 

     st.*

    --,o.recid as line#

    ,o.output

   from st

       ,v$rman_output o

   where 

     o.RMAN_STATUS_RECID=st.recid

     and (  o.output not like 'Backup Set%'

        and o.output not like '  Backup Piece%'

        and o.output not like 'input%'

        and o.output not like 'channel%'

        and o.output not like 'piece%'

     )

   order by --command_id,start_time,

     st.recid,o.recid;

undef _start

################################################################################

# RECID      :      48105

# COMMAND_ID : 2022-04-13T21:17:28

# START_TIME : 21:17:28

# END_TIME   : 21:17:32

# OPERATION  : RMAN

# STATUS     : COMPLETED WITH ERRORS

OUTPUT

--------------------------------------------------------------------------------

 unavailable;

==========

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,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 a14

col eday for a14

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

,to_char(j.start_time,'DAY','nls_date_language=ENGLISH') Sday

,to_char(j.end_time,'DAY','nls_date_language=ENGLISH') 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'  ------------<<<<<<<<<<<<<<<<<

--and j.output_bytes/1024/1024 > 19000000   --- for Big backup only

-- and j.output_bytes/1024/1024 > 566579

order by j.start_time;

======

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

set linesize 500 pages 300

col OUTPUT_BYTES_DISPLAY for a12

col elapsed_seconds for 99999999.99

col COMPRESSION_RATIO for 9999.99

col TIME_TAKEN_DISPLAY for a12

col OUTPUT_BYTES_DISPLAY1 for a22

select * from (

SELECT 

--jd.session_recid,

 --        jd.session_stamp,

jd.output_bytes_display output_bytes_display1,

  jd.time_taken_display,

     --    jd.session_key,

         jd.start_time,

         jd.end_time,

         jd.elapsed_seconds,

      -- jd.time_taken_display,

         jd.output_device_type,

         jd.status,

         jd.input_type,

         jd.compression_ratio,

         jd.output_bytes_display,

         x.cf,

         x.df,

         x.i0,

         x.i1,

         x.l,

         ro.inst_id

    FROM V$RMAN_BACKUP_JOB_DETAILS jd

         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.backup_type || d.incremental_level = 'D'

                         THEN

                            d.pieces

                         ELSE

                            0

                      END)

                      DF,

                   SUM (

                      CASE

                         WHEN    (d.backup_type || d.incremental_level = 'D0')

                              OR (d.backup_type || d.incremental_level = 'I0')

                         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 = jd.session_recid

               AND x.session_stamp = jd.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 = jd.session_recid

               AND ro.session_stamp = jd.session_stamp

   WHERE jd.start_time > TRUNC (SYSDATE) - 20

  and output_bytes_display like '%T%'  ------

  --and output_bytes_display1

ORDER BY jd.start_time

)

;

====

set lines 1000 pages 1000

set numformat 99999999999999999999

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;

col output_size1 for a12 heading "output_size"

col STATUS for a24

select distinct r.object_type,r.SESSION_RECID,r.START_TIME,r.END_TIME,(r.END_TIME-r.START_TIME)*24 "ET(Hr)",

upper(dbms_xplan.format_size2(r.output_bytes )) output_size1 ,

--r.OUTPUT_BYTES/1048576/1024 "SIZE (GB)",

r.status,r.OUTPUT_DEVICE_TYPE,r.input_bytes,

INPUT_BYTES_PER_SEC/1048576 "read (MB/sec)",

r.output_bytes,

OUTPUT_BYTES_PER_SEC/1048576 "output (MB/sec)"

from v$rman_status r, v$rman_backup_job_details d

where r.OPERATION like '%BACKUP%'

and d.SESSION_RECID=r.SESSION_RECID

and r.END_TIME > sysdate -7

;

define NUMBER_OF_DAYS=4

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,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 output_size1 for a9

col "ET(Hr)" for 9999.99

col timeT for a15

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,

 -- REGEXP_SUBSTR(NUMTODSINTERVAL(NVL(j.END_TIME,j.START_TIME) - j.START_TIME,'day') ,'\d{2} \d{2}:\d{2}:\d{2}\.\d{3}' ) timeT,

  (j.END_TIME-j.START_TIME)*24 "ET(Hr)",

--  (j.output_bytes/1024/1024) output_mbytes,

upper(dbms_xplan.format_size2(j.output_bytes )) output_size1,

  j.status, j.input_type

  /* ,decode(to_char(j.start_time, 'd'), 1, 'Monday', 2, 'Tuesday',

                                     3, 'Wednesday', 4, 'Thursday',

                                     5, 'Friday', 6, 'Saturday',

                                     7, 'Sunday') 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;

col output_size for a10

col output_size1 for a12 heading "output_size"

col TAG for a20

col STATUS for a10

col DB_unique_name  for a15

select distinct  SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') DB_unique_name, p.tag, s.incremental_level, 

upper(dbms_xplan.format_size2(r.output_bytes )) output_size1 ,

(r.END_TIME-r.START_TIME)*24 "ET(Hr)",

r. status, r.object_type,r.SESSION_RECID,r.START_TIME,r.END_TIME,

r.OUTPUT_BYTES/1048576/1024 "SIZE (GB)",r.status,r.OUTPUT_DEVICE_TYPE,r.input_bytes/1048576 "input (MB)",

INPUT_BYTES_PER_SEC/1048576 "read (MB/sec)",

--r.output_bytes/1048576 "output (MB)",

--r.output_bytes

  upper(dbms_xplan.format_size2(r.output_bytes )) output_size ,

OUTPUT_BYTES_PER_SEC/1048576 "output (MB/sec)"

from v$backup_piece p,v$rman_status r, v$rman_backup_job_details d, v$backup_set s

where p.RMAN_STATUS_RECID=r.RECID 

and p.RMAN_STATUS_STAMP=r.STAMP

and r.OPERATION like '%BACKUP%'

and d.SESSION_RECID=r.SESSION_RECID

and s.set_stamp=p.set_stamp 

and s.set_count=p.set_count

and s.incremental_level=0     --- <<< level 0

and r.END_TIME > sysdate -7

;

=========================================

database size !!

set linesize 300

col "Database Size"  for a20

col "Free space"        for a20

col "Used space"      for a20

col hostname    for a37

col Instance    for a15

col DB_unique_name  for a15

col Tdate               heading 'Date'

col "Database SizeH" for a15

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

select sysdate Tdate,SYS_CONTEXT('USERENV','HOST') hostname,sys_context('USERENV', 'INSTANCE_NAME') Instance ,

SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') DB_unique_name,

round(sum(used.bytes)/1024/1024/1024 ) || ' GB' "Database Size" ,

dbms_xplan.format_number(sum(used.bytes)) "Database SizeH"

,round(sum(used.bytes)/1024/1024/1024 ) - round(free.SS/1024/1024/1024) || ' GB' "Used space"

,round(free.SS /1024/1024/1024) || ' GB' "Free space",LOG_MODE

from (select bytes from v$datafile

union all

select bytes from v$tempfile

union all

select bytes from v$log) used , (select sum(bytes) SS from dba_free_space) free,(select LOG_MODE from v$database)

group by free.SS,LOG_MODE

/

define NUMBER_OF_DAYS=20

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,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 output_size1 for a9

col "ET(Hr)" for 9999.99

col timeT for a15

select * from (

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,

 -- REGEXP_SUBSTR(NUMTODSINTERVAL(NVL(j.END_TIME,j.START_TIME) - j.START_TIME,'day') ,'\d{2} \d{2}:\d{2}:\d{2}\.\d{3}' ) timeT,

  (j.END_TIME-j.START_TIME)*24 "ET(Hr)",

--  (j.output_bytes/1024/1024) output_mbytes,

upper(dbms_xplan.format_size2(j.output_bytes )) output_size1,

  j.status, j.input_type

   ,decode(to_char(j.start_time, 'd'), 1, 'Monday', 2, 'Tuesday',

                                     3, 'Wednesday', 4, 'Thursday',

                                     5, 'Friday', 6, 'Saturday',

                                     7, 'Sunday') 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

)

where 1=1

--and output_size1 like '%T%'

and status ='FAILED'

;

https://anuj-singh.blogspot.com/search?q=gv%24backup_sync_io