Monitoring Oracle in Windows June 2003 Oracle Tips by Terry Clark

Today, the major percentage of Oracle databases reside on UNIX platform machines.  Whether  its IBM?s AIX, HP?s UX, Sun?s Solaris, or the increasingly popular Linux, the UNIX OS coupled with a 64-bit hardware architecture yields a high performance, high capacity, and high reliability Oracle environment. 

Up until the release of the Windows 2000 Server family of products, Microsoft servers were mostly deployed as file sharing and print servers.  Oh sure, NT had some hearty applications like the Exchange e-mail server and SQL Server databases, but the really robust, mission critical databases and applications resided on UNIX boxes or the legacy IBM mainframe platform.  With the release of Windows 2000 Server, however, Microsoft began a campaign to break into the datacenter touting significant reliability improvements and a significant cost differential (both hardware and software) over the UNIX platform.  There?s no question that Microsoft still needs to improve it?s reliability and lessen its? vulnerability to virus attacks; but moreover, the Windows OS must move to a 64-bit platform in order to provide the expandability and performance required for really heavy-duty Oracle databases and applications.

Microsoft is promising enterprise class reliability with its? new Windows 2003 Server.  Coupled with Intel?s new 64-bit Itanium processors,  Microsoft will begin to close the gap in performance and scalability that UNIX currently enjoys.

Once all this comes to fruition, many Oracle DBAs, with many years of UNIX experience under their belts, could find themselves installing and maintaining mission critical databases on a Windows platform.  Any hardcore UNIX guru accustomed to the power and robustness of scripting under a UNIX shell, may be sorely disappointed when they are exposed to the Windows native command prompt environment. 

Writing Oracle scripts for Windows

In response to concerns over its? lack of scripting capabilities, Microsoft enhanced the command (cmd) prompt environment under Windows 2000.  This enhancement, however, still lagged behind the powerful scripting capabilities of the UNIX shell.  Additionally, Microsoft released Windows Services For UNIX (SFU) to more closely emulate a variety of UNIX shells and UNIX utilities to ease the migration from a UNIX to a Windows environment.  While SFU is certainly a more comprehensive solution than the native command prompt, I found it to be a much more complicated solution than my final choice of tools and it still didn?t provide total compatibility for porting UNIX scripts to Windows.

I was recently assigned the task of converting some heavily used UNIX scripts to a Windows 2000 Server platform.  A firm believer in the ?keep it simple? philosophy, I searched for a tool that would provide UNIX-like commands to the Windows scripting environment without installing a completely new shell.  My search led to an inexpensive product called the UNIXDos Toolkit from Professional Software Solutions (http://www.profsoftware.com/UNIXdos/) that provided all of the UNIX-like functions I needed.

That?s a little history behind the inception of this project.  Now let?s buckle down and see how I utilized the UNIXDos Toolkit to automate the Oracle / Windows environment.

Many a DBA has developed scripts to automate routine functions, especially those that need to be done on a recurring basis. For example, if you?ve deployed the tuning methodology described in High-Performance Tuning with STATSPACK by Don Burleson, you have implemented scripts for gathering performance statistics and producing regular reports in your UNIX environment.  How can that same methodology be ported to a Windows Server environment?

The remainder of this article will demonstrate solutions developed to address that question.  Using the UNIXDos Toolkit and an e-mail gateway product  that enhanced the native Windows command prompt environment, I was able to implement the following solutions:

• Using batch files to invoke Oracle processes

• Establishing an e-mail gateway

• Monitoring the Windows alert log

• Monitoring for trace and dump files

• Monitoring disk usage in Windows

• Using the Windows AT command to schedule jobs

Using batch files to invoke Oracle processes

In UNIX you have a number of shell environments (ksh, bash, etc.) that provide an interpretive scripting language for writing command line programs. Generally, a script developed on vendor A?s version of UNIX will work on vendor B?s version with little or no tweaking. Windows, however, is a completely different animal. Windows also provides a command line environment as well as an interpretive batch (bat) language for writing programs; but no effort was made by Microsoft to conform to standards developed for UNIX.  Consequently, you will find that many UNIX commands have different names under Windows or don?t even have a equivalent command in the Windows environment (see table-1).  This makes the conversion of UNIX scripts to a native Windows batch environment somewhat challenging. 

As a UNIX DBA, learning a completely different language dialect just to port a few scripts from UNIX to Windows seems bothersome. My project consisted of doing just that ? porting a handful of existing korn shell scripts to a Windows 2000 Server environment.  Of course porting the scripts alone would be of no value unless they could be scheduled to run automatically at predetermined, recurring intervals (akin to cron in UNIX).  Fortunately, Windows also includes a scheduling service, which we?ll discuss later.

Whereas the UNIX scripts I was converting often invoked in-line SQL code using redirection operators (<<, >>), I found it necessary to extract the SQL code from the UNIX scripts and place the SQL in a separate file. It was then easy to invoke SQL*PLUS passing it a run parameter pointing to the SQL file. The snip of code in example-1 demonstrates how this is done. It?s important to recognize that the SQL code must use the spool function to redirect output to a file, otherwise that output would be lost.  When the SQL*PLUS session completes, control is returned to the next statement in the batch program where additional actions can be performed on the spooled SQL*PLUS output file.

 

Establishing an e-mail gateway in Windows

You?ll notice a reference to e-mail within the comments of the code in example-1.  I?ve found e-mail to be a very effective way of distributing reports and alert information in a timely, automated manner.  E-mail distribution was also the methodology used by the UNIX scripts that I was converting.  Since Windows does not provide a command prompt gateway to e-mail, I again looked for a solution that would provide the same functionality as the popular UNIX Sendmail program. Indigo Star Software (http://www.indigostar.com/sendmail.htm) has developed and markets the exact solution I was seeking.  IndigoMail (formerly Sendmail for Windows) allows the sending of e-mail messages, with attachments, from Windows batch programs.

Example-2 is another snip of batch file code that demonstrates how the Sendmail program is invoked .  This program snip is passed a report file name (for instance a SQL*PLUS spooled file) which it uses in the ?attach parameter of the Sendmail program. The sendmail recipient (TO), sender (FROM), and subject information are set up as variables to facilitate the use of the batch program on different servers and in different sites.  The sendmail message text is generated dynamically using echo statements.

Of course if I didn?t want to send the report file as an attachment, but instead wanted it to appear directly within the message text, I could simply eliminate the ?attach parameter and pass the report file to sendmail using the ?messagefile= parameter.  

 

Monitoring the Windows alert log

Now for a practical example, let?s monitor the Oracle alert log for error messages and send those messages to the DBA via e-mail.   The code in example-3 uses the following logic flow:

?          read the alert log extracting all of the Oracle error messages into an alert_log_err.txt file

?          compare the alert_log_err.txt file to a file containing previously extracted errors (alert_log_old.txt)

?          send any differences (new errors) to the DBA via e-mail

 

Monitoring for trace and dump files

Another practical script, monitors the Oracle trace and dump directories for new occurrences of trace files.  These files can then be sent to the DBA via e-mail for remedial action.  Trace_alert.bat (example-4) checks the appropriate directories for new files.  When new files are found send_trace.bat (example-5) is called to determine if it?s a file that we want to process or ignore and to e-mail the first 300 lines of each file to the DBA.  The trace files are moved to a holding directory so that they won?t be processed again.

 

 

Monitoring disk usage in Windows

Disk_Space.bat (example-6) is  a script that monitors disk free space.  If a disk volume has less free space than a pre-determined threshold, an alert message is sent to the DBA via, e-mail using the sendalrt.bat script (example-7).

 

 

Using the Windows AT command to schedule jobs

None of the scripts we?ve demonstrated here would be of much value without a way to schedule them to run at pre-determined intervals.  In UNIX the cron daemon performs the scheduling via instructions it finds in crontab.  A Windows Server has a scheduler service that performs the same function.

The Windows Scheduler can be programmed to run batch scripts either from the Windows Control Panel interface or via command line execution of the AT command. I chose to use the command line method because it is a self documenting, easy to modify method of controlling the job schedule.  The Windows Scheduler needs to know a few basic facts in order to run a job:

• Time of day to run (military HH:MM) the job
   

• Day to run the job ? either the day of week (M,T,W,Th,F,S,Su) or day of month (1-31) with multiple values separated by commas
   

• What to run ? in our case the CMD processor followed by the batch command to be executed

While I recognize that there are a number of books and articles written on native Windows Scripting, I thought it might be interesting to take a different approach and see Windows learn some UNIX tricks. Neither of the products used (UNIXDos and Sendmail) were very expensive, with a server license costing around $50 for each. 

I?ve enjoyed sharing my practical experience with the conversion of UNIX scripts to a Windows environment and I hope that you have found this article to be both informative and useful when you are faced with a similar task.

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.