Monitor Oracle Windows dump and trace files Oracle Database Tips by Donald Burleson

See these additional resources for Oracle Windows scripts:

• Dr. Hall has a great collection of downloadable Oracle scripts in his book "Oracle Job Scheduling".
   
• For UNIX and Linux Oracle scripts, see Jon Emmon's book "Oracle Shell Scripts".
   
• For scheduling Windows jobs, see Windows Oracle Job Schedulers examples
   
• For more Oracle Windows scripts, see my notes on Oracle scripts for Windows.

Below are scripts that monitor the Windows Oracle trace and dump directories for new occurrences of trace or dump files on the Windows server.  The bat scripts can then be sent the DBA an e-mail for remedial action, including the trace file contents.  The Windows script below checks the appropriate directories for new files. 

@ECHO OFF

 

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

This script will send new trace and dump files.  When new files are found, the Windows script below 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.

 

@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

 

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.