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
