[sql] ORA-12560: TNS:protocol adaptor error

enter image description here

I Google[d] for this error ORA-12560: TNS:protocol adaptor error but not able to find the actual reason and how to solve this error ?

Can anyone tell me a perfect solution to solve login problem.

This question is related to sql oracle oracle9i ora-12560

The answer is


I try 2 option:

  1. You change service OracleService in Service Tab -> Running
  2. Login with cmd command: sqlplus user_name/pass_word@orcl12C Note: orcle12c -> name of OracleService name run in you laptop

After searching alot got a simple way to solve it. Just follow the steps.

  1. Check status of your listener.
    1. open command prompt and type lsnrctl status
    2. You will get no listener.
  2. Now open listener.ora file which is present in following directory: C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN

    1. Open that file and change the host parameter with you computer name
    2. You can get your computer name by right click on My Computer and check you computer name, and replace host parameter with your computer name as follows:

      LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = Electron-PC)(PORT = 1521) ) ) )

      So here you can observe HOST = Electron-PC, which is my computer name.

    3. Save the listener.ora file and again return to cammand propt

    3.Type the following in command prompt lsnrctl start

This will start the OracleTNSListner.

you can check it in the service by opening services tab of Task Manager. if not started automatically you can start it.

Just this much and you are ready to work again on oracle.

Best of Luck.


It really has worked on my machine. But instead of OracleServiceORCL I found OracleServiceXE.


You need to tell SQLPlus which database you want to log on to. Host String needs to be either a connection string or an alias configured in your TNSNames.ora file.


Quite often this means that the listener hasn't started. Check the Services panel.

On Windows (as you are) another common cause is that the ORACLE_SID is not defined in the registry. Either edit the registry or set the ORACLE_SID in a CMD box. (Because you want to run sqlplusw.exe I suggest you edit the registry.)


I have solved the problem the easy way. My oracle was running just fine in the past. After I installed MS SQL Server was when I noticed this problem. I just uninstalled MS SQL Server on my machine then the problem was gone. Make sure you restart your computer after that. Now I can connect to Oracle database through SQLPlus again. My guess is that there's some conflict between the two. Hope this helps.


In my case I didn't have an OracleService (OracleServiceORCL) in Windows Services.msc as described in Bharathi's answer.

I executed this command:

C:\> ORADIM -NEW -SID ORCL

and then the OracleService called OracleServiceORCL just showed up and got started in Services.msc. Really nice.


Source: https://forums.oracle.com/forums/message.jspa?messageID=4044655#4044655


In my case, (ORA-12560: TNS protocol adapter error)Issue cause of database connection issue like database, user name and password.

Once you got the issue. Initially you have to check connection details, after check the oracle service and further more.

I missed some connection details, So only i got TNS protocol adapter error, I will changed the connection details, It would be working fine.


from command console, if you get this error you can avoid it by typing

c:\> sqlplus /nolog

then you can connect

SQL> conn user/pass @host:port/service

Seems like database is not up. It might be due to restarting machine and the instance is not set to autostart and it so not started munually after starting from services Screen.

Just goto Command prompt

  1. Set Oracle SID C:>set oracle_sid=ORCL

  2. Now run Net start command. C:>net start oracleserviceORCL


If none the above work, then try this : Modify the LISTENER.ora (mine is found in : oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora) ==> add a custom listener that points to your database(SID), example my SID is XZ0301, so :

## Base XZ03001

SID_LIST_LISTENER_XZ03001=(SID_LIST=(SID_DESC=(ORACLE_HOME =
E:\oracle\product\11.2.0\dbhome_1)(SID_NAME= XZ03001)))

LISTENER_XZ03001=(DESCRIPTION_LIST=(ADDRESS=(PROTOCOL =
TCP)(HOST=MyComputerName)(PORT= 1521)))

DIAG_ADR_ENABLED_LISTENER_XZ03001=ON

ADR_BASE_LISTENER_XZ03001=E:\oracle

Restart your machine

For Windows 7, use the following to modify the LISTENER.ora: - Go to Start > All Programs > Accessories - Right click Notepad and then click Run as Administrator . - File>open and navigate to the tnsnames.ora file. - Make the changes then it should allow you to save


Just to add up, follow the screenshot and choose local account to start if not selected. Then start the service.

enter image description here


Add to the enviroment vars the following varibale and value to identify the place of the tnsnames.ora file:

TNS_ADMIN

C:\oracle\product\10.2.0\client_1\network\admin


In my case (for OracleExpress) the service was running, but I got this issue when trying to access the database via sqlplus without connection identifier:

sqlplus sys/mypassword as sysdba  

To make it work I needed to add the connection identifier (XE for Oracle Express), so following command worked ok:

sqlplus sys/mypassword@XE as sysdba

If you still get ORA-12560, make sure you can ping the XE service. Use:

tnsping XE

And you should get OK message along with full connection string (tnsping command is located in oracle's installation dir: [oracle express installation dir]\app\oracle\product\11.2.0\server\bin). If you can not ping make sure your tnsnames.ora file is reachable for sqlplus. You might need to set TNS_ADMIN environment variable pointing to your ADMIN directory, where the file is located, for example:

TNS_ADMIN=[oracle express installation dir]\app\oracle\product\11.2.0\server\network\ADMIN

Another possible solution that just worked for me...considering I was using my local login as the dba permissions.

Follow the steps to get to Services. Right click on the instance and go to 'Log On'? (might not be the name but it's one of the tabs containing permissions). Change the settings to use LOCAL.


Flow the flowing steps :

  1. Edit your listener.ora and tnsnames.ora file in $Oracle_home\product\11.2.0\client_1\NETWORK\ADMIN location

    a. add listener.ora file

    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    

    )

ADR_BASE_LISTENER = C: [here c is oralce home directory]

b. add in tnsnames.ora file

    SCHEMADEV =
            (DESCRIPTION =
                     (ADDRESS_LIST =
                     (ADDRESS = (PROTOCOL = TCP)(HOST = dabase_ip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SCHEMADEV)
    )
  )
  1. Open command prompt and type
    sqlplus username/passowrd@oracle_connection_alias

Example :
username : your_database_username
password : Your_database_password
oracle_connection_alias : SCHEMADEV for above example.


ORA-12560: TNS:erro de adaptador de protocolo

  1. set Environment Variables: ORACLE_BASE, ORACLE_HOME, ORACLE_SID
  2. make sure your user is part of ORACLE_GROUP_NAME (Windows)
  3. make sure the file ORACLE_HOME/network/admin/sqlnet.ora is: SQLNET.AUTHENTICATION_SERVICES = (NTS)
  4. (Windows) Be carefull when you add a new Oracle client: adding a new path to the PATH env. variable can mess things up. The first entry in this variable makes a difference: certify that the sqlplus executable in the ORACLE_HOME (ORACLE_HOME/bin) comes first in the PATH env. variable.

I had "ORA-12560: TNS:protocol adaptor error" problem, and I googled it for 2 hours for not paying attention to details. I opened command prompt and then I had this:

C:\Users\Frodo>set oracle_sid=<DB name>

... while it should be lie this:

C:\>set oracle_sid=<DB name>

C:> should be instead of C:\Users\Frodo> - that was my problem; so this worked:

C:\Users\Frodo> cd c:
C:\>set oracle_sid=<DB name>
C:\>exp ........

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to oracle9i

ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client How to select only 1 row from oracle sql? ORA-01008: not all variables bound. They are bound Oracle PL/SQL - How to create a simple array variable? ORA-12560: TNS:protocol adaptor error ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function) UTL_FILE.FOPEN() procedure not accepting path for directory? Get a list of all functions and procedures in an Oracle database Best way to do multi-row insert in Oracle?

Examples related to ora-12560

ORA-12560: TNS:protocol adaptor error