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 watcher1 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.
