Hi all,
lately I digged on this. Here is my solution:
1) Standard
#!/usr/local/bin/perl -T
use DBI;
use strict;
use warnings;
delete $ENV{ 'ENV' };
$ENV{ORACLE_SID} = "CAT";
$ENV{ORACLE_HOME} = "/oracle/CAT/817_64";
$ENV{NLS_LANG} = "AMERICAN_AMERICA.WE8DEC";
$ENV{ORA_NLS} = $ENV{ORACLE_HOME} . "/ocommon/nls/admin/data";
$ENV{PATH} = $ENV{ORACLE_HOME} . "/bin:/usr/bin";
$ENV{LD_LIBRARY_PATH} = $ENV{ORACLE_HOME} . "/lib";
my $connect_mode = 2; # 2=SYSDBA
my $dbh = DBI->connect( 'dbi:Oracle:','', '',
{ ora_session_mode => $connect_mode,
PrintError => 1, AutoCommit => 0 } )
or die "Cannot connect: $DBI::errstr\n\n";
print "Connected.\n";
...
2) several instances with different ORACLE_HOMEs
2a) important: $ENV{ORACLE_HOME} must for each instance be set to the
$ORACLE_HOME you compiled your DBD::Oracle with!
2b) you cannot do 'connect internal' via SQL*Net! Use instead:
...
if ( $dbh = DBI->connect( 'dbi:Oracle:',"[EMAIL PROTECTED]", 'passwd',
{ ora_session_mode => $connect_mode,
PrintError => 1, RaiseError => 0, AutoCommit => 0 } ) ) {
$debug >= 1 && print "~~ connected.\n";
}
...
cu,
Christian
----- Original Message -----
From: "Rouquart Wim" <[EMAIL PROTECTED]>
To: "edward pena" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[email protected]>
Sent: Wednesday, March 09, 2005 10:07 AM
Subject: RE: (Fwd) FW: DBI connect as sysdba
In what way doesn't it work consistently and how should it be adapted so it does
? We really need SYSDBA privilege for our script...
Thanx,
Wim.
________________________________
Van: edward pena [mailto:[EMAIL PROTECTED]
Verzonden: di 8/03/2005 18:41
Aan: [EMAIL PROTECTED]; Rouquart Wim; [email protected]
Onderwerp: RE: (Fwd) FW: DBI connect as sysdba
Actually, that does not work consistently.
Edward Pena
Cisco Systems, Inc.
ESA-Glbl-ERP-DW-MFG
RTP, NC 27709-4987
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 08, 2005 12:38 PM
To: [EMAIL PROTECTED]; [email protected]
Subject: Re: (Fwd) FW: DBI connect as sysdba
[EMAIL PROTECTED] jseger]$ perl -MDBD::Oracle -e'print $DBD::Oracle::VERSION
. "\n"'
1.15
[EMAIL PROTECTED] jseger]$ perldoc DBD::Oracle /SYSDBA ...skipping
ora_session_mode
The ora_session_mode attribute can be used to connect with SYSDBA
authorization and SYSOPER authorization. The ORA_SYSDBA and
ORA_SYSOPER constants can be imported using
use DBD::Oracle qw(:ora_session_modes);
This is one case where setting ORACLE_SID may be useful since
con-
necting as SYSDBA or SYSOPER via SQL*Net is frequently disabled
for
security reasons.
Example:
$dsn = "dbi:Oracle:"; # no dbname here
$ENV{ORACLE_SID} = "orcl"; # set ORACLE_SID as needed
delete $ENV{TWO_TASK}; # make sure TWO_TASK isn't set
$dbh = DBI->connect($dsn, "", "", { ora_session_mode =>
ORA_SYSDBA});
It has been reported that this only works if $dsn does not
contain
a SID so that Oracle then uses the value of the ORACLE_SID (not
TWO_TASK) environment variable to connect to a local instance.
Also
the username and password should be empty, and the user executing
the script needs to be part of the dba group or osdba group.
____________________________
Jeff Seger
Fairchild Semiconductor
[EMAIL PROTECTED]
____________________________
Tim Bunce <[EMAIL PROTECTED]>
03/08/2005 12:13 PM
Please respond to Wim.Rouquart; Please respond to dbi-users
To: [email protected]
cc: [EMAIL PROTECTED]
Subject: (Fwd) FW: DBI connect as sysdba
----- Forwarded message from Rouquart Wim <[EMAIL PROTECTED]> -----
Delivered-To: [EMAIL PROTECTED]
X-SPF-Guess: pass (seems reasonable for [EMAIL PROTECTED] to mail
through 193.121.44.105)
X-Pobox-Antispam: Bad HELO hostname returned DENY: no A or MX records found
for sweeper.CRONOS.BE
Subject: FW: DBI connect as sysdba
Date: Tue, 8 Mar 2005 11:03:38 +0100
From: "Rouquart Wim" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
Hi there,
I'm sorry to disturb you, but I saw your name (and email address) in a
thread concerning the usage of
DBI in Perl when trying to connect as sysdba. I have just run into the
same problem as you described in
that thread, namely getting an ORA-1034 as if the db is down, when it
isnt. Connecting as user system
works fine... I was wondering if you ever solved this problem, as the
responses to the thread seem
inconclusive.
Thank you for your time.
Wim Rouquart
Oracle DBA
Uptime Technologies
PS. I emailed the original thread starter, but he was no longer 'living'
at the email address of the
time he posted his question :-(
----- End forwarded message -----
