oracle-pga-monitor

#!/usr/bin/env ruby

=begin

Munin Plugin for PGA memory components monitoring

Author: Wilfred Chau <openapp.developer@gmail.com>
Date: 2011-05-13
Version: 1.0

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License version 2
as published by the Free Software Foundation.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License along
with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

Prerequistes:
  1) env.ORACLE_HOME set in munin-node
  2) rubygems
  3) oci8 - DBI gem for connecting to Oracle
     * instruction of installing oci8 is available here:
       http://ruby-oci8.rubyforge.org/en/InstallBinaryPackage.html

Usage:
  1) copy this script to the munin install plugins directory (e.g. /usr/share/munin/plugins)
  2) chmod to allow executable to others
  3) create symbolic link in /etc/munin/plugins
           ln -s /usr/share/munin/plugins/oracle_<sid>_pga.rb /etc/munin/plugins/oracle_<sid>_pga.rb
           ** replace <sid> with your oralce sid

Parameters:
  autoconf
  config (required)

Configurable variables:
  orauser : oracle user who has select privilege to query v$pgastat view
  orapass : password for the oracle user
  dbport  : port used by the monitored instance (notice: numeric value)
  dbname  : database to be monitored
  dbhost  : host or ip address of db instance

#%# family=auto
#%# capabilities=autoconf

=end

require 'rubygems'
require 'oci8'

orauser  = 'munin'
orapass  = 'munin'
dbport   = 1522
dbname   = 'orcl'
dbhost   = 'localhost'

tnsname = "(DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = #{dbhost})(PORT = #{dbport}))
            (CONNECT_DATA = (SID = #{dbname})))"

def runQuery(name, query)
  rows = $conn.exec(query)
  puts "#{name}.value #{rows.fetch}"
  rows.close
end

#
# Queries
#
pga_target_query = "SELECT TO_CHAR(ROUND(decode(unit,'bytes',(value)/(1024*1024),value),2)) pga_target
                    from V$PGASTAT where name = 'aggregate PGA target parameter'"

pga_query = "SELECT TO_CHAR(ROUND(decode(unit,'bytes',(value)/(1024*1024),value),2)) pga
                     from V$PGASTAT where name = 'total PGA inuse'"

pga_components = { 'pga_target' => pga_target_query,
                   'pga_in_use' => pga_query }

#
# autoconf
#
case ARGV[0]
when 'autoconf'
  if tnsname.length > 1 && orauser.length > 1 && orapass.length > 1
    puts 'yes'
  else
    puts 'no'
    puts "Usage: #{__FILE__} autoconf|conf"
  end
  exit 0
#
# config definition
#
when 'config'
  puts 'graph_args --base 1024k -r --lower-limit 0'
  puts "graph_title Oracle PGA from #{dbname}"
  puts 'graph_category db'
  puts 'graph_info This graph shows the PGA memory usage (in MB)'
  puts 'graph_vlabel MB'
  puts 'graph_scale no'
  puts 'graph_period second'

  pga_components.keys.each do |p|
    puts "#{p}.label #{p}"
    puts "#{p}.info PGA: #{p}"
    puts "#{p}.type GAUGE"
    puts "#{p}.draw LINE1"
  end

  exit 0
end

$conn = OCI8.new(orauser, orapass, tnsname)
pga_components.each do |pc, query|
  runQuery(pc, query)
end
$conn.logoff