FAQ
Hello,

Can someone help with me a DBI issue I'm having in connecting to an Oracle 10g DB server?

We have production scripts that loads data into DB (oracle9i) using DBI module. We recently migrated our database to oracle 10g. Our DBA has implemented load balancing on this 10g server and gave us the new tnsnames entry as...

hoststring.10G =
(DESCRIPTION=
(LOAD_BALANCE=yes)
(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.123)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.124)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=xxxx)
)
)

Currently, using the DBI module I am able to connect to these servers (x.x.x.123 & x.x.x.124) individually using the following code...

my $db_conn_dsn = "dbi:Oracle:host=HOSTNAME;port=PORTNUMBER;sid=SID";
my $db_conn_user= "XXXXX";
my $db_conn_pass= "XXXXX";

my $dbh = DBI->connect($db_conn_dsn,$db_conn_user,$db_conn_pass,{ RaiseError => 1, AutoCommit => 0 } );

But I want to connect to the load balanced configuration instead of connecting to the servers separately. Is there any way I can refer to the host string ("hostname.10G") in tnsnames.ora file for DBI module connection information. Finally, I want to use the load balanced configuratin rather than connecting to individual servers.

Any help in this regard is greatly appreciated.

Thanks & Regards
Srinu

Search Discussions

  • Martin Gainty at Jan 10, 2009 at 1:26 am
    Sri-
    Oracle connects via the TNS entry as seen in %ORACLE_HOME%/network/admin/tnsnames.ora
    Here the TNS Name DB1 is associated to Service IDentifier of DB 1 SID=DB1
    which in this case is listening on localhost Port 1521

    DB1=
    (DESCRIPTION=
    (ADDRESS=
    (PROTOCOL=tcp)
    (HOST=localhost)
    (PORT=1521)
    )
    (CONNECT_DATA=
    (SERVER = DEDICATED)
    (SID=DB1)
    (SERVICE_NAME=DB1)
    )
    )

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )

    If you are willing to configure different TNS to use different ports you *can* have multiple TNS entries / localhost

    Warm Regards
    Martin
    ______________________________________________
    Disclaimer and confidentiality note
    Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.



    Date: Fri, 9 Jan 2009 14:31:28 -0800
    From: write2srinu@yahoo.com
    Subject: Oracle::DBI module - Issues in connecting to Oracle 10G
    To: dbi-users@perl.org

    Hello,

    Can someone help with me a DBI issue I'm having in connecting to an Oracle 10g DB server?

    We have production scripts that loads data into DB (oracle9i) using DBI module. We recently migrated our database to oracle 10g. Our DBA has implemented load balancing on this 10g server and gave us the new tnsnames entry as...

    hoststring.10G =
    (DESCRIPTION=
    (LOAD_BALANCE=yes)
    (ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.123)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.124)(PORT=1521))
    (CONNECT_DATA=
    (SERVICE_NAME=xxxx)
    )
    )

    Currently, using the DBI module I am able to connect to these servers (x.x.x.123 & x.x.x.124) individually using the following code...

    my $db_conn_dsn = "dbi:Oracle:host=HOSTNAME;port=PORTNUMBER;sid=SID";
    my $db_conn_user= "XXXXX";
    my $db_conn_pass= "XXXXX";

    my $dbh = DBI->connect($db_conn_dsn,$db_conn_user,$db_conn_pass,{ RaiseError => 1, AutoCommit => 0 } );

    But I want to connect to the load balanced configuration instead of connecting to the servers separately. Is there any way I can refer to the host string ("hostname.10G") in tnsnames.ora file for DBI module connection information. Finally, I want to use the load balanced configuratin rather than connecting to individual servers.

    Any help in this regard is greatly appreciated.

    Thanks & Regards
    Srinu


    _________________________________________________________________
    Windows Live™ Hotmail®: Chat. Store. Share. Do more with mail.
    http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_hm_justgotbetter_howitworks_012009
  • Mike Nhan at Jan 12, 2009 at 3:53 pm

    hoststring.10G =
    (DESCRIPTION=
    (LOAD_BALANCE=yes)
    (ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.123)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.124)(PORT=1521))
    (CONNECT_DATA=
    (SERVICE_NAME=xxxx)
    )
    )

    Currently, using the DBI module I am able to connect to these servers (x.x.x.123 & x.x.x.124) individually using the following code...

    my $db_conn_dsn = "dbi:Oracle:host=HOSTNAME;port=PORTNUMBER;sid=SID";
    my $db_conn_user= "XXXXX";
    my $db_conn_pass= "XXXXX";

    my $dbh = DBI->connect($db_conn_dsn,$db_conn_user,$db_conn_pass,{ RaiseError => 1, AutoCommit => 0 } );
    my $db_conn_dsn='dbi:Oracle:hoststring.10G';
    my $dbh=DBI->connect($db_conn_dsn,$db_con_user,$db_con_pass,{AutoCommit=>0,RaiseError=>1})

    should do the trick. Simply replace "host=....;sid=SID" with the tnsalias
    that your dba provides you. Make sure the TNS_ADMIN env is set to point
    to the location of the tnsnames.ora and should be fine.

    Michael

    --
    ---//---
    Time flies like the wind. Fruit flies like bananas.
    --- Groucho Marx

    Either write something worth reading or do something worth writing.
    --- Benjamin Franklin

    A meeting is an event at which the minutes are kept and the hours are lost

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedJan 9, '09 at 10:31p
activeJan 12, '09 at 3:53p
posts3
users3
websitedbi.perl.org

3 users in discussion

Martin Gainty: 1 post Mike Nhan: 1 post Srinu: 1 post

People

Translate

site design / logo © 2022 Grokbase