Rman backup Details

select to_char(min(a),’mm-dd-yyyy hh24:mi:ss’) Start_Time,
to_char(max(b),’mm-dd-yyyy hh24:mi:ss’) End_Time,
(max(b)-min(a))*1440 Minutes
from (
select bs.start_time a
,bs.completion_time b
FROM
v$backup_set bs
, (select distinct
set_stamp
, set_count
, tag
, device_type
from v$backup_piece
where status in (‘A’, ‘X’)) bp
, (select distinct
set_stamp
, set_count
, ‘YES’ spfile_included
from v$backup_spfile) sp
WHERE
bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bs.set_stamp = sp.set_stamp (+)
AND bs.set_count = sp.set_count (+)
AND bs.start_time > SYSDATE -1
order by 1
)

SELECT
bs.recid bs_key
, bp.tag tag
, DECODE(backup_type
, ‘L’, ‘Archived Logs’
, ‘D’, ‘Datafile Full’
, ‘I’, ‘Incremental’) backup_type
, device_type device_type
, DECODE( bs.controlfile_included
, ‘NO’, null
, bs.controlfile_included) controlfile_included
, sp.spfile_included spfile_included
, bs.incremental_level incremental_level
, bs.pieces pieces
, TO_CHAR(bs.start_time, ‘mm/dd/yy HH24:MI:SS’) start_time
, TO_CHAR(bs.completion_time, ‘mm/dd/yy HH24:MI:SS’) completion_time
, (bs.elapsed_seconds/60) elapsed_minutes
FROM
v$backup_set bs
, (select distinct
set_stamp
, set_count
, tag
, device_type
from v$backup_piece
where status in (‘A’, ‘X’)) bp
, (select distinct
set_stamp
, set_count
, ‘YES’ spfile_included
from v$backup_spfile) sp
WHERE
bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bs.set_stamp = sp.set_stamp (+)
AND bs.set_count = sp.set_count (+)
AND bs.start_time > SYSDATE -1
ORDER BY
bs.recid

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: