Re: Connecting 'as sysdba' using DBD::Oracle
by tweetiepooh (Hermit) on Aug 05, 2009 at 11:08 UTC
|
$dsn = "dbi:Oracle:ABCD12";
and make sure that sqlnet.ora has
NAMES.DEFAULT_DOMAIN = COMPANYDOMAIN.COM
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
$dsn = "dbi:Oracle:DS8CC2";
or
$dsn = "dbi:Oracle:DS8CC2.ORACLEOUTSOURCING.COM";
the error becomes
DBI connect('DS8CC2','',...) failed: ORA-01031: insufficient privilege
+s (DBD ERROR: OCISessionBegin)
and
DBI connect('DS8CC2.ORACLEOUTSOURCING.COM','',...) failed: ORA-01031:
+insufficient privileges (DBD ERROR: OCISessionBegin)
respectively.
But as I mentioned, there's no issue while connecting directly using sqlplus '/as sysdba' and $TNS_ADMIN/sqlnet.ora does have the entry:
NAMES.DEFAULT_DOMAIN = ORACLEOUTSOURCING.COM
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
| [reply] [Watch: Dir/Any] |
Re: Connecting 'as sysdba' using DBD::Oracle
by wazoox (Prior) on Aug 05, 2009 at 09:52 UTC
|
I didn't use DBD::Oracle for years, but I remember that
- it's a real PITA to set up
- it's quite touchy about releases.
What are the platforms and Oracle versions of the machine running the script and the DB server? Do they match? | [reply] [Watch: Dir/Any] |
|
It's Oracle 10g running on Enterprise Linux AS4. I am running the script on the DB node itself.
| [reply] [Watch: Dir/Any] |
Re: Connecting 'as sysdba' using DBD::Oracle
by FloydATC (Deacon) on Aug 05, 2009 at 10:49 UTC
|
I have never used DBD::Oracle myself and I'm just an Oracle novice so this may be completely worthless, but did you actually use
my $dbh = DBI->connect($dsn, "", "", { ora_session_mode => ORA_SYSDBA
+});
or did you just remove the username before posting? Try using the username "system" like so:
my $dbh = DBI->connect($dsn, "system", $sysdba_password, { ora_session
+_mode => ORA_SYSDBA });
with the proper $sysdba_password ofcourse.
--
Time flies when you don't know what you're doing
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
According to the DBD::Oracle documentation the username and password are supposed to be blank.
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.
Which according to me seems correct as while connecting sqlplus '/as sysdba' we do not provide any username/password.
| [reply] [Watch: Dir/Any] [d/l] |
|
OK, timtowtdi I guess :-)
$ sqlplus
SQL*Plus: Release 9.2.0.6.0 - Production on Wed Aug 5 13:29:01 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: system as sysdba
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL>
--
Time flies when you don't know what you're doing
| [reply] [Watch: Dir/Any] [d/l] |
Re: Connecting 'as sysdba' using DBD::Oracle
by gulden (Monk) on Aug 05, 2009 at 12:16 UTC
|
This is the way I'm connecting to a Oracle Database:
my $database = 'TEST_DB';
my $DSN = "dbi:Oracle:$database";
my $dbh = DBI->connect( $DSN,
$DBUSER,
$DBPASS,
{AutoCommit => 1, RaiseError => 1}) or die "
+Can't connect DB [$!]";
The content of the tnsnames.ora file is:
.../oracle/product/10.2.0/client_1/network/admin/tnsnames.ora
TEST_DB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(Host = 192.168.1.1)(Port = 1526))
(CONNECT_DATA =
(SID = TESTDB)
)
)
With the "sysdba" option '{ora_session_mode => ORA_SYSDBA}, i've got the following error, since that user doesn't have that privilege:
DBI connect('TEST_DB','user',...) failed: ORA-01031: insufficient priv
+ileges (DBD ERROR: OCISessionBegin)
I hope it helps.
«A contentious debate is always associated with a lack of valid arguments.»
| [reply] [Watch: Dir/Any] [d/l] [select] |
Re: Connecting 'as sysdba' using DBD::Oracle
by Mr. Muskrat (Canon) on Aug 05, 2009 at 14:34 UTC
|
Is the user running the script a member of the dba, osdba or ORA_DBA group (depending on OS and configuration)?
| [reply] [Watch: Dir/Any] |
Re: Connecting 'as sysdba' using DBD::Oracle
by FloydATC (Deacon) on Aug 07, 2009 at 10:49 UTC
|
Just installed DBD::Oracle and the following works for me:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use DBD::Oracle qw(:ora_session_modes);
my $dbhost = "my.server.name";
my $dbuser = "sys"; # not "system" or "/"
my $dbpass = "password";
my $sid = "SIDNAME";
my $dbh = DBI->connect(
"dbi:Oracle:host=$dbhost;sid=$sid",
$dbuser,
$dbpass,
{ ora_session_mode => ORA_SYSDBA }
);
if ($dbh) {
print "Connected\n";
} else {
die $DBI::errstr;
}
$dbh->disconnect;
Server is Oracle9i Enterprise Edition Release 9.2.0.6.0
Update: No TNS file, no ORACLE_SID environment variable.
--
Time flies when you don't know what you're doing
| [reply] [Watch: Dir/Any] [d/l] |
|
Hi,
Your solution work because technically you are supplying the username and password which simulates connecting from the network to the database
I believe the issue here is if you are going to use the script to run locally on the server, there should not be a need to supply username and password because you are already on the server itself. The main idea for this is to not having to specify passwords on your scripts for security reason.
| [reply] [Watch: Dir/Any] |
|
I know that this is an old thread, but I was having the same problem and Google popped it to the top of the list when I searched for 'perl oracle connect as sysdba'. Just in case anyone else starts looking for an answer to this, the solution is to use a dsn of 'dbi:Oracle:' and undef for the username and password. Provided that you are running as oracle (or are a member of the dba group) on the host where the database is running and you have the ORACLE_SID variable set properly and you use the ora_session_mode => ORA_SYSDBA pair in the connection hash you will connect OK. At least that worked for me.
| [reply] [Watch: Dir/Any] |
|
|