WIXLIB

Monitoringdatabases withzabbix Ronald Rood @ik zelf1

What I doprincipal consultant @ Experis Ciber[Oracle] DBA, also postgres, cockroachDBOracle ACEOracle Certified MasterFather of 2ScoutingSkeelerhttp://twitter.com/ik atabasesmonitoring enthusiastIT veteran, does not believe something is impossible2 Database monitoring using Zabbix

What Experis Ciber doesKlik om de tekststijl Klik om de tekststijl van het model te bewerkenvan het model te

Monitoring solutionshome grown scripts and emailOracle Enterprise ManagerOracle Grid ControlOracle Cloud ControlNagiosZabbixheart beat . 4 Database monitoring using Zabbix

Why Zabbix?The design principle: KISSHeartbeatMature[Re]active developmentActive community IRC is very active irc://verne.freenode.net/zabbix Zabbix forum https://www.zabbix.com/forum/Large installed baseVery stableGood Oracle database citizen (can still be improved)easy to extend with plugins5 Database monitoring using Zabbix

Installation of zabbix - on OracleInstallation from source - name: install instant client /usr/lib/oracle/12.1/client/ yum: name {{ item }} state present with items: - oracle-instantclient12.1-basic-12.1.0.2.0-1.x86 64.rpm - oracle-instantclient12.1-devel-12.1.0.2.0-1.x86 64.rpm - oracle-instantclient12.1-precomp-12.1.0.2.0-1.x86 64.rpm - oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86 64.rpm ./configure --enable-server --with-oracle yes --with-oracle-include /usr/include/oracle/12.1/client64 -with-oracle-lib /usr/lib/oracle/12.1/client64/lib --with-net-snmp --with-ssh2 --with-openipmi --with-ldap--with-libcurl --with-jabber --with-unixodbc --with-openssl --with-libxml2"pre build packages yum zabbix offical yum repository at http://repo.zabbix.com/server and proxy must have same main version this is a pity, nice would be to have backward compatibility to make upgrades more manageableserver (and proxy) supports all versions of agents wow!6 Database monitoring using Zabbix

Configuration of zabbixServerAgentProxy7 Database monitoring using Zabbix

Configuration of zabbixserver does all central functionsupdate databaseinsert new datamaintain historyanalyse datatrigger alertsactivate scripts/actionspush notifications - Telegramticket creationsmaintain history8 Database monitoring using Zabbix

Configuration of zabbix - with ansibleagent does data collectionactivepassiveauto registration - must be active agent- name: adjust agent config filelineinfile: dest {{ item.file }} regexp " {{ item.key }} * " line "{{ item.key }} {{ item.value}}" create yes state presentwith items:- { file: /etc/zabbix/zabbix agentd.conf, key: LogFileSize, value: 1 }- { file: /etc/zabbix/zabbix agentd.conf, key: User, value: zabbixa }- { file: /etc/zabbix/zabbix agentd.conf, key: Hostname, value: "{{ zabbix hostname }}" }- { file: /etc/zabbix/zabbix agentd.conf, key: HostMetadataItem, value: "system.uname" }- { file: /etc/zabbix/zabbix agentd.conf, key: Server, value: "{{ zabbix agents server }}" }- { file: /etc/zabbix/zabbix agentd.conf, key: ServerActive, value: "{{ zabbix agents server}}" }9 Database monitoring using Zabbix

Configuration of zabbixproxy way to couple networks to serveractivepassivedoes caching when server not reachablesince v3 encryption supportedthink about upgrades!10 Database monitoring using Zabbix

Upgrade from v2 to v3 to v41.2.3.4.5.6.7.8.9.Prepare packagesshutdown proxiesshutdown serverwhen using sqlite as proxy database: remove cache database (no upgrade)upgrade proxiesstart proxies - this re-creates the cache database (no upgrade for sqlite)upgrade serverstart servercached data comes in first (maintenance mode might prevent a few alerts)Downtime is a matter of minutesDatabase upgrade is automatic11 Database monitoring using Zabbix

Data collectiondata collection is not limited by agentsuser scripts can collect data and act as extension for active agents key,script - stdout keep it quick!user scripts can collect data and use zabbix sender to send data to serverzabbix sender not only handles measurements, also lld json arrays (on one line)12 Database monitoring using Zabbix

templatesthe biggest pitfall is creating items on hostsalmost certainly there will be more hosts with same item[s]create templatesuse MACROS for tunablesuse value lists to explain the meaning of valuesuse prefixes to visually relate MACROS to templates13 Database monitoring using Zabbix

lld - Low Level Discoveryvery powerful mechanism to detect variable lists of items (tablespaces,users)LLD basically gives lists of itemspasses a json array to the serverthink about what happens when item is no longer discoveredThe discovered items can have all kinds of definitions on them (triggers,graphs, screens)In the discovery rule there are the prototypesalso possible for HOSTS14 Database monitoring using Zabbix

example lld data Permanent TableSpaceshost p ts.lld 1458212406 {“data":[{"{#TS NAME}": "SYSTEM", "{#PDB}": null}, {"{#TS NAME}": "CTXD", "{#PDB}": null}, {"{#TS NAME}": "OWAPUB", "{#PDB}": null}, {"{#TS NAME}": "ODM", "{#PDB}": null} snip ]}host has to be known in zabbix and have the template attachedp ts.lld has to be a key in the discovery rules for the host1458212406 unix timestamp when the discovery was done (date “ %s”)data is the discovered arrayThere will be item prototypes where {#TS NAME} and {#PDB} are placeholders forthe names in Item PrototypesWhen using zabbix sender, make sure the complete array is on 1 line.15 Database monitoring using Zabbix

Item prototypes for p ts.lldfffZabbix Trapper and Calculated types.16 Database monitoring using Zabbix

Simple item, from Zabbix trapper17 Database monitoring using Zabbix

Calculated Item prototype18 Database monitoring using Zabbix

Predictive Item Prototypehow much data to analyze?19 Database monitoring using Zabbix

Example data for zabbix senderhost p ts[,USERS,maxsize] 1458212417 524288000host p ts[,SYSTEM,maxsize] 1458212417 18027118592host p ts[,APPS TS INTERFACE,maxsize] 1458212417 18874368000host p ts[,ODM,maxsize] 1458212417 104857600Think about quoting!Space is column delimiter, if space can be in key, quote the keyMissing value? - null20 Database monitoring using Zabbix

Host discoveryagent can register itself to the serverserver can scan for new hosts in the networkwith zabbix sender we can auto define hosts using templatesa host is owner of discovered items (also discovered hosts)21 Database monitoring using Zabbix

Database monitoring integrationthere are several tools to monitor databases and pass data to zabbixZabbix since v3 also has internal odbc supporttools like dbforbix Java based and a bit hard to grasp (for me) .py is born - Oracle only https://github.com/ikzelf/zbxorazbxdb added as refactored copy of zbxora but database agnostic /zbxdb-genericdatabase-plugin https://github.com/ikzelf/zbxdb22 Database monitoring using Zabbix

Database monitoring integration - zbxdbzbxdb is a zabbix plugin consisting ofzbxdb.pydatabase query files for primary/standby/asm instanceszabbix template Low Level Discovery rules (lld)itemstriggersgraphsqueries per vendor per version of databasezbxdb starterzbxdb senderzbx alertlog.shzbx discover oradbsworks from zabbix v2 (never used v1)Database versions depend on their python driver availability and capabilities23 Database monitoring using Zabbix

Database monitoring integration - zbxdbVery user create-new-metrics-for-zbxora.htmlVery openVery simple to useruns from a client (the machine running the proxy is a good candidate)needs a regular Oracle client installation (instant client is OK) if monitoring oraclerequires python 3 or newerrequires database driver[s]monitors itselfcollects data in files per connectionzbxdb sender collects the zbxora output and sends them to the server(crontab)zbxdb sender keeps a little history for debugging purposeszbxdb starter is meant to guarantee your monitors are running (crontab)do NOT run as root or any database ownerdoes NOT need any special OS privilegeruns as a regular database client with monitoring privileges in the databaseuses 1 session per database and tries to keep that forever24 Database monitoring using Zabbix

Configuration of zbxdb[zbxdb]db url //IP-ADDRESS/ORAPROD1username cistatspassword db type oracledb driver cx Oracleinstance type rdbmsrole normalout dir HOME/zbxora outhostname OracleDB1checks dir etc/zbxdb checkssite checks sap,ebspassword enc Z2xhQUMzYTdiNote password encInitially enter password and leave password enc empty.Upon first start zbxdb will fill password enc with an ‘encrypted’ version ofpassword and clear password in the config file.25 Database monitoring using Zabbix

other zbxdb config example[zbxdb]db url: //localhost/fsdb01username: cistatspassword: knowoneknowsdb type: msssql# db type: postgres# db type: mysql# db type: mssql# db type: db2server: hostname.domainserver port: 1433db name: masterdb driver: pytds# db driver: psycopg2# db driver: mysql.connector# db driver: ibm db dbirole: normal# for ASM instance role should be SYSDBAout dir: HOME/zbxora outhostname: testhostchecks dir: etc/zbxdb checkssite checks: NONEusing Zabbix26 Database monitoringinstance type:rdbms Database monitoring using Zabbix

db typeCan be anything.db type is used to find the SQL files in {checks dir}db type should have it’s own directory in {checks dir}db type is also used to load the corresponding module fromdbconnections27 Database monitoring using Zabbix

db driverzbxdb uses this driver to connect to the database.It needs to be installed separately.Since the driver raises the errors and since the drivers have different ways toreport errors, there is also a drivererrors moduleif you want to use a different driver, just create thecorresponding script in drivererrors/ so it can be loaded byzbxdb28 Database monitoring using Zabbix

site checksThe intention is to have your site or application specific checks here. In the gitcode there are only generic SQL’s aiming mostly on availability and capacity.If no site checks, just remove the parameter or make it empty.29 Database monitoring using Zabbix

instance typeIn Oracle we have RDBMS, ASM for instance types. For Oracle, thedbconnection module detects this byself. Others can do the same but for now, itis input - and mostly ‘rdbms’30 Database monitoring using Zabbix

Usage TIPs for zbxdbuse zabbix server or zabbix proxy server as monitoring hostuse a separate Linux account to run zbxdbno special OS privileges neededdo NOT run as root or a database ownerdoes need zabbix senderzabbix sender needs access to zabbix server or zabbix proxyzbxdb hardly uses any CPU and is most of the time sleepingsince zbxdb runs a separate process for every database, use zbxdb starterzbxdb starter launches all configuration that it finds with a second sleepbetween 2 starts, making sure there are no CPU spikes on the serverif zbxdb wakes up on the 13th second, it will always try to wake up on a 13thsecond.zbxdb also monitors itself, if the script changes, it will relaunch itselfzbxdb also monitors the checks files. If they change, they will be reloadedzbxdb also monitors it’s configuration file. If it is changed and zbxdb is notconnected to a database, it will reload the config file.zbxdb uses about 24KB memory per instance.31 Database monitoring using Zabbix

Oracle Alertlog monitoring - lldAlertlog discovery done by zbx alertlog.sh and should be used as a userparameter for zabbix agent.If used for Oracle, the agent’s OS account should also have the Oracle dbagroup membership becausealertog.sh will try to connect to each running instance to find the log.xml location thatis passed to zabbix.needs to be able to read the log.xmlWe also send lines with ‘time ’ to the server so eventually alerts can be cleared.For that we make sure that our databases perform a log switch at least everyhour causing some lines to be written.32 Database monitoring using Zabbix