ORA-12560: TNS:protocol adaptor error
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.
19 Answers 19
- Go to the windows machine that hosts the Oracle database server
- Go to Start -> Run -> Services.msc in Windows. Locate OracleService (here OracleServiceORCL ) and click on Start to start the oracle database service (if not already running)
- Once it is up and running, from the command prompt run the following:
I use Oracle 11g on Windows 8.1 . Before all, I set ORACLE_SID . sqlplus works, no problem, then I close it. I reopen, I try many times, but not working. Even, when service is running, sqlplus doesn’t work.
I am using windows 8 and oracle 12c. Instead of «OracleServiceORCL» i have found «OracleServiceORACLE12C». I have just started it and i have to not run the command. Ur answer is very useful.
In my case I didn’t have an OracleService (OracleServiceORCL) in Windows Services.msc as described in Bharathi’s answer.
and then the OracleService called OracleServiceORCL just showed up and got started in Services.msc. Really nice.
You really saved me, I stuck in this issue for months. Since I have vpn I was able to work on the server. I really wanted to configure on local you helped me. Thanks
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.
- Set Oracle SID C:>set oracle_sid=ORCL
- Now run Net start command. C:>net start oracleserviceORCL
As Tom also suggests here asktom.oracle.com/pls/apex/…. In 2018, this would apply to your docker container, virtualbox image, etc.
from command console, if you get this error you can avoid it by typing
SQL> conn user/pass @host:port/service Add to the enviroment vars the following varibale and value to identify the place of the tnsnames.ora file:
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:
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 After searching alot got a simple way to solve it. Just follow the steps.
- Check status of your listener.
- open command prompt and type lsnrctl status
- You will get no listener.
- Open that file and change the host parameter with you computer name
- 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.
- 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.
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.
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.
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:\oracleFor 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