This discussion is archived
6 Replies Latest reply: Apr 30, 2012 2:33 AM by 516721 RSS

OracleXE 10.2 - can not connect: ns main err code: 12535

516721 Newbie
Currently Being Moderated
I installed Oracle XE on Win7 SP1. Installation went OK. I can log in to the web administration console. I can login to SQLplus.

However, log in with SQL developer (or other JDBC client) produces "Got minus one from a read call"

Firewall is OFF - I can "telnet localhost 1521"

In the log I see this:

Fatal NI connect error 12170.
VERSION INFORMATION:
     TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
     Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production
     Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production
Time: 25-APR-2012 21:42:16
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: <unknown>


tnsping xe gives this:

Used parameter files:
D:\progs\oracle\app\oracle\product\10.2.0\server\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = electrosun)(PORT = 1521)) (CONNECT_DATA = (SERVE
R = DEDICATED) (SERVICE_NAME = XE)))
TNS-12537: TNS:connection closed


lsnrctl status gives this

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 25-APR-2012 21:38:52
Uptime 0 days 0 hr. 33 min. 49 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File D:\progs\oracle\app\oracle\product\10.2.0\server\network\admin\listener.ora
Listener Log File D:\progs\oracle\app\oracle\product\10.2.0\server\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=electrosun)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "XE_XPT" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully



I have seen so many forum posts about this without any practical advice or solution (besides 'check firewall', again, i can telnet to port 1521).

Does this diagnostic output gives any clue? Is tnsping output OK? Is there anything else I can check?

Thanks!
  • 1. Re: OracleXE 10.2 - can not connect: ns main err code: 12535
    orafad Oracle ACE
    Currently Being Moderated
    dario wrote:
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=electrosun)(PORT=1521)))
    Can you telnet to electrosun 1521?

    What does following commands reveal?

    sqlplus user/pw@'localhost:1521'

    sqlplus user/pw@'electrosun:1521'
  • 2. Re: OracleXE 10.2 - can not connect: ns main err code: 12535
    clcarter Expert
    Currently Being Moderated
    ns main err code: 12535 ... TNS-12535: ... ns secondary err code: 12606
    A 12535 + 12606 error might be an attempt to get a shared server connection when a server process is busy doing something else or somehow not available for a new session, check your $ lsnrctl services output in the stanza under the "Dxxx", check the connection count details, if the counts is low, or even 0, and the "Refused" count is 0, its not a shared server connect issue.

    Post your lsnrctl services output if you need a hand interpreting the info.
    tnsping xe ... TNS-12537
    No, that's not a 'tnsping OK' output at all. MOS note Troubleshooting Guide TNS-12535 or ORA-12535 or ORA-12170 Errors 119706.1 might be helpful there.

    It does appear that something is not quite correct with the networking configuration, either the host ethernet adapter, maybe a TCP buffer, something along those lines. Or might need adjustments to the database listener.ora, and/or all client sqlnet.ora files to increase sqlnet timeout parameters.

    But first ... make sure your connection attempts are going to the correct host- verify your hostname, your IP address, and that both the hostname and IP address resolve correctly.

    In a command box:
    $ ipconfig /all
    ... ? perhaps more than one adapter ?
    ... IP address: n.n.n.n
    $ hostname
      ==> electrosun (?)
    $ nslookup electrosun
    ... IP address: <n.n.n.n>
    $ nslookup n.n.n.n
    ... hostname: electrosun ... (?)
    $ tracert  electrosun
    ... # should only be one line here ending in <FQDN> [n.n.n.n]
    FQDN should be your fully qualified hostgame, i.e. electrosun.yourcompany.com or something similar. And a tracert <FQDN> might be a good check as well.

    Once the networking particulars are verified, a next step might be to try sqlnet/listener .ora file connect timeouts. Or enabling a client trace. Both of those steps can be a bit tedious.
  • 3. Re: OracleXE 10.2 - can not connect: ns main err code: 12535
    516721 Newbie
    Currently Being Moderated
    @orafad, @clcarter thank you very much for your help, I will answer in the single post:

    First of all, to clarify: electrosun is my workstation, there are no other computers involved, everything is on the single machine.
    tnslsnr listens to 0.0.0.0 so it listens on all interfaces and i can telenet to localhost:1521 and electrosun:1521.

    This is fresh oracle XE installation, there are no user accounts, no schemas and server is doing nothing so I wouldn't say server is busy doing something else.

    This is "lsnrctl services" output:

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
    Services Summary...
    Service "CLRExtProc" has 1 instance(s).
    Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0
    LOCAL SERVER
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0
    LOCAL SERVER
    Service "XEXDB" has 1 instance(s).
    Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
    "D000" established:0 refused:0 current:0 max:1002 state:ready
    DISPATCHER <machine: ELECTROSUN, pid: 192>
    (ADDRESS=(PROTOCOL=tcp)(HOST=electrosun)(PORT=51094))
    Service "XE_XPT" has 1 instance(s).
    Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:4 refused:0 state:ready
    LOCAL SERVER
    Service "xe" has 1 instance(s).
    Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:4 refused:0 state:ready
    LOCAL SERVER
    The command completed successfully

    XE has 4 connection established???

    I tried sqlplus user/pw@'localhost/electrosun:1521' and it refuses connection after 60 seconds with "ORA-12537: TNS:connection closed". But if I just type "sqlplus" i get prompted for usr/pwd and I can log in and browse tables in system schema.

    tracert electrosun
    Tracing route to electrosun [192.168.1.43]
    1 <1 ms <1 ms <1 ms electrosun [192.168.1.43]

    There are so many forum posts and other search hits on google with similar description but I didn't see any solution. I also searched ORA12537 but found nothing that helps.
  • 4. Re: OracleXE 10.2 - can not connect: ns main err code: 12535
    clcarter Expert
    Currently Being Moderated
    Hmmmm well with:
    "D000" established:0 refused:0 current:0 max:1002 state:ready
    Connect attempts don't seem to be coming in via a Shared connection. Peek the oracle doc to see the explanation of for ora-12535, although it may not help much, something isn't getting through to the listener. http://docs.oracle.com/cd/E11882_01/server.112/e17766/net12500.htm#sthref3565

    And you did mention a $ telnet <hostname> 1521 does not return a "connection refused" ? Sometimes one may think the firewall is disabled, but maybe a domain group or some other Windows policy setting could prevent that from working as intended. At the least, be sure and visit the firewall applet and add port 1521 to the Exceptions list.
    sqlplus user/pw@'localhost/electrosun:1521'
    Isn't quite correct, I think, maybe a variation of an instant client (?) connect specifier, for a listener connection you want to specify <user>@<tnsalias> and for the XE install, a tnsalias "xe" should already be present in your %ORACLE_HOME%/network/admin/tnsnames.ora file.

    For a connection that uses the listener try:
    sqlplus user@xe
    ... password ...
    -- or *maybe*
    sqlplus user@localhost/xe
    -- or 
    sqlplus user@electrosun/xe
    Don't quote me on those instant client connect identifiers, I don't use those much at all. My loss, maybe. Also not exactly positive what the sqlnet protocols that are in play for a default out-of-the-box setup.

    Does your sqlnet.ora file have any changes, or is it unchanged after running the installer? Should have just one (non-comment or not-blank) line with:
    SQLNET.AUTHENTICATION_SERVICES = (NTS)
    Can suggest a couple easy things to try, stop the listener, move (rename) your listener.ora file, and start the listener. Use a sys or system connection, and register the instance:
    $ lsnrctl stop
    $ cd %ORACLE_HOME% # that *should work, verify the location
    $ move listener.ora listener.ora.bk0
    $ lsnrctl start
    $ sqlplus system
     ... passord ... Connected.
    alter system register;
    exit
    $ lsnrctl status
    ... 
    $ lsnrctl services
    Also, the status/services line with
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
    should show the TCP protocol, not IPC. Maybe. Using a non-existent listener.ora file ought to cause a change in that line of the status and services output. Also, try this content for a listener.ora file and see what happens:
    LISTENER =
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=n.n.n.n)(PORT=1521))
    ) 
    Be sure to stop the listener before changing the file. Also, setting host=<hostname> and host=0.0.0.0 all may be worth trying as well, it only takes a listener stop, edit, listener start, and sqlplus system ... alter system register; to see if there's a change. I believe orafad gets the kudos for the non-existent listener.ora trick.
    if I just type "sqlplus" i get prompted for usr/pwd and I can log in
    Right, a connection to the database from the db host, as long as the client environment is set properly, uses a bequeath, not a listener, connection.

    Edited by: clcarter on Apr 26, 2012 6:52 PM
    tns-12535 not -12537
  • 5. Re: OracleXE 10.2 - can not connect: ns main err code: 12535
    516721 Newbie
    Currently Being Moderated
    I tried without listener.ora (moved it listener.ora.bak) but LSNRCTL didn't really work after that:

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
    TNS-12541: TNS:no listener
    TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
    32-bit Windows Error: 2: No such file or directory
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.43)(PORT=1521)))
    TNS-12537: TNS:connection closed
    TNS-12560: TNS:protocol adapter error
    TNS-00507: Connection closed

    I also tried with IP address instead of host name.

    I don't think this will ever work.

    I just checked with my previous Oracle XE installation in WinXP: listener.ora is exactly the same as the new one in Win7. But it worked in WinXP.

    I tried with "lsnrctl trace", and got huge trc file, but couldn't figure out anything (it is too big to be posted here). I can make it available if anyone is willing to check it.
  • 6. Re: OracleXE 10.2 - can not connect: ns main err code: 12535
    516721 Newbie
    Currently Being Moderated
    I traced tnslistner, trace is available at https://www.dropbox.com/s/p80cop5k1kyrhb9/trace.zip

    I would be thankful if someone is willing to take a look. I traced with USER, ADMIN and SUPPORT levels and cut out output in the separate files. There is also a big file that contains everything.

    I traced "sqlplus system@xe" and allways got ERROR: ORA-12537: TNS:connection closed.

    Thanks,

    Dario

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points