Category: Database

In Oracle 10g RAC, every time a node/instance/service goes up/down, that event can be trapped and used to make user defined callouts. So every time a state change occurs, a FAN event is posted to ONS immediately. When a node receives an event through ONS, it will asynchronously execute all executables in the server side callouts directory.

There could be lot of applications to using this feature of callouts. For example, when an instance goes down, we all know that services running on that instance are relocated to other available instances. But when that instance comes back up, those relocated services need to be manually put back to their preferred instance. By using FAN callouts, we can automate this task.

1. Go to $ORA_CRS_HOME/racg and create usrco directory on all the nodes. So the server side callout directory would look something like this:
/oracle/product/crs/racg/usrco

2. Place your callout scripts under this dir. This will be called on every state change. You could use any executable like shell script or a perl script.
I have tested this on a 2-node Oracle RAC database version 10.2.0.2 on x64 RHEL4U7.
Save the below sample script as /oracle/product/crs/racg/usrco/instup_relocate.pl
This one traps the INSTANCE UP event and calls another script to relocate the services

#!/usr/local/bin/perl
# instup_relocate.pl
# This is a callout program that will, on an INSTANCE UP event relocate services back
# This script is supposed to reside in $CRS_HOME/racg/usrco as an executable on all the nodes. “usrco” directory needs to be created for callouts.
use strict;

# Define Oracle and Crs Home
my $CRS_HOME=”/oracle/product/crs”;
my $ORACLE_HOME=”/oracle/product/10.2″;

# TMP refers to the log location only
my $TMP = “/tmp”;

# Enable logging
my $LOGFILE = “$TMP/SRV_co.log”;

# Define variables that would be captured by callout event
my $instance;
my $database;
my $host;
my $service;
my $reason;
my $card;
my $status;
my ($key,$value) = “”;

# Open logfile
local *LOG_FILE;
open (LOG_FILE, “>>$LOGFILE”) or do
{
   print “Cannot open $LOGFILE\n”;
   exit(1);
};

# Uncomment these lines if only interested in specific events

if ($ARGV[0] ne “INSTANCE”) { exit(0); };
#if ($ARGV[0] ne “SERVICEMEMBER”) { exit(0); };
#if ($ARGV[0] ne “SERVICE”) { exit(0); };
#if ($ARGV[0] ne “NODE”) { exit(0); };

for (my $i=0; $i <= $#ARGV; $i++)
{
    print LOG_FILE “For Loop $i $ARGV[$i]\n”;
    if ($ARGV[$i] =~ m#=#)
    {
        ($key,$value) = (split /=/, $ARGV[$i]);
        #print “Key = $key  Value = $value\n”;
        if ($key eq “service”)
        {
            $service = $value;
        } elsif ($key eq “instance”)
        {
            $instance = $value;
            $ENV{ORACLE_SID} = $value;
        } elsif ($key eq “database”)
        {
            $database = $value;
        } elsif ($key eq “host”)
        {
            $host = $value;
        } elsif ($key eq “card”)
        {
            $card = $value;
        } elsif ($key eq “status”)
        {
            $status = $value;
        } elsif ($key eq “reason”)
        {
            $reason = $value;
        }
    }
}
print LOG_FILE “Arg=$ARGV[0]\n”;
print LOG_FILE “DB=$database\n”;
print LOG_FILE “Host = $host DB = $database Inst = $instance Service = $service Status = $status Reason = $reason\n”;
# Call relocate service after instance up event is trapped.
#
if ($status eq “up” && $ARGV[0] eq “INSTANCE”)
{
    print LOG_FILE “Instance up found. Calling relocate services for $database\n”;
    # Call Service relocate
    sleep(30);
    system(“/usr/local/bin/perl /oracle/scripts/relocate_service.pl $database”);
    print LOG_FILE “Success!!!\n”;
}
else
{
    print LOG_FILE “Failed: Instance up check failed\n”;
}

Save the below sample script as /oracle/scripts/relocate_service.pl

#!/usr/local/bin/perl
# relocate_service.pl
# This script does a comparison between srvctl config and srvctl status and accordingly relocates the service back based on the config.

my $ret;
my $host1;
# Get dbname passed as an argument from command line
my $dbname;
$argc=scalar @ARGV;
$dbname = $ARGV[0];
chop($host1= `/bin/hostname`);
$ret = system(“srvctl config service -d $dbname|sed ‘s/PREF: //’|sed ‘s/AVAIL:.*\$//’ >/tmp/config_service.log”);
$ret = system(“srvctl status service -d $dbname | sed ‘s/\,\ /\,/g’|cut -f2,7 -d ‘ ‘ | tr -s ‘,’ ‘ ‘>/tmp/status_service.log”);

open(READ_CONFIG, “/tmp/config_service.log”);
open(READ_STATUS, “/tmp/status_service.log”);

my ($rl_from, $rl_to, $no_relocs,$conf_inst_i,$status_inst_i);

# Due to HTML issues, please replace “<" by "” by “>”
while ($config_line = )
{
   chomp($config_line);
   chop($config_line);

# Due to HTML issues, please replace “<" by "” by “>”
   $status_line = ;
   chomp($status_line);
   if ($config_line eq  $status_line)
   {
      print  “$config_line OK\n”;
   }
   else
   {
     # Array to store relocate to/from instance names for each service
     my @relocate_from;
     my @relocate_to;

     ($conf_serv,@conf_inst) = split / /,$config_line;
     ($status_serv,@status_inst) = split / /,$status_line;

     # Relocate to which node
     my $i=0;
     my $found;

     for (@conf_inst)
     {
         $conf_inst_i = $_;
         $found=0;

         for (@status_inst)
         {
           $status_inst_j = $_;
           if ($conf_inst_i eq $status_inst_j) { $found=1; last;}

         }

         $relocate_to[$i++] = $conf_inst_i if (! $found);
     }

     # Relocate from which node
     my $j=0;
     for (@status_inst)
     {
         $status_inst_i = $_;
         $found=0;
         for (@conf_inst)
         {
           $conf_inst_j = $_;
           if ($status_inst_i eq $conf_inst_j) { $found=1; last;}
         }

         $relocate_from[$j++] = $status_inst_i if (! $found);
     }
     $rl_from= scalar @relocate_from;
     $rl_to=scalar @relocate_to;

     # How many relocations need to be done
     $no_relocs=$rl_from;
     if ($rl_from > $rl_to)
     {
        $no_relocs=$rl_to;
     }

     # Relocate for all possible instances
     if ($no_relocs > 0)
     {
        for ($i=0; $i<$no_relocs; $i++)
        {
             $relocate_cmd = “srvctl relocate service -d $dbname -s \”$conf_serv\” -i $relocate_from[$i] -t $relocate_to[$i]”;
             $ret = `$relocate_cmd 2>&1`;
             print  “RELOCATED: $relocate_cmd\n”;
        }
     }

     # Start services on the remaining preferred instanecs
     if ($rl_to > $rl_from)
     {
         for ($i=$no_relocs; $i<$rl_to; $i++)
         {
             $start_serv_cmd=”srvctl start service -d $dbname -s \”$conf_serv\” -i $relocate_to[$i]”;
             $ret = system(“$start_serv_cmd”);
             print  “STARTED: $start_serv_cmd\n”;
         }
      }
   }
}#End of while

if ($no_relocs > 0)
{
  $ret = system(“srvctl status service -d $dbname | sed ‘s/\,\ /\,/g’|cut -f2,7 -d ‘ ‘ | tr -s ‘,’ ‘ ‘>/tmp/status_service_new.log”);
}

Here are the test results. We can see that after an instance is brought back up, the service srv_inst1 is relocated back to it’s preferred instance by the callout script without any manual intervention.

% srvctl config service -d testdb
srv_inst1 PREF: testdb1 AVAIL: testdb2
srv_inst2 PREF: testdb2 AVAIL: testdb1

% srvctl status service -d testdb
Service srv_inst1 is running on instance(s) testdb1Service srv_inst2 is running on instance(s) testdb2

% srvctl stop instance -d testdb -i testdb1

% srvctl status service -d testdb
Service srv_inst1 is running on instance(s) testdb2Service srv_inst2 is running on instance(s) testdb2

% srvctl start instance -d testdb -i testdb1

% srvctl status service -d testdb
Service srv_inst1 is running on instance(s) testdb1Service srv_inst2 is running on instance(s) testdb2

I would thank ritzy .

Thank you
Osama mustafa