Monitoring Your Oracle Database With Grafana

Hi everybody,

Let’s talk about Dashboarding Oracle Databases with Grafana.

I always felt the need of a graphical monitoring tool for basic database things such as volume of archives, back-up archives, state of services, offline disks, space of diskgroup, consum of UNDO, consum of TEMP, space of filesystem, space of every diskgroups in all clusters. OEM seems just too much complicated to give a simple online graphical dashboard for this.

So I developed a “collector” of data that sends the data to Influxdb and generate these graphs. Simple like that.

Have a look on how it looks like:

Ok, but I how did it?
Here it goes a piece of code:

1 – Colect Data With Scheduler Procedure and Scheduler JOB:

----------------------------------------
-- Procedure:
----------------------------------------
CREATE OR REPLACE PROCEDURE SYS_DBA.PRC_ENVIA_DADOS_GRAFANA_5_MIN IS
var_MENSAGEM VARCHAR2(300);
var_BANCO VARCHAR2(30);
var_HOSTNAME VARCHAR2(50);
var_CLUSTER VARCHAR2(15);
var_VERSAO VARCHAR2(2);
var_CDB VARCHAR2(3);
var_RETORNO UTL_FILE.file_type;
CUR_LEVEL0 SYS_REFCURSOR;
CUR_DISCOS SYS_REFCURSOR;
CUR_DISCOS_OFFLINE SYS_REFCURSOR;
CUR_LEVEL1 SYS_REFCURSOR;
CUR_TABLESPACE2 SYS_REFCURSOR;
CUR_SEQUENCES_LEGADO SYS_REFCURSOR;
CUR_TAMANHO_DATABASE SYS_REFCURSOR;
CUR_DBTIME SYS_REFCURSOR;
CUR_STATUS_SERVICE_NAME SYS_REFCURSOR;
CUR_TABLESPACE SYS_REFCURSOR;
CUR_DISCOS_FRA SYS_REFCURSOR;
BEGIN
-- Set hostname
select HOST_NAME into var_HOSTNAME from v$instance;
-- Set cluster
select upper(substr(var_HOSTNAME,4,instr(var_HOSTNAME,'12')-2)) into var_CLUSTER from v$instance;
end if;
-- Type/Version of Database
select substr(version,0,2) into var_VERSAO from v$instance;
if var_VERSAO = '11' then
select UPPER(DB_UNIQUE_NAME) into var_BANCO from v$database;
end if;
if var_VERSAO = '12' then
execute immediate 'select cdb from v$database' into var_CDB;
if var_CDB = 'YES' then
execute immediate 'select UPPER(name) from v$pdbs' into var_BANCO;
end if;
if var_CDB = 'NO' then
select UPPER(DB_UNIQUE_NAME) into var_BANCO from v$database;
end if;
end if;
-- Opens file
var_RETORNO:=UTL_FILE.FOpen('DIR_GRAFANA',lower(var_BANCO) || '5min.txt','w');

-- Cursor oracle_level0
OPEN CUR_LEVEL0 FOR select 'oracle_level0,host=' || var_HOSTNAME || ',' || 'db=' || var_BANCO || ',' || 'status_level0=' || to_char(decode( count(*), '0', '0', '100')) || ' ' || 'quant=' ||
to_char(decode( count(*), '0', '0', '100'))
|| ';' || var_BANCO || ';' || upper('oracle_level0') || ';' || to_char(decode( count(*), '0', '0', '100'))
from (select session_key,
input_type,
status,
to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
time_taken_display,
round(OUTPUT_BYTES/1024/1024/1024,2) || ' GB' as "SIZE_IN_GB"
from v$rman_backup_job_details
where input_type='DB INCR'
and trunc(start_time) in (select distinct trunc(start_time) from gv$backup_set where incremental_level = 0 and start_time >= trunc(sysdate - 7))
and status = 'COMPLETED'
order by session_key desc);
LOOP
FETCH CUR_LEVEL0 INTO var_MENSAGEM;
EXIT WHEN CUR_LEVEL0%NOTFOUND;
-- Input Data
UTL_FILE.put_line(var_RETORNO,var_MENSAGEM);
END LOOP;
-- Close File
UTL_FILE.Fclose(var_RETORNO);
-- Send data to INFLUXDB
begin
dbms_scheduler.set_job_argument_value('JOB_ENVIA_INFLUXDB',1,LOWER(var_BANCO)||'5min');
dbms_scheduler.run_job('JOB_ENVIA_INFLUXDB');
EXCEPTION
when OTHERS then
dbms_output.put_line('Problems sending to INFLUXDB e APEXDBA.'||sqlerrm);
end;
END;
/
-- Scheduler JOB to Send the data:
begin
sys.dbms_scheduler.create_job(job_name => 'SYS_DBA.JOB_ENVIA_DADOS_GRAFANA_5_MIN',
job_type => 'PLSQL_BLOCK',
job_action => 'sys_dba.prc_envia_dados_grafana_5_min;',
start_date => to_date('25-03-2017 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
repeat_interval => 'Freq=Minutely;Interval=5',
end_date => to_date(null),
job_class => 'DEFAULT_JOB_CLASS',
enabled => true,
auto_drop => false,
comments => 'Scheduler job para envio de dados para grafana.');
end;
/

2 – Send data with Shell script

#! /bin/sh
## Send data to INFLUXDB
cat /tmp/$1.txt | cut -d ';' -f1 | curl -i 'http://host.net:8086/write?db=grafanadb' --data-binary @-;

3 – Plot Graph

This is subject for another post!

Enjoy the Oracle steps!