Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 1 March 2015

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;

20 comments:

  1. Thanks a lot, this scrips are gold :)

    ReplyDelete
  2. Actually, I enjoyed a lot while reading this blog. Thanks for the good informative blog. I found one more good resource related to this, Just have a look: https://intellipaat.com/sql-training/

    ReplyDelete
  3. Thanks for sharing the valuable , helpful scripts.

    ReplyDelete
  4. Nice article
    Thanks for sharing the informative blog.

    Microsoft training in Bangalore

    ReplyDelete
  5. Awesome resources!
    Thanks for sharing such good article.

    Statutory compliance services

    ReplyDelete
  6. Great post.
    It really helped me to learn something new. So thanks.
    Mobile App Development Company In Bangalore

    ReplyDelete
  7. This was nice posts. And actually this will be included for those who are getting ready with the interview of many companies. So please keep update like this.

    Recruitment Consultancy in Chennai

    ReplyDelete
  8. Good content in this post and site. We need more fresh and good content like this. Thanks for posting great stuff it’s very useful to me.
    Thanks and regards,
    Oracle Fusion Cloud HCM Training 

    ReplyDelete
  9. I found your blog has very interesting topics,thanks for sharing such an interesting information with us. Erp tree provides best oracle financials online coaching globally.people who interested in oracle go through our site and for more info call us .

    ReplyDelete
  10. Thanks a lot for the article post.Really looking forward to read more. Fantastic. Please Visit:

    SCCM Peer to Peer
    legacy applications
    windows migration

    ReplyDelete
  11. Wow, this article has thought me an important programming skill that has been giving me a hard time while writing my dissertation paper and making Dissertation Data Presentation, I am happy that through reading this article I have been able to correct my program and now it is running. Thanks so much for sharing this information with us. I have found it to be very useful.

    ReplyDelete
  12. Nice article... Thanks for sharing the informative blog.

    Web Development Houston

    ReplyDelete
  13. I would like to appreciate your work for good accuracy and got informative knowledge from here.

    Oracle Fusion financial

    ReplyDelete
  14. What is difference Oracle DBA or Oracle rac dba ... Both are sequential course or different..
    Which is best I nt go for a software developer..

    ReplyDelete