After vanquishing the map error yesterday, we got to the real problem:

sqlplus user@mydb
SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 30 12:53:53 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
Error while trying to retrieve text for error ORA-12154

When I searched for this error in Metalink and Google, I found references to Pro-C, PHP and VB, but nothing related to SQLPLUS. In a moment of weakness, I decided to run changePerm.sh.

Before running it I did an ls -lR $ORACLE_HOME > before

After running it I did an ls -lR $ORACLE_HOME > after

Then I ran diff before after and discovered 750 changes had been made, including opening permissions to OPatch and other directories that really didn’t seem like they should be open to the world. Oracle did give me fair warning when I started the script.

$> ./changePerm.sh $ORACLE_HOME

——————————————————————————-
Disclaimer: The purpose of this script is to relax permissions on some of the
files in the database Oracle Home so that all clients can access them.
Please note that Oracle Corporation recommends using the most restrictive file
permissions as possible for your given implementation. Running this script
should be done only after considering all security ramifications.
——————————————————————————-

But I was feeling the pressure to “fix it”. I then spent hours changing permissions back to their original setting on most of the directories under $ORACLE_HOME, and along the way I discovered that removing access to $ORACLE_HOME/ldap brought the error back. Aha! Here is my revised list of the minimum permission needed for an Oracle client.

chmod 755 /opt/oracle
chmod 755 $ORACLE_HOME
chmod 755 $ORACLE_HOME/jdbc
chmod 755 $ORACLE_HOME/jdbc/lib
chmod 755 $ORACLE_HOME/ldap
chmod 755 $ORACLE_HOME/ldap/mesg
chmod 644 $ORACLE_HOME/ldap/mesg/*
chmod 755 $ORACLE_HOME/network
chmod 755 $ORACLE_HOME/network/admin
chmod 755 $ORACLE_HOME/sqlplus
chmod 755 $ORACLE_HOME/sqlplus/mesg
chmod 644 $ORACLE_HOME/sqlplus/mesg/sp1us.msb
chmod 755 $ORACLE_HOME/nls
chmod 755 $ORACLE_HOME/nls/data
chmod 644 $ORACLE_HOME/nls/data/lx1boot.nlb
chmod 755 $ORACLE_HOME/oracore
chmod 755 $ORACLE_HOME/oracore/zoneinfo
chmod 644 $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

The really odd thing is that I still have many clients that have the ldap directory locked down, and they are running without problems, even though they are using ldap for name resolution. I don’t get it, but it’s late Friday evening, and I don’t need to get it. I just need to go home!