Fatal NI connect errors

Question

I'm wondering if there are any auditing processes I can enable to see what is causing these errors, like sql statements being run, or any other metadata that I could use to track down the source of these errors?

I am aware of DBA_AUDIT_TRAIL (which uses SYS.AUD$), but it isn't giving me what I need. Fine Grained Audit Trail may be what I need? Not sure....

Here are my auditing settings for your review, anything I can change here to give me more to work with?

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------
audit_file_dest                      string      /01/daa01/oracle/db/admin/DAA/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB

Answer 1

In addition to Phil suggestion, I'll activate tracing on sql net.

Just add:

TRACE_LEVEL_SERVER=USER

to: $ORACLE_HOME/network/admin/sqlnet.ora (create it if don't exist)

As for the listener, you can increase the tracing level using ADMIN or SUPPORT options instead of USER. When you found the client who is causing it, you can enable client sql net traces as well.

Fatal NI connect errors are hard to determinate. But in my experience, most of them was caused by a client who left a session opened in TOAD, and then the computer goes to sleep mode.

note* If you have MOS, you should check note [1116960.1] and [1121357.1]

Answer 2

The errors you're eluding to actually happen before the database, so auditing won't help. You'll need to enable listener logging/tracing to diagnose them.

Add:

LOGGING_LISTENER=on
TRACE_LEVEL_LISTENER=USER

... to $ORACLE_HOME/network/admin/listener.ora

Be aware that it will impact connection performance.

To increase the logging level to be more verbose, you can also use the ADMIN and SUPPORT options for the TRACE_LEVEL_LISTENER= parameter.