Forum Stats

  • 3,783,382 Users
  • 2,254,767 Discussions
  • 7,880,376 Comments

Discussions

Another: ORA-12560: TNS:protocol adapter error

I have to connect to several servers in my org so I had an existing tnsnames.ora in c:\oracleclient before I ever tried to install XE.

Given that the XE installer does not want to know about that installation I am not surprised to find I have a duplicate tnsnames.ora in <home>/network/admin. The dupe version of tnsnames.ora has the entries created by the XE installer. However my (restored) TNS_ADMIN variable points to c:\oracleclient\tnsnames.ora which knows nothing of XE. 


How can I connect to both XE running on my local machine and also the other servers in my org?

Thanks for the assist.

I tried copying the content of <home>/network/admin/tnsnames.ora to c:\oracleclient\tnsnames.ora but that does not work so I reverted it.

Per this thread on Stackoverflow https://stackoverflow.com/questions/6894558/ora-12560-tnsprotocol-adaptor-error

I confirmed these services are running:

OracleServiceXE

OracleOraDB18Home3TNSListener

OracleOraDB18Home3MTSRecoveryService

OracleVssWriterXE


I tried to connect using the commands found here: https://docs.oracle.com/en/database/oracle/oracle-database/18/xeinw/connecting-oracle-database-xe.html


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

PS C:\app\Player1\product\18.0.0\dbhomeXE\bin> sqlplus / as sysdba                           

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 22 16:17:11 2021

Version 18.4.0.0.0


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


ERROR:

ORA-12560: TNS:protocol adapter error

Enter user-name:  

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

Content of C:\app\Player1\product\18.0.0\dbhomeXE\network\admin\tnsnames.ora. Note this not the file pointed to by TNS_ADMIN:

XE =

 (DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = mymachine.com)(PORT = 1521))

  (CONNECT_DATA =

   (SERVER = DEDICATED)

   (SERVICE_NAME = XE)

  )

 )


LISTENER_XE =

 (ADDRESS = (PROTOCOL = TCP)(HOST = mymachine.com)(PORT = 1521))



ORACLR_CONNECTION_DATA =

 (DESCRIPTION =

  (ADDRESS_LIST =

   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

  )

  (CONNECT_DATA =

   (SID = CLRExtProc)

   (PRESENTATION = RO)

  )

 )


Content of C:\app\Player1\product\18.0.0\dbhomeXE\network\admin\listner.ora. This file is exactly the same as c:\oracleclient\listner.ora:


DEFAULT_SERVICE_LISTENER = XE


SID_LIST_LISTENER =

 (SID_LIST =

  (SID_DESC =

   (SID_NAME = CLRExtProc)

   (ORACLE_HOME = C:\app\Player1\product\18.0.0\dbhomeXE)

   (PROGRAM = extproc)

   (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Player1\product\18.0.0\dbhomeXE\bin\oraclr18.dll")

  )

 )


LISTENER =

 (DESCRIPTION_LIST =

  (DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = mymachine.com)(PORT = 1521))

   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

  )

 )



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

PS C:\app\Player1\product\18.0.0\dbhomeXE\bin> lsnrctl status                             

LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 - Production on 22-APR-2021 16:46:48


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


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mymachine.com)(PORT=1521)))

STATUS of the LISTENER

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

Alias           LISTENER

Version          TNSLSNR for 64-bit Windows: Version 18.0.0.0.0 - Production

Start Date        22-APR-2021 16:44:09

Uptime          0 days 0 hr. 2 min. 38 sec

Trace Level        off

Security         ON: Local OS Authentication

SNMP           OFF

Default Service      XE

Listener Parameter File  C:\OracleClient\listener.ora

Listener Log File     C:\app\Player1\product\18.0.0\diag\tnslsnr\AJ018157\listener\alert\log.xml

Listening Endpoints Summary...

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mymachine.com)(PORT=1521)))

 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=C:\APP\Player1\PRODUCT\18.0.0\admin\XE\xdb_wallet))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "310ea5b75b1741dbbb7ae9e44d056564" has 1 instance(s).

 Instance "xe", status READY, has 1 handler(s) for this service...

Service "CLRExtProc" has 1 instance(s).

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

Service "XE" has 1 instance(s).

 Instance "xe", status READY, 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 "xepdb1" has 1 instance(s).

 Instance "xe", status READY, has 1 handler(s) for this service...

The command completed successfully

PS C:\app\Player1\product\18.0.0\dbhomeXE\bin>

Answers