Problem Connecting w Perl/DBD::Oracle as SYSDBA

From: J R Longwill <longwill_at_psmfc.org>
Date: Tue, 04 Nov 2003 14:09:59 -0800
Message-ID: <bo9807$97a$1@nntp.psmfc.org>

We have two Oracle instances running on one server (say, instance AA and instance BB). It is a Linux Red-Hat Advanced Server 2.1 system running Oracle 9.2.0.4.

I've got a perl program which is trying to connect to the databases as user sys, as sysdba.

Some excerpts from the perl program are here..

---

...

use DBI;
use DBD::Oracle qw(:ora_session_modes);
use diagnostics;
use strict;

my $db_name     = "AA";                 # or "BB"
my $user        = "sys";
my $passwd      = "pswd123";

$ENV{ORACLE_SID} = $db_name;

...

my $dbh = DBI->connect("dbi:Oracle:".$db_name ,$user ,$passwd, {

     ora_session_mode => ORA_SYSDBA,
     RaiseError => 1,
     AutoCommit => 0 }

)

   or die "Can't open $db_name database: $DBI::errstr";
$dbh->commit();

... <do some SELECT statements, etc...>

$sth->finish();
$dbh->disconnect;

...

---

The crazy thing is, my program (bk3.pl) WORKS for one database (AA) but NOT for the other (database BB).

I have carefully examined the differences between the two database/instances and can find none of consequence.

The error I get when running this for database BB seems to be occuring in the DBI library, at the exact point of connection to the database. The error is:

         Uncaught exception from user code:
         DBI connect('bb','sys',...) failed:  at ./bk3.pl line 80
         Carp::croak('DBI connect(\'bb\',\'sys\',...) failed: ') called 
  at /usr/lib/perl5/site_perl/5.6.1/i386-linux/DBI.pm line 579
         DBI::__ANON__() called at 
/usr/lib/perl5/site_perl/5.6.1/i386-linux/DBI.pm line 629
         DBI::connect('DBI', 'dbi:Oracle:bb', 'sys', 'pswd123', 

'HASH(0x8335710)') called at ./bk3.pl line 80

The main error seems to be line 579 of
/usr/lib/perl5/site_perl/5.6.1/i386-linux/DBI.pm but I can't be sure.

I have tried many variations of the syntax on the connect statement. I have tried making changes to the tnsnames.ora file and restarting the Oracle listener several times. I have tried the alternate syntax which bypasses/ignores the Oracle listener process. But in each scenario, the program often works for instance AA, but never for instance BB.

Note however that the connect DOES work for both AA and BB when I try to connect as a regualr user and NOT as "sys". So..

         Something is wrong when connecting as "sys" on any instance other than "AA" with the sysdba directive:

                 "ora_session_mode => ORA_SYSDBA"
         as obtained from the DBD::Oracle library.

SO.. I'm stumped! Does anyone have some ideas on this particular situation? I am sure that we are using the latest DBD::Oracle library, version 1.14.

Many thanks for your assistance.

--Jim Longwill :^) Received on Tue Nov 04 2003 - 16:09:59 CST