DBI connects to one oracle instance but cannot connect to others

We run Oracle 10g under Linux. There are seven database instances:
prod, tmp1,...,tmp6. The TNSNAMES.ORA file treats all seven instances
equally:

prod =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(COMMUNITY = tcp.world)
(PROTOCOL=tcp)
(HOST=localhost)
(PORT=1521)
)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = prod)
)
)

..... identical segments for the tmp1 - tmp6 instances

I can sqlplus into any instance. I can run a local perl script to
connect to the production instance "prod". I can also run perl
scripts from a remote server to connect remotely to any of these
instances. But my perl script on localhost cannot connect to any of
the tmp instances tmp1...tmp6.

This script works:

use DBI;

$ENV{"ORACLE_HOME"} = "/u01/app/oracle/OraHome_1";
$ENV{"ORACLE_SID"} = "prod";

$odsn="DBI:Oracle:prod";
$ouid="xxxx";
$opwd="xxxx";

$odbh= DBI->connect ($odsn,$ouid,$opwd);

--- works perfectly BUT the identical for the tmp instances DOES NOT
WORK:

use DBI;

$ENV{"ORACLE_HOME"} = "/u01/app/oracle/OraHome_1";
$ENV{"ORACLE_SID"} = "tmp1";

$odsn="DBI:Oracle:tmp1";
$ouid="xxxx";
$opwd="xxxx";

$odbh= DBI->connect ($odsn,$ouid,$opwd);

I get the following error message: TNS:could not resolve the connect
identifier specified

What am I doing wrong????

Thank you for any hint.
Anke

RE: DBI connects to one oracle instance but cannot connect to others

Anke,

In $odsn=3D"DBI:Oracle:prod", "prod" is just a connection string. If =
you
are connecting to local database, try to use: $odsn=3D"DBI:Oracle:",
remember to set the instance that you try to connect: $ENV{ORACLE_SID} =
=3D
'tmp1'

Alan
=20

-----Original Message-----
From: bartona@ohsu.edu [mailto:bartona@ohsu.edu]=20
Sent: Wednesday, June 13, 2007 11:16 AM
To: dbi-users@perl.org
Subject: DBI connects to one oracle instance but cannot connect to
others

We run Oracle 10g under Linux. There are seven database instances:
prod, tmp1,...,tmp6. The TNSNAMES.ORA file treats all seven instances
equally:

prod =3D
(DESCRIPTION=3D
(ADDRESS_LIST=3D
(ADDRESS=3D
(COMMUNITY =3D tcp.world)
(PROTOCOL=3Dtcp)
(HOST=3Dlocalhost)
(PORT=3D1521)
)
)
(CONNECT_DATA =3D
(SERVER =3D DEDICATED)
(SID =3D prod)
)
)

..... identical segments for the tmp1 - tmp6 instances

I can sqlplus into any instance. I can run a local perl script to
connect to the production instance "prod". I can also run perl scripts
from a remote server to connect remotely to any of these instances. But
my perl script on localhost cannot connect to any of the tmp instances
tmp1...tmp6.

This script works:

use DBI;

$ENV{"ORACLE_HOME"} =3D "/u01/app/oracle/OraHome_1"; $ENV{"ORACLE_SID"} =
=3D
"prod";

$odsn=3D"DBI:Oracle:prod";
$ouid=3D"xxxx";
$opwd=3D"xxxx";

$odbh=3D DBI->connect ($odsn,$ouid,$opwd);

--- works perfectly BUT the identical for the tmp instances DOES NOT
WORK:

use DBI;

$ENV{"ORACLE_HOME"} =3D "/u01/app/oracle/OraHome_1"; $ENV{"ORACLE_SID"} =
=3D
"tmp1";

$odsn=3D"DBI:Oracle:tmp1";
$ouid=3D"xxxx";
$opwd=3D"xxxx";

$odbh=3D DBI->connect ($odsn,$ouid,$opwd);

I get the following error message: TNS:could not resolve the connect
identifier specified

What am I doing wrong????

Thank you for any hint.
Anke

Re: DBI connects to one oracle instance but cannot connect to others

try this

use DBI;
$odsn="DBI:Oracle:";
$ouid="xxxx";
$opwd="xxxx";

$odbh= DBI->connect ($odsn,$ouid."@tmp1",$opwd);

Though this most likely has little to do with DBI or DBD:Oracle and more how
you are using your TNSNAME file

----- Original Message -----
From:
To:
Sent: Wednesday, June 13, 2007 11:15 AM
Subject: DBI connects to one oracle instance but cannot connect to others


> We run Oracle 10g under Linux. There are seven database instances:
> prod, tmp1,...,tmp6. The TNSNAMES.ORA file treats all seven instances
> equally:
> (DESCRIPTION=
> (ADDRESS_LIST=
> (ADDRESS=
> (COMMUNITY = tcp.world)
> (PROTOCOL=tcp)
> (HOST=localhost)
> (PORT=1521)
> )
> )
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SID = prod)
> )
> )
>
> .... identical segments for the tmp1 - tmp6 instances
>
> I can sqlplus into any instance. I can run a local perl script to
> connect to the production instance "prod". I can also run perl
> scripts from a remote server to connect remotely to any of these
> instances. But my perl script on localhost cannot connect to any of
> the tmp instances tmp1...tmp6.
>
> This script works:
>
> use DBI;
>
> $ENV{"ORACLE_HOME"} = "/u01/app/oracle/OraHome_1";
> $ENV{"ORACLE_SID"} = "prod";
>
> $odsn="DBI:Oracle:prod";
> $ouid="xxxx";
> $opwd="xxxx";
>
> $odbh= DBI->connect ($odsn,$ouid,$opwd);
>
> --- works perfectly BUT the identical for the tmp instances DOES NOT
> WORK:
>
> use DBI;
>
> $ENV{"ORACLE_HOME"} = "/u01/app/oracle/OraHome_1";
> $ENV{"ORACLE_SID"} = "tmp1";
>
> $odsn="DBI:Oracle:tmp1";
> $ouid="xxxx";
> $opwd="xxxx";
>
> $odbh= DBI->connect ($odsn,$ouid,$opwd);
>
> I get the following error message: TNS:could not resolve the connect
> identifier specified
>
> What am I doing wrong????
>
> Thank you for any hint.
> Anke
>
>