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.
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.