Oracle monitoring

[den_crane]

Unzip ZABORA.zip, change oracle environment in oraenv, change oracle connection params (user/pass) in cont.sql, change full path to oraenv and cont.sql in zabora.

$ ./zabora
zabora version: 1.2
usage:
    ./zabora checkactive [SID]     -- Check Intance is active and open.
    ./zabora usercount [SID]       -- Count of users connected to Oracle.
    ./zabora activeusercount [SID] -- Count of active users.
    ./zabora dbsize [SID]          -- Size of user data (without temp).
    ./zabora dbfilesize [SID]      -- Size of all datafiles.
    ./zabora version [SID]         -- Oracle version (Banner).
    ./zabora dsksortratio [SID]    -- Disk sorts ratio.
    ./zabora rcachehit [SID]       -- Read Cache hit ratio.
    ./zabora uptime [SID]          -- Instance Uptime (seconds).
    ./zabora commits [SID]         -- User Commits.
    ./zabora rollbacks [SID]       -- User Rollbacks.
    ./zabora deadlocks [SID]       -- Deadlocks.
    ./zabora redowrites [SID]      -- Redo Writes.
    ./zabora tblscans [SID]        -- Table scans (long tables).
    ./zabora tblrowsscans [SID]    -- Table scan rows gotten.
    ./zabora indexffs [SID]        -- Index fast full scans (full).
    ./zabora hparsratio [SID]      -- Hard parse ratio.
    ./zabora netsent [SID]         -- Bytes sent via SQL*Net to client.
    ./zabora netresv [SID]         -- Bytes received via SQL*Net from client.
    ./zabora netroundtrips [SID]   -- SQL*Net roundtrips to/from client.
    ./zabora logonscurrent [SID]   -- Logons current.
    ./zabora lastarclog [SID]      -- Last archived log sequence.
    ./zabora lastapplarclog [SID]  -- Last applied archive log (at standby).
    ./zabora freebufwaits [SID]    -- free buffer waits.
    ./zabora bufbusywaits [SID]    -- buffer busy waits.
    ./zabora logswcompletion [SID] -- log file switch completion.
    ./zabora logfilesync [SID]     -- log file sync.
    ./zabora logprllwrite [SID]    -- log file parallel write.
    ./zabora enqueue [SID]         -- enqueue waits.
    ./zabora dbseqread [SID]       -- db file sequential read waits.
    ./zabora dbscattread [SID]     -- db file scattered read.
    ./zabora dbsnglwrite [SID]     -- db file single write.
    ./zabora dbprllwrite [SID]     -- db file parallel write.
    ./zabora directread [SID]      -- direct path read.
    ./zabora directwrite [SID]     -- direct path write.
    ./zabora zaboraver             -- Version of this script.

Test the script

$ ./zabora version
Oracle8i Release 8.1.7.4.0 - Production

Add UserParameter to zabbix_agentd.conf

$echo "UserParameter=oracle[*],/opt/zabbix/zabora \$1 \$2" >>zabbix_agentd.conf

Test from zabbix agent

$ ./zabbix_agentd -c /etc/zabbix_agentd.conf -t oracle[version]
oracle[/opt/zabbix/zabora version ]           [t|Oracle8i Release 8.1.7.4.0 - Production]

Add Items in Zabbix

Description                               Type          Key                   Type of information Units      Update interval Store value  History    Trends      Applications
Oracle/Deadlocks.                         ZABBIX agent  oracle[deadlocks]     Numeric (integer)              600             Delta        7          365         Oracle
Oracle/Disk sorts ratio.                  ZABBIX agent  oracle[dsksortratio]  Numeric (float)     %          600             As Is        7          365         Oracle
Oracle/Hard parse ratio.                  ZABBIX agent  oracle[hparsratio]    Numeric (float)     %          600             As Is        7          365         Oracle
Oracle/Index fast full scans (full).      ZABBIX agent  oracle[indexffs]      Numeric (integer)              600             Delta        7          365         Oracle
Oracle/Intance is active and open.        ZABBIX agent  oracle[checkactive]   Numeric (integer)              300             As Is        7          365         Oracle
Oracle/Read Cache hit ratio.              ZABBIX agent  oracle[rcachehit]     Numeric (float)     %          600             As Is        7          365         Oracle
Oracle/Redo Writes.                       ZABBIX agent  oracle[redowrites]    Numeric (integer)              600             Delta        7          365         Oracle
Oracle/Size of all datafiles.             ZABBIX agent  oracle[dbfilesize]    Numeric (integer)   B          7200            As Is        7          365         Oracle
Oracle/Size of user data (without temp).  ZABBIX agent  oracle[dbsize]        Numeric (integer)   B          7200            As Is        7          365         Oracle
Oracle/Table scan rows gotten.            ZABBIX agent  oracle[tblrowsscans]  Numeric (integer)              600             Delta        7          365         Oracle
Oracle/Table scans (long tables).         ZABBIX agent  oracle[tblscans]      Numeric (integer)              600             Delta        7          365         Oracle
Oracle/Uptime.                            ZABBIX agent  oracle[uptime]        Numeric (integer)   s          600             As Is        7          365         Oracle
Oracle/User Commits.                      ZABBIX agent  oracle[commits]       Numeric (integer)              600             Delta        7          365         Oracle
Oracle/User Rollbacks.                    ZABBIX agent  oracle[rollbacks]     Numeric (integer)              600             Delta        7          365         Oracle
Oracle/Usercount                          ZABBIX agent  oracle[usercount]     Numeric (integer)              300             As Is        7          365         Oracle
Oracle/Version                            ZABBIX agent  oracle[version]       Character                      7200            As Is        7          365         Oracle
Oracle/ZaboraVer                          ZABBIX agent  oracle[zaboraver]     Character                      7200            As Is        3          365

[Paul Hanson]

Thanks for sharing this.

[jcanning]

Thanks so much for posting this. It's what I have been looking for.

Cheers!

[den_crane]

Good news wildcards in ZabbixW32.exe works.

I created zabora.bat :

C:\ZABBIX>zabora.bat uptime
10193506

UserParameter=oracle[*],c:\zabbix\zabora.bat $1 $2
Timeout=30

[den_crane]

On some windows system environment variable path invisible for services, and zabora.bat couldn't find sqlplus.

set nls_lang=american_cis.cl8mswin1251
set path=C:\ora92\bin

if user LocalSystem (owner of zabbix service) not included in group ORA_DBA, cont.sql requare real password for "conn" command

[den_crane]

Oracle user for zabora:

CREATE USER "ZABBIX" IDENTIFIED BY "password";
GRANT "CONNECT" TO "ZABBIX";
grant select on v_$instance to zabbix;
grant select on v_$sysstat to zabbix;
grant select on v_$session to zabbix;
grant select on dba_free_space to zabbix;
grant select on dba_data_files to zabbix;
grant select on dba_tablespaces to zabbix;
grant select on v_$log to zabbix;
grant select on v_$archived_log to zabbix;
grant select on v_$loghist to zabbix;
grant select on v_$system_event to zabbix;
grant select on v_$event_name to zabbix;

[mfortier]

thank you for sharing

[StanZoid]

Do you have mods to allow this to check multiple instances on a single server?

StanZoid

[Treitor]

Thank´s a lot for these scripts.

[den_crane]

Originally Posted by StanZoid

Do you have mods to allow this to check multiple instances on a single server?

StanZoid

./zabora checkactive [SID]