# easyAGENT for Oracle
# $Id: Oracle.pm,v 1.2 2006/08/28 22:24:46 abob Exp $
#
#  should have user:pass:sid in /usr/local/soap/etc/oracle
#
package Oracle;

use strict;
use bytes;
use lib '/usr/local/soap/lib';
use INCPATH;
BEGIN {
  @INC = @INCPATH::MYINC;
}

use DBI;

my @ORATABS=("/var/opt/oracle/oratab","/etc/oratab");

my $oratab = sub {
    my $ora_sid = $_[0];

    if($ora_sid =~ m/'/) {
      die("invalid ora_sid\n");
    }

    my $home = `/usr/local/bin/dbhome '$ora_sid'`;
    if($? == 0) {
      chomp($home);
      $ENV{'ORACLE_HOME'}=$home;
      return $home;
    } else {
      print STDERR "cannot find ORACLE_HOME for sid $ora_sid\n";
      return undef;
    }
};

my $setup_connection = sub {
  my($ora_sid) = @_;
  my $ora_con = 'dbi:Oracle:'.$ora_sid;

  my($ora_user,$ora_pass);

  if (not defined($ENV{'ORACLE_HOME'})) {
    $oratab->($ora_sid);
  } 

  open(CONF,"/usr/local/soap/etc/oracle") or return(undef,"UNKNOWN no config file");
  while(<CONF>){
    chomp;
    my ($user,$pass,$sid) = split(':');
    if ($sid eq $ora_sid) {
      ($ora_user,$ora_pass) = ($user,$pass);
    }
  }

  if (not defined($ora_user)){
    return(undef,"UNKNOWN No user defined for sid $ora_sid");
  }

  my $dbh = DBI->connect($ora_con, $ora_user, $ora_pass) or return(undef,"CRITICAL connect failed: $DBI::errstr");
  return($dbh,undef);
};

sub ts {
    my($ts_name, $ora_sid) = split(':', $_[1]);
    my ($ts_size, $ts_free, $ts_type, $ts_auto);

    my($dbh,$error) = $setup_connection->($ora_sid);
    if($error) {
      return $error;
    }

    eval {
	    my $proc = $dbh->prepare(q{
		    begin
			    dp_monitor.query_tablespace(
				    ts_name => :ts_name,
				    ts_type => :ts_type,
				    ts_size => :ts_size,
				    ts_free => :ts_free,
				    ts_auto => :ts_auto
			    );
		    end;
	    });
	    $proc->bind_param(":ts_name", $ts_name);
	    $proc->bind_param_inout(":ts_type", \$ts_type, 100);
	    $proc->bind_param_inout(":ts_size", \$ts_size, 100);
	    $proc->bind_param_inout(":ts_free", \$ts_free, 100);
	    $proc->bind_param_inout(":ts_auto", \$ts_auto, 100);
	    $proc->execute;
    };
    $dbh->disconnect;

    my $ts_auto_dis = "";
    if ($ts_auto > 0) {
	$ts_auto_dis = " AUTO";
    }
    my $ts_used = $ts_size - $ts_free;
    my $ts_used_meg = $ts_used / 1024 / 1024;
    my $ts_size_meg = $ts_size / 1024 / 1024;
    if ($ts_size_meg < 1000) {
	return sprintf("%.2fM/%.2fM%s",$ts_used_meg,$ts_size_meg,$ts_auto_dis);
    } else {
	$ts_size_meg /= 1024;
	$ts_used_meg /= 1024;
	return sprintf("%.2fG/%.2fG%s",$ts_used_meg,$ts_size_meg,$ts_auto_dis);
    }
}

sub quota {
    my($ts_user, $ts_name, $ora_sid) = split(':', $_[1]);
    my ($ts_curv, $ts_maxv);

    my($dbh,$error) = $setup_connection->($ora_sid);
    if($error) {
      return $error;
    }

    eval {
	    my $proc = $dbh->prepare(q{
		    begin
			    dp_monitor.query_quota(
				    ts_name => :ts_name,
				    ts_user => :ts_user,
				    ts_curv => :ts_curv,
				    ts_maxv => :ts_maxv
			    );
		    end;
	    });
	    $proc->bind_param(":ts_name", $ts_name);
	    $proc->bind_param(":ts_user", $ts_user);
	    $proc->bind_param_inout(":ts_curv", \$ts_curv, 100);
	    $proc->bind_param_inout(":ts_maxv", \$ts_maxv, 100);
	    $proc->execute;
    };
    $dbh->disconnect;

    my $ts_curv_meg = $ts_curv / 1024 / 1024;
    my $ts_maxv_meg = $ts_maxv / 1024 / 1024;
    return $ts_curv_meg."M/".$ts_maxv_meg."M";
}

sub pcount {
        my($ora_sid) = $_[1];
        my ($ps_count, $ps_total);

        my($dbh,$error) = $setup_connection->($ora_sid);
        if($error) {
          return $error;
        }

        eval {
                my $proc = $dbh->prepare(q{
                        begin
                                dp_monitor.query_pcount(
                                        ps_count => :ps_count,
                                        ps_total => :ps_total
                                );      
                        end;    
                });
                $proc->bind_param_inout(":ps_count", \$ps_count, 100);
                $proc->bind_param_inout(":ps_total", \$ps_total, 100);
                $proc->execute;
        };
	$dbh->disconnect;

        return $ps_count."/".$ps_total;
}

sub bcmr {
        my($ora_sid) = $_[1];
        my ($var_bchr, $var_bcmr);

        my($dbh,$error) = $setup_connection->($ora_sid);
        if($error) {
          return $error;
        }

	eval {
		my $proc = $dbh->prepare(q{
			begin
				dp_monitor.query_bchr(
					arg_bchr => :arg_bchr
				);      
			end;    
		});
		$proc->bind_param_inout(":arg_bchr", \$var_bchr, 100);
		$proc->execute;
	};
	$dbh->disconnect;

	$var_bcmr=(1-$var_bchr);
	return sprintf("%.2f/%.2f",$var_bcmr,100);
}

sub lcmr {
        my($ora_sid) = $_[1];
        my ($var_lchr, $var_lcmr);
	my ($ora_user,$ora_pass);

        my($dbh,$error) = $setup_connection->($ora_sid);
        if($error) {
          return $error;
        }

	eval {
		my $proc = $dbh->prepare(q{
			begin
				dp_monitor.query_lchr(
					arg_lchr => :arg_lchr
				);      
			end;    
		});
		$proc->bind_param_inout(":arg_lchr", \$var_lchr, 100);
		$proc->execute;
	};
	$dbh->disconnect;

	$var_lcmr=(1-$var_lchr);
	return sprintf("%.2f/%.2f",$var_lcmr,100);
}

sub dmr {
        my($ora_sid) = $_[1];
        my ($var_dhr, $var_dmr);

        my($dbh,$error) = $setup_connection->($ora_sid);
        if($error) {
          return $error;
        }

	eval {
		my $proc = $dbh->prepare(q{
			begin
				dp_monitor.query_dhr(
					arg_dhr => :arg_dhr
				);      
			end;    
		});
		$proc->bind_param_inout(":arg_dhr", \$var_dhr, 100);
		$proc->execute;
	};
	$dbh->disconnect;

	$var_dmr=(1-$var_dhr);
	return sprintf("%.2f/%.2f",$var_dmr,100);
}

sub cf {
	my($cf_type, $ora_sid) = split(':', $_[1]);
	my ($cf_curv, $cf_maxv);
	
        my($dbh,$error) = $setup_connection->($ora_sid);
        if($error) {
          return $error;
        }

	eval {
		my $proc = $dbh->prepare(q{
			begin
				dp_monitor.query_controlfile(
					cf_type => :cf_type,
					cf_curv => :cf_curv,
					cf_maxv => :cf_maxv
				);
			end;
		});
		$proc->bind_param(":cf_type", $cf_type);
		$proc->bind_param_inout(":cf_curv", \$cf_curv, 100);
		$proc->bind_param_inout(":cf_maxv", \$cf_maxv, 100);
		$proc->execute;
	};
	$dbh->disconnect;

        return $cf_curv."/".$cf_maxv;
}

sub asm {
    my($asm_name, $ora_sid) = split(':', $_[1]);
    if(not defined($ENV{ORACLE_HOME})) {
      $oratab->($ora_sid);
    }
    if($asm_name =~ /[^a-zA-Z0-9]/) {
      die("invalid volume name\n");
    }

    $ENV{'ORACLE_SID'} = $ora_sid;
    my $cmd = "sudo -u oracle ".$ENV{ORACLE_HOME}."/bin/asmcmd lsdg -H $asm_name";
    my $lsdg = `$cmd`;
    my(@lsdg_array) = split(/\s+/,$lsdg);

    if($lsdg_array[0] ne "MOUNTED") {
      die("disk group ${asm_name} not found in ${ora_sid}\n");
    }

    return sprintf "%.2fM/%.2fM\n", ($lsdg_array[7]-$lsdg_array[8]), $lsdg_array[7];
}

sub locks {
        my($ora_sid) = $_[1];
        my ($lk_count);

        my($dbh,$error) = $setup_connection->($ora_sid);
        if($error) {
          return $error;
        }

        eval {
                my $proc = $dbh->prepare(q{
                        begin
                                dp_monitor.query_deadlock(
                                        lk_count => :lk_count
                                );      
                        end;    
                });
                $proc->bind_param_inout(":lk_count", \$lk_count, 100);
                $proc->execute;
        };
	$dbh->disconnect;

        return $lk_count;
}

1;