Hi,

I having trouble using a password that contains special characters like % .

Error is always the same, even if I put the password in quotes or not:

Unable to connect to oracle:invalid URL escape "%m" source=main.go:179'

Just need some help to make the connection work.

Hi,

I'm using custom metric to collect my database information, but when i start my oracledb_exporter， I got the problem:

$ ./oracledb_exporter -log.format "logger:syslog?appname=oracledb_exporter&local=1" -log.level info -default.metrics oracledb-metrics.toml
INFO[0000] Starting oracledb_exporter 0.2.2              source=main.go:335
ERRO[0000] Error scraping for rman_backup_status : No metrics found while parsing  source=main.go:188

This is my metrics :

[[metric]]
context = "rman_backup_status"
labels = [ "start_time", "input_type" ]
metricsdesc = { value="Gauge metric with rman backup status (5:FAILED; 4:RUNNING WITH ERRORS; 3:COMPLETED WITH ERRORS; 2:RUNNING WITH WARNINGS; 1:COMPLETED WITH WARNINGS; 0:COMPLETED)." }
request = "SELECT to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') as start_time, input_type, decode(status, 'FAILED', 5, 'RUNNING WITH ERRORS', 4, 'COMPLETED WITH ERRORS', 3, 'RUNNING WITH WARNINGS', 2, 'COMPLETED WITH WARNINGS', 1, 0) as value FROM v$rman_backup_job_details WHERE start_time = (SELECT max(start_time) FROM v$rman_backup_job_details)"

Execute in sqlplus：

SELECT to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') as start_time, 
             input_type, 
             decode(status, 'FAILED', 5, 'RUNNING WITH ERRORS', 4, 'COMPLETED WITH ERRORS', 3, 'RUNNING WITH WARNINGS', 2, 'COMPLETED WITH WARNINGS', 1, 0) as value 
  FROM v$rman_backup_job_details 
WHERE start_time = (SELECT max(start_time) FROM v$rman_backup_job_details);

START_TIME          INPUT_TYPE         VALUE
------------------- ------------- ----------
2019-07-21 19:00:22 DB INCR                1

the result is always only one row，what am i doing wrong?

Thanks,

for this example:

export DATA_SOURCE_NAME=system/oracle@myhost

which part is username? which part is password?

Hello,

When running release 0.22 (oracledb_exporter.0.2.2.darwin-amd64) I get the following when trying to execute:

/oracledb_exporter -log.level error -web.listen-address 9161
ERRO[0000] Error while connecting to user/password@MY_CONN source=main.go:76
panic: sql: unknown driver "oci8" (forgotten import?)

goroutine 1 [running]:
main.NewExporter(0xc000018041, 0x1b, 0x0)
/home/travis/gopath/src/github.com/iamseth/oracledb_exporter/main.go:77 +0x509
main.main()
/home/travis/gopath/src/github.com/iamseth/oracledb_exporter/main.go:351 +0x1d1

I do have the DATA_SOURCE_NAME environment variable set and have verified connectivity to the DB using oracle client. Thank you for your time and consideration!

This seems to be the same issue as #33. I am running v0.2.0 of the exporter. It's connected to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production. This is the error I get:

Mar 26 12:41:54 mgworker15.occ.liberty.edu oracledb_exporter-BBTSPRD[15598]: time="2019-03-26T12:41:54-04:00" level=error msg="Error scraping for wait_class:sessions : No metrics found while parsing" source="main.go:176"

The metric is defined as follows:

[[metric]]
context = "wait_class:sessions"
labels = [ "wait_class" ]
metricsdesc = { count = "Average number of sessions per wait_class per second in the past minute from gv$active_session_history." }
request = '''
select nvl(wait_class,'CPU + CPU Wait') as wait_class,
round(sum(1)/60, 2) count
from gv$active_session_history
where sample_time >= sysdate - (1/24/60)
group by wait_class
'''

I do not want to ignorezeroresult because this query should always have a value.

The query works fine when I run it as the user that the exporter is running as.

SQL> select nvl(wait_class,'CPU + CPU Wait') as wait_class,
round(sum(1)/60, 2) count
from gv$active_session_history
where sample_time >= sysdate - (1/24/60)
group by wait_class ;

WAIT_CLASS							      COUNT
---------------------------------------------------------------- ----------
CPU + CPU Wait							       1.35
Other									.05
Concurrency								.02
System I/O								.03
User I/O								.02

I have several other Oracle DBs that I am monitoring with this exporter and they're all using this exporter with this query without any problems.

We are planning to customize(.go) for our needs and would like to display multiple(fields) strings in the output using table format but we are getting the below error when we try to display that . can some one please help us to how to can add this functionality .go file to display more than 2 values in the grafana.

panic: inconsistent label cardinality: expected 2 label values but got 4 in []string{"STATUS", "!stdayofthe month", "sameday", "XXX"}
like as shown below

XXX "1st day of the month" "someday" "Running"

Any help would be much appreciated.

Hi,

I don't know how often does the exporter run the queries against the DB, is it like every minute, five minutes, etc.
I am asking as the use case is like, we may not want to metrics that often and some more often.
Is it possible to explicitly configure this somewhere?

Please excuse my question(or my lack of Prometheus way) as I am coming from ELK and metricbeat, where the metrics generation interval can be set for each module(exporter) and I am using this exporter for just Oracle DB.

Hello @iamseth, I appreciate your work, the oracledb_exporter you built is usable.
It would be really helpful if the oracle sql query would not be hard coded into the exporter.
The exporter would provide much more flexibility if each user could add his custom SQL query and associate a metric name to it over an external config file.
It is not difficult to change the code you provided and add additional query or replace existing ones but it would make more sense to keep the code and the versioning managed by you and the config file with each query/metric separated.
Let me know if you consider improving the oracledb_exporter as I suggested.
Thank you.
K.

Hi,
Thanks a lot for your exporter, it helps a lot for my job!
Here is question that I encountered during using this exporter, that is:

1、I have added a metric via my-custom-metrics.toml_

[[metric]]
context = "libraryCache"
labels = ["namespace"]
metricsdesc = { pinhits= "objects in cache", pins="all of the objects that executed", reloads="Total reload counts", invalidations="Metric with objects miss counts"}
request = "SELECT namespace, pinhits, pins, reloads, invalidations FROM v$librarycache"
metricstype = { namespace= "gauge" }

2、I can also get the metrics via Prometheus http://XXXXX:9090

3、The question is I cannot get the value of metrics via Grafana??

Regarding for your answers!
Thanks a lot!

time="2019-09-27T11:39:10+08:00" level=info msg="Starting oracledb_exporter 0.2.3" source="main.go:337"
panic: interface conversion: interface {} is nil, not string

goroutine 36 [running]:
main.GeneratePrometheusMetrics(0xc000186240, 0xc0001642a0, 0xc0001c80d4, 0x39f, 0x0, 0x0)
/home/travis/gopath/src/github.com/iamseth/oracledb_exporter/main.go:313 +0x6a0
main.ScrapeGenericValues(0xc000186240, 0xc0001b4180, 0xc000188710, 0xd, 0xc00018e400, 0x4, 0x4, 0xc000180f90, 0x0, 0x0, ...)
/home/travis/gopath/src/github.com/iamseth/oracledb_exporter/main.go:260 +0x11d
main.ScrapeMetric(0xc000186240, 0xc0001b4180, 0xc000188710, 0xd, 0xc00018e400, 0x4, 0x4, 0xc000180f90, 0x0, 0x0, ...)
/home/travis/gopath/src/github.com/iamseth/oracledb_exporter/main.go:216 +0xe8
main.(*Exporter).scrape(0xc0001686c0, 0xc0001b4180)
/home/travis/gopath/src/github.com/iamseth/oracledb_exporter/main.go:189 +0x21a
main.(*Exporter).Collect(0xc0001686c0, 0xc0001b4180)
/home/travis/gopath/src/github.com/iamseth/oracledb_exporter/main.go:145 +0x3c
main.(*Exporter).Describe(0xc0001686c0, 0xc000168720)
/home/travis/gopath/src/github.com/iamseth/oracledb_exporter/main.go:137 +0xb0
github.com/iamseth/oracledb_exporter/vendor/github.com/prometheus/client_golang/prometheus.(*registry).Register.func1(0x7ebf80, 0xc0001686c0, 0xc000168720)
/home/travis/gopath/src/github.com/iamseth/oracledb_exporter/vendor/github.com/prometheus/client_golang/prometheus/registry.go:218 +0x3b
created by github.com/iamseth/oracledb_exporter/vendor/github.com/prometheus/client_golang/prometheus.(*registry).Register
/home/travis/gopath/src/github.com/iamseth/oracledb_exporter/vendor/github.com/prometheus/client_golang/prometheus/registry.go:217 +0xa2

Excuse me, I met a question after the oracledb_exporter be running after more hours, the output occurs this:

ERRO[1942] Error pinging oracle: ORA-12537: TNS:connection closed source=main.go:136
ERRO[1947] Error pinging oracle: ORA-12537: TNS:connection closed source=main.go:136
ERRO[1952] Error pinging oracle: ORA-12537: TNS:connection closed source=main.go:136

Due to this, the path http://localhost:9161/metrics couldn't return any metric.

I use a image of webdizz/oracle-xe-11g-sa:latest up for oracle db.
After some research I login into the oracle container to tail the tnslnr listener.log:

tail -f /u01/app/oracle/diag/tnslsnr/cb2abd620214/listener/trace/listener.log

Thu Jun 29 03:45:31 2017
29-JUN-2017 03:45:31 * service_update * XE * 0
29-JUN-2017 03:45:34 * service_update * XE * 0
Thu Jun 29 03:46:15 2017
WARNING: Subscription for node down event still pending
29-JUN-2017 03:46:15 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=cb2abd620214)(USER=root))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=1866470
40)) * status * 0
WARNING: Subscription for node down event still pending
29-JUN-2017 03:46:18 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=cb2abd620214)(USER=root))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=1866470
40)) * status * 0
Thu Jun 29 03:47:17 2017
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cb2abd620214)(PORT=1521)))
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cb2abd620214)(PORT=8080))(Presentation=HTTP)(Session=RAW))

In our environment (linux) we are using oracle with one active db and one standby.
What we are seeing when running the exporter on the standby machine, is that the exporter is consuming more and more memory.
Eventually the memory consumption will go up to 80%.

ERRO[1477] Error pinging oracle: ORA-01033: ORACLE initialization or shutdown in progress
source=main.go:168
INFO[1477] Try to reconnect... source=main.go:171
ERRO[1477] Unable to connect to oracle:ORA-01033: ORACLE initialization or shutdown in progress
source=main.go:179

I'm using Oracle instant client 12.2

On the active db the exporter is behaving normally and stays around 0.1 / 0.2 %

I ran a build using the latest version of the code.

Hi,

I'm using custom metric to collect some space usage. But this metric is not scraping
Here is my query "select round((space_used-space_reclaimable)*100/space_limit,1) fra_usage from v$recovery_file_dest";

When i tested i cannot add metric request using this views v$recovery_file_dest, v$recovery_area_usage.
Other system views like v$sysmetric work fine.

What am i doing wrong?

Thanks,

Hi,

The exporter is not collecting metrics after DB restart. It's not creating new connection to database.
"Error pinging oracle: ORA-03114: not connected to ORACLE\n" source="main.go:168"

Thanks,

Would it be possible to provide some data_source_example with password?

I have small to 0 knowledge on Oracle and i am failing to setup a string with username, password, host, port and database

Could you provide the feature to customize exporter listening port on Docker container version?

hi
could you please advise how to run it on alpine?

[root@ecs-01 oracledb_exporter.0.2.1.linux-amd64]# ./oracledb_exporter -log.level debug -web.listen-address :9161 -default.metrics default-metrics.toml
INFO[0000] Starting oracledb_exporter 0.2.1 source=main.go:323
ERRO[0012] Error scraping for tablespace : Oracle query timed out source=main.go:176
INFO[0012] Listening on :9161 source=main.go:345
ERRO[0029] Error scraping for tablespace : Oracle query timed out source=main.go:176
ERRO[0045] Error scraping for tablespace : Oracle query timed out source=main.go:176
ERRO[0058] Error scraping for tablespace : Oracle query timed out source=main.go:176

What is the reason for this mistake?

Hi all,
I can't get the ORACLE fra information.But if I replace v$flash_recovery_area_usage with v$session, it works.

default-metrics.toml
[[metric]]
context = "test"
metricsdesc = { value = "oracle fra" }
request = "select sum(percent_space_used) as value from v$flash_recovery_area_usage"

[[metric]]
context = "test1"
metricsdesc = { value = "oracle fra" }
request = "select count(0) as value from v$flash_recovery_area_usage"

error.log
{"level":"error","msg":"Error scraping for test : No metrics found while parsing","source":"main.go:190","time":"2019-11-27T16:32:12+08:00"}
{"level":"error","msg":"Error scraping for test1 : No metrics found while parsing","source":"main.go:190","time":"2019-11-27T16:32:12+08:00"}

$ echo $DATA_SOURCE_NAME
dbmon/XXXXXXXXXX@test
$ sqlplus dbmon/XXXXXXXXXX@test

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 27 16:38:52 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sum(percent_space_used) as value from v$flash_recovery_area_usage;

 VALUE

  9.36

SQL> select count(0) as value from v$flash_recovery_area_usage;

 VALUE

 7

This is probably tangentially related to #34, but I want to continue discussion here.

TL;DR -- the Linux releases of versions 0.2.2 and 0.2.1 do not work with Oracle Client 12.2. I'm guessing this is due to cf9761d or ab9debd, but I wanted to confirm that this is expected behavior.

Is there some way to compile the binary in a way that allows for different versions of Oracle client to be installed on the target system?

Hi - do you have any dashboards for grafana laying around - could maybe be checked in and follow the code and/or shared at grafana.net ?

Hi,

I would like to add a custom query (sql select read from config) that generates a gauge per row. I have some backend devs here who could like to formulate a query/view best.

will be my first go code :-) , i nearly got lost compiling the thing on windows (the oracle driver parts).

my idea would be like:

content, err := ioutil.ReadFile(*configFile)
err = yaml.Unmarshal(content, &config)

// ScrapeCustomSQL collects a custom query wiith name/value columns in its rows.
func ScrapeCustomSQL(db *sql.DB, ch chan<- prometheus.Metric, config.sqlQuery string) error 
..
 rows.Scan(&name, &value)

ch <- prometheus.MustNewConstMetric(
     prometheus.NewDesc(prometheus.BuildFQName(namespace, "custom", name),
        "Generic counter metric from sql in Oracle.", []string{}, nil),
    prometheus.GaugeValue,
    value,

The default scape interval 5s is too frequent for some targets. How can I change it except Modifing the source code? Further more，maybe different targets need different scape interval. Besides, is there any ways to configure other parameters, such as the size of cashed data，log file and so on.

Please suggest how to connect multiple data source and get the guery output using this

Hi all,
i'm testing 0.2.2 precompilated version for linux
i tried to configure the dsn with something like :
DATA_SOURCE_NAME="/@myservice as ssydba"
but it seems not supported.
The idea is monitoring the databases from the database server itself without managing dedicated monitoring user and password for every database.

Do you think this kind of feature is possible ?

Thanks a lot!

[[metric]]
context = "recovery_file_dest"
labels = [ "name" ]
metricsdesc = { bytes="recovery dest total size",usebytes="used sizes",reusebytes="reclaime size" }
request = '''
SELECT t.NAME as "name",
decode(t.SPACE_LIMIT, 0, '-1', t.SPACE_LIMIT) as "bytes",
decode(t.SPACE_USED, 0, '-1', t.SPACE_USED) as "usebytes",
decode(t.SPACE_RECLAIMABLE, 0, '-1', t.SPACE_RECLAIMABLE) as "reusebytes"
from v$recovery_file_dest t
'''

Hi all,
Under Running section, we can see the steps, however looks that not the complete instruction for the execution of the exporter.

$ /path/to/binary -l log.level error -l web.listen-address 9161
-bash: /path/to/binary: No such file or directory
$

can someone help me what is "/path/to/binary" path in this context?

Hello,

Error scraping for wait_time: sql: Scan error on column index 1: converting driver.Value type string (",01") to a float64: invalid syntax source="main.go:144"

I have also the same problem with any metrics returned in float64 from a sql request, did you know how to fix it ? Thx in advance

Hi,
i see that this error is common and sql response should be integer.. but i am doing count. which is number and therefore integer

this is metric config

[[metric]]
context = "server_session"
labels = [ "server_1" ]
request = "select count(case when machine ='server01' then 1 end) as server_1 from v$session WHERE TYPE <> 'BACKGROUND'"
metricsdesc = { server_1 = "Session on server01" }
metricstype = { server_1 = "counter" }

if i do plain select count(*) from v$session it looks fine

thanks
tomislav

can you pls make a 64bit cenos release?

thanks

[root@ecs003 oracle_exporter]# ./oracledb_exporter -h
./oracledb_exporter: error while loading shared libraries: libclntsh.so.18.1: cannot open shared object file: No such file or directory
[root@ecs003 oracle_exporter]# find / -iname "libclntsh*.*" -type f
/usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1

oracle_exporter version：oracledb_exporter.0.2.1
oracle client version：11.2.0.4.0

why oracledb_exporter depend on libclntsh.so.18.1?

There was a fatal error occurs when I executed go get github.com/mattn/go-oci8 and copy oci8.pc file and then go get as follow:

go get -u github.com/iamseth/oracledb_exporter

The error details is:

# github.com/iamseth/oracledb_exporter/vendor/github.com/mattn/go-oci8
/usr/local/golang/src/github.com/iamseth/oracledb_exporter/vendor/github.com/mattn/
go-oci8/oci8.go:4:17: fatal error: oci.h: No such file or directory
 #include <oci.h>
                 ^
compilation terminated.

And my go env is:

go version go1.6 linux/amd64

I'm not getting any tablespace metrics from my oracle instance. After doing some debugging, I found that it's because the query breaks when dfs.bytes is null. The query works just fine as is in SQL Developer and other tools, but when run in the exporter I get an OCI_SUCCESS_WITH_INFO and an empty row set.

Coalescing the value of dfs.bytes with 0 fixed the problem for me...pull request to follow.

Hello,

I'm running into this error below:
time="2019-02-14T14:25:45Z" level=error msg="Error scraping for activity: ORA-00942: table or view does not exist\n" source="main.go:144" time="2019-02-14T14:25:45Z" level=error msg="Error scraping for tablespace: ORA-00942: table or view does not exist\n" source="main.go:149" time="2019-02-14T14:25:45Z" level=error msg="Error scraping for wait_time: ORA-00942: table or view does not exist\n" source="main.go:154" time="2019-02-14T14:25:45Z" level=error msg="Error scraping for sessions: ORA-00942: table or view does not exist\n" source="main.go:159" time="2019-02-14T14:25:45Z" level=error msg="Error scraping for process: ORA-00942: table or view does not exist\n" source="main.go:164

Looks like a permission issue and maybe the $DATA_SOURCE_NAME connection string is problematic.

I tried the following:
export DATA_SOURCE_NAME="db_monitoring/password@localhost/service?"
export DATA_SOURCE_NAME="db_monitoring/password@localhost/service?as=sysdba"

Any suggestions?

Could oracledb_exporter support multiple oracle instances ? Like read oracle tnsnames.ora file.

Hi,
I defined the environment DATA_SOURCE_NAME with username/[email protected]:1521/xe，and run it in container, but I got error like this:
level=error msg="Error pinging oracle: can't OCIEnvCreate" source="main.go:136"
I guess the DATA_SOURCE_NAME was not in correct form, could you please correct me?

I can connect to my database with the following tns description string:
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.0.228)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SID = xe)) )

docker run --name oracle -d -p 8080:8080 -p 1521:1521 sath89/oracle-12c
docker run -d --link=oracle -p 9161:9161 -e DATA_SOURCE_NAME=system/oracle@oracle/xe.oracle.docker iamseth/oracledb_exporter

But my metric shows as ::

HELP go_gc_duration_seconds A summary of the GC invocation durations.

TYPE go_gc_duration_seconds summary

go_gc_duration_seconds{quantile="0"} 3.42e-05
go_gc_duration_seconds{quantile="0.25"} 5.3e-05
go_gc_duration_seconds{quantile="0.5"} 6.57e-05
go_gc_duration_seconds{quantile="0.75"} 9.95e-05
go_gc_duration_seconds{quantile="1"} 0.0051045
go_gc_duration_seconds_sum 0.0189801
go_gc_duration_seconds_count 126

HELP go_goroutines Number of goroutines that currently exist.

TYPE go_goroutines gauge

go_goroutines 10

HELP go_memstats_alloc_bytes Number of bytes allocated and still in use.

TYPE go_memstats_alloc_bytes gauge

go_memstats_alloc_bytes 779360

HELP go_memstats_alloc_bytes_total Total number of bytes allocated, even if freed.

TYPE go_memstats_alloc_bytes_total counter

go_memstats_alloc_bytes_total 9.916064e+06

HELP go_memstats_buck_hash_sys_bytes Number of bytes used by the profiling bucket hash table.

TYPE go_memstats_buck_hash_sys_bytes gauge

go_memstats_buck_hash_sys_bytes 1.443744e+06

HELP go_memstats_frees_total Total number of frees.

TYPE go_memstats_frees_total counter

go_memstats_frees_total 13945

HELP go_memstats_gc_sys_bytes Number of bytes used for garbage collection system metadata.

TYPE go_memstats_gc_sys_bytes gauge

go_memstats_gc_sys_bytes 405504

HELP go_memstats_heap_alloc_bytes Number of heap bytes allocated and still in use.

TYPE go_memstats_heap_alloc_bytes gauge

go_memstats_heap_alloc_bytes 779360

HELP go_memstats_heap_idle_bytes Number of heap bytes waiting to be used.

TYPE go_memstats_heap_idle_bytes gauge

go_memstats_heap_idle_bytes 4.530176e+06

HELP go_memstats_heap_inuse_bytes Number of heap bytes that are in use.

TYPE go_memstats_heap_inuse_bytes gauge

go_memstats_heap_inuse_bytes 1.400832e+06

HELP go_memstats_heap_objects Number of allocated objects.

TYPE go_memstats_heap_objects gauge

go_memstats_heap_objects 5175

HELP go_memstats_heap_released_bytes_total Total number of heap bytes released to OS.

TYPE go_memstats_heap_released_bytes_total counter

go_memstats_heap_released_bytes_total 4.530176e+06

HELP go_memstats_heap_sys_bytes Number of heap bytes obtained from system.

TYPE go_memstats_heap_sys_bytes gauge

go_memstats_heap_sys_bytes 5.931008e+06

HELP go_memstats_last_gc_time_seconds Number of seconds since 1970 of last garbage collection.

TYPE go_memstats_last_gc_time_seconds gauge

go_memstats_last_gc_time_seconds 1.5119417177522001e+09

HELP go_memstats_lookups_total Total number of pointer lookups.

TYPE go_memstats_lookups_total counter

go_memstats_lookups_total 100

HELP go_memstats_mallocs_total Total number of mallocs.

TYPE go_memstats_mallocs_total counter

go_memstats_mallocs_total 19120

HELP go_memstats_mcache_inuse_bytes Number of bytes in use by mcache structures.

TYPE go_memstats_mcache_inuse_bytes gauge

go_memstats_mcache_inuse_bytes 3472

HELP go_memstats_mcache_sys_bytes Number of bytes used for mcache structures obtained from system.

TYPE go_memstats_mcache_sys_bytes gauge

go_memstats_mcache_sys_bytes 16384

HELP go_memstats_mspan_inuse_bytes Number of bytes in use by mspan structures.

TYPE go_memstats_mspan_inuse_bytes gauge

go_memstats_mspan_inuse_bytes 20976

HELP go_memstats_mspan_sys_bytes Number of bytes used for mspan structures obtained from system.

TYPE go_memstats_mspan_sys_bytes gauge

go_memstats_mspan_sys_bytes 32768

HELP go_memstats_next_gc_bytes Number of heap bytes when next garbage collection will take place.

TYPE go_memstats_next_gc_bytes gauge

go_memstats_next_gc_bytes 4.194304e+06

HELP go_memstats_other_sys_bytes Number of bytes used for other system allocations.

TYPE go_memstats_other_sys_bytes gauge

go_memstats_other_sys_bytes 794968

HELP go_memstats_stack_inuse_bytes Number of bytes in use by the stack allocator.

TYPE go_memstats_stack_inuse_bytes gauge

go_memstats_stack_inuse_bytes 360448

HELP go_memstats_stack_sys_bytes Number of bytes obtained from system for stack allocator.

TYPE go_memstats_stack_sys_bytes gauge

go_memstats_stack_sys_bytes 360448

HELP go_memstats_sys_bytes Number of bytes obtained by system. Sum of all system allocations.

TYPE go_memstats_sys_bytes gauge

go_memstats_sys_bytes 8.984824e+06

HELP http_request_duration_microseconds The HTTP request latencies in microseconds.

TYPE http_request_duration_microseconds summary

http_request_duration_microseconds{handler="prometheus",quantile="0.5"} 2989.7
http_request_duration_microseconds{handler="prometheus",quantile="0.9"} 2989.7
http_request_duration_microseconds{handler="prometheus",quantile="0.99"} 2989.7
http_request_duration_microseconds_sum{handler="prometheus"} 50758.7
http_request_duration_microseconds_count{handler="prometheus"} 10

HELP http_request_size_bytes The HTTP request sizes in bytes.

TYPE http_request_size_bytes summary

http_request_size_bytes{handler="prometheus",quantile="0.5"} 446
http_request_size_bytes{handler="prometheus",quantile="0.9"} 446
http_request_size_bytes{handler="prometheus",quantile="0.99"} 446
http_request_size_bytes_sum{handler="prometheus"} 4416
http_request_size_bytes_count{handler="prometheus"} 10

HELP http_requests_total Total number of HTTP requests made.

TYPE http_requests_total counter

http_requests_total{code="200",handler="prometheus",method="get"} 10

HELP http_response_size_bytes The HTTP response sizes in bytes.

TYPE http_response_size_bytes summary

http_response_size_bytes{handler="prometheus",quantile="0.5"} 1550
http_response_size_bytes{handler="prometheus",quantile="0.9"} 1550
http_response_size_bytes{handler="prometheus",quantile="0.99"} 1550
http_response_size_bytes_sum{handler="prometheus"} 15192
http_response_size_bytes_count{handler="prometheus"} 10

HELP oracledb_exporter_last_scrape_duration_seconds Duration of the last scrape of metrics from Oracle DB.

TYPE oracledb_exporter_last_scrape_duration_seconds gauge

oracledb_exporter_last_scrape_duration_seconds 0.0006298

HELP oracledb_exporter_last_scrape_error Whether the last scrape of metrics from Oracle DB resulted in an error (1 for error, 0 for success).

TYPE oracledb_exporter_last_scrape_error gauge

oracledb_exporter_last_scrape_error 1

HELP oracledb_exporter_scrapes_total Total number of times Oracle DB was scraped for metrics.

TYPE oracledb_exporter_scrapes_total counter

oracledb_exporter_scrapes_total 12

HELP oracledb_up Whether the Oracle database server is up.

TYPE oracledb_up gauge

oracledb_up 0

HELP process_cpu_seconds_total Total user and system CPU time spent in seconds.

TYPE process_cpu_seconds_total counter

process_cpu_seconds_total 0.33

HELP process_max_fds Maximum number of open file descriptors.

TYPE process_max_fds gauge

process_max_fds 1.048576e+06

HELP process_open_fds Number of open file descriptors.

TYPE process_open_fds gauge

process_open_fds 8

HELP process_resident_memory_bytes Resident memory size in bytes.

TYPE process_resident_memory_bytes gauge

process_resident_memory_bytes 2.3199744e+07

HELP process_start_time_seconds Start time of the process since unix epoch in seconds.

TYPE process_start_time_seconds gauge

process_start_time_seconds 1.51192666348e+09

HELP process_virtual_memory_bytes Virtual memory size in bytes.

TYPE process_virtual_memory_bytes gauge

process_virtual_memory_bytes 3.64326912e+08

I am using version 0.2.2 of the oracledb_exporter. My issue is similar to issue #50 but converting the value to an integer did not solve my problem.

[[metric]]
  context = "backup"
  metricsdesc = { last_backup_seconds = "Number of seconds since the last backup of a data file of an archivelog mode database."  }
  labels = [ "data_file" ]
  request = '''
  with cte (last_backup_seconds, data_file) as (
    select ((86400*(sysdate - coalesce(b.tijd, d.creation_time)))) as last_backup_seconds
    ,      d.name as data_file
    from   gv$datafile d
    ,      (select file#,max(tijd) tijd
           from  (select file#, max(completion_time) tijd
                  from   gv$backup_datafile
                  group  by file#
                  union
                  select file#, time                 tijd
                  from   gv$backup)
           group by file#)   b
    where  b.file# = d.file#
  )
  select CAST(last_backup_seconds as integer) as last_backup_seconds, data_file from cte
'''

results in:

bash-4.2$ /opt/prometheus/exporters/oracledb_exporter -log.level error -web.listen-address 192.168.40.13:9161 -default.metrics /etc/default/default-metrics.toml
ERRO[0000] Error scraping for backup : No metrics found while parsing  source=main.go:188

The cte part of the query is not really necessary but was added for readability while debugging this problem. Any idea what causes the error and do you have any tips how to debug this kind of problem in the oracledb_exporter?

When I run the query in Oracle I get:

LAST_BACKUP_SECONDS
-------------------
DATA_FILE
--------------------------------------------------------------------------------
	      69381
/opt/oracle/oradata/XE/pdbseed/sysaux01.dbf

	   28440138
/opt/oracle/oradata/XE/system01.dbf

	   28440115
/opt/oracle/oradata/XE/users01.dbf

In sqlplus I have to add set lines 600 to get a more readable result:

LAST_BACKUP_SECONDS DATA_FILE
------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	      69542 /opt/oracle/oradata/XE/pdbseed/sysaux01.dbf
	   28440299 /opt/oracle/oradata/XE/system01.dbf
	   28440276 /opt/oracle/oradata/XE/users01.dbf

I don't think the length of the data_file column is causing the error because I replaced that column with the file# column and I got the same error.

Hello

Is it possible to increase queryTimeout from 5 to 10 seconds?
https://github.com/iamseth/oracledb_exporter/blob/master/main.go#L30

Or may be make it as environment variable.

We have a big database and from time to time some queries are slow, and on output we are getting intermittent graphs in grafana

By the way, I have tried to build and image by myself and got the next error

 ---> Running in 7f53a86f6a14
github.com/BurntSushi/toml
oracledb_exporter/vendor/github.com/mattn/go-oci8
# oracledb_exporter/vendor/github.com/mattn/go-oci8
vendor/github.com/mattn/go-oci8/oci8.go:4:17: fatal error: oci.h: No such file or directory
 #include <oci.h>
                 ^
compilation terminated.
oracledb_exporter/vendor/github.com/beorn7/perks/quantile
oracledb_exporter/vendor/github.com/golang/protobuf/proto
oracledb_exporter/vendor/github.com/prometheus/common/internal/bitbucket.org/ww/goautoneg
oracledb_exporter/vendor/github.com/prometheus/common/model
oracledb_exporter/vendor/github.com/prometheus/procfs
oracledb_exporter/vendor/github.com/Sirupsen/logrus
oracledb_exporter/vendor/github.com/prometheus/client_model/go
oracledb_exporter/vendor/github.com/matttproud/golang_protobuf_extensions/pbutil
oracledb_exporter/vendor/github.com/prometheus/common/log
oracledb_exporter/vendor/github.com/prometheus/common/expfmt
oracledb_exporter/vendor/github.com/prometheus/client_golang/prometheus
The command '/bin/sh -c go build -v -ldflags "-X main.Version=${VERSION} -s -w"' returned a non-zero code: 2```

Could you please help?

Thanks

Hi, Can we get a new release with the fixed typo?

I tried to build oracledb_exporter for windows, but this fails to build for me due to:

/home/mika/src/gopath/src/github.com/mattn/go-oci8/connector.go:14:10: undefined: OCI8Connector
/home/mika/src/gopath/src/github.com/mattn/go-oci8/connector.go:20:22: undefined: OCI8Connector
/home/mika/src/gopath/src/github.com/mattn/go-oci8/connector.go:21:9: undefined: OCI8Driver
[...]
/home/mika/src/gopath/src/github.com/mattn/go-oci8/connector.go:21:9: too many errors

I'm aware that the problem is lying within https://github.com/mattn/go-oci8, but I'm wondering if there are any plans to provide releases for Windows?

Thanks for providing oracledb_exporter!

Hi,
Is that a valid docker container image to run an Oracle server?

https://github.com/lausser/check_oracle_health by @lausser is rather useful and contains several Oracle-specific metrics and KPIs.

Can you see yourself adding those?

CC: @bastischubert

Thank you for this nice job 👍

the query is
select count(*) from v$process;

Thanks

`[root@gujp-server0005 oracledb_exporter.0.2.3.linux-amd64]# ./oracledb_exporter -log.level=debug -query.timeout=15 -web.listen-address=9162

INFO[0000] Starting oracledb_exporter 0.2.3 source=main.go:337

INFO[0000] Listening on 9162 source=main.go:359

FATA[0000] listen tcp: address 9162: missing port in address source=main.go:360
`

If I delete the parameter web.listen-address, it works fine.
`[root@gujp-server0005 oracledb_exporter.0.2.3.linux-amd64]# ./oracledb_exporter -log.level=debug -query.timeout=15

INFO[0000] Starting oracledb_exporter 0.2.3 source=main.go:337

INFO[0000] Listening on :9161 source=main.go:359
`

@iamseth , in the code there is a reliance on DATA_SOURCE_NAME environment variable:

export DATA_SOURCE_NAME=system/oracle@myhost

Is there a specific reason to do that? What if we pass dsn via flags?

diff --git a/main.go b/main.go
index 5e96929..b7e3636 100644
--- a/main.go
+++ b/main.go
@@ -4,7 +4,6 @@ import (
        "database/sql"
        "flag"
        "net/http"
-       "os"
        "strings"
        "time"

@@ -407,9 +406,10 @@ func cleanName(s string) string {
 }

 func main() {
+    var dsn string
+    flag.StringVar(&dsn, "dsn", "system/oracle@localhost", "data source name")
        flag.Parse()
        log.Infoln("Starting oracledb_exporter " + Version)
-       dsn := os.Getenv("DATA_SOURCE_NAME")
        exporter := NewExporter(dsn)
        prometheus.MustRegister(exporter)
        http.Handle(*metricPath, prometheus.Handler())

Then,

$ dist/oracledb_exporter.linux-amd64 -h
Usage of dist/oracledb_exporter.linux-amd64:
  -dsn string
    	data source name (default "system/oracle@localhost")
  -log.format value
    	If set use a syslog logger or JSON logging. Example: logger:syslog?appname=bob&local=7 or logger:stdout?json=true. Defaults to stderr.
  -log.level value
    	Only log messages with the given severity or above. Valid levels: [debug, info, warn, error, fatal].
  -web.listen-address string
    	Address to listen on for web interface and telemetry. (default ":9161")
  -web.telemetry-path string
    	Path under which to expose metrics. (default "/metrics")

Not really an issue but more a feature request with something already implemented but not working.
patch.txt

In short: it would be nice to have space_limit and space_used information of a FRA of an oracle db.

The query you see in the patch is working from sqlplus, but if i compile and launch oracledb_exporter i get

ERRO[0000] Error scraping for FRA space: OCI_SUCCESS_WITH_INFO source=main.go:

which is kind of odd and i don't know how to debug it.
It would be nice to have FRA information in the exporter, if you find a way to include it in the code it would be awesome

My Oracle version is 11.2.0.4。when I execute the command below：
[oracle@db oracledb_exporter.0.2.2.linux-amd64]$ ./oracledb_exporter
i found below information:
./oracledb_exporter: error while loading shared libraries: libclntsh.so.18.1: cannot open shared object file: No such file or directory

and my lib below：
[root@db opt]# find / -name libclntsh.*
/tmp/OraInstall2017-05-15_10-41-31AM/ext/lib/libclntsh.so.11.1
/u01/app/product/11.2.0/db_1/inventory/backup/2017-05-15_10-41-43AM/Scripts/ext/lib/libclntsh.so.11.1
/u01/app/product/11.2.0/db_1/inventory/Scripts/ext/lib/libclntsh.so.11.1
/u01/app/product/11.2.0/db_1/lib/libclntsh.so
/u01/app/product/11.2.0/db_1/lib/libclntsh.so.10.1
/u01/app/product/11.2.0/db_1/lib/libclntsh.so.11.1

I loaded your source code and tested it in my win 10 PC. But it returns an error.