Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
FYI, it looks like DBA_AUDIT_TRAIL is basically only giving me LOGON/LOGOFF events. –  unclenevin Aug 6 '13 at 20:36
add comment

2 Answers

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]

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.