Orabbix:"Another way to monitor Oracle reloaded"

[dalle]

Thanks for the advice. I tried, but it did not help, CPU load is going higher and higher.

But your advice gave me an idea. I disabled all but one (Hit ratio) query in the list and after I started orabbix. CPU is normal and I am getting "Hit ratio" data in zabbix.
I will enable the queries one-by-one, and I will let you know when the cpu starts to burn.

Yep!
probably is a full table scan or similar let me know if you find something.
Thankyou
Andrea

[brian]

Yep!
probably is a full table scan or similar let me know if you find something.
Thankyou
Andrea

OK, test is done.
The problematic query is "locks". Now all other queries are in QueryList and CPU load on Oracle server stays around 0.1.

[dalle]

THANKYOU for your feedback!

[jvalenzani]

I'm downloading RC3 now for testing. I'm still getting "Not Supported" items with RC1, but i think it's not related.

Thanks for your help.

[dalle]

please try to add the following statement to query.props archive.NoDataFound=0
and let me know if you solve the unsupported on archive

[lioxa_zab]

Feature request

Hi Dalle , I'm trying to monitor 10 DB with orabbix, all configured and working properly. But if any of this DB is stopped/unreachable orabbix stops working (very quiet). After I realize that I'm not getting any data from orabbix (2 weeks) passed , I tried to restart orabbix and got error

java.lang.NullPointerException
at main.main(main.java:205)

Then it took me a while to figure out which database is causing a problem. I did start orabbix.

So I would like to ask u at least 2 big changes in orabbix.
1. If one of the DB not responding keep working with others.
2. On orabbix start up give some debug output , which DB making problems.

Thanx. I hope that it's possible to make this kind of changes.

[dalle]

Hi Dalle , I'm trying to monitor 10 DB with orabbix, all configured and working properly. But if any of this DB is stopped/unreachable orabbix stops working (very quiet). After I realize that I'm not getting any data from orabbix (2 weeks) passed , I tried to restart orabbix and got error

java.lang.NullPointerException
at main.main(main.java:205)

Then it took me a while to figure out which database is causing a problem. I did start orabbix.

So I would like to ask u at least 2 big changes in orabbix.
1. If one of the DB not responding keep working with others.
2. On orabbix start up give some debug output , which DB making problems.

Thanx. I hope that it's possible to make this kind of changes.

Hi lioxa_zab version are you running?
because Orabbix 0.9 and 1.1.0-rc3 should running as you described on point "1" (on my environment i'm monitoring 72 databases and 13 of them make an offline backup and everything work right)
The only thing is that you are using Orabbix 1.1.0-RC2 or RC1 where there was a logic error diuring connection.
The only things may be that my db stay offline just for some hour...
about the point "2" Orabbix should write the name of DB that throws error diuring connection.
Thankyou for your feedback please let me know I'll do all that i can

[lioxa_zab]

Hi lioxa_zab version are you running?
because Orabbix 0.9 and 1.1.0-rc3 should running as you described on point "1" (on my environment i'm monitoring 72 databases and 13 of them make an offline backup and everything work right)
The only thing is that you are using Orabbix 1.1.0-RC2 or RC1 where there was a logic error diuring connection.
The only things may be that my db stay offline just for some hour...
about the point "2" Orabbix should write the name of DB that throws error diuring connection.
Thankyou for your feedback please let me know I'll do all that i can

Strange , I'm running 1.0.4 version. Do u think I should move to orabbix-1.1.0-RC3 ?

[dalle]

Strange , I'm running 1.0.4 version. Do u think I should move to orabbix-1.1.0-RC3 ?

yes try the last version should solve all your trouble.
Still on RC3 (Candidate Release) but i didn't got any trouble from me or other people.

[dalle]

Orabbix 1.1.0 released

Orabbix 1.1.0 Has been released.
On this release I've moved to more standard library. And I've utilized apache library.

I've modified query according to post

http://www.zabbix.com/forum/showpost.php?p=73509&postcount=122

and as suggestet from Brian H. resultset is protected from null values
Thank you Brian H.

Added a parameter
<QueryName>.Period=10 that introduce a period execution between two executions of the specified query
value are expressed in minute.If not specified any value use the default value of 5 minute
Added parameter
<QueryName>.Active=[true|false] if true query is executed otherwise skipped
Added a queryfile to each databases spcified by
<DBNAME>.QueryListFile=./conf/query.props
so each database can have his own customized parameter files.
Added
<QueryName>.NoDataFound default value if no data found
<QueryName>.RaceConditionQuery
<QueryName>.RaceConditionValue if this value match the returned from "RaceConditionQuery" QueryName is executed otherwise skipped

http://bit.ly/fLuqis

[Varamaster]

DB Size dont update status on orabbix 1.1.0

I update to orabbix 1.1.0 and DB Size dont update.

I just change .jar and query.props .

[dalle]

Do you have some error on logs?
please verify that there aren't special and unprintable char on query.props where is defined dbsize.Query
And in particular each line should end with blank space followed by back slash, until the last line of dbsize.Query and let men know

[dalle]

Do you have some error on logs?
please verify that there aren't special and unprintable char on query.props where is defined dbsize.Query
And in particular each line should end with blank space followed by back slash, until the last line of dbsize.Query and let men know

Probably i've founded your trouble replace query.props with this query.pros and let me know

[Varamaster]

Some of the tablespace does not match with toad data about tablespace.

I m Probe query of tablespace and does not deliver the data correctly.

Some of the tablespace does not match with toad data about tablespace.

SQL> SELECT * FROM (
2 select '- Tablespace ->',t.tablespace_name ktablespace,
3 '- Type->',substr(t.contents, 1, 1) tipo,
4 '- Used(MB)->',trunc((d.tbs_size-nvl(s.free_space, 0))/1024/1024) ktbs_em_uso,
5 '- ActualSize(MB)->',trunc(d.tbs_size/1024/1024) ktbs_size,
6 '- MaxSize(MB)->',trunc(d.tbs_maxsize/1024/1024) ktbs_maxsize,
7 '- FreeSpace(MB)->',trunc(nvl(s.free_space, 0)/1024/1024) kfree_space,
8 '- Space->',trunc((d.tbs_maxsize - d.tbs_size + nvl(s.free_space, 0))/1024/1024) kspace,
9 '- Perc->',decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize)) kperc
10 from
11 ( select SUM(bytes) tbs_size,
12 SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize, tablespace_name tablespace
13 from ( select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
14 from dba_data_files
15 union all
16 select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
17 from dba_temp_files
18 )
19 group by tablespace_name
20 ) d,
21 ( select SUM(bytes) free_space,
22 tablespace_name tablespace
23 from dba_free_space
24 group by tablespace_name
25 ) s,
26 dba_tablespaces t
27 where t.tablespace_name = d.tablespace(+) and
28 t.tablespace_name = s.tablespace(+)
29 order by 8)
30 where kperc > 93
31 and tipo <>'T'
32 and tipo <>'U'
33 /

'-TABLESPACE->' KTABLESPACE '-TYPE-> T '-USED(MB)-> KTBS_EM_USO '-ACTUALSIZE(MB)-> KTBS_SIZE '-MAXSIZE(MB)-> KTBS_MAXSIZE '-FREESPACE(MB)-> KFREE_SPACE
--------------- ------------------------------ -------- - ------------ ----------- ------------------ ---------- --------------- ------------ ----------------- -----------
'-SPACE-> KSPACE '-PERC-> KPERC
--------- ---------- -------- ----------
- Tablespace -> DS_DATA_HIST - Type-> P - Used(MB)-> 1650 - ActualSize(MB)-> 1660 - MaxSize(MB)-> 1660 - FreeSpace(MB)-> 10
- Space-> 10 - Perc-> 99

- Tablespace -> DS_DATATEMP - Type-> P - Used(MB)-> 1961 - ActualSize(MB)-> 2064 - MaxSize(MB)-> 2064 - FreeSpace(MB)-> 103
- Space-> 103 - Perc-> 95

- Tablespace -> DATA_GES - Type-> P - Used(MB)-> 4204 - ActualSize(MB)-> 4204 - MaxSize(MB)-> 4204 - FreeSpace(MB)-> 0
- Space-> 0 - Perc-> 100


'-TABLESPACE->' KTABLESPACE '-TYPE-> T '-USED(MB)-> KTBS_EM_USO '-ACTUALSIZE(MB)-> KTBS_SIZE '-MAXSIZE(MB)-> KTBS_MAXSIZE '-FREESPACE(MB)-> KFREE_SPACE
--------------- ------------------------------ -------- - ------------ ----------- ------------------ ---------- --------------- ------------ ----------------- -----------
'-SPACE-> KSPACE '-PERC-> KPERC
--------- ---------- -------- ----------
- Tablespace -> DS_DATA_CONTROL - Type-> P - Used(MB)-> 8274 - ActualSize(MB)-> 8400 - MaxSize(MB)-> 8400 - FreeSpace(MB)-> 126
- Space-> 126 - Perc-> 98

- Tablespace -> DS_DATA_INTERFAZ_1 - Type-> P - Used(MB)-> 17834 - ActualSize(MB)-> 18534 - MaxSize(MB)-> 18534 - FreeSpace(MB)-> 700
- Space-> 700 - Perc-> 96

[dalle]

You're right, the problem is caused by AUTOEXTEND=ON
I have not found a solution to this problem till now i'm looking how solve thi issue
On production environment our policy requires us to not have autoextend enabled (to avoid problems of disk space) so I had not noticed this thing
Thanks for the feedback

I m Probe query of tablespace and does not deliver the data correctly.

Some of the tablespace does not match with toad data about tablespace.

SQL> SELECT * FROM (
2 select '- Tablespace ->',t.tablespace_name ktablespace,
3 '- Type->',substr(t.contents, 1, 1) tipo,
4 '- Used(MB)->',trunc((d.tbs_size-nvl(s.free_space, 0))/1024/1024) ktbs_em_uso,
5 '- ActualSize(MB)->',trunc(d.tbs_size/1024/1024) ktbs_size,
6 '- MaxSize(MB)->',trunc(d.tbs_maxsize/1024/1024) ktbs_maxsize,
7 '- FreeSpace(MB)->',trunc(nvl(s.free_space, 0)/1024/1024) kfree_space,
8 '- Space->',trunc((d.tbs_maxsize - d.tbs_size + nvl(s.free_space, 0))/1024/1024) kspace,
9 '- Perc->',decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize)) kperc
10 from
11 ( select SUM(bytes) tbs_size,
12 SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize, tablespace_name tablespace
13 from ( select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
14 from dba_data_files
15 union all
16 select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
17 from dba_temp_files
18 )
19 group by tablespace_name
20 ) d,
21 ( select SUM(bytes) free_space,
22 tablespace_name tablespace
23 from dba_free_space
24 group by tablespace_name
25 ) s,
26 dba_tablespaces t
27 where t.tablespace_name = d.tablespace(+) and
28 t.tablespace_name = s.tablespace(+)
29 order by 8)
30 where kperc > 93
31 and tipo <>'T'
32 and tipo <>'U'
33 /

'-TABLESPACE->' KTABLESPACE '-TYPE-> T '-USED(MB)-> KTBS_EM_USO '-ACTUALSIZE(MB)-> KTBS_SIZE '-MAXSIZE(MB)-> KTBS_MAXSIZE '-FREESPACE(MB)-> KFREE_SPACE
--------------- ------------------------------ -------- - ------------ ----------- ------------------ ---------- --------------- ------------ ----------------- -----------
'-SPACE-> KSPACE '-PERC-> KPERC
--------- ---------- -------- ----------
- Tablespace -> DS_DATA_HIST - Type-> P - Used(MB)-> 1650 - ActualSize(MB)-> 1660 - MaxSize(MB)-> 1660 - FreeSpace(MB)-> 10
- Space-> 10 - Perc-> 99

- Tablespace -> DS_DATATEMP - Type-> P - Used(MB)-> 1961 - ActualSize(MB)-> 2064 - MaxSize(MB)-> 2064 - FreeSpace(MB)-> 103
- Space-> 103 - Perc-> 95

- Tablespace -> DATA_GES - Type-> P - Used(MB)-> 4204 - ActualSize(MB)-> 4204 - MaxSize(MB)-> 4204 - FreeSpace(MB)-> 0
- Space-> 0 - Perc-> 100


'-TABLESPACE->' KTABLESPACE '-TYPE-> T '-USED(MB)-> KTBS_EM_USO '-ACTUALSIZE(MB)-> KTBS_SIZE '-MAXSIZE(MB)-> KTBS_MAXSIZE '-FREESPACE(MB)-> KFREE_SPACE
--------------- ------------------------------ -------- - ------------ ----------- ------------------ ---------- --------------- ------------ ----------------- -----------
'-SPACE-> KSPACE '-PERC-> KPERC
--------- ---------- -------- ----------
- Tablespace -> DS_DATA_CONTROL - Type-> P - Used(MB)-> 8274 - ActualSize(MB)-> 8400 - MaxSize(MB)-> 8400 - FreeSpace(MB)-> 126
- Space-> 126 - Perc-> 98

- Tablespace -> DS_DATA_INTERFAZ_1 - Type-> P - Used(MB)-> 17834 - ActualSize(MB)-> 18534 - MaxSize(MB)-> 18534 - FreeSpace(MB)-> 700
- Space-> 700 - Perc-> 96