Sunday, May 15, 2011

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

Thursday, May 5, 2011

Oracle RAC instance startup - ORA-29760

Having worked on Oracle RAC since 9i, there hasn't been a time when I haven't faced something new or realized something was not what I thought it was. I think this is one of the reasons why I enjoy working on these systems - always a challenge.
I stopped my RAC database to put it into noarchivelog mode using the srvctl command
[oracle@oradba10t oracle]$ srvctl stop database -d labrac

Next I proceeded to connect to the first instance to startup in mount mode
[oracle@oradba10t oracle]$ sqlplus /nolog
SQL*Plus: Release - Production on Thu May 5 09:47:15 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORA-29760: instance_number parameter not specified

Wait, what's this? I've never encountered this error message before. Googled a bit and realized that most of the responses were a bit far fetched since the database had been in perfect working order before I stopped it.
Then I realized that my ORACLE_SID was set to the database name and not the instance name. Makes sense.

[oracle@oradba10t oracle]$ echo $ORACLE_SID

Set the ORACLE_SID to the instance name and startup again
[oracle@oradba10t oracle]$ export ORACLE_SID=labrac1
[oracle@oradba10t oracle]$ sqlplus /nolog              
SQL*Plus: Release - Production on Thu May 5 09:50:42 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size                  2083632 bytes
Variable Size             905970896 bytes
Database Buffers          285212672 bytes
Redo Buffers               14692352 bytes
Database mounted.

That's all there is to it. The correct solution would be to put the environment variables in the .profile of .bash_profile of the oracle user. This is one of my lab environments and I hadn't put the usual scripts in place to take care of the environment variables for RAC.