Solved

ORA-12560: TNS:protocol adapter error

Posted on 2016-11-20
8
349 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 38

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 70

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 38

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 70

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 70

Expert Comment

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

Expert Comment

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

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

624 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