Sometimes you, as an DBA, is blamed for everything. The database is slow, unavailable, unpatched, and the list goes on and on.

Sometimes, in rare situations, you can prove then wrong :D

Last week we have been called to analyze a intermitent application issue. The app team blame the database, showing the “java.sql.SQLException: Closed Connection” error on app logs. Everything at database level was checked out, and rechecked again, until they call us. Long debugging hours and still no results, we tried a different approach. What about sniffing the eth card at app server..checking the communication flow between APP and Database.

And so we did it:

tcpdump -i eth0 tcp port 1521 -A -s1500 | awk ‘$1 ~ “ORA-” {i=1;split($1,t,“ORA-”);while (i <= NF) {if (i == 1) {printf("%s",“ORA-“t[2])}else {printf("%s “,$i)};i++}printf(”\n”)}’

This give us the nice output:

bla@app_blaserver:~ # tcpdump -i eth0 tcp port 1521 -A -s1500 | awk ‘$1 ~ “ORA-” {i=1;split($1,t,“ORA-”);while (i <= NF) {if (i == 1) {printf("%s”,“ORA-“t[2])}else {printf("%s “,$i)};i++}printf(”\n”)}’ tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on eth0, link-type EN10MB (Ethernet), capture size 1500 bytes ORA-01403:no data found ORA-00913:too many values ORA-01403:no data found ORA-01403:no data found ORA-01403:no data found ORA-01403:no data found ORA-01403:no data found ORA-01403:no data found ORA-01403:no data found ORA-01403:no data found ORA-01438:value larger than specified precision allowed for this column ORA-06512:at line 2 ORA-00937:not a single-group group function ORA-01403:no data found ORA-01403:no data found ORA-00937:not a single-group group function ORA-01403:no data found ORA-00937:not a single-group group function ORA-01403:no data found ORA-00937:not a single-group group function ORA-01403:no data found

The ORA-01403 is expected after the fetch of each cursor being processed - no bad news here.

Hummmm…and when the ORA-01438/ORA-06512/ORA-00937 are raised what happens to the connection? You got it right?

After checking what was causing the errors, the intermittent issue stops, everyone was happy - incluing the DBA team :D

(You need to adapt the script to fit the listener port and eth card in your box, okay?)

As always feedbacks are very welcome.

See you around,

Hang.