How to Monitor RMAN Backup through SQL Query

To Monitor RMAN you can use OEM or any other tools such as “Quest Backup Report for Oracle”.
You can also used following Views in oracle 9i to check the RMAN backup status:
v$rman_configuration
v$backup_set
v$backup_piece
v$backup_spfile
v$session_longops
Script to check RMAN Configuration:
SELECT  name “PARAMETERS”, value “RMAN CONFIGURATION”
FROM  v$rman_configuration
ORDER BY name;

PARAMETERS RMAN CONFIGURATION
BACKUP OPTIMIZATION ON
CHANNEL DEVICE TYPE DISK FORMAT   ‘H:ORABACK%U’
CONTROLFILE AUTOBACKUP ON
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘H:ORABACK%F’
DEFAULT DEVICE TYPE TO DISK
RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS

Script to List RMAN Backup Piece:
SELECT bs.recid,
DECODE(   bp.status, ‘A’, ‘Available’, ‘D’, ‘Deleted’, ‘X’, ‘Expired’) status , bp.handle
handle, TO_CHAR(bp.start_time, ‘dd/mm/yyyy HH24:MI:SS’)start_time
, TO_CHAR(bp.completion_time, ‘dd/mm/yyyy HH24:MI:SS’) completion_time, bp.elapsed_seconds  “ELAPSED”
FROM
v$backup_set bs JOIN v$backup_piece bp USING (set_stamp,set_count)
WHERE
bp.status IN (‘A’, ‘X’) AND bp.completion_time > sysdate-1
ORDER BY bp.completion_time desc, bs.recid, bp.piece#;
Script to List RMAN Backup Set:
SELECT  bs.recid, DECODE(backup_type, ‘L’, ‘Archived Logs’, ‘D’, ‘Datafile Full’, ‘I’, ‘Incremental’) backup_type,
device_type “type”, DECODE(bs.controlfile_included, ‘NO’, null, bs.controlfile_included) controlfile,
sp.spfile_included spfile, bs.incremental_level L,TO_CHAR(bs.start_time, ‘dd/mm/yyyy HH24:MI:SS’) start_time
, TO_CHAR(bs.completion_time, ‘dd/mm/yyyy HH24:MI:SS’)  completion_time, bs.elapsed_seconds “ELAPSED”, bp.tag, bs.block_size “BLOCK”
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 completion_time > sysdate -1
AND 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 (+)
ORDER BY  completion_time desc, bs.recid;
Script to List RMAN Job done:
select SID, serial#, opname, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at, elapsed_seconds elapsed, username
from v$session_longops
Where totalwork = sofar
AND opname NOT LIKE ‘%aggregate%’
AND start_time > sysdate -1
AND opname like ‘RMAN%’;

Note: If you need to query running status of rman backup then you have to set totalwork > sofar in where condition.
Script to Monitor RMAN Job in Progress:
SELECT sid, serial#, sofar, totalwork, opname, round(sofar/totalwork*100,2) AS pct_complete
FROM v$session_longops
WHERE opname LIKE ‘RMAN%’
AND opname NOT LIKE ‘%aggregate%’ AND totalwork != 0
AND sofar <> totalwork;
Script to list RMAN spfile & Controlfile Backup:
SELECT bs.recid, sp.spfile_included spfile
, TO_CHAR(bs.completion_time, ‘dd/mm/yyyy HH24:MI:SS’) completion_time
, DECODE(status, ‘A’, ‘Available’, ‘D’, ‘Deleted’, ‘X’, ‘Expired’) status, handle
FROM v$backup_set  bs, v$backup_piece  bp, (select distinct set_stamp, set_count, ‘YES’ spfile_included
from v$backup_spfile) sp
WHERE bs.set_stamp = bp.set_stamp
AND bs.completion_time > sysdate -1
AND bs.set_count = bp.set_count
AND bp.status IN (‘A’, ‘X’)
AND bs.set_stamp = sp.set_stamp
AND bs.set_count = sp.set_count
ORDER BY  bs.completion_time desc, bs.recid, piece#;
Apart from the above view you can also used below views in oracle 9i to find the size of rman backup:
v$backup_datafile
v$backup_redolog
Script to find size of Datafile and Archivelog Backup:
select trunc(completion_time) “BACKUP_DATE”, sum(blocks*block_size)/1024/1024 “DBF_BACKUP”,
(SELECT sum(blocks*block_size)/1024/1024  from v$backup_redolog
WHERE first_time > sysdate-1) “ARC_BACKUP”
from v$backup_datafile
WHERE completion_time > sysdate – 1
group by trunc(completion_time)
order by 1 DESC;
You can also use V$SESSION_LONGOPS to estimate when a job will complete for an RMAN backup or restore operation, because the total amount of work, the time the job began, and the amount of work left are known values.
SELECT sid, serial#, 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
FROM v$session_longops
WHERE opname LIKE ‘RMAN%’
AND opname NOT LIKE ‘%aggregate%’
AND totalwork != 0
AND sofar <> totalwork;
In oracle 10 onwards you can used these view to find the rman backup and operation:
v$rman_status
v$rman_output
v$rman_backup_job_details
Script to Report RMAN full, incremental and archivelog backups
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;
Script ro Report RMAN full, incremental without archivelog backups
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;

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s