Solved

ORA-12560: TNS:protocol adapter error

Posted on 2016-11-20
8
50 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This article describes some very basic things about SQL Server filegroups.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 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

12 Experts available now in Live!

Get 1:1 Help Now