{"id":898,"date":"2024-06-04T16:13:29","date_gmt":"2024-06-04T14:13:29","guid":{"rendered":"https:\/\/anjo.pt\/keyword-oracle\/?p=898"},"modified":"2024-06-10T09:52:13","modified_gmt":"2024-06-10T07:52:13","slug":"how-to-use-zabbix-to-monitor-oracle-db-a-quick-test-run","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2024\/06\/04\/how-to-use-zabbix-to-monitor-oracle-db-a-quick-test-run\/","title":{"rendered":"How to use Zabbix to monitor Oracle DB – a quick test run"},"content":{"rendered":"\n

Currently I’m testing different solutions for monitoring (mostly) Oracle databases. One solution in the shortlist is Zabbix.<\/p>\n\n\n\n

Zabbix is open-source and currently has a quite active community helping out.<\/p>\n\n\n\n

On Zabbix website there is a nice quick download and setup instructions<\/a>:<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

I’ll use a Oracle Linux 9 VM under Windows WSL2<\/a> for installing Zabbix. For the exercise, I’ll configure it with PostgreSQL. The database installation step is missing. So, here are all the steps I’ve done.<\/p>\n\n\n\n\n\n\n\n

1. Install Zabbix<\/h3>\n\n\n\n

Disable using Zabbix packages provided by Oracle-epel, by adding “excludepkgs=zabbix*” to \/etc\/yum.repos.d\/oracle-epel-ol9.repo :<\/p>\n\n\n

\n# cat \/etc\/yum.repos.d\/oracle-epel-ol9.repo\n\n[ol9_developer_EPEL]\nname=Oracle Linux $releasever EPEL Packages for Development ($basearch)\nbaseurl=https:\/\/yum$ociregion.$ocidomain\/repo\/OracleLinux\/OL9\/developer\/EPEL\/$basearch\/\ngpgkey=file:\/\/\/etc\/pki\/rpm-gpg\/RPM-GPG-KEY-oracle\ngpgcheck=1\nenabled=1\nexcludepkgs=zabbix*\n<\/pre><\/div>\n\n\n
Add Zabbix repository<\/p>\n\n\n
\n# rpm -Uvh https:\/\/repo.zabbix.com\/zabbix\/6.4\/rhel\/9\/x86_64\/zabbix-release-6.4-1.el9.noarch.rpm\n\n# dnf clean all\n<\/pre><\/div>\n\n\n
Install Zabbix server, frontend and agent<\/p>\n\n\n
\n# dnf install zabbix-server-pgsql zabbix-web-pgsql zabbix-nginx-conf zabbix-sql-scripts zabbix-selinux-policy zabbix-agent\n<\/pre><\/div>\n\n\n
2. Install PostgreSQL<\/h3>\n\n\n\n
At the time of writing, the latest version was PostgreSQL 16. Again, to make sure I get the packages from the best source, I install posture repo<\/p>\n\n\n
\n# dnf install https:\/\/download.postgresql.org\/pub\/repos\/yum\/reporpms\/EL-9-x86_64\/pgdg-redhat-repo-latest.noarch.rpm\n<\/pre><\/div>\n\n\n
And install the latest version<\/p>\n\n\n
\n# dnf install postgresql16 postgresql16-server\n<\/pre><\/div>\n\n\n
Then we initialize and start the postgreSQL server (I do not enable the service, as this is only a test)<\/p>\n\n\n
\n# \/usr\/pgsql-16\/bin\/postgresql-16-setup initdb\nInitializing database ... OK\n\n# systemctl start postgresql-16\n\n# ss -antpl | grep 5432\nLISTEN 0      200        127.0.0.1:5432       0.0.0.0:*    users:(("postgres",pid=23637,fd=7))\n<\/pre><\/div>\n\n\n
3. Create and configure Zabbix repository DB<\/h3>\n\n\n\n
Create a user called zabbix and then a database also called zabbix, having the created user as owner<\/p>\n\n\n
\n# sudo -u postgres createuser --pwprompt zabbix\n# sudo -u postgres createdb -O zabbix zabbix\n<\/pre><\/div>\n\n\n
Fill up the repository DB with the initial schema and data. It will make a long output<\/p>\n\n\n
\n# zcat \/usr\/share\/zabbix-sql-scripts\/postgresql\/server.sql.gz | sudo -u zabbix psql zabbix\n<\/pre><\/div>\n\n\n
Put the Password you setup for zabbix user above in the configuration file<\/p>\n\n\n
\n# read -s -p "Zabbix password:" DBPWD\n# sed -i -E "s\/^# DBPassword=\/DBPassword=$DBPWD\/" \/etc\/zabbix\/zabbix_server.conf \n<\/pre><\/div>\n\n\n
4. Configure the nix webserver <\/h3>\n\n\n\n
Set the port and hostname in configuration file<\/p>\n\n\n
\n# sed -i -E 's\/^#.*listen\/listen\/' \/etc\/nginx\/conf.d\/zabbix.conf\n# sed -i -E "s\/^#server_name.*\/server_name $(hostname -f);\/" \/etc\/nginx\/conf.d\/zabbix.conf\n# grep -E 'listen|server_name' \/etc\/nginx\/conf.d\/zabbix.conf\n<\/pre><\/div>\n\n\n
On WSL2 you need to find your computer IP address and then you can access the Zabbix:<\/p>\n\n\n
\n# hostname -I\n172.31.152.146\n<\/pre><\/div>\n\n\n
5. Finish configuration<\/h3>\n\n\n\n
Then you can continue with Zabbix configuration on the web browser<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
As Zabbix per default uses the Public schema from PostgreSQL, on the screen below, leave the field “Database Schema” empty. <\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
On the next screen it asks for the Server name, and you can give the result of $(hostname) command.<\/p>\n\n\n\n
And you can login to Zabbix. The default user is Admin, and password zabbix:<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
6. Install Zabbix Agent2<\/h3>\n\n\n\n
On the server with Oracle database, download and install Zabbix Agent2<\/p>\n\n\n
\n# rpm -Uvh https:\/\/repo.zabbix.com\/zabbix\/6.4\/rhel\/9\/x86_64\/zabbix-release-6.4-1.el9.noarch.rpm\n\n# dnf clean all\n\n# dnf install zabbix-agent2\n<\/pre><\/div>\n\n\n
Update the Agent configuration file. After some fight, these were the sittings I changed in the monitored server \/etc\/zabbix\/zabbix_agent2.conf file:<\/p>\n\n\n
\nServer=0.0.0.0\/0           # Accept connections from any Zabbix Server\nListenPort=12051            # Port where Agent listen for Server requests - default Port did not work\nServerActive=127.0.1.2  # IP Address of the Zabbix Server\nHostname=Anjo-X13      # Hostname of the monitored server\n<\/pre><\/div>\n\n\n
And restart the Agent<\/p>\n\n\n
\n# systemctl restart zabbix-agent2\n<\/pre><\/div>\n\n\n
Create Oracle user<\/h3>\n\n\n\n
On the database to be monitored, create a database user:<\/p>\n\n\n
\nexport z_crd="zabbix_pwd"\n\n$   sqlplus -S -L \/ as sysdba <<EOF\nSET PAGES 0 HEAD OFF ECHO OFF FEED OFF\nWHENEVER SQLERROR EXIT SQL.SQLCODE\nCREATE USER c##zabbix_mon IDENTIFIED BY "${z_crd}";\nALTER USER c##zabbix_mon SET CONTAINER_DATA=ALL CONTAINER=CURRENT;\nGRANT CONNECT, CREATE SESSION TO c##zabbix_mon;\nGRANT SELECT_CATALOG_ROLE to c##zabbix_mon;\nGRANT SELECT ON v_\\$instance TO c##zabbix_mon;\nGRANT SELECT ON v_\\$database TO c##zabbix_mon;\nGRANT SELECT ON v_\\$sysmetric TO c##zabbix_mon;\nGRANT SELECT ON v_\\$system_parameter TO c##zabbix_mon;\nGRANT SELECT ON v_\\$session TO c##zabbix_mon;\nGRANT SELECT ON v_\\$recovery_file_dest TO c##zabbix_mon;\nGRANT SELECT ON v_\\$active_session_history TO c##zabbix_mon;\nGRANT SELECT ON v_\\$osstat TO c##zabbix_mon;\nGRANT SELECT ON v_\\$restore_point TO c##zabbix_mon;\nGRANT SELECT ON v_\\$process TO c##zabbix_mon;\nGRANT SELECT ON v_\\$datafile TO c##zabbix_mon;\nGRANT SELECT ON v_\\$pgastat TO c##zabbix_mon;\nGRANT SELECT ON v_\\$sgastat TO c##zabbix_mon;\nGRANT SELECT ON v_\\$log TO c##zabbix_mon;\nGRANT SELECT ON v_\\$archive_dest TO c##zabbix_mon;\nGRANT SELECT ON v_\\$asm_diskgroup TO c##zabbix_mon;\nGRANT SELECT ON sys.dba_data_files TO c##zabbix_mon;\nGRANT SELECT ON DBA_TABLESPACES TO c##zabbix_mon;\nGRANT SELECT ON DBA_TABLESPACE_USAGE_METRICS TO c##zabbix_mon;\nGRANT SELECT ON DBA_USERS TO c##zabbix_mon;\nEOF\n<\/pre><\/div>\n\n\n
7. Add host to Zabbix server<\/h3>\n\n\n\n
On Zabbix web interface go to “Monitoring” – “Hosts” and click “Create Host” on the top right.<\/p>\n\n\n\n
This is the configuration that worked with me. The IP address is the one from Anjo-X13, the monitored server.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
On the “Macros” tab I had to add the following three parameters and respective values<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
After this, I restarted the agent and started to get information.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
Zabbix seems quite complete solution, even if the wording used is not very clear. I did not manage to make the “Named Sessions” to work, which would allow to configure the database credentials directly on the Agent configuration file.<\/p>\n\n\n\n
Two monitoring “Items” I disabled:<\/p>\n\n\n\n
    \n
  • oracle.redolog.info – it expects minimum 5 redolog groups, which I do not have on my test DBs<\/li>\n\n\n\n
  • oracle.tbs_used_pct – which checks the percentage of allocated space used for each tablespace, not looking at the autoextend (there is oracle.tbs_used_from_max_pct for that).<\/li>\n<\/ul>\n\n\n\n
    When looking for a lighter alternative to Enterprise Manager, which also monitors other RDBMS, Zabbix is something to consider. <\/p>\n","protected":false},"excerpt":{"rendered":"
    Currently I’m testing different solutions for monitoring (mostly) Oracle databases. One solution in the shortlist is Zabbix. Zabbix is open-source and currently has a quite active community helping out. On Zabbix website there is a nice quick download and setup instructions: I’ll use a Oracle Linux 9 VM under Windows WSL2 for installing Zabbix. For […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-898","post","type-post","status-publish","format-standard","category-oracle","czr-hentry"],"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/898","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=898"}],"version-history":[{"count":4,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/898\/revisions"}],"predecessor-version":[{"id":920,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/898\/revisions\/920"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=898"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=898"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=898"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}