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:
#!/bin/sh 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.