Friday, July 27, 2012

The case of… Oracle 11g Listener not starting (Windows)

Just had this problem today where an Oracle 11g Listener wasn’t starting up and when eventually did start listening on port 1521, it wasn’t taking client connections! The odd part is that as far as I know nothing was changed, either software or network configuration, so why all this?! lsnctrl status was showing this error:
C:\oracle\product\11.1.0\db_1\BIN>LSNRCTL.EXE status
 
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 27-JUL-2012 02:51:26
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server.domain.com)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 61: Unknown error
Focusing with Process Monitor (procmon) on TNSLSNR.exe showed that it was trying to write to the listener.log file. Right-click the file and choose Jump To… brought the file in Explorer, wow, the log file is 4 GB?! Stopping the listener service, removing the log file and restarting the listener service, services are back to normal now!
C:\oracle\product\11.1.0\db_1\BIN>LSNRCTL.EXE status
 
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 27-JUL-2012 02:52:49
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server.domain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production
Start Date                27-JUL-2012 02:52:27
Uptime                    0 days 0 hr. 0 min. 22 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\11.1.0\db_1\network\admin\listener.ora
Listener Log File         c:\oracle\diag\tnslsnr\SERVER\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SERVER.domain.com)(PORT=1521)))
Services Summary...
Service "INSTANCE1_XPT" has 1 instance(s).
  Instance "instance1", status READY, has 4 handler(s) for this service...
Service "instance1" has 1 instance(s).
  Instance "instance1", status READY, has 4 handler(s) for this service...
The command completed successfully

I guess now what’s left is trying to figure out how to reduce the amount of logging, it appears that it logs every connection being made, which eventually fills up the log and create the problem.

[…]
27-JUL-2012 03:17:21 * (CONNECT_DATA=(SID=instance1)(CID=(PROGRAM=JDBC Thin Client) 
HOST=__jdbc__)(USER=xxx))) * (ADDRESS=(PROTOCOL=tcp)(HOST=xx.xx.xx.xx)(PORT=53616))
 * establish * instance1* 0
[…]

1 comment :

  1. This was the fix I was looking for. Worked perfectly. Had no idea the log file had gotten so big.

    Thanks again for the solution!

    ReplyDelete