Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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. 

Table-1

Function

UNIX

Windows

Change directory

cd

cd

Copy a file

cp

copy

Delete a file

rm

del

Find a string

grep

find

List a directory

ls

dir

Make a directory

mkdir

md

Extract a field

cut

 

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.

Example-1

 

@echo OFF
REM +-------------------------------------
REM | Set up client specific variables
REM +-------------------------------------
 
set BC_DIR=C:\BC
?
?
?
?
 
REM +-------------------------------------
REM | Run and spool the DBA Reports
REM +-------------------------------------
 
sqlplus -s perfstat/perfstat @%BC_DIR%\SQL\weekly_rpt_dba
 
 
REM +-------------------------------------
REM | E-Mail the Object Statistics Reports
REM +-------------------------------------
 
?
?
?
?
exit

 

 

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.  

Example-2

 

@ECHO OFF
 
REM +-------------------------------------
REM | Set up client specific variables
REM +-------------------------------------
 
set BC_DIR=C:\BC
set FROM=Oracle@Client.com
set TO=Client@remote-dba.net
set SUBJECT=*** Client TRACE ALERT
 
set FILENAME=%1
 
?
?
?
?
REM +-------------------------------------------------------------
REM | Build message text
REM +-------------------------------------------------------------
 
echo. > message.txt
echo 'Please see attached dump identified on' >> message.txt
udate >> message.txt
 
sendmail -messagefile=message.txt -from=%FROM% -subject="%SUBJECT%" -attach=%FILENAME%  %TO%
 
:END
rm -s message.txt
rm -s %FILENAME%
 
exit

 

 

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

Example-3

 

@ECHO OFF
REM +---------------------------------
REM |  ALERT_LOG.BAT
REM |
REM |  Author:  T. Clark
REM |  Written: 03/15/03
REM |              
REM +---------------------------------
REM +-------------------------------------
REM | Set up client specific variables
REM +-------------------------------------
 
set BC_DIR=C:\BC
set FROM=Oracle@Client.com
set TO=Client@remote-dba.net
set SUBJECT=Client Alert Log
 
set ORACLE_SID=MYDB
set ORACLE_BASE=C:\ORACLE
set ORACLE_HOME=C:\ORACLE\ORA92
 
REM +-------------------------------------------------------------
REM | Now let's go get all the errors from the Alert Log
REM +-------------------------------------------------------------
 
egrep -h -e ORA- -e Errors %ORACLE_BASE%\admin\%ORACLE_SID%\bdump\ALERT_%ORACLE_SID%.LOG > alert_log_err.txt
 
REM +-------------------------------------------------------------
REM | If we don't have an old file to compare with, then
REM | create the old file
REM +-------------------------------------------------------------
 
if exist %BC_DIR%\Report\alert_log_old.txt goto COMPARE
touch %BC_DIR%\Report\alert_log_old.txt
 
:COMPARE
REM +-------------------------------------------------------------
REM | Compare the errors that we got this time with errors we
REM | got last time, keeping only new errors.
REM +-------------------------------------------------------------
 
comm -23 alert_log_err.txt %BC_DIR%\Report\alert_log_old.txt > To_e-Mail.txt
 
:MOVE
REM +-------------------------------------------------------------
REM | Save all the current errors for next time.
REM +-------------------------------------------------------------
 
mv alert_log_err.txt %BC_DIR%\Report\alert_log_old.txt
 
REM +-------------------------------------------------------------
REM | If new errors then e-mail them else exit
REM +-------------------------------------------------------------
 
getlines 1 1 To_e-mail.txt > del.it
if errorlevel 1 goto END
 
REM +-------------------------------------------------------------
REM | Mail the Alert Log to the DBA staff
REM +-------------------------------------------------------------
 
banner Alert Log > message.txt
echo. >> message.txt
udate >> message.txt
echo. >> message.txt
cat message.txt To_e-mail.txt >> xxx.txt
 
sendmail -messagefile=xxx.txt -from=%FROM% -subject="%SUBJECT%" %TO%
 
:END
rm -s del.it
rm -s message.txt
rm -s To_e-mail.txt
rm -s xxx.txt
 
exit

 

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.

Example-4

 

@ECHO OFF
 
REM +---------------------------------
REM |  TRACE_ALERT.BAT
REM |
REM |  Author:  T. Clark
REM |  Written: 03/16/03
REM |              
REM +---------------------------------
REM +-------------------------------------
REM | Set up client specific variables
REM +-------------------------------------
 
set BC_DIR=C:\BC
set ORACLE_SID=MYDB
set ORACLE_BASE=C:\ORACLE
set ORACLE_HOME=C:\ORACLE\ORA92
 
ucd %BC_DIR%\script
 
REM +-------------------------------------------------------------
REM | Now let's go find trace files
REM +-------------------------------------------------------------
 
ufind %ORACLE_BASE%\ADMIN\%ORACLE_SID%\bdump -name '*.trc' -print > new_trc.tmp
ufind %ORACLE_BASE%\ADMIN\%ORACLE_SID%\udump -name '*.trc' -print >> new_trc.tmp
ufind %ORACLE_HOME%\RDBMS\trace -name '*.trc' -print >> new_trc.tmp
 
REM +-------------------------------------------------------------
REM | Check to see if we found new trace files, if not exit
REM +-------------------------------------------------------------
 
getlines 1 1 new_trc.tmp > del.me
if errorlevel 1 goto END
 
REM +-------------------------------------------------------------
REM | Send the dump files to the DBA staff
REM +-------------------------------------------------------------
 
for /F "tokens=1" %%i in (new_trc.tmp) do send_trace %%i
 
:END
ucd %BC_DIR%\script
 
rm -s new_trc.tmp
rm -s del.me
exit

 

Example-5

 

@ECHO OFF
REM +---------------------------------
REM |  SEND_TRACE.BAT
REM |
REM |  Author:  T. Clark
REM |  Written: 03/18/03
REM |              
REM +---------------------------------
REM +-------------------------------------
REM | Set up client specific variables
REM +-------------------------------------
 
set BC_DIR=C:\BC
set FROM=Oracle@Client.com
set TO=Client@remote-dba.net
set SUBJECT=*** Client TRACE ALERT
 
set FILENAME=%1
 
REM +-------------------------------------------------------------
REM | Strip off first 300 lines to email to the DBA support staff
REM +-------------------------------------------------------------
 
head -300 %FILENAME% > trace_alert.txt
 
REM +-------------------------------------------------------------
REM | Move the new trace file to TEMP so it won't be sent again
REM +-------------------------------------------------------------
 
mv -f %FILENAME%  %BC_DIR%\Temp
 
REM +-------------------------------------------------------------
REM |See if this is an unwanted trace file and ignore if it is
REM +-------------------------------------------------------------
 
grep -e debug_loop  trace_alert.txt
 
If not errorlevel 0 GOTO END
 
REM +-------------------------------------------------------------
REM | Build message text
REM +-------------------------------------------------------------
 
echo. > message.txt
echo 'Please see attached dump identified on' >> message.txt
udate >> message.txt
 
sendmail -messagefile=message.txt -from=%FROM% -subject="%SUBJECT%" -attach=trace_alert.txt %TO%
 
:END
rm -s message.txt
rm -s trace_alert.txt
 
exit

 

 

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

Example-6

 

@ECHO OFF
 
REM +-------------------------------------
REM | Set up client specific variables
REM +-------------------------------------
 
set BC_DIR=C:\BC
 
REM +-------------------------------------------------------------
REM | Define minimum space value in floating point format
REM | Example: 1E5=100,000 (100K)    1.75E5=175,000 (175K)
REM |          2E6=2,000,000 (2MB)
REM +-------------------------------------------------------------
set MINSPACE=1.75E5
 
ucd %BC_DIR%\script
 
REM +-------------------------------------------------------------
REM | Now let's go get disk space information for all disks and
REM | remove commas from the byte values
REM +-------------------------------------------------------------
 
df -a -t | sed -e "s/,//g" > df.txt
 
REM +-------------------------------------------------------------
REM | Select lines for disks that fall within the critical free
REM | space range and send them to the ALERT.TXT file
REM +-------------------------------------------------------------
 
getrng "-d)" -f2 -r 0,%MINSPACE% df.txt > alert.txt
 
REM +-------------------------------------------------------------
REM | If we found disks critically low on space, send an e-mail
REM | alert to the DBA staff
REM +-------------------------------------------------------------
 
test -s1 alert.txt   run: sendalrt.bat alert.txt
 
rm -s df.txt
rm -s alert.txt
 
exit

 

 

 

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

Example-8

 

@echo off
 
REM +---------------------------------
REM |  SCHEDULE.BAT
REM |
REM |  Author:  T. Clark
REM |  Written: 03/24/03
REM |             
REM +---------------------------------
REM +-----------------------------------------------
REM | Set up client specific variables
REM +-----------------------------------------------
 
set BC_DIR=C:\BC
 
REM +-----------------------------------------------
REM | Schedule BAT jobs to run at appropriate
REM | times.
REM |                 ** Caution **
REM |  The following /delete switch clears all
REM |  jobs from the Windows Scheduler
REM +-----------------------------------------------
 
AT /delete /yes
 
REM +-----------------------------------------------
REM | Schedule Monday Table & Index Reports
REM +-----------------------------------------------
 
AT  6:30 /every:M  cmd /c %BC_DIR%\script\get_obj_stats.bat
AT  7:30 /every:M  cmd /c %BC_DIR%\script\weekly_rpt_dba.bat
AT  7:40 /every:M  cmd /c %BC_DIR%\script\weekly_rpt_mgt.bat
 
REM +-----------------------------------------------
REM |  A daily STATSPACK alert
REM +-----------------------------------------------
 
AT  7:00 /every:M,T,W,Th,F,S,Su cmd /c %BC_DIR%\script\statspack_alert_9i.bat
 
REM +-----------------------------------------------
REM | Disk Free Space Alert every 6 hours
REM +-----------------------------------------------
 
AT  6:30 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\disk_space.bat
AT 12:30 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\disk_space.bat
AT 18:30 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\disk_space.bat
AT 00:30 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\disk_space.bat
 
REM +-----------------------------------------------
REM | Schedule a Monday STATSPACK snapshot removal
REM +-----------------------------------------------
 
AT  7:45 /every:M  cmd /c %BC_DIR%\script\sprem.bat
 
REM +-----------------------------------------------
REM | Schedule a periodic Trace File alert
REM +-----------------------------------------------
 
AT  0:00 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\trace_alert.bat
AT  6:00 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\trace_alert.bat
AT 12:00 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\trace_alert.bat
AT 18:00 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\trace_alert.bat
 
REM +-----------------------------------------------
REM | Schedule a daily Alert Log check
REM +-----------------------------------------------
 
AT  7:15 /every:M,T,W,Th,F,S,Su cmd /c %BC_DIR%\script\alert_log.bat

 

 

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.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational