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
Thank you very much for this excellent solution. It helped me to solve my problem.
ReplyDeleteThanks mate,much appreciated!! it helped me - Guru
ReplyDelete