Archive

Archive for February, 2011

Sample Perl Script to connect Oracle DB

February 25, 2011 Leave a comment

#!/usr/bin/perl
# Run under Oracle Perl, for DBI.
BEGIN {
die “ORACLE_HOME not set\n” unless $ENV{ORACLE_HOME};
unless ($ENV{OrAcLePeRl}) {
$ENV{OrAcLePeRl} = “$ENV{ORACLE_HOME}/perl”;
$ENV{PERL5LIB} = “$ENV{PERL5LIB}:$ENV{OrAcLePeRl}/lib:$ENV{OrAcLePeRl}/lib/site_perl”;
$ENV{LD_LIBRARY_PATH} = “$ENV{LD_LIBRARY_PATH}:$ENV{ORACLE_HOME}/lib32:$ENV{ORACLE_HOME}/lib”;
exec “$ENV{OrAcLePeRl}/bin/perl”, $0, @ARGV;
}
}

use strict;
use warnings;

use DBI;
my (@Table);
my($username, $password) = (‘vi’, ‘vi’);

my $dbh = DBI->connect(“dbi:Oracle:$ENV{ORACLE_SID}”, $username, $password) or die;
my $sql = qq{ SELECT tname FROM tab where rownum < 10};
my $sth = $dbh->prepare( $sql );
$sth->execute();
while ( @Table = $sth->fetchrow_array(  ) ) {
print “Table Name: @Table\n”;
}
[oracle@rh55-64b ~]$

Reference : http://www.orafaq.com/wiki/Perl

Categories: Perl Scripts

WARNING: Subscription for node down event still pending

February 15, 2011 Leave a comment

Symptoms

In  listener log file, the warning message happened

WARNING: Subscription for node down event still pending
14-JAN-2011 11:13:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=gsqsvmdb)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=1
85599744)) * status * 0
2011-01-14 11:14:09.862000 +08:00
WARNING: Subscription for node down event still pending
14-JAN-2011 11:14:09 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=gsqsvmdb)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=1
85599744)) * status * 0
2011-01-14 11:17:02.792000 +08:00
WARNING: Subscription for node down event still pending
14-JAN-2011 11:17:02 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=gsqsvmdb)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=1
85599744)) * status * 0
2011-01-14 11:21:37.933000 +08:00
WARNING: Subscription for node down event still pending
14-JAN-2011 11:21:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=gsqsvmdb)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=1
85599744)) * status * 0
2011-01-14 11:21:52.666000 +08:00
WARNING: Subscription for node down event still pending
14-JAN-2011 11:21:52 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=gsqsvmdb)(USER=

Cause
These messages are related to the Oracle10g TNS Listener’s default subscription to the Oracle Notification Service (ONS). In a non-RAC environment it is recommended to disable this subscription.

Solution
Set the following parameter in the listener.ora:

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF

Where <listener_name> should be replaced with the actual listener name configured in the
LISTENER.ORA file.

This will prevent the messages from being written to the log file, but also prevent the 10g TNS
Listener from hanging periodically.

Categories: TNS

Add service to single instance database

February 15, 2011 Leave a comment

1. Login database as sysdba

2.Add ha_gsqs database service name to service_names parameter

SQL> alter system set service_names=’gsqsqa,ha_gsqs’ scope=both;

3. Register the database service to listener

SQL> alter system register;

4. Testing the service name

$ sqlplus gsqs/gsqs@gsqsvmdb:1521/ha_gsqs

SQL*Plus: Release 11.1.0.7.0 – Production on Tue Feb 15 11:51:36 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

 

Categories: Administration