Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Connecting 'as sysdba' using DBD::Oracle

by laminee (Novice)
on Aug 05, 2009 at 09:28 UTC ( [id://786017]=perlquestion: print w/replies, xml ) Need Help??

laminee has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I am trying to replicate the sqlplus '/as sysdba' connection using DBI and DBD::Oracle by following the guidelines from http://search.cpan.org/~pythian/DBD-Oracle-1.23/Oracle.pm#Connect_Attributes.

My tnsnames.ora is like this:

#Generated tnsnames.ora by gen_tnsnames.sh script #Localnode service connectivity ABCD12.COMPANYDOMAIN.COM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = testserver24.companydomain.co +m )(PORT = 30710)) ) (CONNECT_DATA = (INSTANCE_NAME = ABCD12) (SERVICE_NAME = ABCD12.COMPANYDOMAIN.COM) ) )
and I have no problem connecting with the usual sqlplus '/as sysdba'.

I am doing the following in my script:

use DBI; use DBD::Oracle qw(:ora_session_modes); my $dsn = "dbi:Oracle:"; #$ENV{ORACLE_SID} = "ABCD12"; $ENV{ORACLE_SID} = "ABCD12.COMPANYDOMAIN.COM"; delete $ENV{TWO_TASK}; my $dbh = DBI->connect($dsn, "", "", { ora_session_mode => ORA_SYSDBA +});
and getting the error:
DBI connect('','',...) failed: ORA-12545: Connect failed because targe +t host or object does not exist (DBD ERROR: OCIServerAttach)

Specifying ORACLE_SID with/without the domain name doesn't change the error message. I have checked that $TNS_ADMIN is defined and the host name is present in /etc/hosts.

What am I doing wrong here?

Thanks, Ani

Replies are listed 'Best First'.
Re: Connecting 'as sysdba' using DBD::Oracle
by tweetiepooh (Hermit) on Aug 05, 2009 at 11:08 UTC
    Try
    $dsn = "dbi:Oracle:ABCD12";
    and make sure that sqlnet.ora has
    NAMES.DEFAULT_DOMAIN = COMPANYDOMAIN.COM
      With
      $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

        Your original post gave error 12545 which is often related to naming. Now you are getting permission errors. So getting closer since you are now reaching the database.

        Oracle docs indicate that error 1031 "insufficient privileges" is when you try to change username or password without appropriate privilege. As other have mentioned check you are in the correct groups to use the sysdba permission and that the user also is granted that in Oracle.

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
    1. it's a real PITA to set up
    2. 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?
      It's Oracle 10g running on Enterprise Linux AS4. I am running the script on the DB node itself.
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
      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.
        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
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.»
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)?

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

      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.

        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.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://786017]
Approved by wazoox
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others rifling through the Monastery: (6)
As of 2024-03-28 12:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found