LATEST TOPICS

Oracle Database: Finding Trace Event Codes to help in debugging

We often need to set different level of tracing for debugging database issues. However, it is not realistic for a DBA to remember the list of all the tracing event codes that can be set for debugging purpose.

Oracle stores the list and purpose of these tracing events under a file called oraus.msg. This file can be located as $ORACLE_HOME/rdbms/mesg/oraus.msg and it has the list of all database event/error codes mentioned in a readable format.

In fact, when we look up for a ORA- error with the help of oerr command, Oracle scans this message file to present us with the relevant information related to the database event/error.

Generally all the events starting from the range 10000 to 10999 are used for debugging purpose (as per the comments in orus.msg file). We can simply grep the keyword for which we want to set the debugging and can get the list of event codes that we can set for that.

For example:

With respect to database rollback segments; we can set the following list of tracing events for debugging.

[oracle@labserver ~]$ grep ^10[0-9][0-9][0-9]  $ORACLE_HOME/rdbms/mesg/oraus.msg | grep -i rollback
10477, 00000, "simulated rollback error"
10510, 00000, "turn off SMON check to offline pending offline rollback segment"
10512, 00000, "turn off SMON check to shrink rollback segments"
10617, 00000, "Cannot create rollback segment in dictionary managed tablespace"
10641, 00000, "Cannot find a rollback segment to bind to"
10642, 00000, "Found rollback segments in dictionary managed tablespaces"
10750, 00000, "test rollback segment blksize guessing for index array insert"

With respect to SQL queries, we can set the following list of tracing events for debugging.

[oracle@labserver ~]$ grep ^10[0-9][0-9][0-9]  $ORACLE_HOME/rdbms/mesg/oraus.msg | grep -i sql
10046, 00000, "enable SQL statement timing"
10079, 00000, "trace data sent/received via SQL*Net"
10133, 00000,    "testing for SQL Memory Management"
10134, 00000,    "tracing for SQL Memory Management for session"
10241, 00000, "remote SQL execution tracing/validation"
10253, 00000, "limit SQL text returned from X$KGLNA[1]"
10255, 00000, "pl/sql parse checking"
10336, 00000, "Do remote object transfer using remote SQL"
10337, 00000, "enable padding owner name in slave sql"
10369, 00000, "test SQL monitoring feature"
10386, 00000, "parallel SQL hash and range statistics"
10731, 00000, "dump SQL for CURSOR expressions"
10840, 00000, "trace / debug pl/sql caching module (kkxmInitCache)"
10841, 00000, "Default un-inintialized charact set form to SQLCS_IMPLICIT"

For a list of all the documented tracing events that can be used for debugging, we can use any of the following script (on UNIX platform) to fetch list of event codes.

First script uses a FOR Loop to fetch the event codes starting from 10000 to 10999 with the help oerr command.

#!/bin/sh
#
# Description: Script to fetch event codes
# Author : Abu Fazal Mohammed Abbas
# Name: get_oratrc_code_1.sh
#

# Check the existence of ORATAB
if [ -f /etc/oratab ]; then
        export OTAB=/etc/oratab
elif [ -f /var/opt/oracle/oratab ]; then
        export OTAB=/var/opt/oracle/oratab
else
        echo "Could not locate ORATAB. Terminating trace event lookup"
        exit 1
fi

# Set environment
export ORACLE_HOME=`cat $OTAB | grep -v 'ASM' | grep -v '^$' | grep -v '^#' | head -1 | cut -d ":" -f2`
export PATH=$PATH:$ORACLE_HOME

# Fetch the event codes with oerr command
for tevent in {10000..10999}
do
        $ORACLE_HOME/bin/oerr ora $tevent
done

The second script uses a regular expression along with the grep command to directly fetch the event codes starting with 10000 till 10999 from the $ORACLE_HOME/rdbms/mesg/oraus.msg file.

#!/bin/sh
#
# Description: Script to fetch event codes
# Author : Abu Fazal Mohammed Abbas
# Name: get_oratrc_code_2.sh
#

# Check the existence of ORATAB
if [ -f /etc/oratab ]; then
        export OTAB=/etc/oratab
elif [ -f /var/opt/oracle/oratab ]; then
        export OTAB=/var/opt/oracle/oratab
else
        echo "Could not locate ORATAB. Terminating trace event lookup"
        exit 1
fi

# Set environment
export ORACLE_HOME=`cat $OTAB | grep -v 'ASM' | grep -v '^$' | grep -v '^#' | head -1 | cut -d ":" -f2`

# Fetch the event codes from oraus.msg file
grep ^10[0-9][0-9][0-9] $ORACLE_HOME/rdbms/mesg/oraus.msg

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed