Solved

ORA-12560: TNS:protocol adapter error

Posted on 2016-11-20
8
85 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 68

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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 68

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 68

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now