?
Solved

ORA - 12560: TNS: Protocol Adapter error

Posted on 2007-07-29
13
Medium Priority
?
3,019 Views
Last Modified: 2012-08-14
OS - Windows XP
Oracle version - 10g
Trying to logon to a db using command shell. As I type sqlPlus in the C:\ it asks for username and then password. I am using System user with correct password. Receiving the folowing error:
ORA - 12560 TNS: Protocol Adapter error.
0
Comment
Question by:Lville
12 Comments
 
LVL 4

Expert Comment

by:jtrifts
ID: 19591546
I assume the database is up, mounted and listeners are running...(confirm).

http://www.cryer.co.uk/brian/oracle/ORA12560.htm

According to the above link, ORacle gets confused and raises a generic error as it doesn't know what SID or instance to connect to.

Can you post your entry from your TNSNAMES.ORA file?

Regards,
JT
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 19594850
For username enter
<username>@<TNSNAME FOR THE DATA BASE>
0
 

Author Comment

by:Lville
ID: 19596758
JT,
Might be that was the reason. With the installation of Oracle 10g the defaulot database ORCL was created. I could login to the that db. I created a new database using the DBCA. After that I was getting the error I mentioned before. Now I deleted the 2nd database. It is still not working. I checked the TNSNAMES.ORA file. There is everything generic, nothing related to my database.

Thanks
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:Lville
ID: 19596794
JT,
I found the right  TNSNames.ora file.
Here is the entry for my default db ORCL:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
0
 

Expert Comment

by:mxwx
ID: 19598476
can you tnsping your oracle instance? <tnsping orcl>
if so, can you connect to the instance with some other users
have you tried to connect internal (as sysdba)
do you have a firewall up and running?
try to disable the firewall and check again
do you have made changes in your host file <system32/drivers/etc/hosts>
can you post the output of <route -print>
0
 

Author Comment

by:Lville
ID: 19599983
I was very much frustrated with the problem. All of a sudden I tryed with the user Sysman (system and sys failed to login) and was accepted. After that I applied Connect system/password in the sql prompt, I was connected as the user SYSTEM. It seemed to be strange. This morning I saw comment from mxwx and found a hint about trying different user.
Can anybody tell me why Sysman could login but system and Sys user couldn't?
0
 

Expert Comment

by:mxwx
ID: 19600455
If you can connect with some other user you instance should be running. For further assistance it would be polite and helpful if you try to do what you ar asked to do. It makes it easier to help you mor precisely. Try to connect with
  CONNECT / AS SYSDBA

If that works you should try to connect with
  CONNECT SYS/YOURWASSWORD AS SYSDBA

Both is nearly the same and gives you admininstrator access to the db. After that you should try to connect to the db with something like

CONNECT SYSTEM/YOURPASSWORD@ORCL (substitute ORCL with your oracle SID)
0
 

Author Comment

by:Lville
ID: 19602221
I can connect as sysdba only after I connect as SYSMAN. I tried SYS/password at the begining didn't work. Still I can't open SQL editor using SYSTEM or SYS account. I have to open as SYSMAN and from their I can use connect statement to connect using SYSTEM or SYS account.
Also using SQL editor login window I still can't login. It still produces the ORA 12560: TNS:Protocol adapter error.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 19862070
First:  username and password are really ignored if you are in the DBA group.  As soon as Oracle sees 'AS SYSDBA' it connects as SYS.  So if " / as sysdba " works, so will " fred/flintstone as sysdba " even if no fred user exists.

The 12560 more than likely is caused by an improper tnsnames.ora file config.  I see you mentioned a 2nd database but no mention of that being added to the tnsnames file.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 100 total points
ID: 19866512
1. Turn off the default firewall of XP if any. Anbtiviruses also.
If you have installed Oracle with running firewall I will
strongly recommend to drop the installation and make a new one.

2. Check if the service is registered:

c:\> lsnrctl services

If not fix it so:

For STATIC registration of the Oracle service to the Listener:

Go to the remote 10g installation.
In ...\network\admin open listener.ora

see

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Ora10g)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
  )

Add the entry
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
with regard to the particular Oracle Home and SID Name.

Restart the listener:
c:>lsnrctl stop
c:>lsnctl start

0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 100 total points
ID: 19871887
Two things to try:
1. Run: regedit, go to: HKey_Local_Machine,Software,Oracle and create a "LOCAL" entry.  (The "type" should be: "REG_SZ".)  Set that to the value from your tnsnames.ora file for your database instance.
2. Or, when you enter the username, include "@[tns_alias]".  For example, if the alias in your tnsnames.ora file is: "mydb", then at the "Username:" prompt, enter: "system@mydb", then enter the password for "system" when prompted.

Note: the usernames and passwords for Oracle are *NOT* case-sensitive, so you can enter them in upper, lower, or mixed-case.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20228849
Forced accept.

Computer101
EE Admin
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

850 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