asked on

ORA-12560: TNS:protocol adapter error

hi am in oracle 12c database am geting this error

C:\App\product\12.1.0\dbhome_1\NETWORK\ADMIN>sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 20 10:53:20 2016

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

SQL> connect /as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error

but is listner is up and running
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hosname)(PORT=1522)))
The command completed successfully
LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
System parameter file is D:\app\OracleUser\product\12.1.0\dbhome_1\network\admin\listener.ora
Log messages written to D:\app\OracleUser\diag\tnslsnr\hosname\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hosname)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hosname)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date 20-NOV-2016 10:42:41
Uptime 0 days 0 hr. 0 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\app\OracleUser\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File D:\app\OracleUser\diag\tnslsnr\hosname\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hosname)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hosname)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date 20-NOV-2016 10:42:41
Uptime 0 days 0 hr. 0 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\app\OracleUser\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File D:\app\OracleUser\diag\tnslsnr\hosname\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hosname)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
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
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hosname)(PORT=1522)))
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
Service "mmui" has 1 instance(s).
Instance "munt", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "munt" has 1 instance(s).
Instance "munt", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "muntXDB" has 1 instance(s).
Instance "munt", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: hosname, pid: 4592>
(ADDRESS=(PROTOCOL=tcp)(HOST=hosname)(PORT=49993))
The command completed successfully

Geert G

did you set the oracle_sid ?

either
set oracle_sid=munt
or
set oracle_sid=muni

and then sqlplus / as sysdba

if you get an error of insufficient privileges or password error
make sure your user is in the local security group ora_dba

Qlemo

IDK if the local connection still uses something diffeent from TCP in 12c, but if not, you'll have to provide the non-default port. Did you try by providing the full connection string, like
sqlplpus /@localhost:1522/mmui

chalie001

ASKER

i did that am geting
C:\>SET ORACLE_HOME=D:\App\OracleUser\product\12.1.0\dbhome_1

C:\>SET_ORACLE_SID=mmui
'SET_ORACLE_SID' is not recognized as an internal or external command,
operable program or batch file.

C:\>SET ORACLE_SID=mmui

C:\>sqlplus /@localhost:1522/mmui

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 20 13:55:57 2016

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Geert G

this only works from the machine hosting the databases

after the set oracle_home

set path=%oracle_home\bin;%path%
this to ensure sqlnet tries to use the oracle home of that database first

or did you set env var TNS_ADMIN to point to a location ?

in services.msc check if the service OracleMMUI is running ?

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chalie001

ASKER

set oracle_sid=sid

Qlemo

Geert showed that setting first and in detail, i just repeated / clarified.

Geert G

qlemo,
thanx, points suck, so why should i get any ?