0

I've just installed Oracle database 12c in my local machine plus SQL Developer and when attempting to start sqlplus from the command prompt I get the following error: 

C:\WINDOWS\system32>sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 9 09:44:14 2018

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

Enter user-name: system
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

The fact is that if I run:

sqlplus system/<pass>@localhost:1521/orcl.carver-as.com as sysdba

then I can connect successfully :s

Ok, here are my relevant files and outputs for you to try to help me.

Oracle Admin installation path:

c:\oracle\dperezd\virtual\product\12.2.0\dbhome_1\network\admin\

Oracle Client installation path:

c:\oracle\client\dperezd\product\12.2.0\client_1\Network\Admin\

Sid: orcl

tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 09-AGO-2018 09:46:01

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Archivos de parßmetros utilizados:
c:\oracle\dperezd\virtual\product\12.2.0\dbhome_1\network\admin\sqlnet.ora


Adaptador TNSNAMES utilizado para resolver el alias
Intentando contactar con (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.carver-as.com)))
Realizado correctamente (0 mseg)

C:\WINDOWS\system32>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 09-AGO-2018 09:53:23

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

Conectßndose a (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DPEREZD.carver-as.com)(PORT=1521)))
ESTADO del LISTENER
------------------------
Alias                     LISTENER
Versi¾n                   TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production
Fecha de Inicio       09-AGO-2018 09:35:29
Tiempo Actividad   0 dÝas 0 hr. 17 min. 56 seg.
Nivel de Rastreo        off
Seguridad               ON: Local OS Authentication
SNMP                      OFF
Parßmetros del Listener   C:\oracle\dperezd\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora
Log del Listener          C:\oracle\dperezd\virtual\diag\tnslsnr\DPEREZD\listener\alert\log.xml
Recibiendo Resumen de Puntos Finales...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DPEREZD.carver-as.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DPEREZD.carver-as.com)(PORT=5500))(Security=(my_wallet_directory=C:\ORACLE\DPEREZD\VIRTUAL\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Resumen de Servicios...
El servicio "CLRExtProc" tiene 1 instancia(s).
  La instancia "CLRExtProc", con estado UNKNOWN, tiene 1 manejador(es) para este servicio...
El servicio "orcl.carver-as.com" tiene 1 instancia(s).
  La instancia "orcl", con estado READY, tiene 1 manejador(es) para este servicio...
El servicio "orclXDB.carver-as.com" tiene 1 instancia(s).
  La instancia "orcl", con estado READY, tiene 1 manejador(es) para este servicio...
El comando ha terminado correctamente

C:\WINDOWS\system32>lsnrctl service

LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 09-AGO-2018 09:53:53

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

Conectßndose a (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DPEREZD.carver-as.com)(PORT=1521)))
Resumen de Servicios...
El servicio "CLRExtProc" tiene 1 instancia(s).
  La instancia "CLRExtProc", con estado UNKNOWN, tiene 1 manejador(es) para este servicio...
    Manejador(es):
      "DEDICATED" establecido:0 rechazado:0
         LOCAL SERVER
El servicio "orcl.carver-as.com" tiene 1 instancia(s).
  La instancia "orcl", con estado READY, tiene 1 manejador(es) para este servicio...
    Manejador(es):
      "DEDICATED" establecido:2 rechazado:0 estado:ready
         LOCAL SERVER
El servicio "orclXDB.carver-as.com" tiene 1 instancia(s).
  La instancia "orcl", con estado READY, tiene 1 manejador(es) para este servicio...
    Manejador(es):
      "D000" establecidos:0 rechazados:0 actuales:0 mßximo:1022 estado:ready
         DISPATCHER <machine: DPEREZD, pid: 12252>
         (ADDRESS=(PROTOCOL=tcp)(HOST=DPEREZD.carver-as.com)(PORT=1825))
El comando ha terminado correctamente

c:\oracle\dperezd\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora file:

# listener.ora Network Configuration File: C:\oracle\dperezd\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oracle\dperezd\virtual\product\12.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\oracle\dperezd\virtual\product\12.2.0\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DPEREZD.carver-as.com)(PORT = 1521))
    )
  )

c:\oracle\dperezd\virtual\product\12.2.0\dbhome_1\network\admin\tsnames.ora:

# tnsnames.ora Network Configuration File: C:\oracle\dperezd\virtual\product\12.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.carver-as.com)
    )
  )

c:\oracle\dperezd\virtual\product\12.2.0\dbhome_1\network\admin\sqlnet.ora:

# sqlnet.ora Network Configuration File: C:\oracle\dperezd\virtual\product\12.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

I have read that setting TNS_ADMIN environment varible might help so in Windows I did:

set TNS_ADMIN=c:\oracle\dperezd\virtual\product\12.2.0\dbhome_1\network\admin\
lsnrctl stop
lsnrctl start

to no avail, still the problem persists.

Ok, I hope this information is enough for anybody to help me.

It's really weird because the service is started and running and ping is OK and sqlplus with full set of parameters just connects but sqlplus just alone won't no matter what I do.

Thanks.

Improve this question

2 Answers 2

Reset to default
1

Most of the times you get a TNS:protocol adapter error on a local installation, is due to a not properly set ORACLE_SID variable.

For Example, from my local installation:

My actual variables:

CMD> set ora
ORACLE_HOME=C:\app\database_12\product\12.1.0\dbhome_1
ORACLE_SID=PRDDB

Test Connection:

CMD> sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Gio Ago 9 16:35:49 2018

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

Immettere il nome utente: /as sysdba

Connesso a:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit
Disconnesso da Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Change the ORACLE_SID

CMD> set ORACLE_SID=TEST

CMD>set ora
ORACLE_HOME=C:\app\database_12\product\12.1.0\dbhome_1
ORACLE_SID=TEST

Test Connection

CMD> sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Gio Ago 9 16:36:17 2018

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

Immettere il nome utente: /as sysdba
ERROR:
ORA-12560: TNS: errore dell'adattatore del protocollo


Immettere il nome utente:

Another thing you can check is your PATH variable and make sure you have your ORACLE_HOME set BEFORE your client home.

Hope this helps.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thank you very much Edward. I realized my ORACLE_HOME and ORACLE_SID variables were not set so I set them according your instructions like so: set ORACLE_HOME=c:\oracle\dperezd\virtual\product\12.2.0\dbhome_1 set ORACLE_SID=orcl Still no luck and the weird thing is that if I write: sqlplus system/<pass>@orcl as sysdba then it connects correctly, but not with just sqlplus with no parameters.
Mmh.. Weird.. What’s the output of sho parameter local_listener ? Using the @orclyou’re using the listener (the one on the 1521) maybe there’s something wrong with the local_listener? I’m not in front of a PC right now, but i’ll try to give you more support when i’ll be :)
Thanks again for your effort. sho parameter local_listener=>local_listener string LISTENER_ORCL
Oh my Gosh! Finally got it. Sometimes you don't believe what the problem was, and thats my case now. I also have a client installation and sqlplus from client was being executed, what means that the environment variables are not in the correct order. Now it works as expected so I'll close the case but thanks for your time.
As stated in my answer "make sure you have your ORACLE_HOME set BEFORE your client home" btw happy you solved your problem! :)
|
0

Lets do this:
1. Open Oracle Net Configuration Assistant
2. Select radio button option at Local Net Service Name Configuration, then click Next
3. Select radio button option at Reconfigure, then click next
4. Select net service name you want to reconfigure, either ORACLR_CONNECTION_DATA or ORCL, then click Next
5. input your Service Name, if your service name of installed oracle as ORCL, then write ORCL to the field, then click Next
6. select the protocol you want to use, for example, select TCP, then click Next
7. input hostname where your oracle database engine (service) installed, for example, installed on your desktop or notebook,
then fill as localhost, and fill your port number (select option Use the Standar port number of 1521) or if use other port, fill the port number
Then click next.
8. select Yes perform test,
if necessary, change user logon
then click next
9. if no configure another net service name, select No and then click next
10. Click Finish

you should download, extract and install the ODTwithODAC183.zip and BuildTools_Full.exe if you want use to connect on .net framework from MS Visual Studio

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.