Oracle (LOCAL=NO)

oracle

yesterday we restarted our database on production and after that we started listen post one hour. We saw later in the night that server was flooded with oracle (LOCAL=NO) and stopped accepting request for new connections.
Later we killed the processes and system was back to normal.

any idea what could have been caused these defunct processes.

I looked into sqlnet logs and I found this..

Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=100.20.1.1.)(PORT=1522))(CONNECT_DATA=(SID=xyz)(CID=(PROGRAM=emagent)(HOST=abc.com)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Solaris: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.3.0 - Production
  Time: 23-AUG-2014 10:19:01
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537
    TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 507
    TNS-00507: Connection closed

…

Fatal NI connect error 12541, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=100.20.1.1.)(PORT=1522))(CONNECT_DATA=(SID=xyz)(CID=(PROGRAM=emagent)(HOST=abc.com)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Solaris: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.3.0 - Production
  Time: 22-AUG-2014 15:37:26
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12541
    TNS-12541: TNS:no listener
    ns secondary err code: 12560
    nt main err code: 511
    TNS-00511: No listener
    nt secondary err code: 146
    nt OS err code: 0

and the entire sqlnet.log file was als flooded with such messages. When we killed the defunct processes, sqlnet.log file also got stopped updated.

Best Answer

The part that says PROGRAM=emagent suggests that your issue was related to Oracle Enterprise Manager Grid Control Agent. Did you have issues with the Oracle Agent at that time? Check your Agent with emctl status agent (after setting agent environment). The agent also has some log and trc files you could check.

  • If you connect from a different host (via SQL*Net), you will certainly see LOCAL=NO on the process.
  • If you see LOCAL=YES, then the process was created by a local connection from the same server using (i.e. Bequeath), bypassing SQL*Net. This happens when you do sqlplus / as sysdba.
  • If you see LOCAL=NO, the session may or may not have originated from a different host. For example, you could connect from the same host, but explicitly using SQL*Net. An EM agent running on the same host as the database typically connects as LOCAL=NO (at least the EM agents in my Oracle environment work that way).
Related Question