Solved

ORA-12560: TNS:protocol adapter error

Posted on 2016-11-20
8
172 Views
Last Modified: 2016-11-28
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
0
Comment
Question by:chalie001
  • 3
  • 3
  • 2
8 Comments
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41894616
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
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 41894618
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
0
 

Author Comment

by:chalie001
ID: 41894736
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
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41894760
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 ?
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 41894840
Geert's last comment is missing a percent:
set path=%oracle_home%\bin;%path%

Open in new window

You should only need to make sure ORACLE_HOME and ORACLE_SID are properly set, then try again to connect with
sqlplus / as sysdba

Open in new window

As said, this has to be (a) locally on the server, (b) using the server instance installation data (on D:). Your command shows you are running this from a client dir on C:.
0
 

Author Closing Comment

by:chalie001
ID: 41895039
set oracle_sid=sid
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 41895093
Geert showed that setting first and in detail, i just repeated / clarified.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41904379
qlemo,
thanx, points suck, so why should i get any ?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Read about achieving the basic levels of HRIS security in the workplace.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question