RMAN backup progress, I/O performance, and bottlenecks.

practical SQL queries you can run against Oracle views V$BACKUP_ASYNC_IO and V$BACKUP_SYNC_IO to monitor RMAN backup progress, I/O performance, and bottlenecks.

🟢 1. Check If RMAN Is Using Async I/O

SELECT DISTINCT aio_type 
FROM v$backup_async_io;

If it returns READ, WRITE, RMAN is using async I/O.

🟡 2. Monitor Average I/O Latency – Async I/O

SELECT    
 aio_type,   
 ROUND(AVG(elapsed_time / 1000), 2) AS avg_latency_ms,   
 COUNT(*) AS io_ops,   
 ROUND(SUM(bytes)/1024/1024, 2) AS total_mb 
FROM v$backup_async_io 
GROUP BY aio_type;

🔵 3. Monitor Average I/O Latency – Sync I/O

SELECT    
 ROUND(AVG(elapsed_time / 1000), 2) AS avg_latency_ms,   
 COUNT(*) AS io_ops,   
 ROUND(SUM(bytes)/1024/1024, 2) AS total_mb 
FROM v$backup_sync_io;

🔍 4. Find Slowest Async I/O Operations

SELECT    
 filename,   
 aio_type,   
 elapsed_time / 1000 AS latency_ms,   
 bytes,   
 io_start_time,   
 io_completion_time 
FROM v$backup_async_io 
ORDER BY elapsed_time DESC 
FETCH FIRST 10 ROWS ONLY;

📋 5. Summary by File (Async I/O)

SELECT    
 filename,   
 aio_type,   
 COUNT(*) AS io_ops,   
 ROUND(SUM(bytes)/1024/1024, 2) AS total_mb,   
 ROUND(AVG(elapsed_time / 1000), 2) AS avg_latency_ms  
FROM v$backup_async_io 
GROUP BY filename, aio_type 
ORDER BY avg_latency_ms DESC;

📅 6. I/O Over Time Window

SELECT    
 TO_CHAR(io_start_time, 'HH24:MI') AS minute,   
 COUNT(*) AS io_ops,   
 ROUND(SUM(bytes)/1024/1024, 2) AS total_mb 
FROM v$backup_async_io 
WHERE io_start_time > SYSDATE - (30/1440) 
GROUP BY TO_CHAR(io_start_time, 'HH24:MI') 
ORDER BY minute;

How to Combine With RMAN Job Info

If you're running a catalog or want to cross-reference with V$SESSION_LONGOPS:

SELECT   
 so.opname,   
 so.target_desc,   
 so.start_time,   
 so.sofar,   
 so.totalwork,   
 ROUND(so.sofar / so.totalwork * 100, 2) AS percent_done,   
 so.elapsed_seconds,   
 so.time_remaining 
FROM v$session_longops so 
WHERE so.opname LIKE '%RMAN%'   
AND so.totalwork > 0   
AND so.sofar <> so.totalwork;