Oracle problems in Rails with rake, but not with site

Question

Hi guys,

I'm working on a Rails site that connects to an Oracle database, and though I didn't build the site from scratch, I'm doing maintenance work. The site uses the delayed_jobs plugin to handle some background tasks and I'd like to be able to run rake jobs:work on the development server to periodically process all jobs in the queue (due to the server's configuration, running a daemonized version of the script on the development server isn't an option). However, whenever I try running the command, I get the following classic Oracle error:

error while trying to retrieve text for error ora-12154

Ordinarily, I'd think this would be an authentication problem (e.g. incorrect credentials in database.yml), but the site is up and running fine (and doing lots of database stuff). I've tried adding RAILS_ENV=production as a parameter to rake to force it to run in in the production environment, but got the same error (there are two separate rails installations for the production and development versions of the site, and I've set the "development" and "production" credentials in development's db config file to be identical).

I'm not sure what could be causing this error, and I don't have a ton of experience using Oracle with rails. Any suggestions?

Thanks a lot!

Justin

EDIT (10/26/09): Still can't figure out what's causing the problem here. The app continues to run (and talk to the database) without a problem, but rake keeps throwing DB errors. So does script/console, which shows a prompt but first complains with the same Oracle error message. I'm going to keep looking, but I'm running out of ideas...

EDIT(10/26/09, later): Following the advice of this link, I set both ORACLE_HOME and TNS_ADMIN to point to the directory where tnsnames.ora lives. Just setting ORACLE_HOME had no obvious effect, but now that TNS_ADMIN points to the right place, I've started getting segmentation faults whenever I try to open the console or run rake:

/usr/local/lib/ruby/site_ruby/1.8/oci8.rb:184: [BUG] Segmentation Fault

and get booted unceremoniously back to the prompt. Any further ideas?

Answer 1

The oracle error says the following:

ORA-12154 is generated by the oracle network layer. TNS error message is thrown during the logon process to a database. This error indicates that the communication software in Oracle ( SQL *Net or Net8 ) did not recognize the host/service name specified in the connection parameters. This error almost always indicates a misconfiguration of the oracle tns entries.

Can you connect to your oracle instance using sqlplus or another db tool?

It is odd that the app runs fine though.

Is there an $ORACLE_SID laying around somewhere that could be pointing to a db that doesn't exist?

Answer 2

IN sql server I would probaly run profuiler to see what is actually being sent vice what I think I have set up. I'm sure Oracle aslo has some type of profiling utility. I would try that and see, you may find it isn't using the credentials you tink it is.

Answer 3

Well, as Mike mentioned, ora-12145 means TNS couldn't resolve the database identifier (TNS is Oracle's name-to-database mapping, sorta-kinda like DNS). If you can find your tnsnames.ora file, you can see what databases are configured there and compare that to the database.yml file. The fact that it works as a delayed job but not from the command line is a bit odd, though, and makes me think that perhaps there are some environment variables being set in one context that aren't in the other.

If neither of those pan out, there's a long list of troubleshooting suggestions at http://ora-12154.ora-code.com that are specific to that error code.

Answer 4

Finally got it...turns out that ORACLE_HOME wasn't being correctly set as an environment variable for my user account. Now rake, script/console, etc. are humming along happily.