RMAN reports, or What Is the ETA of My Backup?

Over-the-Top Tales from the Trenches.
Motto: Bringing order to the chaos of every day DBA life.

Dear Diary,

Are we there yet? How much farther?

Ever heard this during a recovery of a downed database?

The poor thing has either a minor scratch, requires immediate attention, or is dead on arrival (DOA).

You could cut the tension in the air with a knife, and you have the dreaded watcher¹ standing so close you can see their face mirrored on your monitor, making your typing accuracy go to 10%.

Of course you are prepared. You have many recovery tests under your belt, and you know what the log apply speed is… right? Your nifty little SQL script even knows from the size of the archive redo logs what a good approximate ETA for a recovery is.

Forget wading through pages of rman outputs, tallying up each backup piece duration, Oracle 10G comes riding to the rescue, with some nifty v$rman_ views.

So what is the ETA of my backup?

column INPUT_BYTES_PER_SEC_DISPLAY heading 'Input IO|per sec' format a10
column OUTPUT_BYTES_PER_SEC_DISPLAY heading 'Output IO|per sec' format a10
column TIME_TAKEN_DISPLAY heading 'Time' format a12

select start_time,elapsed_seconds,status,
INPUT_BYTES_PER_SEC_DISPLAY,OUTPUT_BYTES_PER_SEC_DISPLAY ,TIME_TAKEN_DISPLAY
from V$RMAN_BACKUP_JOB_DETAILS order by start_time;

Sample Output:

Input IO   Output IO
START_TIME     ELAPSED_SECONDS STATUS                  per sec    per sec    Time
-------------- --------------- ----------------------- ---------- ---------- ------------
20061224 01:13              61 COMPLETED                  54.92K     41.34K  00:01:01
20061225 01:04            1043 COMPLETED                  33.24M      5.20M  00:17:23
20061226 01:04             717 COMPLETED                  24.23M      3.86M  00:11:57
20061227 01:04             443 COMPLETED                  57.08M      1.70M  00:07:23
20061228 01:04             776 COMPLETED                  23.67M      4.17M  00:12:56
20061229 01:04             465 COMPLETED                  55.33M      2.13M  00:07:45
20061230 01:04             462 COMPLETED                  55.40M      1.98M  00:07:42
20061231 01:04             783 COMPLETED                  22.69M      4.02M  00:13:03
20070101 01:02             492 COMPLETED                  49.76M    837.45K  00:08:12
20070102 01:04             823 COMPLETED                  23.40M      4.31M  00:13:43
20070103 01:02             582 COMPLETED                  45.63M      2.41M  00:09:42

What is the current and past status of each RMAN step?

Note: This can be modified to only show any current jobs by adding WHERE SID <> 0.

column sid format 9999
column MBYTES_PROCESSED heading 'Size' format 999,999,999
column operation format a15 word wrap

select sid,operation,status,MBYTES_PROCESSED,
start_time,end_time,input_bytes,output_bytes
from v$rman_status
order by start_time
/

Sample Output:

SID OPERATION       STATUS                          Size START_TIME     END_TIME       INPUT_BYTES OUTPUT_BYTES
----- --------------- ----------------------- ------------ -------------- -------------- ----------- ------------
0 BACKUP          COMPLETED                          3 20061224 01:13 20061224 01:14     3430400      2582016
0 CONTROL FILE AN COMPLETED                         17 20061224 01:14 20061224 01:14    17956864     18038784
D SPFILE AUTOBA
CK

0 DELETE          COMPLETED                          0 20061224 01:16 20061224 01:17           0            0
0 RMAN            COMPLETED                     34,703 20061225 01:00 20061225 01:24  3.6388E+10   5723127808
0 DELETE          COMPLETED                          0 20061225 01:02 20061225 01:04           0            0
0 BACKUP          COMPLETED                         34 20061225 01:04 20061225 01:04    35913728     36044800
0 CONTROL FILE AN COMPLETED                         17 20061225 01:04 20061225 01:04    17956864     18038784
D SPFILE AUTOBA
CK

0 BACKUP          COMPLETED                     11,032 20061225 01:04 20061225 01:12  1.1567E+10   3529506816
0 BACKUP          COMPLETED                     23,598 20061225 01:12 20061225 01:20  2.4744E+10   2119819264
0 BACKUP          COMPLETED                          5 20061225 01:20 20061225 01:21     5330432      2657280
0 CONTROL FILE AN COMPLETED                         17 20061225 01:21 20061225 01:21    17956864     18038784
D SPFILE AUTOBA
CK

Have Fun.

Paul

1. The watcher is a fictional character normally associated with video game arcades.