Wednesday 20 February 2013

Introduction to tracing

This can be applied to connection/auithentication/encryption issues
(in that case ignore inside the pipe symbols ||)
- be sure to check out sqlplus and oracle client 
(it is a different 'C' language based code path) rather than the default thin/Java
 code path shipped by default with sqldeveloper and sqlcl, before going any further,

If there is an syntax error in a vendor program when using sql pl/sql or indeed any ora- error you can trace it.

Introduction to tracing - internet search for more:

Warning - this can produce large files
1/alter session set sql_trace=true;
2/or via SQLDeveloper Reports->Data Dictionary Reports ->Database Administration->Sessions->Sessions
pick a database then pick a session then right click 'Trace Session'
show parameter USER_DUMP_DEST
--for the output directory for 1/ or 2/
3/or Ethernet sniffer (wireshark) (This is what I usually use if I need to trace:)
works for me to capture then save in default format and then look for your "ora-" error in a text editor.
(note "ORAxxxx no data found" is used to end a result set so might not be the error you are looking for)
4/ alter your Oracle sqlnet.ora and put in
For thick client including JDBC thick:
trace_level_client=16
For server side (i.e. background Oracle processes) whether you are using JDBC thick or JDBC thin: (for sqlnet.ora) (might need to stop and start the database)
trace_level_server=16
There are ways to extract the data out its presented in
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  |...............................|
Most likely you want what is inside the ||. As a bonus you could track whether it is sent or received
For Thin client: There is a different type of logging for thin(I have not used it myself): http://docs.oracle.com/cd/B28359_01/java.111/b31224/diagnose.htm#CIAIDFIC
5/ On Linux
strace -f -o /tmp/trout -s 1000 the_command_with_args
or
strace -f -o /tmp/trout -s 1000-p the_process_id

You might be looking for the last error - or the first serious error (which may be simple and cascade into more serious errors that reach the screen).

Remember to switch your tracing off so you do not fill the hard disk!

Regards,
Turloch

1 comment:

Turloch O'Tierney said...

I have not used the following but using them is on my medium sized todo list: On linux there is also ltrace (which I only heard about recently) and Oracle DTrace.