TNS-12560: TNS:protocol adapter error

TNS-12560: TNS:protocol adapter error

The Problem

Running Oracle 11g database on a Windows platform you are trying to log onto the database using the listener and you receive the following error message:

Fatal NI connect error 12560, connecting to:
(LOCAL=NO)

You decide to try a local connection, which just means that you connect directly to the database without going via the listener. In Windows, you would do it as follows:

    SET ORACLE_SID=TESTDB

    sqlplus "sys/password as sysdba"

This assumes that the database name is TESTDB. When attempting to make the connection in this way it works and you can connect to the database without the 12560 error message.

Next, you check the listener.log file which can be found (by default) in ORACLE_HOME\network\log or if you are running 11g and using a diagnostic destination you might find it in ORACLE_BASE\diag\tnslsnr\’servername’\'listenername’\listener.log and you find the following information:

Time: 12-MAR-2013 09:31:34
Tracing not turned on.
Tns error struct:
ns main err code: 12560

TNS-12560: TNS:protocol adapter error
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
opiodr aborting process unknown ospid (14996) as a result of ORA-609
Mar 12 09:31:34 2013
Stopping background process CJQ0

The Cause

There can be many reasons why you come across this TNS 12560 error message. The reason for this particular case was because I was running the Windows service for the database as a non-local system account so that it could write out to a file share, but I hadn’t changed the listener used to connect to this database to run as a non-local system account.

The Solution

Change the user account that the listener is running as to the same as the one that the database service runs as, restart the listener and this should resolve the issue.

Other Causes of the ORA-12560: TNS:protocol adapter error

Listener Not Started

If your OS is Windows then you should check that the service is started. Here is what happens if you attempt a connection, without using the listener, to a database whose service is not started:

set oracle_sid=TESTDB

sqlplus "sys as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 20 14:47:12 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

ERROR:

ORA-12560: TNS:protocol adapter error

It’s always best to run through the most basic checks first, such as are all the services started for the database and listener, do the configuration files exist, are they correct and have any changes been made recently to any of them.

No Username in Connection String – Using a Variable to Connect?

Another example is that you might not be specifying the a username in your connection string as is the problem in this case:

Enter user-name:

ERROR:

ORA-12560: TNS:protocol adapter error

This was done by just pressing enter on the keyboard when prompted for the username which is obvious to anyone why they are getting the error but if you are passing the username as a variable somewhere in a script to build up the connection string then it might not be so obvious what is happening so bear that in mind if you come across this issue.

Connecting from One DB to Another – ORA-12560

A very similar example is if you are connecting through from one schema to another within SQL Plus and you don’t specify a username:

SQL> connect

Enter user-name:

ERROR:

ORA-12560: TNS:protocol adapter error

Warning: You are no longer connected to ORACLE.

With Variables in Scripts Use SET DEFINE ON

Again, if you are using any kind of variable it is definitely worthwhile checking that the value of it is what you expect it to be. A common cause for variables to not have a value in a SQL script is the SET DEFINE OFF SQL Plus environment setting. Any variable will not be displayed when called using the amerpsand (&) symbol. You should ensure that you SET DEFINE ON before any point where you are using a variable.

I hope that this helps you resolve your TNS-12560 or ORA-12560 error message as I know how much of a pain they can be to see sometimes!

- See more at: http://www.ora00600.com/wordpress/scripts/databaseconfig/tns-12560/#sthash.mGsydXLD.dpuf