Orabbix:"Another way to monitor Oracle reloaded"

[sybex]

Hi, ...

in my zabbix_server logfile i see something like that:

   743:20100520:163449.285 Item [.oracle:pool_free_mem] error: Type of received value [147,84] is not suitable for value type [Numeric (float)]
   746:20100520:163449.321 Item [.oracle:pool_lib_cache] error: Type of received value [60,09] is not suitable for value type [Numeric (float)]
   745:20100520:163449.331 Item [.oracle:pool_misc] error: Type of received value [420,03] is not suitable for value type [Numeric (float)]
   744:20100520:163449.337 Item [.oracle:pool_sql_area] error: Type of received value [412,05] is not suitable for value type [Numeric (float)]
   742:20100520:163449.346 Item [.oracle:sga_shared_pool] error: Type of received value [420,03] is not suitable for value type [Numeric (float)] 

The specific item has the right setting "Numeric float", but zabbix won't accept that. Is there a error maybe on the "." or "," ???

These are the only items which have problems like this.

[dalle]

Hi, ...

in my zabbix_server logfile i see something like that:

   743:20100520:163449.285 Item [.oracle:pool_free_mem] error: Type of received value [147,84] is not suitable for value type [Numeric (float)]
   746:20100520:163449.321 Item [.oracle:pool_lib_cache] error: Type of received value [60,09] is not suitable for value type [Numeric (float)]
   745:20100520:163449.331 Item [.oracle:pool_misc] error: Type of received value [420,03] is not suitable for value type [Numeric (float)]
   744:20100520:163449.337 Item [.oracle:pool_sql_area] error: Type of received value [412,05] is not suitable for value type [Numeric (float)]
   742:20100520:163449.346 Item [.oracle:sga_shared_pool] error: Type of received value [420,03] is not suitable for value type [Numeric (float)] 

The specific item has the right setting "Numeric float", but zabbix won't accept that. Is there a error maybe on the "." or "," ???

These are the only items which have problems like this.

mmm may be a problem of internationalization... should depend by locale i think...
something like this is never happened to me... anyway "," should be just if the user is ITALIAN (or place where "," is used as separator) or has a locale set in environment of root.
I hope that this my idea can be useful but i'm not sure about the cause.

[dalle]

I'm just asking but someone have founded or noticed a sorta of memory leack or similar? this seems happened with IBM's jvm but i'm not realy sure about, for this reason i'm asking to all user to check their memory occupation and if someone notice something of similar please notice to me !
Thank's to everyone
Nice week end!
Dalle

[nayara]

Sorry delay.
Is working properly!
Very good template!

[davidpe]

Hey, can this tool be used to run queries against one database and not all of them at once? I'm thinking about using it for application type monitoring through queries and not so much monitoring Oracle itself. Thanks

[untergeek]

I imagine so, but at present the way to do that would be separate instances of Orabbix. Each database would have to have its own instance of Orabbix because you'd need to configure it separately.

For example,

/opt/orabbix-productname1
/opt/orabbix-productname2
/opt/orabbix-productname3

etc...

I imagine that would get pretty tedious, but you get the idea. It's either that or run the Orabbix instance remotely on the application server. It only runs queries so if the db traffic is already open, what's the difference of adding a bit more?

Obviously you'd have to create your own template for this, but that's what happens with custom stuff.

[newbie01.oracle]

Hi,

The example template_oracle.xml is not empty and am using it. I got the following from the logs which is similar to what you have, but don't know how to check on Zabbix if any data are send to the Zabbix server. Any one knows how to check that something has been uploaded to the Zabbix server?

From the log, it has the following so I assume it ran fine but not sure how to check on the Zabbix server.

I've uploaded the orabbix files that am using, hopefully you can check and advise if there is anything wrong somewhere. FYI, I took out the jar files and other big files otherwise I will not be able to attach the zip files.

2010-06-16 11:36:32,666 [main] INFO  Orabbix - Starting Orabbix Version 0.6
 2010-06-16 11:36:35,923 [main] INFO  Orabbix - Connected as ZABBIX
 2010-06-16 11:36:36,384 [main] INFO  Orabbix - --------- on Database -> AMSXXX
 2010-06-16 11:36:49,897 [pool-1-thread-1] INFO  Orabbix - Done with dbJob on database DB1 elapsed time 13230 ms
 2010-06-16 11:41:49,845 [pool-1-thread-2] INFO  Orabbix - Done with dbJob on database DB1 elapsed time 12935 ms

I change the log to DEBUG mode and it shows that the SQL are getting executed against the Oracle database, so that means I got data. Note below from the log file

 2010-06-16 12:30:07,215 [pool-1-thread-1] DEBUG Orabbix - Actual query is session_active statement is select count(*) from v$session where TYPE!='BACKGROUND' and status='ACTIVE'
 2010-06-16 12:30:07,437 [pool-1-thread-1] DEBUG Orabbix - resultset returned from query session_active resultset -->4
 2010-06-16 12:30:07,437 [pool-1-thread-1] DEBUG Orabbix - Actual query is session_inactive statement is select SUM(Decode(Type, 'BACKGROUND', 0, Decode(Status, 'ACTIVE', 0, 1))) FROM V$SESSION
 2010-06-16 12:30:07,644 [pool-1-thread-1] DEBUG Orabbix - resultset returned from query session_inactive resultset -->1
 2010-06-16 12:30:07,644 [pool-1-thread-1] DEBUG Orabbix - Actual query is session statement is select count(*) from v$session
 2010-06-16 12:30:07,880 [pool-1-thread-1] DEBUG Orabbix - resultset returned from query session resultset -->19

But on Zabbix Latest Data, I don't see Oracle in the list of Items. On the Graphs, I can see the Oracle lists on the Graph drop down list, i.e. Archivelog, Sessions etc. but no data, so where are the data going to?

FYI, when I imported the template that comes with the orabbix-0.6.1.zip file, I did not make any changes and simply import it. Am I supposed to change the ip and port sections below?

<?xml version="1.0"?>
<zabbix_export version="1.0" date="15.04.10" time="10.32">
        <hosts>
                <host name="Template_Oracle">
                        <useip>0</useip>
                        <dns></dns>
                        <ip>0.0.0.0</ip>
                        <port>10050</port>
                        <status>3</status>
                        <groups>
                                <group>Templates</group>
                        </groups>
                        <items>
                                <item type="2" key="ALERTLOG" value_type="4">
                                        <description>Alertlog High</description>
                                        <ipmi_sensor></ipmi_sensor>
                                        <delay>30</delay>
                                        <history>90</history>
                                        <trends>0</trends>
                                        <status>0</status>

[newbie01.oracle]

Hi all,

To anyone who responded to this post, thanks a lot. BTW, my problem has been resolved. Thanks to mwiewior.

The solution in case any one is interested can be found in the thread : Orabbix install requirement - HELP PLEASE

The most recent post to that thread read as below:

Hi mwiewior

I kept on repeating what you said over and over and over ... "You need to add hosts to monitoring that have the same names as you put in the orabbix configuration files and apply oracle_template on them" ... and guess what, it is finally working now ... am getting data across ...

From the log, the host tag still says gibberish ... oh well, maybe that is by design

 2010-06-19 01:48:02,625 [Zabbix-sender] DEBUG Orabbix - sending <req>[B]<host>QU1TWFhY</host>[/B]<key>ZGdlcnJvcg==</key><data>bm9uZQ==</data></req>

Anyway, now am excited to know if you know what I have to do to generate graphs for tablespaces? That is for example, if I want to create graphs of the tablespace usage?

Any advise/suggestion will be very much appreciated. Thanks a lot again for your help.

[newbie01.oracle]

Orabbix HELP - Graphs for DB and Tablespace Growth

Hi,

Finally managed to get orabbix working after a week ... :-) It looks very good and impressive. Thanks a lot to Andrea Dalle Vacche for writing the plugin.

I want to be able to have graphs for database growth history and tablespace usage history, something that shows the size of the database and tablespace over a period of time. Can any orabbix user please advise on whether they've already done this and if so, how?

I just need some guideline for how to start. For the tablespaces, all the tablepace should show in one graph where they are stacked to each other.

Also, if I have some special testing that I have to do where I have to run the sql for it, do I just modify the query.props with the required SQL and then include that as one of the items for the host?

BTW, the ALERT log monitoring, is it already included with orabbix or do I need to download and run loganalizer.

Any advise will be very much appreciated. Thanks in advance.

[dalle]

Thankyou for your "thanks"
i was in holiday so i didn't read my mail.
I never found a solution to keep under monitor tablespaces... sorry... because you should add a specific query for each tablespace that you want to monitor and a specific item relative to the tablespace that you want to graph.
The trouble is that it's easy monitor "system" and "users" and "temp" that are standard and with the same name for all Oracle's databasesbut if you want monitor my_tablespace_name that exist just on my_database is an hell...

Btw you are going right you need to modify query.props and add an item to template to see your new data. Orabbix will retrieve the item's data from all databases and will send to Zabbix server.

Loganalizer is what i'm using to keep under monitor Oracle's alertlog and for me work well. I don't use Loganalyzer integrated with zabbix because it send too much data for zabbix.
e.g an oracle error is something like 2-3 lines Loganalyzer grep 3 line before and 3 after and those lines are too much data for Zabbix that don't send mail so big....

let me know if you need more help

[den_crane]

query shows count of dbspace which need to add datafiles.

select to_char(count(*), 'FM99999999999999990') retvalue
             from
               (select (asize-nvl(freesize,0))/avsize usedperc
                from
                   (select a.tablespace_name, sum(BYTES) asize, SUM( CASE WHEN AUTOEXTENSIBLE='YES'
                                       THEN MAXBYTES 
                                       ELSE BYTES END)  avsize
                    from dba_data_files a
                    group by a.tablespace_name ) af, 
               (select sum(f.bytes) freesize, tablespace_name 
                from dba_free_space f group by f.tablespace_name ) ff
                where af.tablespace_name = ff.tablespace_name(+)  
                  and af.tablespace_name not like 'UNDO%'
                  and af.tablespace_name not like 'RBS%' 
                  and af.tablespace_name not like 'SYSAUX%')
             where usedperc > 0.90; 

may be itl be usefull

[dalle]

Thanks den_crane!

query shows count of dbspace which need to add datafiles.

select to_char(count(*), 'FM99999999999999990') retvalue
             from
               (select (asize-nvl(freesize,0))/avsize usedperc
                from
                   (select a.tablespace_name, sum(BYTES) asize, SUM( CASE WHEN AUTOEXTENSIBLE='YES'
                                       THEN MAXBYTES 
                                       ELSE BYTES END)  avsize
                    from dba_data_files a
                    group by a.tablespace_name ) af, 
               (select sum(f.bytes) freesize, tablespace_name 
                from dba_free_space f group by f.tablespace_name ) ff
                where af.tablespace_name = ff.tablespace_name(+)  
                  and af.tablespace_name not like 'UNDO%'
                  and af.tablespace_name not like 'RBS%' 
                  and af.tablespace_name not like 'SYSAUX%')
             where usedperc > 0.90; 

may be itl be usefull

[dalle]

Orabbix 0.9 released

on this release i've correct some bugs:

• Fixed a minor bug about quantity of thread generated.
• Fixed problem concerning wrong Locale setting on server
• Added support for more than one Zabbix Server.
• Completely rewrote the "sender" Object
• Improved performances
• Tested and completely compatible with Zabbix 1.8.2

Fixed a minor bug about quantity of thread generated.
Now orabbix support more than one Zabbix server so you can easily migrate from one version to zabbix to anotherone and keep both live, or you can have a Zabbix server that act as "backup" etc...
You can download directly from my website simply registering and downloading the Orabbix 0.9

http://www.smartmarmot.com/product/orabbix/download/

or on sourceforge as usual.

Sources are already committed on sourceforge.

More infos available on my website.

[dalle]

Orabbix 0.9.1 is out!

Orabbix 0.9.1 is available as ususal:

• on my syte

• or downloadabale from sourceforge

On this I've added a check "alive" that receive "1" if a database is available, and if database is not available Orabbix don't goes ahead with all checks.
I've added also new trigger (thankyou Thiago for this idea!) that sum Alive values to undestand if Orabbix is going well or not this trigger is "No data received from Orabbix" and is made so:
{Template_Oracle:alive.sum(600)}<1
With this i consider that Orabbix run at least one time on 10 minutes.

nice day
Dalle

[untergeek]

You've completely rewritten the sender object. I tried to upgrade from 0.6.1 today and it won't start

        java.lang.NullPointerException
	at java.io.File.<init>(Unknown Source)
	at com.smartmarmot.orabbix.Configurator.<init>(Configurator.java:48)
	at main.main(main.java:76)

I re-iterate: It was 100% fully functional before I attempted an upgrade. When I try to start it, I get this error. Even with DEBUG set, I see nothing but:

2010-07-22 15:13:05,863 [main] INFO  Orabbix - Starting Orabbix Version 0.9.1

Nothing more. Any ideas? This is RHEL 5 with the specified java package (which was fully functional with 0.6.1).

$ /usr/java/jre1.6.0_20/bin/java -version
java version "1.6.0_20"
Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
Java HotSpot(TM) 64-Bit Server VM (build 16.3-b01, mixed mode)