Oracle : Important Queries for RMAN

Share via:

Post Views: 0

RMAN Backup Size :

set linesize 300
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;

PROMPT
PROMPT +————————————————————————+
PROMPT | Report : RMAN Status |
PROMPT | Instance : &current_instance |
PROMPT +————————————————————————+

SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
COL Backup_Size FORMAT A30
COL Backup_Time FORMAT A30
col start_time for a20
col input_type for a20
col status for a25
col end_time for a20
select session_key,
input_type,
status,
to_char(start_time,’yyyy-mm-dd hh24:mi’) start_time,
to_char(end_time,’yyyy-mm-dd hh24:mi’) end_time,
output_bytes_display Backup_Size,
time_taken_display Backup_Time
from v$rman_backup_job_details
order by session_key asc;

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;

RMAN Backup History :

set lines 220
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 a10 trunc
col time_taken_display for a10 heading “TIME|TAKEN”
col output_instance for 9999 heading “OUT|INST”
col START_TIME for a30
col end_time for a30
col dow for a15
col INPUT_TYPE for a15
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;

RMAN Check block change tracking :

select status, bytes from v$block_change_tracking;

RMAN Progress :

SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;

SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN instance_name FORMAT a10 HEADING ‘Instance’
COLUMN sid HEADING ‘Oracle|SID’
COLUMN serial_num HEADING ‘Serial|#’
COLUMN opname FORMAT a30 HEADING ‘RMAN|Operation’
COLUMN start_time FORMAT a18 HEADING ‘Start|Time’
COLUMN totalwork HEADING ‘Total|Work’
COLUMN sofar HEADING ‘So|Far’
COLUMN pct_done HEADING ‘Percent|Done’
COLUMN elapsed_seconds HEADING ‘Elapsed|Seconds’
COLUMN time_remaining HEADING ‘Seconds|Remaining’
COLUMN done_at FORMAT a18 HEADING ‘Done|At’

SELECT
i.instance_name instance_name
, sid sid
, serial# serial_num
, b.opname opname
, TO_CHAR(b.start_time, ‘mm/dd/yy HH24:MI:SS’) start_time
, b.totalwork totalwork
, b.sofar sofar
, ROUND( (b.sofar/DECODE( b.totalwork
, 0
, 0.001
, b.totalwork)*100),2) pct_done
, b.elapsed_seconds elapsed_seconds
, b.time_remaining time_remaining
, DECODE( b.time_remaining
, 0
, TO_CHAR((b.start_time + b.elapsed_seconds/3600/24), ‘mm/dd/yy HH24:MI:SS’)
, TO_CHAR((SYSDATE + b.time_remaining/3600/24), ‘mm/dd/yy HH24:MI:SS’)
) done_at
FROM
gv$session a
JOIN gv$session_longops b USING (sid,serial#)
JOIN gv$instance i ON ( i.inst_id = a.inst_id
AND i.inst_id = b.inst_id)
WHERE
a.program LIKE ‘rman%’
AND b.opname LIKE ‘RMAN%’
AND b.opname NOT LIKE ‘%aggregate%’
AND b.totalwork > 0
ORDER BY
i.instance_name
, b.start_time;