{"id":964,"date":"2024-12-27T21:24:36","date_gmt":"2024-12-27T21:24:36","guid":{"rendered":"https:\/\/w3buddy.com\/?p=964"},"modified":"2026-01-15T13:15:44","modified_gmt":"2026-01-15T07:45:44","slug":"oracle-rman-sql-scripts-monitoring-backup","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/oracle-rman-sql-scripts-monitoring-backup\/","title":{"rendered":"Oracle RMAN: Scripts to Monitor Backup Progress"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">These SQL scripts help monitor and check various aspects of RMAN (Recovery Manager) backups in Oracle databases. Below is a summary of what each script does:<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1. Check All Backups<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">set lines 300\ncol STATUS format a30\ncol hrs format 999.99\ncol START_TIME for a30\ncol INPUT_TYPE for a30\ncol END_TIME for a30\nselect\nSESSION_KEY, INPUT_TYPE, STATUS,\nto_char(START_TIME,'mm\/dd\/yy hh24:mi') start_time,\nto_char(END_TIME,'mm\/dd\/yy hh24:mi')   end_time,\nelapsed_seconds\/3600                   hrs\nfrom V$RMAN_BACKUP_JOB_DETAILS\norder by session_key;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">2. Check DB Incremental Backup<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">col STATUS format a9\ncol hrs format 999.99\nselect\nSESSION_KEY, INPUT_TYPE, STATUS,\nto_char(START_TIME,'mm\/dd\/yy hh24:mi') start_time,\nto_char(END_TIME,'mm\/dd\/yy hh24:mi')   end_time,\nelapsed_seconds\/3600                   hrs\nfrom V$RMAN_BACKUP_JOB_DETAILS\nwhere input_type='DB INCR'\norder by session_key;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">3. Check DB Archive Backup<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">set lines 10000\ncol STATUS format a9\ncol hrs format 999.99\nselect\nSESSION_KEY, INPUT_TYPE, STATUS,\nto_char(START_TIME,'mm\/dd\/yy hh24:mi') start_time,\nto_char(END_TIME,'mm\/dd\/yy hh24:mi')   end_time,\nelapsed_seconds\/3600                   hrs\nfrom V$RMAN_BACKUP_JOB_DETAILS\nwhere input_type='ARCHIVELOG'\norder by session_key;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">4. Check Full Backup<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">set lines 200 pages 100\ncol STATUS format a25\ncol start_time for a20\ncol end_time for a20\ncol input_bytes_display format a9\ncol output_bytes_display format a9\ncol HRS:MIN for a10\n\nselect \/*+ rule*\/ SESSION_KEY,INPUT_TYPE, b.incr_lvl, STATUS,\n\tto_char(START_TIME,'mm\/dd\/yy hh24:mi') start_time,\n\tto_char(END_TIME,'mm\/dd\/yy hh24:mi')   end_time,\n\tdecode(to_char(start_time, 'd'), 1, 'Sunday', 2, 'Monday',\n\t\t\t\t\t\t\t\t\t\t3, 'Tuesday', 4, 'Wednesday',\n\t\t\t\t\t\t\t\t\t\t5, 'Thursday', 6, 'Friday',\n\t\t\t\t\t\t\t\t\t\t7, 'Saturday') dow,\n\ttrunc(elapsed_seconds\/3600) ||':' || trunc(mod((elapsed_seconds\/60),60)) \"HRS:MIN\",\n\tceil(((LEAD(START_TIME, 1, START_TIME ) OVER (ORDER BY START_TIME)) - START_TIME)*24 + (elapsed_seconds)\/3600 + 6) AS parameter_HRS ,\n\tinput_bytes_display, \n\toutput_bytes_display,\n\tOUTPUT_DEVICE_TYPE\n\tfrom V$RMAN_BACKUP_JOB_DETAILS r join (select distinct SESSION_RECID,session_stamp, incremental_level incr_lvl from v$backup_set_details where incremental_level = 0) b\n\ton r.session_stamp = b.session_stamp  and r.SESSION_RECID = b.SESSION_RECID\t\n\tWhere input_type in ('DB INCR')\n\tunion all\n\tselect SESSION_KEY,INPUT_TYPE, NULL incr_lvl, STATUS,\n\tto_char(START_TIME,'mm\/dd\/yy hh24:mi') start_time,\n\tto_char(END_TIME,'mm\/dd\/yy hh24:mi')   end_time,\n\tdecode(to_char(start_time, 'd'), 1, 'Sunday', 2, 'Monday',\n\t\t\t\t\t\t\t\t\t\t3, 'Tuesday', 4, 'Wednesday',\n\t\t\t\t\t\t\t\t\t\t5, 'Thursday', 6, 'Friday',\n\t\t\t\t\t\t\t\t\t\t7, 'Saturday') dow,\n\ttrunc(elapsed_seconds\/3600) ||':' || trunc(mod((elapsed_seconds\/60),60)) \"HRS:MIN\",\n\tceil(((LEAD(START_TIME, 1, START_TIME ) OVER (ORDER BY START_TIME)) - START_TIME)*24 + (elapsed_seconds)\/3600 + 6) AS parameter_HRS ,\n\tinput_bytes_display, \n\toutput_bytes_display,\n\tOUTPUT_DEVICE_TYPE\n\tfrom V$RMAN_BACKUP_JOB_DETAILS r \n\tWhere input_type in ('DB FULL')\n\torder by 1;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">5. Monitor RMAN Current Backup Progress<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">col dbsize_mbytes      for 99,999,990.00 justify right head \"DBSIZE_MB\"\ncol input_mbytes       for 99,999,990.00 justify right head \"READ_MB\"\ncol output_mbytes      for 99,999,990.00 justify right head \"WRITTEN_MB\"\ncol output_device_type for a10           justify left head \"DEVICE\"\ncol complete           for 990.00        justify right head \"COMPLETE %\" \ncol compression        for 990.00        justify right head \"COMPRESS|% ORIG\"\ncol est_complete       for a20           head \"ESTIMATED COMPLETION\"\ncol recid              for 9999999       head \"ID\"\n\nselect recid\n     , output_device_type\n     , dbsize_mbytes\n     , input_bytes\/1024\/1024 input_mbytes\n     , output_bytes\/1024\/1024 output_mbytes\n     , (output_bytes\/input_bytes*100) compression\n     , (mbytes_processed\/dbsize_mbytes*100) complete\n     , to_char(start_time + (sysdate-start_time)\/(mbytes_processed\/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete\n  from v$rman_status rs\n     , (select sum(bytes)\/1024\/1024 dbsize_mbytes from v$datafile) \n where status='RUNNING'\n   and output_device_type is not null\n\/<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">6. Check RMAN Backup Logs<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">set lines 300 pages 1000\nselect output\nfrom v$rman_output\nwhere session_key = 15       \norder by recid;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">7. Check Backup Running Sessions<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SET LINESIZE 500\nSET PAGESIZE 1000\ncol sid for 9999\ncol Serial# for 99999\ncol Program for a30\ncol \"OS User\" for a10\ncol Process for a6\ncol \"Schema Name\" for a15\ncol \"SQL Text\" for a60\n-- SET FEEDBACK OFF\nSELECT s.sid,s.serial#,s.LOGON_TIME,\ns.status \"Status\",\np.spid \"Process\",\ns.schemaname \"Schema Name\",\ns.osuser \"OS User\",\ns.program \"Program\",\na.sql_id,\nSubstr(a.sql_text,1,120) \"SQL Text\"\nFROM   v$session s,\nv$sqlarea a,\nv$process p\nWHERE  s.sql_hash_value = a.hash_value (+)\nAND    s.sql_address    = a.address (+)\nAND    s.paddr          = p.addr\n## AND    s.SID='&amp;SID'\nAND s.program like '%rman%'\nAND    s.type &lt;>'BACKGROUND';<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Note: if you want to kill any RMAN session then you can pick sid,#serial numbers from the output of above query and then kill the session using below command.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Note:<\/strong> If you need to kill any RMAN session, you can use the SID and serial number from the output of the above query, and then terminate the session using the following command.<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">ALTER SYSTEM KILL SESSION '12,10124' IMMEDIATE;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">These scripts provide comprehensive visibility into the status and progress of RMAN backup operations and can be adjusted based on specific needs, such as filtering by backup type or session key.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Also read:<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/w3buddy.com\/blog\/rman-commands-reference\/\"><strong>RMAN Commands Reference<\/strong><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>These SQL scripts help monitor and check various aspects of RMAN (Recovery Manager) backups in Oracle databases. Below is a summary of what each script does: 1. Check All Backups 2. Check DB Incremental Backup 3. Check DB Archive Backup 4. Check Full Backup 5. Monitor RMAN Current Backup Progress 6. Check RMAN Backup Logs [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-964","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/964","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=964"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/964\/revisions"}],"predecessor-version":[{"id":1561,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/964\/revisions\/1561"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=964"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=964"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=964"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}