Little Known Uses for Oracle Database’s Error Tool

June 2, 2010

Oracle Database’s Oracle Error messaging system is often forgotten but can become a staple for many Database Administrators.

One need only look at the Oracle documentation to realize that the extent and number of errors that could be generated are enormous within the oracle product set. Most DBAs and database developers will tend to thumb the error message book more often than we would all like to admit. What many DBAs know, but not all, and what many developers don’t know about, and is often forgotten about, is that there is a nice little utility called the Oracle Error Tool (oerr) that will look up most Oracle errors and give the same information that is available in the manuals. What I hope to do in this article is revisit the oerr utility and possibly present a few things you might not have thought of before but would like to know. Also, I’ll give a nice little utility I’ve created that will actually use the current alert log and merge the Oracle messages with the errors found.

If you’ve never used the oerr utility it is actually quite easy. Just access a Unix-based system that has Oracle installed and issue, from the Unix command line, the following syntax:

oerr code error_number

Where ‘code’ is, as will be shown later, a facility (ORA, TNS, RMAN, etc.) and error_number is the error being looked up within the facility code. For example, if we were to get an ORA-01237 in the alert log we would issue the following oerr command to display the appropriate cause of error and action required.

[root@ludwig mesg]# oerr ora 01237
01237, 00000, "cannot extend datafile %s"
// *Cause: An operating system error occurred during the resize.
// *Action: Fix the cause of the operating system error and retry the command.

As a word of caution or note, you may want to know the following caveats about using the oerr utility:

While the oerr utility is quite simplistic there is very little in the way of documentation. This is where this article comes in; hoping you and I both might be able to exploit some of the internals within the utility.
There is no oerr utility for the Windows platform.
To combat the Windows platform issue and somehow make searching Oracle errors easier, many web-type oerr interfaces have sprung up that you can use if you like.
Sometimes when using the oerr utility, entering the exact Oracle error given does not always return a message. It is up to the user to either add or remove zeros from the error number to help facilitate lookup.

The first thing about the oerr utility, if researching the tool, I’d ask myself is where does this utility reside on disk and can I actually learn anything from it. Is it a binary file or some script I steal code from? If you perform a which command on Unix you quickly find out that this utility is the $ORACLE_HOME/bin directory, just as we’d probably assume.

[root@ludwig bin]# which oerr
/opt/app/oracle/product/11.2.0/dbhome_1/bin/oerr

The cool thing about this utility is that it is a shell script; meaning we can gain some valuable insight into how Oracle goes about looking up the error. For instance we can quickly glean from this file that there is, what Oracle calls, a facility file ($ORACLE_HOME/lib/facility.lis) that does nothing more than take a code (ORA, TNS, RMAN, etc.) that has been passed and points the script to the proper message file. ($ORACLE_HOME/$Component/mesg/${Facility}us.msg). For an example, if we were to invoke the oerr utility such as:

[root@ludwig mesg]# oerr ora 01237

The oerr utility would grep ‘ora’ from the facility file, getting ‘rdbms’ as the component, and point to the $ORACLE_HOME/rdbms/mesg/oraus.msg message file. You should notice the ‘rdbms’ in the path and the message file oraus.msg prefixed with the ‘ora’ facility. The facilities file itself has just over 220 different facility/component combinations; equating to just as many message files. For instance if we wanted to see all the message files for component ‘rdbms’ or ‘network’ now all we have to do is issue the following commands:

[root@ludwig lib]# ls -l $ORACLE_HOME/rdbms/mesg/*us.msg 
[root@ludwig lib]# ls -l $ORACLE_HOME/network/mesg/*us.msg

Once the appropriate message file has been located, the oerr utility can get down to business extracting the appropriate message for the error code provided by the user through a simple awk command:

# Search the message file for the error code, printing the message text
# and any following comments which should give the cause and action for
# the error.
awk "BEGIN { found = 0; }
/^[0]*$Code/ { found = 1; print ; next;}
/^/// { if (found) { print; } next; }
{ if (found) { exit; } }" $Msg_File

Probably the most famous message file would have to be the $ORACLE_HOME/rdbms/mesg/oraus.msg file, which contains many if not all of the Oracle server errors. I’d suggest looking inside this file as well as it holds a little bit more information than you might think. Probably the most important bit of information is the categorization of errors that is maintained. Granted I haven’t looked and verified them all but I’d assume Oracle to keep some form of order here. When inside the file, if you’re using the vi editor, or wanting to grep the file, a simple search of similar to ‘^/ ‘ will point you to sections such as the following that categorize Oracle errors. This in itself is of great use especially if you’re trying be proactive on potential errors; possibly writing procedures to check conditions that might trigger such errors. Anyways here is a snippet of what you might find:

/ 10 - 49 user session and session switching errors
/ 101 .. 149 shared server errors
/ 150 .. 159 Oracle*XA errors
/ 160 .. 199 Distributed transaction errors (continued)
/ 1070 - 1099: V6 program interface errors
/ 1100 to 1279: db file errors (continue at 63000)
/ 1280-1399 LogMiner errors
/ 1384 - 1398 Reserved for 4K block size log file errors
/ 1380-1399 KST tracing errors renumbered at 2480-2499 for MERGE
/ 1490 - 1499 Analyze Parse and Execution Errors
/ 1500 - 1699 V6 Execution errors
/ 10630-10639 Reserved for Online Segment Shrink
/ 10034, 00000, "access path analysis (APA*)"
/ 10072 is available.
/ 10302 - 10318 Reserved for internal triggers
/ 10325 - 10335 reserved for recovery testing
/ 10552 -10589 reserved for recoverable media/standby recovery
/ 10710 - 10729 are Reserved for BITMAP row sources

Oracle’s Oracle Error Utility (oerr) is at the core very simple. It takes in a facility (code) and an error number and prints out cause and actions that can be used by DBAs and developers alike. Within both the oerr shell script and its supporting files there is additional information that we should be able to tap into and help tune our databases both before and after errors occur. It is my suggestion to not use the oerr utility just to look up errors but to find errors that could happen and do something to circumvent them, like putting some form detection mechanism in place to help find problems before they rear their ugly heads. Now as promised, below is a shell script I created to extract those Oracle error messages and merge them into the current alert log for viewing. The script itself has a menu system that offers three choices:

1. Evaluate errors – this will search and extract Oracle errors and then look up the error messages; merging both error and message and placing you in a viewable file (vi style).

2. Full alert log – this does nothing more than place you in a viewable file (vi style) to look at the complete alert log.

3. Just errors – will just extract the Oracle errors and then place you in a viewable file (vi style) so you can look at them.

So here is the code: Enjoy:

#!/bin/sh
#----------------------------------------------------------------------------
# File: DIAGALERT.sh
#----------------------------------------------------------------------------
#----------------------------------------------------------------------------
# Environment Setup
#----------------------------------------------------------------------------
export AWK=/bin/awk
export BASENAME=/bin/basename
export CAT=/bin/cat
export ECHO=/bin/echo
export GREP=/bin/grep
export HEAD=/usr/bin/head
export LS1="/bin/ls -1"
export MV=/bin/mv
export TAIL=/usr/bin/tail
export VIEW=/bin/view
export WC=/usr/bin/wc
export PGM=`${BASENAME} $0 .sh`
export TMPDIR=/tmp
#------------------------------------------------------
# Menu
#------------------------------------------------------
badchoice () { MSG="Bad Choice ... Please Try Again" ; }
themenu () {
clear
${ECHO}
${ECHO} "Investigate Alert Log"
${ECHO}
${ECHO} "Select:"
${ECHO} " e) Evaluate errors"
${ECHO} " f) Full alert log"
${ECHO} " j) Just errors"
${ECHO}
${ECHO} " x) Exit"
${ECHO}
${ECHO} $MSG
${ECHO}
${ECHO} Select letter and then press ENTER ;
}
#----------------------------------------------------------------------------
# Get diag information from database
#----------------------------------------------------------------------------
${ECHO} "SELECT replace(name,' ','')||':'||value FROM v$diag_info;" > ${TMPDIR}/${PGM}.sql
${ECHO} "SELECT 'homepath:'||replace(homepath.value,adrbase.value||'/','')" >> ${TMPDIR}/${PGM}.sql
${ECHO} " FROM v$diag_info homepath, v$diag_info adrbase" >> ${TMPDIR}/${PGM}.sql
${ECHO} " WHERE homepath.name = 'ADR Home'" >> ${TMPDIR}/${PGM}.sql
${ECHO} " AND adrbase.name = 'ADR Base';" >> ${TMPDIR}/${PGM}.sql
${ECHO} "exit" >> ${TMPDIR}/${PGM}.sql
su - oracle -c "sqlplus -s / as sysdba @${TMPDIR}/${PGM}.sql" > ${TMPDIR}/${PGM}.lst
diagalert=`${GREP} DiagAlert ${TMPDIR}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
adrbase=`${GREP} ADRBase ${TMPDIR}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
homepath=`${GREP} homepath ${TMPDIR}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
while true
do
themenu
read answer
${ECHO}
${ECHO} "**************************************" > ${TMPDIR}/${PGM}
${ECHO} "** You have been placed in a viewer **" >> ${TMPDIR}/${PGM}
${ECHO} "** **" >> ${TMPDIR}/${PGM}
${ECHO} "** To move around use: **" >> ${TMPDIR}/${PGM}
${ECHO} "** arrow keys **" >> ${TMPDIR}/${PGM}
${ECHO} "** page-up **" >> ${TMPDIR}/${PGM}
${ECHO} "** page-down **" >> ${TMPDIR}/${PGM}
${ECHO} "** **" >> ${TMPDIR}/${PGM}
${ECHO} "** To quit use: **" >> ${TMPDIR}/${PGM}
${ECHO} "** :q (Shift-colon-q) **" >> ${TMPDIR}/${PGM}
${ECHO} "** ZZ (Shift-z-z) **" >> ${TMPDIR}/${PGM}
${ECHO} "** **" >> ${TMPDIR}/${PGM}
${ECHO} "**************************************" >> ${TMPDIR}/${PGM}
#-- extract alert log errors for current day and previous day
#-- previous day alerts will be used if state file has previous day as last day
${ECHO} "set echo off" > ${TMPDIR}/${PGM}.adrci
${ECHO} "set termout off" >> ${TMPDIR}/${PGM}.adrci
${ECHO} "set base ${adrbase}" >> ${TMPDIR}/${PGM}.adrci
${ECHO} "set homepath ${homepath}" >> ${TMPDIR}/${PGM}.adrci
${ECHO} "spool ${TMPDIR}/${PGM}.xml" >> ${TMPDIR}/${PGM}.adrci
case $answer in
e|E) ${ECHO} "show alert -P "MESSAGE_TEXT LIKE '%ORA-%'" -term" >> ${TMPDIR}/${PGM}.adrci
${ECHO} "spool off" >> ${TMPDIR}/${PGM}.adrci
adrci script=${TMPDIR}/${PGM}.adrci 1>/dev/null 2>/dev/null
msglines=`${WC} -l ${ORACLE_HOME}/rdbms/mesg/oraus.msg | ${AWK} -F" " '{print $1}'`
${GREP} -n -v ^/ ${ORACLE_HOME}/rdbms/mesg/oraus.msg | ${GREP} -n : > ${TMPDIR}/${PGM}.msg
${CAT} ${TMPDIR}/${PGM}.xml |
while read LINE
do
ora=`${ECHO} ${LINE} | ${GREP} '^ORA-' | ${AWK} -F"-" '{print $1}'`
nbr=`${ECHO} ${LINE} | ${GREP} '^ORA-' | ${AWK} -F"-" '{print $2}' | ${AWK} -F" " '{print $1}' | ${AWK} -F":" '{print $1}'`
nbrL=`${ECHO} ${nbr} | ${WC} -L`
case ${nbrL} in
1) nbr=0000${nbr};;
2) nbr=000${nbr};;
3) nbr=00${nbr};;
4) nbr=0${nbr};;
esac
if [ "${ora}" = "ORA" ]
then
${ECHO} ${LINE} >> ${TMPDIR}/${PGM}
beg=`${GREP} :${nbr}, ${TMPDIR}/${PGM}.msg | ${AWK} -F":" '{print $1}'`
begline=`${GREP} :${nbr}, ${TMPDIR}/${PGM}.msg | ${GREP} ^${beg}: | ${AWK} -F":" '{print $2}'`
fromline=`expr ${msglines} - ${begline}`
fromline=`expr ${fromline} + 1`
end=`expr ${beg} + 1`
endline=`${GREP} ^${end}: ${TMPDIR}/${PGM}.msg | ${AWK} -F":" '{print $2}'`
ttllines=`expr ${endline} - ${begline}`
${TAIL} -${fromline} ${ORACLE_HOME}/rdbms/mesg/oraus.msg | ${HEAD} -${ttllines} |
while read ORALINE
do
${ECHO} " "${ORALINE} >> ${TMPDIR}/${PGM}
done
${ECHO} " " >> ${TMPDIR}/${PGM}
${ECHO} " " >> ${TMPDIR}/${PGM}
else
${ECHO} ${LINE} >> ${TMPDIR}/${PGM}
fi
done
${VIEW} ${TMPDIR}/${PGM}
;;
f|F) ${ECHO} "show alert -term" >> ${TMPDIR}/${PGM}.adrci
${ECHO} "spool off" >> ${TMPDIR}/${PGM}.adrci
adrci script=${TMPDIR}/${PGM}.adrci 1>/dev/null 2>/dev/null
${CAT} ${TMPDIR}/${PGM}.xml >> ${TMPDIR}/${PGM}
${VIEW} ${TMPDIR}/${PGM}
${ECHO}
${ECHO} Press ENTER to Continue...;
;;
j|J) ${ECHO} "show alert -P "MESSAGE_TEXT LIKE '%ORA-%'" -term" >> ${TMPDIR}/${PGM}.adrci
${ECHO} "spool off" >> ${TMPDIR}/${PGM}.adrci
adrci script=${TMPDIR}/${PGM}.adrci 1>/dev/null 2>/dev/null
${CAT} ${TMPDIR}/${PGM}.xml >> ${TMPDIR}/${PGM}
${VIEW} ${TMPDIR}/${PGM}
${ECHO}
${ECHO} Press ENTER to Continue...;
read
;;
x|X) break;;
*) badchoice;;
esac
done

Additional Resources

Oracle FAQ’s Oerr
Oracle HOW TO translate error messages

Little Known Uses for Oracle Database’s Error Tool

Additional Resources

Get the Free Newsletter!

Latest Articles

SQL SORT BY Statement

Working with NULL Values in SQL

Working with SQL AND, OR, and NOT Operators

How to Use Databases With Python

Advertisers

Menu

Our Brands

Little Known Uses for Oracle Database’s Error Tool

Additional Resources

Related Articles

Best Certifications for Database Administrators

Best Courses for Database Administrators

TYPE Definition Change in Oracle 21c

Get the Free Newsletter!

Latest Articles

SQL SORT BY Statement

Working with NULL Values in SQL

Working with SQL AND, OR, and NOT Operators

How to Use Databases With Python

Advertisers

Menu

Our Brands