ORA - 12560: TNS: Protocol Adapter error

Posted on 2007-07-29
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.
Question by:Lville
    LVL 4

    Expert Comment

    I assume the database is up, mounted and listeners are running...(confirm).

    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?

    LVL 14

    Expert Comment

    For username enter
    <username>@<TNSNAME FOR THE DATA BASE>

    Author Comment

    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.


    Author Comment

    I found the right  TNSNames.ora file.
    Here is the entry for my default db ORCL:

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

    Expert Comment

    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>

    Author Comment

    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?

    Expert Comment

    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

    If that works you should try to connect with

    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)

    Author Comment

    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.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.
    LVL 47

    Accepted Solution

    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


      (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

    LVL 34

    Assisted Solution

    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.
    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    This post first appeared at Oracleinaction  ( 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…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    761 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