Metricbeat: Oracle Module

[pickypg]

Metricbeat already supports MySQL. It would be great if it could also support Oracle.

As a side note, here's a list of SQL Drivers that may prove handy: https://github.com/golang/go/wiki/SQLDrivers

[kwmullet]

here here!

[fkgre]

+1

[zzs92dream]

which module surrports oracle now ?

[odbaeu]

I'm making good progress is writing an oracle beat.
https://github.com/odbaeu/oracledbbeat
Update (2017-06-12): The above repo is currently on hold. New metricbeat module repo for oracle database: https://github.com/odbaeu/oracledb_metricbeat

I will focus on availability first and later on performance.

Hints or comments are very much welcome. Just open an "Issue" on my repo.

[zzs92dream]

That's great! thanks a lot @odbaeu

[andrewkroh]

@odbaeu Great! If you have questions while developing please let us know! Also there's IRC (which is handy for a quick question when one of us online).

You should consider developing this as a module for Metricbeat and use Metricbeat as a framework to have it in a standalone Beat.

[odbaeu]

I'm back from a short break from this project. Thanks for the encouraging words.

@andrewkroh did you check my source code? I think that's what I started doing... or am I totally wrong?

[ruflin]

@odbaeu I think you created your own beat by using libbeat as a library. You can also use metricbeat as a library which will make it contributing back as a module much easier. See https://www.elastic.co/guide/en/beats/metricbeat/current/creating-metricbeat-module.html

[fkgre]

anyupdate?

[odbaeu]

I continue to write a metricbeat module here: https://github.com/odbaeu/oracledb_metricbeat
Please note that I never compiled the module to test it in a "real" environment.

[chenyg0911]

@odbaeu Hope for your oracledb_metricbeat.

[sayden]

We have started a POC to introduce the Oracle Metricset module here: #11890 At the beginning we want to have a very curated scope with very useful features.

• Our initial target version is 12c R2. An Oracle Docker image is used for development and testing containing store/oracle/database-enterprise:12.2.0.1 obtained from the Oracle Docker Hub. Version 12c R2 was released in 09/2016 on Cloud and 03/2017 for On prem according to this info. 12c R2 version is still supported but it's not the last version, which is 18c from 2018. However I couldn't find Docker image of 18c.
• The module will require a library called instant client to be installed manually in the Metricbeat machine and it seems there's not workaround to overcome this issue. We haven't seen issues using this library with the Go package we are using now https://gopkg.in/goracle.v2
• It will be initially merged with a single Metricset called tablespaces which mainly contains size information about each tablespace and tablespaces files. (DBA permissions are required to access this data).
• More metricsets will be merged containing buffer, caches and indices performance as soon as we merged the initial metricset and module.
• A Kibana dashboard is also under development already.

[odbaeu]

@sayden, thank you very much for taking on! I could not continue my project.

• Please refer to the official Oracle git repo for all kinds of docker files. For example the Oracle 19c docker file.
• For the package goracle.v2 the only thing what should be necessary is the environment variable LD_LIBRARY_PATH which points to the client. When installing the beat on a database server, you do not need an instant client. You can set the path like in the 19c docker file: LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
• If the beat runs in a docker container you can use Oracle Linux as docker image. In Oracle Linux you can install the Instant Client via yum.
  In my build image, I simply added the following lines to the official Oracle Instant Client docker file:

## *****************************************************************************
## Custom modifications
## gcc required to run cgo
ARG GOLANG_VERSION=1.12.5
ARG ARCH=linux-amd64
RUN yum -y install gcc git glibc wget mingw-w64-tools mingw64-gcc mingw64-gcc-c++
RUN wget https://dl.google.com/go/go${GOLANG_VERSION}.${ARCH}.tar.gz
RUN tar -C /usr/local -xzf go${GOLANG_VERSION}.${ARCH}.tar.gz
ENV PATH "$PATH:/usr/local/go/bin"
# The LD_LIBRARY_PATH is not even needed. But in case, it should look like this:
#ENV LD_LIBRARY_PATH "/usr/include/oracle/18.1/client64"

[odbaeu]

BTW: Here are my two main ideas for the beat:

a) Individual monitoring interval of different checks.
For example tablespace data can be captured each 4 hours but the uptime of a database may be captured each minute.
b) Monitoring SQLs should no be hardcoded in the Beat. There is no problem with hardcoding SQLs but I wanted to provide the possibility to write own SQL checks via the config file for people who are more admin than developer.

There are some challenges when monitoring Oracle databases:
To uniquely identify a database we need to keep a few things in mind. It's not so easy to find a practical solution which at the same time meets all monitoring needs.

I came up with the following solution which is based on multiple monitoring projects:

• According to Oracle the DBID (v$database) should always uniquely identified a database. In case of a Data Guard environment this is not the case anymore. Therefor we never monitor a Data Guard Standby Database (all relevant information about the standby(s) can be gathered from the primary database).
• We do not monitor each RAC instance individually. Because if we do so, we monitor the database parts multiple times. This may lead to multiple alerts on (for example) tablespace usage. All relevant information about the other nodes can be captured via the gv$... views (including performance, status, etc.).
• In a larger RAC environment one database does necessarily run on each node. The beat may not know on which nodes the database runs. Therefor the database should always be accessed via the scan listener.
• Multitenant architecture: Each pluggable database (PDB) is identified by the column dbid in v$pdbs, where the column dbid in v$database represents the dbid of the container database. v$pdbs contains only one record inside a PDB.

I think in each record we need the following information:
CDB and non-CDB:

• dbid
• additionally to dbid the more human readable: dbName
• instanceName
• dbUniqueName for Data Guard environments

PDB:

• all from CDB and non-CDB
• pdbDbid
• additionally to pdbDbid the more human readable: pdbName

Source:
dbid, dbName, dbUniqueName: select dbid, name, db_unique_name from v$database
instamceName: select name from v$instance
pdbDbid, pdbName: select dbid, name from v$pdbs

[sayden]

I'll close this because Oracle is already supported in Metricbeat. New requirements on the module must go on a different issue if possible.

Thanks all for the feedback and help! 🙂