Capturing Snoop Data On An Oracle Event

Following from my previous article about capturing TNF data on an Oracle event, I thought I’d also mention that you can do something similar with capturing snoop data.  This really came about because of trying to troubleshooting ORA-29740
issues with Oracle RAC. Most of the time you really want to have a look at these Sunsolve documents for giving hints:

  • Solution 230303 : SunCluster[TM] 3.x: Troubleshooting ORA-29740 in a RAC environment
  • Solution 205145 : Oracle error ORA-29740 (reason 3) resolved with network tuning

But, if you think that network is perhaps something worth chasing and the
actual physical switches & hubs etc don’t hint at a fault then we
could enable the lantracer script to snoop on an interconnect and terminate when Oracle receives an ORA-29740. To do this you’ll need to do the following:

1) in $ORACLE_HOME/bin, place a script called oradbg containing:

echo "stopping Lantracer" >> /tmp/oradbg.$$
/usr/bin/rm /usr/tmp/lan/running
echo `/bin/date` > /tmp/oradbg.$$
echo "done." >> /tmp/oradbg.$$

2) in an sqlplus "/ as sysdba" session run the following:

alter system set "_oradbg_pathname"="<$ORACLE_HOME>/bin/oradbg" scope=spfile;
alter system set event="29740 debug" scope=spfile;

where $ORACLE_HOME is the expanded path of the $ORACLE_HOME env var

3) add execute to the oradbg script

4) restart the instance

5/ create /var/tmp/lan and copy lantracer script to it. chmod 755 lantracer

6/ start lantracer

cd /var/tmp/lan; ./lantracer >/dev/null 2>&1 &

7/ check oracle can stop lantracer by using it to detect a 942 event:

 sqlplus <user>/<password>
alter session set events '942 debug';
select * from <a table that doesn't exist>;

This should remove /var/tmp/lan/running and leave us with some snoop
scripts which should hopefully have captured data during the event.