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!
Grafana plugin for Oracle Enterprise Manager has been released which allows you to directly read data from the repository itself. Please check Oracle downloads.
What does job JOB_ENVIA_INFLUXDB do? How is it defined?