Saturday, December 3, 2011

How to solve ORA-12560: TNS:protocol adapter error

How to solve ORA-12560: TNS:protocol adapter error::

Please note that the following steps THAT I ONLY tested in oracle 11g test database and windows environment and it does not mean that they work as exactly as shown here.. it all depends on what OS one use and what database one use and other validations.. please use your own judgement before you do anything on the database..you only responsible for your actions but no one else...

1. Ensure listener is started
2. All oracle variables configured correctly

In Windows OS:
---------------
C:\Users\Home>set ORACLE_HOME=F:\app\Home2\product\11.2.0\dbhome_1

C:\Users\Home>SET ORACLE_SID=RED

C:\Users\Home>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 3 23:32:18 2011

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

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

C:\Users\Home>oradim -start -sid RED

C:\Users\Home>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 4 00:08:27 2011

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 213913364 bytes
Database Buffers 419430400 bytes
Redo Buffers 5566464 bytes
Database mounted.
Database opened.

SQL> conn sys@red as sysdba
Enter password:
Connected.
SQL>
...
What may be done in UNIX OS:
-----------------------------

please check whether login made with oracle user
please check all the oracle configuration variables are set like ORACLE_SID, ORACLE_HOME, ORACLE_BASE and all...
Ensure listener is up and running (not necessary although if you are not going to append @db name in connect string but just to be sure..

I earlier got this error in UniX boxs but did not copy the output, but i believe these steps may help.. i will post real time output when i see this error in linux... next time..

-- Have a fun in working in oracle technologies .. Cheers...

4 comments:

  1. Hello,

    I followed the instructions but to no avail - do you have any other pointers?

    rah_nad@ntlworld.com

    ReplyDelete
  2. I have 11g. Recently, my sqlPlus gave me an ORA-12514. My corporate application would not connect to the database either. Both of these were working fine.

    I can still connect to "server" databases from my laptop, but my localhost entries (in TNSNames.ora) will not connect.

    I used your method above (startup) and after the database was mounted/opened, I was able to connect to my local database through both sqlPlus and my corporate application. I am wondering if there is something I can do to ensure this happens at startup... Is there something that may have changed in my Oracle installation? Thanks.

    ReplyDelete
  3. In Windows OS:
    ---------------
    SET ORACLE_SID=RED

    what is RED here

    ReplyDelete