Monitoring Oracle with Zabbix

[bicofino]

Hello,

I released a python script to monitor Oracle databases, you can learn more about it on http://bicofino.io/blog/monitoring-oracle-with-zabbix/

It uses Python with cx_Oracle library and the main feature is auto discovery of tablespaces.

Feel free to comment and contribute since the source is on github.

Best Regards,

Danilo Chilene

[jnsvano]

Hi,
You write in manual:

"I have installed Pyora on Zabbix Server, so I have a centralized script to monitor all my Oracle databases.
Add the line below to the file /etc/zabbix_agentd.conf
UserParameter=pyora[*],/home/zabbix/scripts/pyora.py --username $1 --password $2 --address $3 --database $4 $5 $6 $7 $8"

How could you monitor the client with the script installed on server, or do you mean that /etc/zabbix_agentd.conf is a configuration file of a client ?

[bicofino]

Hello,

I mean the /etc/zabbix_agentd.conf but with the same script I can monitor N databases, just need create a new host and set new MACROs.

[jnsvano]

OK, now I understood, thanks. Still couldn't figure out, what defines {$ARCHIVE} macro ?
And, also some checks are not supported (pyora returns "None"), where could be a problem, in user rights or Oracle version (mine is 10.2.0.5):
dbprllwrite, enqueue, freebufwaits, lastapplarclog, query_lock, query_rollbacks, etc....
Also all discovered "Tablespace Use" are not supported.

[bicofino]

Hi,

{$ARCHIVE} macro is for monitoring archive usage.

Try run the queries that return None on Oracle and see if it returns different.
You did some changes on the script and template, are you using the latest version? Check on github.com.

If the error continues check your zabbix_agentd.log and zabbix_server.log

[lbjensen]

No data from pyora

Hi,

First, thanks for your plugin. This is just what I'm looking for, but I cannot get the plugin to send data.

I can connect to the database from the zabbix server.
I can get a result when I run
python /opt/Pyora/pyora.py --username user --password passwd--address IP--database prod show_tablespaces

Then all the tablespaces are listed.

In my zabbix-server.log there are these errors:
[hostnameyora[{$USERNAME},{$PASSWORD},{$ADDRESS},{$DATABASE},sho w_tablespaces]] became not supported: Not supported by Zabbix Agent

Could you give me some hints where to search for a solution

Cheers
Lars Baad-Jensen
Denmark

[jnsvano]

I have the same issue, but when tablespaces is the last thing I'm interested in, I'd like do disable anything related to it.
And, bicofino, I bet you mean by ARCHIVE = ASM, isn't it ?

[bicofino]

Hello Lars,

Are you using the template from github? Because the item show_tablespaces isn't supported on Zabbix.

The correct item is python pyora.py --username pyora --password secret --address 127.0.0.1 --database DATABASE tablespace SYSTEM, that is already in the template as a discovery rule. So you don't need to add tablespaces to monitor.

And yes ARCHIVE means ASM.
You can disable the discovery rule on the template if you don't want monitor tablespaces.

Cya.

[lbjensen]

Is this correct then?

Hi again

OK, thanks for clarify.
Yes, I'm using the github template.

Is this the correct expression?
pyora[{$USERNAME},{$PASSWORD},{$ADDRESS},{$DATABASE}, tablespace {#TABLESPACE}]

Cheers
Lars Baad-Jensen
Denmark

[bicofino]

Hello,

The discovery that use show_tablespaces.

The correct expression on item prototype is pyora[{$USERNAME},{$PASSWORD},{$ADDRESS},{$DATABASE},tab lespace,{#TABLESPACE}]

You can test on your shell also:

Testing using the script:
pyora.py --username zabbix --password zabbix --address 192.168.0.1 --database PROD tablespace USERS

Using zabbix_get(replace 127.0.0.1 with whatever address is configured the script pyora.py on zabbix_agentd.conf)
zabbix_get -s 127.0.0.1 -k pyora[zabbix,zabbix,192.168.0.1,PROD,tablespace,SYSTEM]

Cya.

[jnsvano]

Well, when ARCHIVE is ASM, it's good I tried to use it, but it says "Not supported" by zabbix.
I checked this through direct SQL query from pyora.py:
select trunc((total_mb-free_mb... : it's OK.

When I try to run:
/opt/pyora/pyora.py --username zabbix --password xxx --address 127.0.0.1 --database mydb check_archive DATA
(so DATA or +DATA is my ASM) I got an error:
cx_Oracle.DatabaseError: ORA-00942: table or view does not exist

It seems to be that something is wrong with pyora.py arguments ?

[bicofino]

Hi,

You tried the query directly on Oracle? For me it's working fine.
Also check if user have sufficient rights.

[jnsvano]

Thank you, this is user rights issue. Any idea how to get ordinary user sufficient rights for this ? I tried:
grant select on v$_asm_diskgroup_stat to zabbix
- but it doesn't help, same error.

[bicofino]

I'm using the below:
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;

If don't work try ask a DBA.

[dalle]

For what I can see the "query pack" is more or less the same of orabbix so if you want create an user with limited access to your database you can follow that documentation