Re: Problem Connecting w Perl/DBD::Oracle as SYSDBA

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 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:

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.

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

-- 
Ron Reidy
Oracle DBA