Friday, August 15, 2008

ORA-12560: TNS:protocol adapter error on windows machine while starting oracle

Error Description
On my windows server machine whenever I try to logon to database as a sysdba user it fails with the message ORA-12560.
C:\Documents and Settings\Oracle>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 16 11:01:58 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-12560: TNS:protocol adapter error

Cause of The Error
Oracle classify this as a 'generic protocol adapter error'. In my experience while practice the happening of this error oracle indicates that Oracle client does not know what instance to connect to or what TNS alias to use. This may be due to incorrect ORACLE_SID environmental value setting or Oracle Service is not started yet.

Note that you will hit similar error if the Oracle client software home was set as the first entry in the PATH and not the Oracle database software home. If client home is set as first path then the client version of sqlplus was used. In this case, the connection as SYSDBA was unable to make a connection using the client software because a password file was not in use and this is deemed as a remote connection. Connection as a user other than SYSTEM/SYS worked because a password file from a remote connection is not needed for this type of user.

Solution of The Problem
1)If you have server running then while connecting to database as a sysdba user ensure that you have set properly your ORACLE_SID variable. Try explicitly set your envionmental variable on windows machine by,
>set ORACLE_SID=db_name

2)Ensure that you have right services running on your database. You can check whether oracle is running or not by right click on taskbar then select task manager and see whether oracle.exe is running or not.

If it is not running then it is likely to get ORA-12560: TNS:protocol adapter error on your windows machine. To avoid this error you have to run the service. You can do it from right click on my computer> select manage> Click on services and Applications> Double click on services> find the service name OracleSERVICE$ORACLE_SID. If your database name is ARJU then find the service name OracleSERVICEARJU and start it.

You can start the service also by command prompt,
C:>sc start OracleSERVICE$ORACLE_SID

Using oradim by,
C:>oradim -startup -sid $ORACLE_SID

After starting try to connect to database as sysdba and likely you will be able to connect to your database.

C:\Documents and Settings\Oracle>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 16 11:03:16 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

3)If you are connecting to a remote database then try easy naming method. Don't try to connect by tnsnames alias. Easy naming method is described in http://arjudba.blogspot.com/2008/06/ora-12514-tnslistener-does-not.html

4)Ensure that you are using correct tnsnames.ora and you have correct settings in your tnsnames alias. Whether you have correct tnsnames entry or not you can check that by >tnsping hostname

On unix machine, if you see tnsnames.ora is most likely accurate then at the Unix prompt, echo the TNS_ADMIN environment variable.

% echo $TNS_ADMIN

•If nothing is returned, try to set the TNS_ADMIN environment variable to explicitly point to the location of the TNSNAMES.ORA file.

In C Shell, the syntax is:
% setenv TNS_ADMIN full_path_of_tnsnames.ora_file

In K Shell or bash, the syntax is:
% export TNS_ADMIN=full_path_of_tnsnames.ora_file

On windows machine try setting the environment variable 'LOCAL' to the required connection alias instead. In windows machine environmental variable is set by set keyword.

Now try to connect to database.

5)If you use DHCP to assign IP of the server then try by putting a static IP to the database server.

Important point if you installed Oracle client and Server on same machine
6) Use path environmental variable to check the settings of ORACLE_HOME on windows. An example output,
C:\Users\Administrator>path
PATH=E:\app\Administrator\product\11.2.0\client_1;E:\app\Administrator\product\11.2.0\dbhome_1\bin;C:\Program Files\HP\N
CU;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files
(x86)\The Open Group\WMI Mapper\bin;C:\WINDOWS\SYSTEM32;
Here we see client home is set before server home path and so local SYSDBA connection will fail.
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 28 12:59:01 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12560: TNS:protocol adapter error
Now, lets set ORACLE_HOME environmental variable to server binary files.
C:\Users\Administrator>set ORACLE_HOME=e:\app\Administrator\product\11.2.0\dbhome_1

C:\Users\Administrator>set PATH=%ORACLE_HOME%;%ORACLE_HOME%\bin;%PATH%

C:\Users\Administrator>path
PATH=e:\app\Administrator\product\11.2.0\dbhome_1;e:\app\Administrator\product\11.2.0\dbhome_1\bin;E:\app\Administrator\
product\11.2.0\client_1;E:\app\Administrator\product\11.2.0\dbhome_1\bin;C:\Program Files\HP\NCU;C:\Windows\system32;C:\
Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\The Open Group\WMI M
apper\bin;C:\WINDOWS\SYSTEM32;
and now it is working fine.
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 28 13:01:26 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Related Documents
http://arjudba.blogspot.com/2008/06/ora-12154-tnscould-not-resolve-connect.html
http://arjudba.blogspot.com/2008/06/ora-12514-tnslistener-does-not.html

12 comments:

sadiq said...
This comment has been removed by the author.
sadiq said...

can we start our oracle database 10g through sqlplus without starting service through OS in windows xp

Arju said...

No. On windows you need service to be started. Though there is various ways to start the service which are discussed in this post and for more information you can search within my blog.

Abida Siddika said...

Thank you very much for this excellent solution. It helped me to solve my problem.

Anonymous said...

Thanks mate,much appreciated!! it helped me - Guru

tromance said...

Thank you very much! This problem has plagued me for a long time, and I had chalked it up to Windowns Terminal Services causing the problem. Just setting LOCAL= fixed it.

Anonymous said...

Thank you very much.
I solved my problem with this article tonight.:) and i'm happy now my friend.:) thank you.

Anonymous said...

Thanks a lot Friend,
Your this posting helped me a lot.
And i resolved the same TNS issue by following your blog.
Thanks a lot.
U are doing a great work.

Sudhir Nayak
Oracle DBA

jaiforbyss said...

thanks arju for the wonderful help

Anonymous said...

thanks arju for the wonderful doc and which really helped me and save developer time.

FjCotrina said...

Thank you, it's so fine.

Thanks !!!

Anonymous said...

Thanks really I´ve been wandered through lots of blogs and this was my solution.. bad path! this really helped me since I dont have experience with oracle