Home » RDBMS Server » Networking and Gateways » ORA-12537 TNS connection closed
ORA-12537 TNS connection closed [message #580040] Tue, 19 March 2013 12:41 Go to next message
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
Re: ORA-12537 TNS connection closed [message #580042 is a reply to message #580040] Tue, 19 March 2013 12:50 Go to previous messageGo to next message
BlackSwan
Messages: 22537
Registered: January 2009
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/

Re: ORA-12537 TNS connection closed [message #580044 is a reply to message #580042] Tue, 19 March 2013 13:12 Go to previous messageGo to next message
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


Re: ORA-12537 TNS connection closed [message #580118 is a reply to message #580044] Wed, 20 March 2013 09:22 Go to previous messageGo to next message
BlackSwan
Messages: 22537
Registered: January 2009
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
Re: ORA-12537 TNS connection closed [message #580231 is a reply to message #580118] Thu, 21 March 2013 12:37 Go to previous messageGo to next message
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
Re: ORA-12537 TNS connection closed [message #580236 is a reply to message #580231] Thu, 21 March 2013 13:04 Go to previous messageGo to next message
BlackSwan
Messages: 22537
Registered: January 2009
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
Re: ORA-12537 TNS connection closed [message #580355 is a reply to message #580236] Sat, 23 March 2013 05:42 Go to previous messageGo to next message
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.
Re: ORA-12537 TNS connection closed [message #580395 is a reply to message #580355] Sat, 23 March 2013 20:45 Go to previous messageGo to next message
BlackSwan
Messages: 22537
Registered: January 2009
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)
Re: ORA-12537 TNS connection closed [message #580414 is a reply to message #580395] Sun, 24 March 2013 05:44 Go to previous messageGo to next message
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.
Re: ORA-12537 TNS connection closed [message #580423 is a reply to message #580414] Sun, 24 March 2013 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
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
Re: ORA-12537 TNS connection closed [message #580911 is a reply to message #580414] Fri, 29 March 2013 12:28 Go to previous messageGo to next message
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.
Re: ORA-12537 TNS connection closed [message #580912 is a reply to message #580911] Fri, 29 March 2013 12:33 Go to previous messageGo to next message
BlackSwan
Messages: 22537
Registered: January 2009
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 Go to previous messageGo to next message
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.
Re: ORA-12537 TNS connection closed [message #581019 is a reply to message #581018] Sun, 31 March 2013 18:19 Go to previous message
BlackSwan
Messages: 22537
Registered: January 2009
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/

It almost appears that the instance is crashing.

what clues exist within alert_SID.log file?
Previous Topic: ORA-02047: cannot join the distributed transaction in progress
Next Topic: when creating new listener response is port already used
Goto Forum:
  


Current Time: Wed Jul 30 17:58:03 CDT 2014

Total time taken to generate the page: 0.06309 seconds