Problem Connecting w Perl/DBD::Oracle as SYSDBA

[J R Longwill]

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 qwora_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 :^)

 

[James Willmore]

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.

Have you used the DBI 'trace' method? This method captures the
"conversation" between the script and the database. What you have
above is, well, and indication of a problem, but not too much else.
Plus, the above is really nothing more than a traceback. It's telling
you the connection failed - which you already knew (nice that the
script let's us know the obvious ).

When ever I've had an issue like this, the 'trace' method _usually_
points out what's going on for me. OTOH, my experience with Oracle is
_very_ limited. I installed a "starter" version (from some book I
picked up about 2 years ago) on my Linux box, played around with it a
little, then put it aside :-( Maybe someone else has other ideas.

HTH

Jim
(jwillmore _at_ adelphia _dot_ net)

 

[Ron Reidy]

Can you (the same OS user) connect as sysdba via SQL*Plus to instance BB?

Is your Oracle environment correct (I see you set ORACLE_SID, but what
about ORACLE_HOME, etc.)?

[snip]

use DBI;
use DBD::Oracle qwora_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:

Did you step through using the debugger?

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.

It is not the syntax.

 

[J R Longwill]

To follow up..

I have just stepped through the program using the debugger, in scenarios
both using database AA and BB.. but still have not discerned any
different behavior. I am going to try it again with different
breakpoints to get better detail.

Also.. I would note that the environment vars all appear to be correct..
including the ORACLE_HOME, ORACLE_BASE vars. I had checked this with
some code placed in the program which dumps out the environment vars.
The vars are the same w/ databases AA and BB, except for ORACLE_SID of
course.

I am going to try some other ideas as suggested, including the DBI trace
method..

--Jim :^)

 

[J R Longwill]

I am now happy to report that this problem has been solved!..

When trying out the suggestion to trace the program w/ the DBI trace
utility there was no obvious reason for the failure other than that
Oracle itself was returning an "undef" value to the perl variable in the
case of database BB, whereas it had a hash pointer (to some legit
connection address) in the case of the properly-working AA instance.

So, looking to the Oracle system again, I noted the suggestion from
'ezra' about looking to the REMOTE_LOGIN_PASSWORD oracle variable and
re-creating the orapw file.

Indeed, we did have the var:
remote_login_passwordfile='EXCLUSIVE'
set correctly in both init.ora files for the instances, and we had an "
orapwaa" and "orapwbb" file for the instances. I went ahead and
re-created the "orapwbb" instance file and this *did* resolve the
problem. Now.. a sysdba connection can be made on instance bb both from
within the perl program, and interactively (whereas before it only
worked interactively). Something must have been "wrong" with the old
orapwbb file.

So, many thanks to all; and particularly "ezra" for this assistance!

--Jim :^)

 

[Joel Garry]

Can you (the same OS user) connect as sysdba via SQL*Plus to instance BB?

Is your Oracle environment correct (I see you set ORACLE_SID, but what
about ORACLE_HOME, etc.)?

And more in that vein: orapwd generated properly for each db, and
with proper ownership/protection? You aren't trying to be a root dba?
init.ora variables the same (except where necessary)? Can you
connect as a regular user in sqlplus and then connect as sysdba?
You've checked for duplicate entries in tnsnames.ora? Try changing
the sys password?

jg