Orabbix:"Another way to monitor Oracle reloaded"

[bhiggens]

Experience with Orabbix 1.0.4

Comments from working with version 1.0.4

RAC Environments.
Since the default queries in orabbix monitor instances using the v$ views and the underlying database using the DBA views, have you had thoughts about breaking the queries out into instance monitoring verses database monitoring queries? This would avoid multiple database alerts in a RAC environment or having to build complicated Triggers.

Null field returned in query
Appears if a field is returned as null, a Java java.lang.NullPointerException error is returned. Is that expected, or can it be added as an enhacement request?

XDB
Not everyone has XDB installed and consequently cannot run UTL_INADDR queries in 11g. I have modified the dbversion query accordingly:

select COMP_ID||' '||COMP_NAME||' '||VERSION||' '||STATUS||' <br />' from dba_registry union SELECT ' - SERVERNAME = <b>'||host_name ||'</b><br />'from v$instance union SELECT ' - DB_NAME = <b>'||SYS_CONTEXT ('USERENV', 'DB_NAME') ||'</b> - INSTANCE_NAME = <b>' ||SYS_CONTEXT ('USERENV', 'INSTANCE_NAME')||'</b> <br />' FROM dual

A couple of queries that I have modified.
archive
To prevent alerts when daylight savings time ends, check that first_time is also less than the current time.
From:

WHERE FIRST_TIME >= (sysdate -10/60/24)

To:

WHERE FIRST_TIME >= (sysdate -10/60/24) and first_time < sysdate

locks
For some reason, I have locks on occasion in my 11.1.0.7 database there the LMODE in v$lock is 0 which in the original SQL causes a java.lang.NullPointerException error because the field being returned in NULL. Similiarly the DECODE for REQUEST should have a default value too.
From:

        decode(LMODE, \
                1, 'Null', \
                2, 'Row-S (SS)', \
                3, 'Row-X (SX)', \
                4, 'Share', \
                5, 'S/Row-X (SSX)', \
                6, 'Exclusive') lock_type, \

To:

        decode(LMODE, \
                0, 'None', \
                1, 'Null', \
                2, 'Row-S (SS)', \
                3, 'Row-X (SX)', \
                4, 'Share', \
                5, 'S/Row-X (SSX)', \
                6, 'Exclusive', \
                'Unknown') lock_type, \

Just wanted to share some of my experiences.

Thanks,
Brian

[dalle]

really thank you for all your considerations!
I'm working on your points

[Varamaster]

Connection -

As I can see the connections? The item is disabled on template....

as orabbix do to verify the connections? I cant see it on Query file ...

Connection -

Thanks

[dalle]

Orabbix send a valoue "1" to an item that is called Alive if the connection is going right, the item Connection is deliberately diabled because was an old item and still remain there just for back compatibility.

As I can see the connections? The item is disabled on template....

as orabbix do to verify the connections? I cant see it on Query file ...

Connection -

Thanks

[dalle]

Orabbix send a value "1" to an item that is called Alive if the connection is going right, the item Connection is deliberately disabled because was an old item and still remain there just for back compatibility.

As I can see the connections? The item is disabled on template....

as orabbix do to verify the connections? I cant see it on Query file ...

Connection -

Thanks

[txalamar]

Type of recieved value is not suitable...

Hi there!

could I get some help on this point? I'm still getting the error "Type of received value is no suitable for..." with lastest Orabbix version (1.0.4).

Item [TESTool_free_mem] error: Type of received value [97,87] is not suitable for value type [Numeric (float)]
Item [TESTool_misc] error: Type of received value [846,16] is not suitable for value type [Numeric (float)]
Item [TEST:sga_fixed] error: Type of received value [2,11] is not suitable for value type [Numeric (float)]
Item [TEST:sga_log_buffer] error: Type of received value [7,02] is not suitable for value type [Numeric (float)]
Item [TEST:sga_shared_pool] error: Type of received value [846,16] is not suitable for value type [Numeric (float)]

What could be wrong?

Thanx in advance!

[dalle]

Hi there!

could I get some help on this point? I'm still getting the error "Type of received value is no suitable for..." with lastest Orabbix version (1.0.4).

Item [TESTool_free_mem] error: Type of received value [97,87] is not suitable for value type [Numeric (float)]
Item [TESTool_misc] error: Type of received value [846,16] is not suitable for value type [Numeric (float)]
Item [TEST:sga_fixed] error: Type of received value [2,11] is not suitable for value type [Numeric (float)]
Item [TEST:sga_log_buffer] error: Type of received value [7,02] is not suitable for value type [Numeric (float)]
Item [TEST:sga_shared_pool] error: Type of received value [846,16] is not suitable for value type [Numeric (float)]

What could be wrong?

Thanx in advance!

Your problem should be caused by dot and comma exchange
please read one my old post, and try if this solve

http://www.smartmarmot.com/2010/07/note-about-dot-and-comma/

[txalamar]

Solved

Wow!

setting the env to US solved all my problems.


thanks for that fast reply and congrats for your piece of software... it's great!

Cheers

[bc_of_hh]

Problem with query (Orabbix 1.0.4)

Hi,

first of all thanks for the great tool, for monitoring oracle.
I'm having a little problem with creating a new query:

1) I create a new query in "query.props" file:

jobsfld.Query=select jobs_fail FROM dual
jobsfld.NoDataFound=none

I add jobsfld to QueryList too.

2) Then I create a text item in zabbix with the key jobsfld.

But in log I'm getting an error:

2010-10-20 13:10:41,511 [pool-1-thread-19] ERROR Orabbix - Error on DBEnquiry on query=jobsfld on database=*** Error returned is java.lang.NullPointerException
2010-10-20 13:10:41,511 [pool-1-thread-19] WARN Orabbix - I'm going to return none for query jobsfld on database=***
2010-10-20 13:10:41,595 [pool-1-thread-19] INFO Orabbix - Done with dbJob on database *** QueryList elapsed time 38597 ms

Can anyone tell me what i'm missing? Thanks for any advise.

Best regards,
bc_of_hh

[dalle]

sorry but what should return your query? because i'm receiving

select jobs_fail FROM dual
*
ERROR at line 1:
ORA-00904: "JOBS_FAIL": invalid identifier

[txalamar]

Vars in query.props

Hello people!

I've modified query.props with some perso queries wich need a where clausule, something like:

asm_diskgroup_freespace_INSTANCENAME.Query=SELECT free_mb from v$asm_diskgroup WHERE name = 'INSTANCENAME'

is there a way to use variables in query.props? the only other solution I see is to create separate instances of orabbix, one for each instance.

Thanks!

Txa

[bc_of_hh]

Hi!

Hi, dalle,

thx for fast reply. There is a function within zabbix user in oracle to monitor failed jobs:

CREATE OR REPLACE FUNCTION ZABBIX.JOBS_FAIL
   RETURN  varchar2 is
    answer varchar2(32767);
    text_tmp varchar2(4000);
    cursor a is
    select h.schema_user||'_'||h.job||'_'||h.failures||'_'||h.broken 
from sys.dba_jobs h
where h.last_date is not null and (h.failures>0 or h.broken='Y');
BEGIN
    answer := '';
    text_tmp:= '';
    open a;
     loop
        fetch a into text_tmp;
        exit when a%notfound;
        answer:= text_tmp||';'||answer;
     end loop;
    close a;
  return answer;
  exception
   when others then return null;
END;
/

Is it because the function is returning null if there is no data?
If it is, then how can I change this? Maybe just a "space" data to return, and this will fix the thing?

Regards,
bc_of_hh

[dalle]

Hi, dalle,

thx for fast reply. There is a function within zabbix user in oracle to monitor failed jobs:

CREATE OR REPLACE FUNCTION ZABBIX.JOBS_FAIL
   RETURN  varchar2 is
    answer varchar2(32767);
    text_tmp varchar2(4000);
    cursor a is
    select h.schema_user||'_'||h.job||'_'||h.failures||'_'||h.broken 
from sys.dba_jobs h
where h.last_date is not null and (h.failures>0 or h.broken='Y');
BEGIN
    answer := '';
    text_tmp:= '';
    open a;
     loop
        fetch a into text_tmp;
        exit when a%notfound;
        answer:= text_tmp||';'||answer;
     end loop;
    close a;
  return answer;
  exception
   when others then return null;
END;
/

Is it because the function is returning null if there is no data?
If it is, then how can I change this? Maybe just a "space" data to return, and this will fix the thing?

Regards,
bc_of_hh

YES!! exactly because the function return null !!!! i mean null as type not null as "string" if you modify your function to return a blank or a string e.g. 'no data' or 'nothing' you will solve your trouble, after you'll need to create a trigger that go on fire if find something of different from 'nothing'.
i hope that so you are going to solve everything

[bc_of_hh]

YES!! exactly because the function return null !!!! i mean null as type not null as "string" if you modify your function to return a blank or a string e.g. 'no data' or 'nothing' you will solve your trouble, after you'll need to create a trigger that go on fire if find something of different from 'nothing'.
i hope that so you are going to solve everything

Thank You dalle, for your support, I'm just returning "none" now and it works like a charm.

Best regards,
bc_of_hh

[dalle]

Hi, dalle,

thx for fast reply. There is a function within zabbix user in oracle to monitor failed jobs:

CREATE OR REPLACE FUNCTION ZABBIX.JOBS_FAIL
   RETURN  varchar2 is
    answer varchar2(32767);
    text_tmp varchar2(4000);
    cursor a is
    select h.schema_user||'_'||h.job||'_'||h.failures||'_'||h.broken 
from sys.dba_jobs h
where h.last_date is not null and (h.failures>0 or h.broken='Y');
BEGIN
    answer := '';
    text_tmp:= '';
    open a;
     loop
        fetch a into text_tmp;
        exit when a%notfound;
        answer:= text_tmp||';'||answer;
     end loop;
    close a;
  return answer;
  exception
   when others then return null;
END;
/

Is it because the function is returning null if there is no data?
If it is, then how can I change this? Maybe just a "space" data to return, and this will fix the thing?

Regards,
bc_of_hh

Thank You dalle, for your support, I'm just returning "none" now and it works like a charm.

Best regards,
bc_of_hh

you are welcome, i'm happy that work
have a nice day!
Dalle