Quest Backup Reporter for Oracle 1 Preview
I don't normally do product reviews, but at the request of my fellow Oracle tweeps (@hillbillytoad) I have attempted to do my first review in this post. Here goes.
While working on a project to implement a new enterprise backup software for Oracle (EMC Avamar), I was asked certain questions about the Oracle database backups - how large are the database backups? What is the growth rate of backups? What is the rate of change of databases? How many databases are backed up per hour? How many level 0 and level 1 incremental backups are done over a period of time? It's relatively straightforward to get this information from one or two databases but a little bit more challenging across the enterprise with hundreds of databases. We can query the v$ views in the target databases - v$database, v$backup_set, v$backup_set_details, v$rman_status etc.. or their recovery catalog counterparts rc_database, rc_backup_set, rc_backup_set_details (see summary of RMAN catalog views here). Separately, I was being asked by our Backup and Recovery team why our backups were still being held on tape (TDP for Oracle) longer than hour retention policy and using up several terabytes of storage. Our RMAN scripts regularly delete obsolete backups, or so I thought. Who are the culprits?
After taking a stab at querying the RMAN catalog I soon found out that it would take me a while to get all the information being requested. This didn't include time to format and present the information. I think I tweeted something about this and was directed to Quest Backup Reporter for Oracle Preview Edition. This seemed to be just the tool I was looking for. After downloading and installing QBR I added the targets I needed to query. I was quickly able to see when the last backups were run and if any had failed. Usually our RMAN scripts send emails to a notification list when a backup job fails, but this is not a very convenient way of reviewing failures.
After spending several months previewing the product (through various iterations) I must say that this is an essential tool for gaining visibility in Oracle Backups performed by RMAN.
Please note that this is a review of the Preview edition. Quest Backup Reporter is now generally available here.
Here are few of the features of Quest Backup Reporter with screenshots.
You can register a target called a Data Source to get backup related information from either the control file or the RMAN catalog. QBR supports direct, TNS Name and LDAP connection which makes it very flexible. A user with DBA role or SELECT_CATALOG_ROLE is required for querying the data source.
Once the targets are registered you you see at a glance the date and time of the last backup, failed backups and other violations based on the default policy. It also shows in real-time the status of any running backups.
Policies are used to determine if backups meet certain criteria. There is a default profile but you can define any number of profiles for your environment. For example you can define a production profile for production databases and a test profile for test databases.
Once policies have been created you can assign it to the targets.
Once defined you can list the policies and their properties.
Once a policy has been applied to a target you can click on the Violations tab to see the types and categories of violations. For each violation you are shown additional information such as recommendations. The recommendations themselves are somewhat generic but you also have the option of customizing them. There is also a master/details view which provides some more verbose information about the violations.
Another very useful feature of QBR is the Growth tab. Here you can visualize how the size of that backups change over time and it's correlation to the duration of the backup. For the screenshot shown below I can see that although the backup size was fairly constant the backup times dropped significantly after April 21. This corresponded to the time when we began testing our new media management software.
RMAN Parameters lists the stored configurations of each database. This is similar to the output of a SHOW ALL command in RMAN.
Another feature I found insightful was the ability to see Timelines for each type of backup. It pretty much shows you when each backup was run on a calendar. This can help to determine times to schedule database activities or batch jobs so as not to compete for resources. Also, if there are several backups scheduled to run at the same time causing network congestion, you will be able to see it here.
If you want to drill down into the backups, this is where you'd look. Search through jobs by backup type (full, incremental, archivelog) or by date. If you think the backups are too slow, you can look at the Performance subtab. The Storage Information section is what I found very interesting. Hey, I can tell size the obsolete backups! Now I can make sure to run the delete obsolete command to free up some tapes.
The Storage tabs presents the storage distribution for the backups by database.
Apart from the very extensive feature set I did have a few problems with QBR. Firstly, if you have hundreds of databases to register it could take a very long time to load them once QBR is started. In my evaluation I registered at most 20 data sources. Sometimes it could take up to 30 minutes to load them all. This could be as a result of my catalog database being slow or some other issue (this was still a preview edition). Another related issue is that this could cause some overhead with your catalog database or data dictionary as I found that it polls the data sources in real time. Therefore, if you have 50 databases it will constantly query those 50 databases. I also ran into some "Unhandled exception" errors, but I will chalk those up as beta product issues. The user interface is good but not very intuitive. For example you cannot click on the database if you are not in the Overview tab. It show an expanded view of the servers and databases hierarchically but you cannot expand/collapse the view or click on the databases for that matter.
Overall, Quest Backup Reporter for Oracle is a solid product. It provides a comprehensive view of your Oracle backup environment across the enterprise with some cool and useful graphs and charts. I wish Oracle Enterprise Manager Grid Control had some of these features but QBR does a very good job of filling the gap.
While working on a project to implement a new enterprise backup software for Oracle (EMC Avamar), I was asked certain questions about the Oracle database backups - how large are the database backups? What is the growth rate of backups? What is the rate of change of databases? How many databases are backed up per hour? How many level 0 and level 1 incremental backups are done over a period of time? It's relatively straightforward to get this information from one or two databases but a little bit more challenging across the enterprise with hundreds of databases. We can query the v$ views in the target databases - v$database, v$backup_set, v$backup_set_details, v$rman_status etc.. or their recovery catalog counterparts rc_database, rc_backup_set, rc_backup_set_details (see summary of RMAN catalog views here). Separately, I was being asked by our Backup and Recovery team why our backups were still being held on tape (TDP for Oracle) longer than hour retention policy and using up several terabytes of storage. Our RMAN scripts regularly delete obsolete backups, or so I thought. Who are the culprits?
After taking a stab at querying the RMAN catalog I soon found out that it would take me a while to get all the information being requested. This didn't include time to format and present the information. I think I tweeted something about this and was directed to Quest Backup Reporter for Oracle Preview Edition. This seemed to be just the tool I was looking for. After downloading and installing QBR I added the targets I needed to query. I was quickly able to see when the last backups were run and if any had failed. Usually our RMAN scripts send emails to a notification list when a backup job fails, but this is not a very convenient way of reviewing failures.
After spending several months previewing the product (through various iterations) I must say that this is an essential tool for gaining visibility in Oracle Backups performed by RMAN.
Please note that this is a review of the Preview edition. Quest Backup Reporter is now generally available here.
Here are few of the features of Quest Backup Reporter with screenshots.
You can register a target called a Data Source to get backup related information from either the control file or the RMAN catalog. QBR supports direct, TNS Name and LDAP connection which makes it very flexible. A user with DBA role or SELECT_CATALOG_ROLE is required for querying the data source.
Once the targets are registered you you see at a glance the date and time of the last backup, failed backups and other violations based on the default policy. It also shows in real-time the status of any running backups.
Policies are used to determine if backups meet certain criteria. There is a default profile but you can define any number of profiles for your environment. For example you can define a production profile for production databases and a test profile for test databases.
Once policies have been created you can assign it to the targets.
Policy definitions include the retention period of backups (recovery window of redundancy), full, level 0 and other incremental backup plus archivelog backups as well as performance. Within the various backup types you can specify attributes such as days of the week that the backup should run and maximum run time.
Once defined you can list the policies and their properties.
Once a policy has been applied to a target you can click on the Violations tab to see the types and categories of violations. For each violation you are shown additional information such as recommendations. The recommendations themselves are somewhat generic but you also have the option of customizing them. There is also a master/details view which provides some more verbose information about the violations.
The Backups tab gives a comprehensive overview of the database backups. If you have also added a recovery catalog it can provide additional details about the registered databases.
Another very useful feature of QBR is the Growth tab. Here you can visualize how the size of that backups change over time and it's correlation to the duration of the backup. For the screenshot shown below I can see that although the backup size was fairly constant the backup times dropped significantly after April 21. This corresponded to the time when we began testing our new media management software.
RMAN Parameters lists the stored configurations of each database. This is similar to the output of a SHOW ALL command in RMAN.
Another feature I found insightful was the ability to see Timelines for each type of backup. It pretty much shows you when each backup was run on a calendar. This can help to determine times to schedule database activities or batch jobs so as not to compete for resources. Also, if there are several backups scheduled to run at the same time causing network congestion, you will be able to see it here.
If you want to drill down into the backups, this is where you'd look. Search through jobs by backup type (full, incremental, archivelog) or by date. If you think the backups are too slow, you can look at the Performance subtab. The Storage Information section is what I found very interesting. Hey, I can tell size the obsolete backups! Now I can make sure to run the delete obsolete command to free up some tapes.
The Storage tabs presents the storage distribution for the backups by database.
Apart from the very extensive feature set I did have a few problems with QBR. Firstly, if you have hundreds of databases to register it could take a very long time to load them once QBR is started. In my evaluation I registered at most 20 data sources. Sometimes it could take up to 30 minutes to load them all. This could be as a result of my catalog database being slow or some other issue (this was still a preview edition). Another related issue is that this could cause some overhead with your catalog database or data dictionary as I found that it polls the data sources in real time. Therefore, if you have 50 databases it will constantly query those 50 databases. I also ran into some "Unhandled exception" errors, but I will chalk those up as beta product issues. The user interface is good but not very intuitive. For example you cannot click on the database if you are not in the Overview tab. It show an expanded view of the servers and databases hierarchically but you cannot expand/collapse the view or click on the databases for that matter.
Overall, Quest Backup Reporter for Oracle is a solid product. It provides a comprehensive view of your Oracle backup environment across the enterprise with some cool and useful graphs and charts. I wish Oracle Enterprise Manager Grid Control had some of these features but QBR does a very good job of filling the gap.
Have a thought for possible reasons why the data dictionary queries might have been time consuming. We have seen with other products that hammer the data dictionary (e.g. TOAD) that if the databases do not maintain recent stats collection on the fixed tables and dynamic views/tables then data dictionary queries can take a long time. For example a screen in TOAD took 45 minutes to refresh when stats were old and 2 seconds once stats were collected. I think a lot of DBA's don't know that they need to do this chore and that Oracle does not do it for them. Here are the commands to fix:
ReplyDeleteexec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;
Bert,
ReplyDeleteLack of stats on the dictionary and fixed tables could certainly cause performance issues when querying the dynamic views. A lot of third-party tools that monitor the database frequently do require it. Normally we don't gather those types of stats unless there are issues that warrant it such as before/after an upgrade.
I'm curious to see if this would help. I'd be willing to try it on the RMAN catalog database and use that as the data source. However the preview edition has expired so it's no longer functional.