OraFAQ Forum: Networking and Gateways » ORA-12537 TNS connection closed

Home » RDBMS Server » Networking and Gateways » ORA-12537 TNS connection closed

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-12537 TNS connection closed [message #580040]

Tue, 19 March 2013 12:41

deepakkrjain3
Messages: 8
Registered: October 2011
Location: Delhi

Junior Member

Dear friends,

I am facing ora - 12537 TNS connection closed issue while I am trying to login into database.

I have installed Oracle database 11g on windows 7 premium OS.

Anyone can help me to trace this issue.

Thanks in advance.

Regards,
Deepak

Report message to a moderator

Re: ORA-12537 TNS connection closed [message #580042 is a reply to message #580040]

Tue, 19 March 2013 12:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post results from following OS commands below

lsnrctl status
lsnrctl service

what clues exist withing listener log file?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: ORA-12537 TNS connection closed [message #580044 is a reply to message #580042]

Tue, 19 March 2013 13:12

deepakkrjain3
Messages: 8
Registered: October 2011
Location: Delhi

Junior Member

Hi,

Please look below for lsnrctl status and lsnrctl services command output.

Also please look into exact error message which is I am getting during database connectivity.

lsnrctl status
------------------

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 19-MAR-2013 23:39:21

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 19-MAR-2013 21:47:20
Uptime 0 days 1 hr. 52 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\database_11g\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\database_11g\diag\tnslsnr\deepak_dj\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deepak_dj)(PORT=1521))(PROTOCOL_STACK=(PRESENTATION=GIOP)(SESSION=RAW)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

lsnrctl service
--------------------

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 19-MAR-2013 23:39:24

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
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
The command completed successfully

Attachment: Exact TNS error.jpg
(Size: 28.78KB, Downloaded 2035 times)

Report message to a moderator

Re: ORA-12537 TNS connection closed [message #580118 is a reply to message #580044]

Wed, 20 March 2013 09:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Force the error again, then post last 100 or so lines from d:\database_11g\diag\tnslsnr\deepak_dj\listener\alert\log.xml

open Command Window then do as below

cd D:\database_11g\product\11.2.0\dbhome_1\network\admin\
lsnrctl stop
ren listener.ora listener.ora.sav
lsnrctl start
REM - now wait 60+ seconds before proceeding as below
lsnrctl status
lsnrctl service

COPY the results from above then PASTE all back here

Report message to a moderator

Re: ORA-12537 TNS connection closed [message #580231 is a reply to message #580118]

Thu, 21 March 2013 12:37

deepakkrjain3
Messages: 8
Registered: October 2011
Location: Delhi

Junior Member

Hi,

I have performed all the steps in same way as you suggest.

Please look below for the output.

lsnrctl_status
-------------------------

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 21-MAR-2013 23:03:27

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 21-MAR-2013 23:01:42
Uptime 0 days 0 hr. 1 min. 48 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File d:\database_11g\diag\tnslsnr\deepak_dj\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deepak_dj)(PORT=1521)))
The listener supports no services
The command completed successfully

Lsnrctl_service
-----------------------------------

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 21-MAR-2013 23:03:51

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The listener supports no services
The command completed successfully

Thanks & Regards,
Deepak

Report message to a moderator

Re: ORA-12537 TNS connection closed [message #580236 is a reply to message #580231]

Thu, 21 March 2013 13:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It appears that currently no DB is up & online.
Since V10, Oracle DB automatically register themselves with listener every 60 seconds.

It is impossible to connect to a DB that is down.

open Command Window & do EXACTLY as shown below (line for line)

sqlplus
/ as sysdba
shudown abort
startup
exit
lsnrctl status
lsnrctl service

COPY the results from above then PASTE all back here

Report message to a moderator

Re: ORA-12537 TNS connection closed [message #580355 is a reply to message #580236]

Sat, 23 March 2013 05:42

deepakkrjain3
Messages: 8
Registered: October 2011
Location: Delhi

Junior Member

Hi,

I have performed same steps as you mentioned in your list.

Please see below for lsnrctl status & service.

lsnrctl status
------------------------

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-MAR-2013 16:06:49

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 23-MAR-2013 15:34:14
Uptime 0 days 0 hr. 32 min. 49 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File d:\database_11g\diag\tnslsnr\deepak_dj\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deepak_dj)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

lsnrctl service
------------------------

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-MAR-2013 16:07:12

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The listener supports no services
The command completed successfully.

Report message to a moderator

Re: ORA-12537 TNS connection closed [message #580395 is a reply to message #580355]

Sat, 23 March 2013 20:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Service "orcl" has 1 instance(s).
>Instance "orcl", status READY, has 1 handler(s) for this service.

The database is now known to the listener.

>(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

no remote client can ever connect to this listener & any DB serviced by this listener because of use of 127.0.0.1 (a.k.a. localhost)

Report message to a moderator

Re: ORA-12537 TNS connection closed [message #580414 is a reply to message #580395]

Sun, 24 March 2013 05:44

deepakkrjain3
Messages: 8
Registered: October 2011
Location: Delhi

Junior Member

Sorry back to you again, your last statement is not clear to me and still i am not able to connect database.

Report message to a moderator

Re: ORA-12537 TNS connection closed [message #580423 is a reply to message #580414]

Sun, 24 March 2013 06:08

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

127.0.0.1 cannot be reached by another server as it always and for all server indicates YOUR own server and not the client one.
Ping 127.0.0.1 from your PC and you will ping your PC, and you don't even need to have access to a network to do this.

Regards
Michel

Report message to a moderator

Re: ORA-12537 TNS connection closed [message #580911 is a reply to message #580414]

Fri, 29 March 2013 12:28

deepakkrjain3
Messages: 8
Registered: October 2011
Location: Delhi

Junior Member

Hi,

Still I was not able to connect with database. Now I am getting ORA-12514: TNS: listener does not currently know of service requested in connected descriptor.

Report message to a moderator

Re: ORA-12537 TNS connection closed [message #580912 is a reply to message #580911]

Fri, 29 March 2013 12:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ORA-12514 ALWAYS only occurs due to a problem on DB Server system.
One cause of this problem is when the Oracle database is down & needs to be started.
A remote client send a request to the Listener asking to be connected to a specific service.
If/when the listener does not know anything about that service, the listener responds with ORA-12514

post results from the following two OS commands:
lsnrctl status
lsnrctl service

Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.

*This line contains valuable debugging details. So post this line & surrounding lines.*

for additional debugging suggestions read the URL below:
http://edstevensdba.wordpress.com/2011/03/19/ora-12514/

[Updated on: Fri, 29 March 2013 12:34]

Report message to a moderator

Re: ORA-12537 TNS connection closed [message #581018 is a reply to message #580912]

Sun, 31 March 2013 14:08

deepakkrjain3
Messages: 8
Registered: October 2011
Location: Delhi

Junior Member

Hi,

I notice that when I login via sysdba user and through startup command then I can only able to connect with database but after 2 or 3 minutes my connection got disconnect automatically.

So I have check lsnrctl status / Service for every action i.e. before startup command, after startup command and after connection disconnect.

Before Startup command
-----------------------------------------

lsnrctl status
-------------------------

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 01-APR-2013 00:22:33

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 31-MAR-2013 23:26:46
Uptime 0 days 0 hr. 55 min. 49 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File d:\database_11g\diag\tnslsnr\deepak_dj\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deepak_dj)(PORT=1521)))
The listener supports no services
The command completed successfully

lsnrctl service
-------------------------

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 01-APR-2013 00:22:39

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The listener supports no services
The command completed successfully

After Startup command
-----------------------------------------

lsnrctl status
-------------------------

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 01-APR-2013 00:25:33

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 31-MAR-2013 23:26:46
Uptime 0 days 0 hr. 58 min. 49 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File d:\database_11g\diag\tnslsnr\deepak_dj\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deepak_dj)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

lsnrctl status
-------------------------

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 01-APR-2013 00:25:39

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
The command completed successfully

After connection got disconnect
----------------------------------------------

lsnrctl status
-------------------------

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 01-APR-2013 00:31:30

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 31-MAR-2013 23:26:46
Uptime 0 days 1 hr. 4 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File d:\database_11g\diag\tnslsnr\deepak_dj\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deepak_dj)(PORT=1521)))
The listener supports no services
The command completed successfully

lsnrctl service
-------------------------

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 01-APR-2013 00:31:19

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The listener supports no services
The command completed successfully.

Once the connection is disconnected then I am getting ora-12514 error.

Thanks in advance.

Report message to a moderator

Re: ORA-12537 TNS connection closed [message #581019 is a reply to message #581018]

Sun, 31 March 2013 18:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Report message to a moderator

Previous Topic:	ORA-02047: cannot join the distributed transaction in progress
Next Topic:	when creating new listener response is port already used

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 04:00:38 CDT 2024