Essential RMAN Monitoring Queries for Oracle DBAs

Oracle RMAN (Recovery Manager) monitoring is crucial for ensuring backup success and maintaining database availability. This comprehensive guide provides production-tested SQL queries that Oracle DBAs use daily to monitor backup operations, track progress, analyze performance, and troubleshoot issues across Oracle 11g, 12c, 18c, 19c, 21c, and 23c databases.

1. Monitor Currently Running RMAN Backups

Track all active RMAN backup sessions in real-time including full database backups, incremental backups, and archivelog backups. This query shows session keys, backup types, current status, start/end times, and elapsed duration in hours. Essential for identifying long-running or stuck backup operations during your backup window.
Query for Currently running RMAN backups like Full, Incremental & Archivelog backups

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;
Use Case: Run this query every 15-30 minutes during backup windows to verify scheduled jobs are executing properly, identify performance bottlenecks, and ensure backups complete within SLA timeframes.

2. Track RMAN Backup Progress with Completion Percentage

Monitor real-time backup progress with accurate completion percentage calculations. This query reads from V$SESSION_LONGOPS to show work completed versus total work required, enabling precise estimation of remaining backup time and early detection of stalled operations.
Query for RMAN Total pending and completed work

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, 
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;
Performance Alert: If the completion percentage remains unchanged for 30+ minutes, investigate immediately for I/O bottlenecks, storage connectivity issues, or resource contention.

3. Monitor Backup Progress Size in MB

Track actual data volume processed during RMAN operations in the last 24 hours. This query provides detailed operation tracking, helps calculate backup throughput (MB per second), and is essential for performance analysis and capacity planning.
Query to show the progress size in MB of RMAN backup/recovery

select to_char(start_time, 'dd-mon-yyyy@hh24:mi:ss') "Date", 
status, 
operation,
mbytes_processed
from v$rman_status vs
where start_time > sysdate -1
order by start_time;
Throughput Analysis: Divide mbytes_processed by elapsed time to calculate MB/sec throughput. Typical enterprise speeds: 100-300 MB/sec for disk, 50-150 MB/sec for tape.

4. Analyze Historical RMAN Backup Status

Review comprehensive historical backup performance to identify trends, calculate average backup durations, and troubleshoot recurring issues. Essential for SLA compliance reporting, capacity planning, and backup window optimization.
Query to check historical RMAN Backup Status

set linesize 500 pagesize 2000
col Hours format 9999.99
col STATUS format a10
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,
to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,
elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

5. Historical Backup with Detailed Size Analysis

The most comprehensive RMAN backup analysis query showing input sizes (actual database data), output sizes (compressed backup pieces), compression ratios, device types, and throughput metrics. Critical for storage capacity planning and backup strategy optimization.
Query to check historical RMAN backup with size

set pages 2000 lines 200
COL STATUS FORMAT a9
COL hrs FORMAT 999.99
select 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,
INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,
OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,
OUTPUT_DEVICE_TYPE
FROM V$RMAN_BACKUP_JOB_DETAILS
order by SESSION_KEY;
Compression Analysis: Calculate compression ratio: (1 - OUTPUT_GB / INPUT_GB) x 100%. Typical ratios: 50-70% for database backups, 30-50% for archive logs.

DBA Best Practices for RMAN Monitoring

Daily Monitoring Tasks:
  • Run Query 1 every 15-30 minutes during backup windows
  • Check Query 2 for any backups stuck at low completion percentages
  • Review Query 4 results for failed or long-running jobs
  • Monitor Query 3 for throughput analysis and bottleneck identification
Weekly Analysis:
  • Use Query 5 to analyze backup size trends and compression effectiveness
  • Compare backup durations week-over-week to identify performance degradation
  • Review storage consumption patterns for capacity planning
Troubleshooting Guidelines:
  • If backups show 0% progress for 30+ minutes: Check I/O subsystem and storage connectivity
  • For slower than expected performance: Investigate network bandwidth, parallel channels, and resource contention
  • When backups fail: Review alert.log, check RMAN repository with CROSSCHECK, validate backup pieces
These five essential RMAN monitoring queries provide comprehensive visibility into backup operations from real-time progress tracking to historical performance analysis. Regular use of these production-safe queries ensures backup success, enables proactive issue detection, and maintains database availability.


If you like please follow and comment