Monday, May 21, 2007

Oracle instant client, Ubuntu, and ORA-12154

I've been pulling my f'ing hair out for a couple hours trying to figure out why I couldn't connect to our Oracle databases from an installation of Oracle's instant client for Linux on Ubuntu 7.04 (although this should apply to other installations). No matter what environment variables I set or where I placed tnsnames.ora I would always get a ORA-12154 could not resolve service name error. Here was the problem (thanks to Rajesh Jayaprakash's Linux tricks and how to's): the tnsnames.ora and the sqlnet.ora files were created on Windows and had the extra carriage returns in them. Removing the carriage returns solved the problem.

How f'ing stupid. For those that want quick installation instructions assuming you have the installation files on the root of a mounted CDROM:

sudo -s
mkdir /opt/oracle
cd /opt/oracle
unzip /media/cdrom/instanclient-basic- (etc.version.blah.blah)
unzip /media/cdrom/instanclient-sdk- (etc.version.blah.blah)
unzip /media/cdrom/instanclient-sqlplus- (etc.version.blah.blah) optional
ln -s instantclient_10_2 client
cd client
ln -s libclntsh.so.10.1 libclntsh.so
ln -s libocci.so.10.1 libocci.so
tr -d '\r' < /media/cdrom/tnsnames.ora > /etc/tnsnames.ora
tr -d '\r' < /media/cdrom/sqlnet.ora > /etc/sqlnet.ora

To use SQLplus from a shell you need to add the ORACLE_HOME=/opt/oracle variable and add /opt/oracle/client to variable LD_LIBRARY_PATH to the /etc/bash.bashrc (for example).

No comments: