APEXtras

a development team dedicated to Oracle APEX

SQLPLUS fails to start with error ORA-12560 or ORA-12557

with 2 comments

SQL*Plus would not run on one of our Windows Oracle/APEX servers, although it was running fine on all the other apparently identical servers. ORACLE_HOME and ORACLE_SID were correctly set.

We were getting one or other of these errors:
ORA-12560: TNS:protocol adapter error
ORA-12557: TNS:protocol adapter not loadable

The problem was the Windows “path” environment variable. The \bin directory associated with each of the two Oracle homes was correctly in the path, but in the wrong order. The path associated with the Oracle/Apache HTTP server preceeded the path associated with the Oracle database.

Ie we had: path=C:\oracle\product\10.2.0\http_1\bin;C:\oracle\product\10.2.0\db_1\bin
when we should have had: path=C:\oracle\product\10.2.0\db_1\bin;C:\oracle\product\10.2.0\http_1\bin
(ignoring all the other non-Oracle elements of the path).

Presumably SQL*Plus looks for the configuration files LISTENER.ORA, SQLNET.ORA and TNSNAMES.ORA in the first Oracle home it finds.

With the path correctly set, SQL*Plus would start correctly without environment variables ORACLE_HOME or ORACLE_SID explicitly set – probably because these were also set in the registry keys

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1\ORACLE_SID
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1\ORACLE_HOME

Software Versions:
Oracle 10.2
SQL*Plus 10.1.0.3.0
Windows Server 2003

Written by Roger

March 26th, 2009 at 10:11 am

Posted in General

Tagged with , ,

2 Responses to 'SQLPLUS fails to start with error ORA-12560 or ORA-12557'

Subscribe to comments with RSS or TrackBack to 'SQLPLUS fails to start with error ORA-12560 or ORA-12557'.

  1. Great Work! THX
    this solution worked for me, got 4 DBs on the same machine and some webservers..

    DigitalD

    9 Aug 10 at 15:44

  2. Thanks, this solution worked

    For me this problem araised when I installed Oracle 10.2
    and after that I installed OWB 11.2

    I found there is no oracle_home and created environment variables but this helped me resolved the issue

    Hemanth

    28 Oct 11 at 10:58

Leave a Reply