RMAN BACKUP Scripts

~~~~~~~~~~~~~~~~~~~~~~~~
# RMAN backup history
~~~~~~~~~~~~~~~~~~~~~~~~

set lines 150
set pages 900
col start for a20
col end for a20
col status format a11
col input_bytes_display format a10
col output_bytes_display format a10
col status_weight format 99999
SELECT
to_char(start_time,'YYYY.MM.DD HH24:MI:SS') "Start" ,
to_char(end_time,'YYYY.MM.DD HH24:MI:SS') "End",
status,
status_weight,
input_type,
input_bytes_display,
output_bytes_display
FROM
V$RMAN_BACKUP_JOB_DETAILS
WHERE
( start_time between (SYSDATE - 8) and  (SYSDATE ))
order by
start_time;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# This script will report on all backups – full, incremental and archivelog backups –
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# This script will report all on full and incremental backups, not archivelog backups –
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

col STATUS format a9ACKUP, Scripts
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='DB INCR'
order by session_key;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
RMAN Incremental Report Generation Query
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

set pages 100 lines 200 feedback off markup html on
alter session set nls_date_format='DD-MON-YYYY HH24:MI';
spool c:\backup.html append
select host_name,instance_name from v$instance;
select
(select host_name from v$instance) AS "Host_NAME",
(Select name from v$database) as "DB_NAME",
start_time,end_time,elapsed_seconds/60/60 as "DURATION(HOURS)", INPUT_TYPE,
(r.status) as status,(b.incremental_level) as incremental_level
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 INPUT_TYPE <>'ARCHIVELOG' order by 3;
spool off;
exit