Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 2, 2010

Little Known Uses for Oracle Database's Error Tool

By James Koopmann

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

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:

# 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 () {
${ECHO} "Investigate Alert Log"
${ECHO} "Select:"
${ECHO} " e) Evaluate errors"
${ECHO} " f) Full alert log"
${ECHO} " j) Just errors"
${ECHO} " x) Exit"
${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
read answer
${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
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};;
if [ "${ora}" = "ORA" ]
${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
${ECHO} " "${ORALINE} >> ${TMPDIR}/${PGM}
${ECHO} " " >> ${TMPDIR}/${PGM}
${ECHO} " " >> ${TMPDIR}/${PGM}
${ECHO} ${LINE} >> ${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}
${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}
${ECHO} Press ENTER to Continue...;
x|X) break;;
*) badchoice;;

Additional Resources

Oracle FAQ's Oerr
Oracle HOW TO translate error messages

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM