Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Impossible to connect to database using ORACLE_SID Oracle 12.1.0.2.0

User_08WZ0Jul 20 2016 — edited Jul 28 2016

I'm facing a problem concerning the connection to my database :

I'm able to connect using sqlplus xxx/yyy

--> Connected to  Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

When I use

sqlplus xxx/yyy@zzz

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 15:26:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:

ORA-12537: TNS:connection closed

Here the content of my listener.ora :

LISTENER=

  (DESCRIPTION=

   (ADDRESS_LIST=

    (ADDRESS=

     (HOST=lcoloXXXXv01)

     (PROTOCOL=TCP)

     (PORT=1577)

    )

    (ADDRESS=

     (KEY=ORA_12_1_0_2)

     (PROTOCOL=IPC)

    )

   )

  )

SID_LIST_LISTENER=

(SID_LIST=

  (SID_DESC=

   (ORACLE_HOME=/oracle/product/12.1.0.2)

   (SID_NAME=zzz)

  )

)

)

Here the cobntent of my tnsnames.ora :

zzz=

(DESCRIPTION=

  (ADDRESS=

   (HOST=lcoloXXXXv01)

   (PROTOCOL=TCP)

   (PORT=1577)

  )

  (CONNECT_DATA=

   (SID=zzz)

  )

)

tnsping works well :

Used parameter files:

/project/V1/bin/dbora/dbs/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS= (HOST=lcoloXXXXv01) (PROTOCOL=TCP) (PORT=1577)) (CONNECT_DATA= (SID=zzz)))

OK (0 msec)

lsnrctl status result :

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-JUL-2016 15:32:09

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

Connecting to (DESCRIPTION=(ADDRESS=(HOST=lcoloXXXXv01)(PROTOCOL=TCP)(PORT=1577)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date                19-JUL-2016 08:58:28

Uptime                    1 days 6 hr. 33 min. 41 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/product/12.1.0.2/network/admin/listener.ora

Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/lcoloXXXXv01/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lcoloXXXXv01)(PORT=1577)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=ORA_12_1_0_2)))

Services Summary...

Service "zzz" has 1 instance(s).

  Instance "zzz", status UNKNOWN, has 1 handler(s) for this service...

The content of the log file :

<msg time='2016-07-20T15:34:01.614+02:00' org_id='oracle' comp_id='tnslsnr'

type='UNKNOWN' level='16' host_id='lcolodbdev01'

host_addr='172.29.15.20'>

<txt>20-JUIL.-2016 15:34:01 * (CONNECT_DATA=(SID=zzz)(CID=(PROGRAM=sqlplus)(HOST=lcoloXXXXv01)(USER=myuser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XX.XX)(PORT=42581)) * establish * zzz * 12518

</txt>

</msg>

<msg time='2016-07-20T15:34:01.615+02:00' org_id='oracle' comp_id='tnslsnr'

type='UNKNOWN' level='16' host_id='lcoloXXXXv01'

host_addr='XXX.XX.XX.XX'>

<txt>TNS-12518: TNS:listener could not hand off client connection

TNS-12547: TNS : lost contact

  TNS-12560: TNS:protocol adapter error

   TNS-00517: Lost contect

    Linux Error: 32: Broken pipe

</txt>

</msg>

Thantk's for your answers

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 25 2016
Added on Jul 20 2016
22 comments
2,633 views