How do you connect to a remote Oracle DB using DBI

Ronnie has asked for the wisdom of the Perl Monks concerning the following question:

As part of a cloning exercise I need to be able to connect to a database on a remote server.(This is to extract the trace file so that we can edit it and automate the whole cloning process using a single Perl script) The only problem I'm having with this is the remote connection. I've edited the Oracle TNSNAMES.ora file locally and can connect to the remote box using SQLPLUS manually. When I try and use DBI within a Perl script I get the dreaded -

        Can't connect to ihr-live :: ORA-12154: TNS:could not resolve 
+service name (DBD ERROR: OCIServerAttach)
[download]

The TNSNAMES entry for the remote box looks like -

ihr-live =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL= TCP)(Host= skirlie)(port= 1521))
    (CONNECT_DATA = (SID = IHRLIVE))
  )
[download]

The connection code that I'm using is -

$dbh = DBI->connect('dbi:Oracle:ihr-live@ihr-live',"$uname","$password
+", {
               PrintError => 0,
               RaiseError => 0
               }
[download]

The Oracle database is called ihr-live and I presume that the hostname is taken from the ihr-live alias in the TNSNAMES.ora file. As you can see from this file the remote server is called skirlie and the port being used is the default 1521. I hope I've made a simple mistake can someone point me in the right direction please?

Comment on How do you connect to a remote Oracle DB using DBI Select or Download Code

Replies are listed 'Best First'.
Re: How do you connect to a remote Oracle DB using DBI by Sinistral (Monsignor) on Apr 15, 2008 at 13:36 UTC
The first thing you want to do is to verify that you can connect using SQLPlus. It's a an invalualble tool for allowing you to check if the database is allowing remote connections At a UNIX shell prompt: `$ORACLE_HOME/bin/sqlplus uname@ihr-live/password` [download] where $ORACLE_HOME* is an environment variable that should already be set to the top level Oracle directory, uname is the actual Oracle database user you want, and password is the Oracle database password for that user If that checks out, you can use this as code: `$ENV{'ORACLE_HOME'} = '/path/to/oracle'; $ENV{'ORACLE_SID'} = 'IHRLIVE'; $dbh = DBI->connect('DBI:Oracle:', $uname, $password, { PrintError => 0, RaiseError => 0 });` [download]	[reply] [d/l] [select]
Re: How do you connect to a remote Oracle DB using DBI by stiller (Friar) on Apr 15, 2008 at 11:39 UTC
Try: `'dbi:Oracle:IHRLIVE', $uname, $password, ...` or `'dbi:Oracle:IHRLIVE.WORLD', $uname, $password, ...` to match with SID=IHRLIVE in TNSNAMES hth	[reply] [d/l] [select]
Re^2: How do you connect to a remote Oracle DB using DBI by Ronnie (Scribe) on Apr 15, 2008 at 12:01 UTC
Thanks but I tried that and got the same result.	[reply]
Re^3: How do you connect to a remote Oracle DB using DBI by stiller (Friar) on Apr 15, 2008 at 12:03 UTC
I should have mentioned I keep messing with these... Have you also tried `'dbi:Oracle:ihr-live.WORLD'`?	[reply] [d/l]
Re^4: How do you connect to a remote Oracle DB using DBI by Ronnie (Scribe) on Apr 15, 2008 at 12:10 UTC
Re^5: How do you connect to a remote Oracle DB using DBI by stiller (Friar) on Apr 15, 2008 at 12:16 UTC
Re: How do you connect to a remote Oracle DB using DBI by olus (Curate) on Apr 15, 2008 at 13:59 UTC
From that error message I would first look at your `tnsnames.ora` file before getting into trial and error with the connect string on the script. Here follow a few thoughts: Does the machine where you run the script know that `skirlie` host? Do you have success when you issue the command `tnsping ihrlive`? You shouldn't beat your head against the wall changing your script until you get this working. Is `IHRLIVE` the instance identifier or the database name? If the later then you should use `SERVICE_NAME=IHRLIVE` instead of the SID.	[reply] [d/l] [select]
Re: How do you connect to a remote Oracle DB using DBI by dvryaboy (Sexton) on Apr 15, 2008 at 14:16 UTC
make sure your TNS_ADMIN environment variable is pointing to the directory that contains the tnsnames.ora file. Also try replacing "skirlie" with the IP address of the machine. Capitalization does not matter.	[reply]