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.

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.

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

2 comments:

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

    ReplyDelete
  2. Thanks mate,much appreciated!! it helped me - Guru

    ReplyDelete