Hi Monks.

I'm using DBD::Oracle to make a connection to our Oracle databases. The connection definition includes multiple hosts and a LOAD_BALANCE=on directive (the tnsnames.ora definition follows)

prod_01 =
 (DESCRIPTION = (ADDRESS_LIST = 
     (ADDRESS = (PROTOCOL = TCP)(HOST = rac-host01)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = rac-host02)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = rac-host03)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = rac-host04)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = rac-host05)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = rac-host06)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = db0-vip-01)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = db0-vip-02)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = db0-vip-03)(PORT = 1521))
     (LOAD_BALANCE = yes))
   (CONNECT_DATA =(SERVICE_NAME = production.example.com)))

My connections complete successfully but I'm having intermittent failures with the query I'm running (the same query will fail roughly 1 in 9 times).

#!/usr/bin/perl -w

use DBI;
use DBD::Oracle qw(:ora_types);
use Carp;
use strict;

my $dbh = DBI->connect(
    'dbi:Oracle:prod_01',
    $ENV{PROD_DB_USER},
    $ENV{PROD_DB_PASS}
    ) or croak "Unable to connect: $DBI::errstr";

my $sth = $dbh->prepare( "select * from prod.t_connection_ports order 
+by client_id" );

$sth->execute;

printf( "\n%-15s %10s %10s %10s   %-30s \n\n",
        "Client","Port","Client ID","Status","Machine ID");

while ( my $row = $sth->fetchrow_arrayref ) {
    printf( "%-15s %10s %10s %10s   %-30s \n",
            @$row[3],@$row[2],@$row[0],@$row[4],@$row[1]);
}

[download]

I'd like to interrogate the DBI database handle to identify which of the hosts in the definition list I've been connected to see if there's a correlation between a given host and the query failure.

Is there a way to identify the Oracle host to which I've been connected? I see nothing in DBI or DBD::Oracle docs which might give me this information.

Any pointers in the right direction would be greatly appreciated.

-- AZ

In reply to Identify host connected to in DBD::Oracle connection? by AlephZarro

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.