Clicky Web Analytics

Clicky

Apr
6
Mon
Posted By ritzy on Monday, April 06, 2009
9485 Views 12 Comments


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 "&lt;" by "<" and "&gt;" by ">"
while ($config_line = &lt;READ_CONFIG&gt;)
{
   chomp($config_line);
   chop($config_line);

# Due to HTML issues, please replace "&lt;" by "<" and "&gt;" by ">"
   $status_line = &lt;READ_STATUS&gt;;
   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) testdb1
Service 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) testdb2
Service 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) testdb1
Service srv_inst2 is running on instance(s) testdb2

Categories

Rants & Raves Minimize

  • Gravatar
    anonymous Friday, April 10, 2009 at 3:56 PM
    Re: Using FAN callouts, relocate a service back
    Pretty neat stuff and I have heard others talk about this before (without code), but my question has always been, "Why would you want to do this in production?" Wouldn't you really be better off moving the service back in a controlled manner at a time when it would have less impact on the end user?

    • Gravatar
      Ritzy Friday, April 10, 2009 at 4:09 PM
      Re: Using FAN callouts, relocate a service back
      Thanks for your response.

      When an instance goes down, services running on that instance are relocated anyways. Would you call this controlled one? The reason it's important to relocate a service back when that down instance comes back up is because most of the systems that are on RAC have services configured for load balancing and proper distribution of resources. If this part is not automated, then it's dependent on the kind of monitoring that is implemented and someone manually needs to relocate a service back. I've seen in real life situations when services are not running on their desired instances, resources are not distributed appropriately which adversely affects the end consumers.

      Well, this is just one of the application I mentioned about using the FAN callouts. You could do lot of things that suits your environment. For example, you could capture all the state change events in a central repository database. With OEM, there is a fixed retention period. But with this one, you could have a log of all scheduled/unscheduled maintenance.

      You could also capture OS stats like CPU.Memory usage, list of logged in users at the time of the state change. These could help in doing RCA or build reports for analysis or postmortem.

      The opportunities are endless. FAN callouts is a powerful feature which not may people are aware of and is not implemented widely. Hence I wanted to share this implementation.

    • Gravatar
      prasbaba Wednesday, June 16, 2010 at 4:04 AM
      Re: Using FAN callouts, relocate a service back
      I expect some solution instead of this comment.
      I thought you tried the script whch you posted sucessfully. Anways thanks for the response.

  • Gravatar
    hugo Monday, October 12, 2009 at 9:33 AM
    Re: Using FAN callouts, relocate a service back
    Dear friend,

    I had tried to run your perl program. But the error has been showd as below:

    syntax error at relocate_service.pl line 23, near "= ;"
    syntax error at relocate_service.pl line 110, near "}"

  • Gravatar
    hugo Monday, October 12, 2009 at 10:07 AM
    Re: Using FAN callouts, relocate a service back
    Dear friend,

    I had tried to run your perl program. But the error has been showd as below:

    syntax error at relocate_service.pl line 23, near "= ;"
    syntax error at relocate_service.pl line 110, near "}"

  • Gravatar
    prasbaba Wednesday, June 16, 2010 at 12:56 AM
    Re: Using FAN callouts, relocate a service back
    I had tried to run your perl program. But the error has been showd as below:

    syntax error at relocate_service.pl line 23, near "= ;"
    syntax error at relocate_service.pl line 110, near "}"

    • Gravatar
      Ritzy Wednesday, June 16, 2010 at 3:31 AM
      Re: Using FAN callouts, relocate a service back
      Come on, You could surely fix those little syntax errorshappy
      Anyways thanks for your response. I'll try to fix them and put back the updated code

      • Gravatar
        Ritzy Saturday, August 21, 2010 at 3:35 PM
        Script Issue
        prasbaba and hugo,

        I figured out that the script is fine but while posting READ_CONFIG and READ_STATUS variables were being treated as HTML tags and hence being skipped. I have put a comment in front of those code to replace "<" by "<" and ">" by ">". Hope that helps. The main idea is to identify the callout variables and interpret them which can then be extrapolated to call any script on instance/service up/down event.

  • Gravatar
    Ritzy Wednesday, June 16, 2010 at 4:43 AM
    Re: Using FAN callouts, relocate a service back
    Yes my friend, it was tested and verified on my test bed. Perhaps a typo somewhere while posting it.

    • Gravatar
      anil Wednesday, August 01, 2012 at 4:11 AM

      Thanks Ritzy . Script is very useful for us and working fine .. Good work keep it up ..

  • Gravatar
    Ajay Monday, September 24, 2012 at 2:01 PM

    Script is really good. Thanks for sharing. Is there any reason for keeping sleep(30) before calling service relocate
    # Call Service relocate
    sleep(30);
    Does script wait for CRS to bring services online as much as possible before doing any verification ?

    • Gravatar
      Ritzy Wednesday, September 26, 2012 at 10:24 PM

      At times, CRS takes a while so the sleep is put to be on safer side and have a cleaner way of relocating the services.

  • ritzyblogs.com Saturday, July 31, 2010 at 12:07 AM
    via pingback
    Oracle 11gR2 Features and Bugs| Talk

  • Recommended Oracle DBA Books Minimize

         

    Tag Cloud Minimize


    Archive Posts Minimize
     
    Monthly
      Yearly

      Disclaimer:
      This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.

      This posting has nothing to do with my present or past employer.