APEXtras

a development team dedicated to Oracle APEX

Posts Tagged ‘SQLPLUS

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

leave a comment »

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

26 March, 2009 at 10:11 am

Posted in General

Tagged with , ,