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#;
