Munin contributed plugins

#!/usr/bin/env ruby

=begin

Munin Plugin for SGA memory components monitoring

Author: Wilfred Chau <openapp.developer@gmail.com>

Date: 2011-05-12

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_orcl_sga.rb /etc/munin/plugins/oracle_orcl_sga.rb

Parameters:

	autoconf

	config (required)

Configurable variables:

	orauser : oracle user who has select privilege to query v$sgastat 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().to_s}"

  rows.close

end

#

# Queries

#

shared_pool_query = "SELECT TO_CHAR(ROUND(SUM(decode(pool, 'shared pool',

                    decode(name, 'library cache',0,

                                 'dictionary chace',0,

                                 'free memory',0,

                                 'sql area',0,

                                 (bytes)/(1024*1024)),0)),2)) pool_misc

                    from V$SGASTAT"

buffer_cache_query = "SELECT TO_CHAR(ROUND(SUM(decode(pool,NULL,

                     decode(name, 'db_block_buffers', (bytes)/(1024/1024),

                            'buffer_cache',(bytes)/(1024*1024),0),0)),2)) sga_bufcache

                     from V$SGASTAT"

fixed_area_query = "SELECT TO_CHAR(ROUND(SUM(decode(pool,NULL,

                   decode(name, 'fixed_sga', (bytes)/(1024*1024),0),0)),2)) sga_fixed

                   from V$SGASTAT"

java_pool_query = "SELECT TO_CHAR(ROUND(SUM(decode(pool, 'java pool', (bytes)/(1024*1024),0)),2)) sga_jpool

                   from V$SGASTAT"

large_pool_query = "SELECT TO_CHAR(ROUND(SUM(decode(pool, 'large pool', (bytes)/(1024*1024),0)),2)) sga_lpool

                   from V$SGASTAT"

log_buffer_query = "SELECT TO_CHAR(ROUND(SUM(decode(pool, NULL,

                   decode(name, 'log_buffer', (bytes)/(1024*1024),0),0)),2)) sga_lbuffer

                   from V$SGASTAT"

memory_components = { "fixed_area" => fixed_area_query,

                      "buffer_cache" => buffer_cache_query,

                      "java_pool" => java_pool_query,

                      "large_pool" => large_pool_query,

                      "log_buffer" => log_buffer_query,

                      "shared_pool" => shared_pool_query }

#

# autoconf

#

if ARGV[0] == "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

#

elsif ARGV[0] == "config"

  puts "graph_args --base 1024k -r --lower-limit 0"

  puts "graph_title Oracle SGA from #{dbname}"

  puts "graph_category db"

  puts "graph_info This graph shows the SGA memory usage (in MB)"

  puts "graph_vlabel MB"

  puts "graph_scale no"

  puts "graph_period second"

  memory_components.keys.each do |m|

    puts "#{m}.label #{m}"

    puts "#{m}.info SGA: #{m}"

    puts "#{m}.type GAUGE"

    # make sure fixed_area is at the bottom of the stack

    if (m == 'fixed_area')

      puts "#{m}.draw AREA"

    else

      puts "#{m}.draw STACK"

    end

  end

  exit 0

end

$conn = OCI8.new(orauser, orapass, tnsname)

memory_components.each do |mc, query|

  runQuery(mc, query)

end

$conn.logoff