Another way to monitor Oracle

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

    #16
    Don't worry...

    No problem man. I can wait. Sorry if I'm boring you.

    Comment


      #17
      Originally posted by vjunior1981 View Post

      When I run the file:
      canes2:/etc/zabbix/externalscripts # ./check_ora_cron.sh
      Sending failed. Use option -vv for more detailed output.
      /usr/local/sbin/zabbix_sender -z 10.10.100.139 -p 10051 -s comrtst -k audit -o

      If I don't put the -r parameter it works fine for me, but I don't get any information in the zabbix screen
      Hi, I have exactly the same problem.

      Zabbix sender help says:
      -o --value <Key value> Specify value of the key

      Your sendtrap script sends $MYMSG as key value:
      $ZBX_SENDER -z $ZBX_SERVER -p $ZBX_PORT -s $MYHOST -k $MYKEY -o "$MYMSG";

      And it is definied as third parameter given to sendtrap:
      MYMSG="$3"

      Correct me if I'm wrong, but I can't see third parameter in check_ora_cron.sh:
      ./check_ora_sendtrap.sh -i $host -q ${query%.sql} &

      Am I mistaken or you just forgot about sending query output?

      Comment


        #18
        Originally posted by Mich View Post
        Hi, I have exactly the same problem.

        Zabbix sender help says:
        -o --value <Key value> Specify value of the key

        Your sendtrap script sends $MYMSG as key value:
        $ZBX_SENDER -z $ZBX_SERVER -p $ZBX_PORT -s $MYHOST -k $MYKEY -o "$MYMSG";

        And it is definied as third parameter given to sendtrap:
        MYMSG="$3"

        Correct me if I'm wrong, but I can't see third parameter in check_ora_cron.sh:
        ./check_ora_sendtrap.sh -i $host -q ${query%.sql} &

        Am I mistaken or you just forgot about sending query output?
        the parameter $host is provided by the function :
        gethosts () {
        cd /etc/zabbix/externalscripts/check_ora
        cat credentials | grep -v '^#' | cut -d';' -f 1
        }

        and in particular
        HOSTS=$(gethosts)

        so HOSTS contain a list of values, and inside for.. do cicle $host contain every values of $HOSTS

        for host in $HOSTS; do

        for query the method is the same:

        getqueries () {
        cd /etc/zabbix/externalscripts/check_ora
        ls *.sql
        }

        QUERIES=$(getqueries)
        in the begin of for.. do for every values of $query and of $host execute:

        ./check_ora_sendtrap.sh -r -i $host -q ${query%.sql} &

        if you run
        sh -x check_ora_cron.sh
        you will see the list of values contained inside HOSTS and the same for the values inside QUERIES

        for me is :
        HOSTS='DGDB0101
        ....
        ZBH02'

        QUERIES='audit.sql
        ...
        waits_sqlnet.sql'
        + cd /etc/zabbix/externalscripts
        + for host in '$HOSTS'
        + for query in '$QUERIES'
        + ./check_ora_sendtrap.sh -r -i DGDB0101 -q audit
        + sleep 1

        and in the end run:
        /check_ora_sendtrap.sh -r -i DGDB0101 -q audit

        so if you want debug check_ora_sendtrap.sh :
        sh -x ./check_ora_sendtrap.sh -r -i DGDB0101 -q audit

        you will see the output of the script step by step.

        the last lines for me are:

        + /usr/local/sbin/zabbix_sender -z 10.11.77.93 -p 10051 -s DGDB0101 -k audit -o none
        Info from server: "Processed 1 Failed 0 Total 1 Seconds spent 0.001992"
        sent: 1; skipped: 0; total: 1


        this mean that the trap was sent correctly
        "Processed 1 Failed 0 Total 1 Seconds spent 0.001992"
        Andrea Dalle Vacche
        website:http://www.smartmarmot.com/
        e-mail:
        Author of:Mastering Zabbix Book - second edition
        Zabbix Network Monitoring Essentials

        Comment


          #19
          So, I was definitely mistaken

          Thank you for detailed explanation.

          Well, in my and vjunior1981's case it is not working. May be I will give a closer look to it later.

          Now I'm focusing on your first solution - via external scripts from server.

          Let me know if anyone will find something about this "Sending failed."

          Comment


            #20
            Originally posted by Mich View Post
            So, I was definitely mistaken

            Thank you for detailed explanation.

            Well, in my and vjunior1981's case it is not working. May be I will give a closer look to it later.

            Now I'm focusing on your first solution - via external scripts from server.

            Let me know if anyone will find something about this "Sending failed."
            no problem i'm happy that my explanation was clear
            if i can help you let me know
            Andrea Dalle Vacche
            website:http://www.smartmarmot.com/
            e-mail:
            Author of:Mastering Zabbix Book - second edition
            Zabbix Network Monitoring Essentials

            Comment


              #21
              released on sourceforge

              Hi to everyone i've see that this project involve some people and so i've made a sourceforge project if someone want collaborate or has ideas or something please send me a feedback
              https://sourceforge.net/projects/checkora
              Andrea Dalle Vacche
              website:http://www.smartmarmot.com/
              e-mail:
              Author of:Mastering Zabbix Book - second edition
              Zabbix Network Monitoring Essentials

              Comment


                #22
                Thank you !

                I'm currently testing your programs with Zabbix 1.8 configured on RedHat cluster.

                In my case I'm focused on tablespace values and that does not work for me

                As you said I just need to configure my zabbix user on Oracle, configure my SQL+ client and check_ora.

                I put in crontab check_ora_cron.sh
                */5 * * * * /opt/zabbix/check_ora/check_ora_cron.sh > /opt/zabbix/tmp/zabbix_sender.log

                I can find in my output log:
                /opt/zabbix/bin/zabbix_sender -z 10.1.128.216 -p 10051 -s EMAUL -k tbl_space -o

                But when I try to read value in zabbix last values:
                Waits Controlfile I/O 16 Dec 2009 15:03:51 3 ms waits/s - Graph
                Tablespaces 16 Dec 2009 15:03:36 none - History
                SGA shared pool 16 Dec 2009 15:03:31 109.12 M - Graph

                Have you got specials tips to give me to get tablespaces values ?

                Thanks a lot again for your excellent job !

                Comment


                  #23
                  Originally posted by rde-actinux View Post
                  I'm currently testing your programs with Zabbix 1.8 configured on RedHat cluster.

                  In my case I'm focused on tablespace values and that does not work for me

                  As you said I just need to configure my zabbix user on Oracle, configure my SQL+ client and check_ora.

                  I put in crontab check_ora_cron.sh
                  */5 * * * * /opt/zabbix/check_ora/check_ora_cron.sh > /opt/zabbix/tmp/zabbix_sender.log

                  I can find in my output log:
                  /opt/zabbix/bin/zabbix_sender -z 10.1.128.216 -p 10051 -s EMAUL -k tbl_space -o

                  But when I try to read value in zabbix last values:
                  Waits Controlfile I/O 16 Dec 2009 15:03:51 3 ms waits/s - Graph
                  Tablespaces 16 Dec 2009 15:03:36 none - History
                  SGA shared pool 16 Dec 2009 15:03:31 109.12 M - Graph

                  Have you got specials tips to give me to get tablespaces values ?

                  Thanks a lot again for your excellent job !

                  i've got a looong looong time relationship with tablespace... and the resume is this: with autoextend =yes and when tablespace is composed from multiple datafile is completely an hell write a query that return a sorta of real value, i never finded a way.. sorry
                  Andrea Dalle Vacche
                  website:http://www.smartmarmot.com/
                  e-mail:
                  Author of:Mastering Zabbix Book - second edition
                  Zabbix Network Monitoring Essentials

                  Comment


                    #24
                    Tablespace monitoring

                    Ok, I worked a bit on my tablespace monitoring.

                    I wrote a request for getting my table space percent usage

                    Code:
                    ALTER SESSION SET NLS_NUMERIC_CHARACTERS=".,";
                    
                    SELECT 
                      a.tablespace_name AS name,
                      ROUND(100* b.bytes / a.bytes,1) AS percent_free
                    FROM 
                     (SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_DATA_FILES 
                       GROUP BY tablespace_name) a, 
                     (SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_FREE_SPACE 
                       GROUP BY tablespace_name) b 
                    WHERE a.tablespace_name = b.tablespace_name;
                    On zabbix I can get that values (for example)

                    Code:
                    2009.Dec.17 11:57:20 	
                    
                    SYSTEM                                 20.1
                    INDEX                                  66.7
                    USERS                                  19.1
                    SYSAUX                                 23.4
                    UNDOTBS1                               97.6
                    What I need is to set triggers on this results when value while raise 80. I can't do it actually.

                    I think I can do it passing tablespace name for each database but it will take to long to do (about 50 databases, with about 5 tablespace for each).

                    My question is easy; is it possible to parse this results in zabbix to extract and insert in Zabbix db only the name of the tablespace and % of free space ?

                    Thank you for your answer !

                    Comment


                      #25
                      Originally posted by rde-actinux View Post
                      Ok, I worked a bit on my tablespace monitoring.

                      I wrote a request for getting my table space percent usage

                      Code:
                      ALTER SESSION SET NLS_NUMERIC_CHARACTERS=".,";
                      
                      SELECT 
                        a.tablespace_name AS name,
                        ROUND(100* b.bytes / a.bytes,1) AS percent_free
                      FROM 
                       (SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_DATA_FILES 
                         GROUP BY tablespace_name) a, 
                       (SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_FREE_SPACE 
                         GROUP BY tablespace_name) b 
                      WHERE a.tablespace_name = b.tablespace_name;
                      For what i've seen is impossible parse this kind of output inside zabbix, is more easy do something like this:
                      select * from (
                      SELECT
                      a.tablespace_name AS name,
                      ROUND(100* b.bytes / a.bytes,1) AS percent_free
                      FROM
                      (SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_DATA_FILES
                      GROUP BY tablespace_name) a,
                      (SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_FREE_SPACE
                      GROUP BY tablespace_name) b
                      WHERE a.tablespace_name = b.tablespace_name
                      )
                      where percent_free > 80


                      and simply do a trigger that become red when there is a sorta of output,
                      so you are going to see always green when you don't have nothing that has more than 80 % space used.
                      Consider that "system" tablespace is often fully used.
                      I hope this will help you.

                      Consider also this:
                      if USERS tblspace is made by 2 datafiles and ONE of this has autoextend this query return a wrong value, because don't consider autoextend and maxsize of datafiles. (this is what i mean for trouble with tablespaces query).
                      Last edited by dalle; 18-12-2009, 11:07.
                      Andrea Dalle Vacche
                      website:http://www.smartmarmot.com/
                      e-mail:
                      Author of:Mastering Zabbix Book - second edition
                      Zabbix Network Monitoring Essentials

                      Comment


                        #26
                        Originally posted by gypeWaple
                        Hope your studies help you do better - for the Job you state you will get.

                        Whos paying your way Howie ?

                        Just adding up your comments in your posts - thats all, not really interested - but some things dont add up.
                        ok no more something of funny...
                        btw i really don't know how monitor tablespace inside zabbix a graphic visualization of used space etc.. should be useful but i don't know how do this, if you have any kind of idea let me know.
                        Last edited by dalle; 03-02-2010, 16:10.
                        Andrea Dalle Vacche
                        website:http://www.smartmarmot.com/
                        e-mail:
                        Author of:Mastering Zabbix Book - second edition
                        Zabbix Network Monitoring Essentials

                        Comment


                          #27
                          Monitoring tablespaces

                          I partially solved my problem.

                          First of all, I created a request which admit one argument to get % free space on tablespace :
                          Code:
                          SELECT 
                            /* for debug purpose : 
                                a.tablespace_name AS name, */
                            ROUND(100* b.bytes / a.bytes,1) AS percent_free
                          FROM 
                           (SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_DATA_FILES 
                             GROUP BY tablespace_name) a, 
                           (SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_FREE_SPACE 
                             GROUP BY tablespace_name) b 
                          WHERE a.tablespace_name = b.tablespace_name
                          /* name of tablespace in argument */
                          AND a.tablespace_name = '&1';
                          
                          exit;
                          Then I wrote a small script to launch sql query with args.
                          Code:
                          #!/bin/sh
                          # Oracle environment variables
                          export PATH=$PATH:/home/oracle/bin:/home/oracle/oracle/product/10.2.0/db_1/bin
                          export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
                          
                          SQLPLUS=/home/oracle/oracle/product/10.2.0/db_1/bin/sqlplus
                          
                          IP=
                          INSTANCE=
                          QUERY=
                          ARGS=
                          
                          # Display help
                          printhelp() {
                          	echo "query_oracle.sh - an Oracle query for zabbix"
                          	echo "Usage: query_oracle.sh hostname instance query [parameters] [help]"
                          	echo "       ip          ip adress of oracle server"
                          	echo "       instance    to retrieve credentials from credentials file (must be the same with tnsnames.ora"
                          	echo "       query       execute a query and return just only the output"
                          	echo "       parameters  for extrat arguments"
                          }
                          
                          execquery() {
                          	# `echo "Running $SQLPLUS -S $INSTANCE $QUERY $ARGS query " >> /opt/zabbix1/var/log/zabbix/zabbix_server.log`
                          	RES=$($SQLPLUS -S $INSTANCE $QUERY $ARGS)
                          	RET=$?
                          	if [ $RET -ne 0 ]
                          	then
                          		echo "Error while querying $INSTANCE"
                          	fi
                          	echo $RES
                          }
                          
                          # testing args
                          if [ $# -lt 2 ]
                          then
                          	printhelp
                          	exit 1
                          fi
                          
                          # better way to "shift"
                          IP=$1
                          INSTANCE=zabbix/[email protected]$2
                          [email protected]/opt/zabbix1/etc/zabbix/externalscripts/$3
                          ARGS=$4 $5 $6 $7 $8 $9 # need improvment
                          
                          execquery
                          I add group oracle to my zabbix account and add right read on my tnsnames.ora.

                          In zabbix, I add an Externel check using this script so I can get values for each Oracle instances.

                          What's wrong with it :
                          • I have to enter manually instance name (tnsnames.ora)
                          • I have to enter manually tablespaces name


                          An upgrade could be to monitor each database as zabbix host:
                          • Create each database in zabbix
                          • Modify tnsnames.ora to set instance name with database name in zabbix.


                          I'm quiet busy so in next step I'll try all that improvments.

                          I have no good solutions for tablespaces names

                          (Sorry for my english...)

                          Comment


                            #28
                            i'm replying in late, sorry, i'm looking on your request.
                            If you don't want use tnsnames.ora there is a note wrote by asktom on his forum: http://asktom.oracle.com/pls/asktom/...45033135081903
                            i'm trying to find also a client that don't use tnsnames.ora this can be another way to solve your problem.

                            Sorry but i did not undestand the other two upgrade, actually i'm using already dbname as "host" in side zabbix.
                            Andrea Dalle Vacche
                            website:http://www.smartmarmot.com/
                            e-mail:
                            Author of:Mastering Zabbix Book - second edition
                            Zabbix Network Monitoring Essentials

                            Comment


                              #29
                              version 1.1 as been released

                              relase a minor version 1.1 more info here:
                              http://www.smartmarmot.com/

                              downloadable from here:
                              https://sourceforge.net/projects/checkora/files/
                              pratically i've just added an external configuration file, some comment anc cleaned a few the code.
                              This release is not jet tested on zabbix server 1.8.x because i didn't yet upgraded my servers.
                              For each trouble or question please ask to me.
                              see ya
                              Last edited by dalle; 24-05-2010, 22:22.
                              Andrea Dalle Vacche
                              website:http://www.smartmarmot.com/
                              e-mail:
                              Author of:Mastering Zabbix Book - second edition
                              Zabbix Network Monitoring Essentials

                              Comment


                                #30
                                version 1.1 as been released

                                relase a minor version 1.1 more info here:
                                http://www.smartmarmot.com/
                                downloadable from here:
                                https://sourceforge.net/projects/checkora/files/
                                pratically i've just added an external configuration file, some comment anc cleaned a few the code.
                                This release is not jet tested on zabbix server 1.8.x because i didn't yet upgraded my servers.
                                For each trouble or question please ask to me.
                                see ya
                                Last edited by dalle; 24-05-2010, 22:22.
                                Andrea Dalle Vacche
                                website:http://www.smartmarmot.com/
                                e-mail:
                                Author of:Mastering Zabbix Book - second edition
                                Zabbix Network Monitoring Essentials

                                Comment

                                Announcement

                                Collapse
                                No announcement yet.
                                Working...
                                X