Monitor backup jobs using RMAN views

Proper monitoring of backup jobs is one of the crucial elements in ensuring that your databases are well-enough protected against data loss. You can for example grep your backup logs, put some kind of alerting inside your backup scripts, but the most obvious method is just to use Oracle dictionary views.

In order to get the time when the last successful incremental level 0 backup has finished, we can connect to target database and run:

select max(bsd.completion_time) last_inc_0_time
from v$rman_backup_job_details rbjd, v$backup_set_details bsd
where bsd.session_recid = rbjd.session_recid
and bsd.session_key = rbjd.session_key
and bsd.session_stamp = rbjd.session_stamp
and bsd.incremental_level = 0
and rbjd.status = 'COMPLETED';

LAST_INC_0_TIME          
-----------------
20.01.18 10:34:20

Nevertheless, you need to be careful as the correctness of above query depends on your backup strategy. For example, if your strategy is to backup to disk first and then send backupsets to tape (which is quite common), it can give you wrong results, due to the way Oracle is updating timing information for backupsets. COMPLETION_TIME column in V$BACKUP_SET_DETAILS is updated each time after the job touching the backupset is run. So, after sending backupsets to tapes, you’ll get information about the time this job completed, not the time when “real” level 0 backup had finished. For example, if COMPLETION_TIME equals 20.01.2018 10:34:20 just after incremental level 0 is finished and only two days later you sent those backupsets to tapes, it will change for example to 22.01.2018 08:23:23, causing your monitoring not to work properly. What we should be interested in, is the date when the datafiles finished to be backed up.

Fortunately, there is a way to get this information, even after sending backupsets to disk. For this purpose we can use V$BACKUP_DATAFILE view, so the better query should look like below:

select max(bd.completion_time) last_inc_0_time
from v$rman_backup_job_details rbjd, v$backup_set_details bsd, v$backup_datafile bd
where bsd.session_recid = rbjd.session_recid
and bsd.session_key = rbjd.session_key
and bsd.session_stamp = rbjd.session_stamp
and bsd.incremental_level = 0
and rbjd.status = 'COMPLETED'
and bsd.set_stamp = bd.set_stamp
and bsd.set_count = bd.set_count;

Please just keep in mind that it will give you the time when datafiles finished being backed up, not the time the job has finished, as you can be doing much more in the context of this job, like backing up archived logs, controlfile autobackups etc., so the end time of the job itself as recorded in V$RMAN_BACKUP_JOB_DETAILS will be in later point in time.

But, there is one case when above query again won’t show you correct results… It can happen when full backup is running for a long time and in the meantime, there is another job started, sending backups to tapes. It will pick up backupsets created already by our incremental level 0 still running and update the timing information in V$BACKUP_DATAFILE. After this job completes successfully, our query will report the time of the last backupset sent to tapes… That’s why we need to add another condition, to check for already running incremental level 0 and report only information from before it has started. So, final query would look like below:

select max(bd.completion_time) last_inc_0_time
from v$rman_backup_job_details rbjd, v$backup_set_details bsd, v$backup_datafile bd
where bsd.session_recid = rbjd.session_recid
and bsd.session_key = rbjd.session_key
and bsd.session_stamp = rbjd.session_stamp
and bsd.incremental_level = 0
and rbjd.status = 'COMPLETED'
and bsd.set_stamp = bd.set_stamp
and bsd.set_count = bd.set_count
and rbjd.start_time < (select nvl(min(rbjd.start_time), sysdate)
                        from v$rman_backup_job_details rbjd, v$backup_set_details bsd
                        where bsd.session_recid = rbjd.session_recid
                        and bsd.session_key = rbjd.session_key
                        and bsd.session_stamp = rbjd.session_stamp
                        and bsd.incremental_level = 0
                        and rbjd.input_type = 'DB INCR'
                        and rbjd.status = 'RUNNING');

Of course you can also run similar thing while being connected directly to the catalog, e.g.

   
select max(bd.completion_time) last_inc_0_time
from rc_rman_backup_job_details rbjd, rc_backup_set_details bsd, rc_backup_datafile bd
where bsd.session_recid = rbjd.session_recid
and bsd.session_key = rbjd.session_key
and bsd.session_stamp = rbjd.session_stamp
and bsd.incremental_level = 0
and rbjd.status = 'COMPLETED'
and bsd.set_stamp = bd.set_stamp
and bsd.set_count = bd.set_count
and bsd.db_name = 'DBNAME'
and rbjd.start_time < (select nvl(min(rbjd.start_time), sysdate)
                        from rc_rman_backup_job_details rbjd, rc_backup_set_details bsd
                        where bsd.session_recid = rbjd.session_recid
                        and bsd.session_key = rbjd.session_key
                        and bsd.session_stamp = rbjd.session_stamp
                        and bsd.incremental_level = 0
                        and rbjd.input_type = 'DB INCR'
                        and rbjd.status = 'RUNNING'
                        and bsd.db_name = 'DBNAME');

If you are interested in backups only on tapes or only on disk, you can for example use DEVICE_TYPE column of V$BACKUP_SET or V$BACKUP_SET_DETAILS views (DISK, SBT_TAPE or * in case backupset is available in both locations). You can also use OUTPUT_DEVICE_TYPE from V$RMAN_BACKUP_JOB_DETAILS, but in this case please be aware that due to Bug 21867425: V$RMAN_BACKUP_JOB_DETAILS VIEW SHOWS DISK EVEN IF BACKUP TAKEN TO TAPE in 12.1.0.2, it is not set to SBT for tape backups.

Advertisements

2 thoughts on “Monitor backup jobs using RMAN views”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s