Capacity Planning for Oracle Databases Using Legato NetWorker

John Ouellette

While planning for backups, I wanted to know how large our databases would be in a year. No one I asked could provide an answer, so I decided to figure it out for myself. In this article, I will explain my process.

At the company where I work, we currently use Legato NetWorker 7.0 server and the NetWorker Module for Oracle (NMO) 4.1 for our Oracle backups; this allows us to perform hot backups on each of our 40 databases every night. Our setup consists of a mixture of single-instance development, QA and production 9I RAC cluster databases all supporting our ERP efforts. Two Compaq MSL5000 series jukeboxes house the tapes, each containing two Super DLT drives.

Our setup defines each server that houses a database as its own group and names that group "Servername-Oracle", to distinguish from our normal file system backup, which would simply be named "Servername". Additionally, we define each database to be backed up in the saveset line in the NetWorker client definition. NetWorker knows to use the NMO to do Oracle backups by specifying the string nsrnmo in the client configuration under backup command (see Figure 1). As shown in Figure 1, the db_fin01, db_fin02, and db_fin03 databases live on the server Host02.

Savegroup Notification

After each backup group has finished, Legato NetWorker is capable of performing an action, called a savegroup notification. The default savegroup notification emails the Savegroup Completion Report (SCR) to the root user of the local system with a subject of "Servername's Savegroup Completion". A common approach is to send a message to a mailing list of folks who are responsible for backups, but you may use any email address you wish.

The problem with the default savegroup notification is that there are no internal variables in NetWorker to set the subject to. So if you have 50 different groups running backups every night, you will receive 50 emails with the subject of "Servername's Savegroup Completion" every day. Not only is it cumbersome to sort through that many emails to find information, it is also inadvisable to rely on your mail client to store your SCRs.

A solution to this approach is to define a new action with the ability to:

1. Email you the savegroup completion report with proper subjects.

2. Save a history of the SCR on the file system for later review.

This is exactly what the script sc.sh does in Listing 1. In this listing, lines 1-4 set up variables of day, month, year, and time. They don't need to be changed unless your version of date uses a different syntax.

Lines 5-7 are variables that will need to be changed. The first one is the directory where you will save all your SCRs, the second is the location of your mail binary, and the third is an email address to receive alerts.

Lines 8-10 contain the heart of the script. Line 8 takes the output after a group has finished and redirects it to a temporary file location ($$ means the pid of the running process). Line 9 sets the variable $GROUP, which is derived from the line containing the word savegrp and cut to the proper name of the current group (see Figure 2).

Line 11 emails the log to the defined group.

Line 12 is optionally commented out for troubleshooting purposes, because it will echo all variables into the daemon.log file. Finally, the file is moved to its formal location after ensuring its destination directory exists, and the temp file is removed.

To set this new Savegroup completion in the NetWorker Admin program, you would go to Customize->Notifications-> Savegroup completion reports, set the action to /nsr/res/sc.sh, and click "apply" (see Figure 3). Note that in order to use this script without modifying it, there must be no spaces in the names of your groups.

Now you will receive nicely formatted emails with the subject of "Groupname_Oracle Completion Report" instead of the default subject line for every email. In this case, I received an email with the subject of "Host02_Oracle Completion Report" (see Figure 2).

The benefit here is twofold. First, you can quickly and easily sort through your SCRs in your mail reader by hostname. Second, because each SCR is now saved on the file system, you have a history of how large a database was on a certain date. Once you establish a growth pattern, you will be able to justify hardware purchases with solid data. Later, I'll show you how to quickly get any month's data and create a graph suitable for that hardware budget meeting you're having with the IS Manager.

Using Savegroup Completion Reports

Let's examine the structure of an SCR (Figure 2). The first stanza after the subject line states the type of report, in this case a notice of completion. Next is the group name, number of clients, and the split between successful and failed clients, followed by start and stop times. The SCR finishes with the successful and then unsuccessful savesets.

The NetWorker Module for Oracle also lists an abbreviated output of what the Oracle Recovery Manager (RMAN) has logged. In this case, it is suppressing 811 lines stating which dbf files were backed up. You do not need to worry about the exact files NetWorker has saved to tape; that's the job for RMAN. However, you can see that, in this case, the Oracle database is in hot backup mode, as the archive log files have been backed up and listed in the SCR. Further down, it tells us the name of the RMAN script and the amount of data backed up. These values are the keys to our capacity planning efforts; we will leverage this information in the scripts that we'll view shortly. Finally, you can see the size of the index for this client (in kb and number of files).

Note, however, that there is a bug in the NMO for Tru64 users (Legato patch lgTpa63932) that displays the size of each individual database in the SCR as the size of the group of databases of which it is a part. Please apply the patch if you have less than NMO 4.1.

Accessing the Data

Once we have all this wonderful information, we need a way to quickly access it. I have found it useful to give the DBA manager a spreadsheet of the past 30 days of a database's size. The next script, chrt.sh in Listing 2, shows the date and the size of the database for any full month's worth of data.

The key to this script is what happens in a scheduled versus a manual backup. In a scheduled backup, the NMO will output to a log file in the format of the name of our script, in this case /nsr/res/db_fin01.sh, as you can see in Figure 2. However, in a manual backup, done from the RMAN> prompt, the database pieces get recorded in the same fashion as your rman script syntax (see Figure 4). Also note that /nsr/res/db_fin01.sh is actually how NetWorker records the database backup pieces in its media database:

[root@server01]#mminfo -c host02
HFG478      host02     11/09/04 722 MB    full /nsr/res/db_fin01.sh
HFG478      host02     11/10/04 822 MB    full /nsr/res/db_fin01.sh
HFG478      host02     11/01/04 922 MB    full /nsr/res/db_fin01.sh

Now that we have this data, it can be imported into Excel or your favorite spreadsheet. In Excel 2000, you would go to Data->Get External Data->Import Text file. Make sure you say that the data is delimited and specify semicolon as the delimiter (you will have the option under "other"). Next, select the columns and rows you just imported and click on the chart wizard to create your graph (see Figure 5).

Summing Up

To summarize the size of a database, we introduce the next script, summary.sh (Listing 3). It takes the output of chrt.sh and shows some quick stats (high, low, average, sum, and number of successful days). Here is a summary of three months of data:

[root@server01 /]#./chrt.sh db_fin01 August 2004 > sum_aug.txt
[root@server01 /]#./summary.sh sum_aug.txt 

Sum : 4404
Avg : 142
Days: 31
High: 171
Low: 111

[root@server01 /]#./chrt.sh db_fin01 September 2004 > sum_sep.txt
[root@server01 /]#./summary.sh sum_sep.txt
Sum : 4720
Avg : 157
Days: 30
High: 175
Low: 134

[root@server01 /]#./chrt.sh db_fin01 October 2004  > sum_oct.txt
[root@server01 /nsr/res]#./summary.sh sum_oct.txt
Sum : 5341
Avg : 178
Days: 30
High: 229
Low: 151

So far, we have focused on the growth of one database. The final script, sumday.sh (Listing 4), takes all the SCR and sums up all the data of all the databases in any given day.

By taking a checkpoint at every first of the month, it looks like we're adding about 100 GB per month. Because we have two SDLT drives capable of 60 GB/hour, we should be able to back up everything in five hours. However, if we continue at this growth rate, our backup window will shrink by an hour every month, unless we purchase more drives. Because you will be collecting data every day, you can gather as many data points as you like to make as fine a calculation as you need for your charts or planning efforts.

Conclusion

Can we predict the future now? Not exactly. However, this article provides some good capacity planning tools to justify those budget dollars.

References

Legato NetWorker Module for Oracle -- http://www.legato.com/products/networker/modules/oracle.cfm

Kornshell -- http://www.kornshell.com/

John Ouellette is a systems administrator with 9 years experience in NT and Unix. He believes the command line is king, and can be reached at: john_ouellette@yahoo.com.