Can this exporterr support oracle？

[william-yang]

Can this exporter support orcale? If not, did you have plan to do it?

[free]

[Apologies for not answering before, for some reason I am getting no email notification from newly created issues and I just noticed this.]

As stated in the first paragraph of the README, SQL Exporter supports any database for which a Go driver is available. There exist a couple of Go drivers for Oracle but they both require an external library (oci8.pc) to be placed in a specific location. Seeing as how this is quite error prone and I don't even have an Oracle instance to test against, I have opted to not add it initially.

If you're willing to do the work yourself (including particularly testing and documenting the process) I will happily accept PRs. Otherwise I might need to monitor an Oracle instance at some point (it's not yet decided if or when) so I might do it myself then.

[free]

Oh, I forgot to mention this: if you want to get SQL Exporter working with Oracle, you'll need to follow the installation steps for either of the Oracle drivers, import the driver package into SQL Exporter's sql.go and rebuild SQL Exporter. Not all that complicated (once you've set up the Oracle driver).

[marksteward]

https://github.com/Corundex/database_exporter has started doing this along with a rebrand, which is currently the top hit on Google. Did anyone start work on a PR?

[free]

Did anyone start work on a PR?

Not as far as I know. But we could just "borrow" it back from the Corundex exporter. I'm sure they won't mind :o)

[arnaudlemaignen]

So in the end, will this exporter is supposed to work with Oracle, or should we use the one from Corundex? I tried the Corundex one, it is working mostly but I have issues with long queries (which takes 10min in sqlplus). I got following issues for that query

• [from Gatherer #1] [, collector="db", query="oracle_db_states"] context deadline exceeded

bad thing is, there is no "Issue" blog in the Corundex repo, so I can not report any errors or discuss how to contribute.

[GETandSELECT]

any news on Oracle support?

[xuanyuanaosheng]

@free any news on Oracle support?

[arnaudlemaignen]

I have a working solution but not pushable in the current depot. Actually there are 2 issues:

1. to query an oracle DB in go you need the oracle instant client, this client is depending on the libaio.

The issue is that the sql_exporter is based on busybox and I did not found a good/easy way to install the libaio package with dpkg

RUN wget http://ftp.fr.debian.org/debian/pool/main/liba/libaio/libaio1_0.3.112-3_i386.deb RUN wget http://ftp.fr.debian.org/debian/pool/main/g/glibc/libc6_2.28-10_amd64.deb RUN dpkg -i libc6_2.28-10_amd64.deb RUN dpkg -i libaio1_0.3.112-3_i386.deb

but then tons of dependencies should be installed as well, and did not had the courage to sort those all

2. the packaging for compiling the sql_exporter binary

I add a few lines in query.go and add godror lib (https://github.com/godror/godror, previsouly called goracle) to query with oracle

whatever I am trying to adapt the Makefile, I am ending with this error. It seems related to the CGO compilation of the godror lib

make build

building binaries sql_exporter

github.com/godror/godror

src/github.com/godror/godror/orahlp.go:452:53: undefined: VersionInfo src/github.com/godror/godror/orahlp.go:461:53: undefined: VersionInfo src/github.com/godror/godror/orahlp.go:479:19: undefined: VersionInfo src/github.com/godror/godror/orahlp.go:480:19: undefined: VersionInfo src/github.com/godror/godror/orahlp.go:481:30: undefined: ObjectType src/github.com/godror/godror/orahlp.go:482:31: undefined: Event src/github.com/godror/godror/orahlp.go:482:41: undefined: Subscription src/github.com/godror/godror/orahlp.go:483:10: undefined: StartupMode src/github.com/godror/godror/orahlp.go:484:11: undefined: ShutdownMode src/github.com/godror/godror/orahlp.go:485:59: undefined: Data src/github.com/godror/godror/orahlp.go:485:59: too many errors

WA :

1. change the base image from busybox to centos or other and install libaio

FROM centos:7 RUN yum -y update \ && yum -y install libaio \ && yum clean all ... COPY --from=builder /go/src/github.com/free/sql_exporter/sql_exporter /bin/sql_exporter ...

Copy DB instant client rpms and setup correctly the dependencies

ENV ORACLE_VERSION 12.1.2 ENV ORACLE_VERSION_SHORT 12.1

COPY dependency/oracle-instantclient-$ORACLE_VERSION.rpm /tmp COPY dependency/oracle-instantclient-$ORACLE_VERSION-devel.rpm /tmp RUN rpm -ivh /tmp/oracle-instantclient-$ORACLE_VERSION.rpm RUN rpm -ivh /tmp/oracle-instantclient-$ORACLE_VERSION-devel.rpm

ENV LD_LIBRARY_PATH /usr/lib/oracle/$ORACLE_VERSION_SHORT/client64/lib/${LD_LIBRARY_PATH:+:$LD_LIBRARY_PATH} RUN mkdir /usr/lib/oracle/$ORACLE_VERSION_SHORT/client64/rdbms RUN ln -s /usr/include/oracle/$ORACLE_VERSION_SHORT/client64 /usr/lib/oracle/$ORACLE_VERSION_SHORT/client64/rdbms/public ENV ORACLE_HOME /usr/lib/oracle/$ORACLE_VERSION_SHORT/client64

ENTRYPOINT [ "/bin/sql_exporter" ]

2. Here it is a question of how to build correctly the binary, I am not knowledgeable on that Makefile :(. I had to use tricks to make it works (reusing the Makefile and folder structure from Corundex, ie moving the cmd files in root and moving the root files to the exporter folder).

If someone can help me to solve issue 1. and 2. in the current sql_exporter branch, I would be happy to maintain the oracle DB related items for this exporter. I can provide the whole project if needed and I have the good infra to test it.

[4dr14ns1]

I managed to add godror driver to current sql_exporter. These are the steps:

• install oracle instant client (in my case i used version 12.2 rpm's) (basic + devel if you are using the rpms or basic + sdk for archives)

  
  $ rpm -qa | grep -i oracle-instantclient
  oracle-instantclient12.2-tools-12.2.0.1.0-1.x86_64
  oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64
  oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64
  oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64
  oracle-instantclient12.2-odbc-12.2.0.1.0-1.x86_64
  oracle-instantclient12.2-jdbc-12.2.0.1.0-1.x86_64

$ rpm -qa | grep oracle-instantclient | grep -e basic -e devel oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64 oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64

- prepare package config for oci8 and export LD_LIBRARY_PATH

$ ls -go /usr/local/lib/pkgconfig/ total 4 -rw-r--r--. 1 283 Mar 17 21:36 oci8_linux_amd64.pc lrwxrwxrwx. 1 19 Mar 17 21:36 oci8.pc -> oci8_linux_amd64.pc

$ cat /usr/local/lib/pkgconfig/oci8_linux_amd64.pc prefix=/usr

version=12.2 build=client64

libdir=${prefix}/lib/oracle/${version}/${build}/lib includedir=${prefix}/include/oracle/${version}/${build}

Name: oci8 Description: Oracle database engine Version: ${version} Libs: -L${libdir} -lclntsh Libs.private: Cflags: -I${includedir}

$ export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig $ pkg-config --libs --cflags oci8 -I/usr/include/oracle/12.2/client64 -L/usr/lib/oracle/12.2/client64/lib -lclntsh

$ export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib:/usr/local/lib:/usr/lib64:/lib64:$LD_LIBRARY_PATH

- check that CGO_ENABLED is set to 1

$ go env | grep CGO_ENABLED CGO_ENABLED="1"

- get github.com/godror/godror
`$ go get github.com/godror/godror`
- get github.com/free/sql_exporter
`$ go get github.com/free/sql_exporter`
- apply patch for 
    * .promu.yml (cgo set to true, static set to false -> dynamic executable (oracle lib dependencies) , and ld flags - in this particular case is hardcoded for oracle version 12.2) 
    * sql.go (register godror driver and oracle dsn prefix)

diff --git a/.promu.yml b/.promu.yml index 0297aa0..3b0be69 100644 --- a/.promu.yml +++ b/.promu.yml @@ -1,5 +1,5 @@ go:

• cgo: false
• cgo: true repository: path: github.com/free/sql_exporter build: @@ -7,12 +7,14 @@ build:
  • name: sql_exporter path: ./cmd/sql_exporter flags: -a -tags netgo
• static: false ldflags: | -X {{repoPath}}/vendor/github.com/prometheus/common/version.Version={{.Version}} -X {{repoPath}}/vendor/github.com/prometheus/common/version.Revision={{.Revision}} -X {{repoPath}}/vendor/github.com/prometheus/common/version.Branch={{.Branch}} -X {{repoPath}}/vendor/github.com/prometheus/common/version.BuildUser={{user}}@{{host}} -X {{repoPath}}/vendor/github.com/prometheus/common/version.BuildDate={{date "20060102-15:04:05"}}
• -extldflags '-L/usr/lib/oracle/12.2/client64/lib -lclntsh' tarball: files:
  • LICENSE diff --git a/sql.go b/sql.go index f4f5c98..5446d67 100644 --- a/sql.go +++ b/sql.go @@ -9,6 +9,7 @@ import ( "github.com/ClickHouse/clickhouse-go" // register the ClickHouse driver "github.com/denisenkom/go-mssqldb" // register the MS-SQL driver _ "github.com/go-sql-driver/mysql" // register the MySQL driver
• "github.com/godror/godror" // register the Oracle driver log "github.com/golang/glog" "github.com/lib/pq" // register the PostgreSQL driver ) @@ -55,10 +56,12 @@ func OpenConnection(ctx context.Context, logContext, dsn string, maxConns, maxId switch driver { case "mysql": dsn = strings.TrimPrefix(dsn, "mysql://")
• case "oracle":
• dsn = strings.TrimPrefix(dsn, "oracle://")
• driver = "godror" case "clickhouse": dsn = "tcp://" + strings.TrimPrefix(dsn, "clickhouse://") }

• // Open the DB handle in a separate goroutine so we can terminate early if the context closes.
  var (
          conn *sql.DB

• run make and make tarball

  $ make
  >> formatting code
  >> building binaries
  >   sql_exporter
  >> running tests
  ?       github.com/free/sql_exporter    [no test files]
  ?       github.com/free/sql_exporter/cmd/sql_exporter   [no test files]
  ?       github.com/free/sql_exporter/config     [no test files]
  ?       github.com/free/sql_exporter/errors     [no test files]
  $ ldd sql_exporter
      linux-vdso.so.1 =>  (0x00007ffc8034e000)
      libdl.so.2 => /usr/lib64/libdl.so.2 (0x00007f44af5c6000)
      libpthread.so.0 => /usr/lib64/libpthread.so.0 (0x00007f44af3aa000)
      libclntsh.so.12.1 => /usr/lib/oracle/12.2/client64/lib/libclntsh.so.12.1 (0x00007f44ab905000)
      libc.so.6 => /usr/lib64/libc.so.6 (0x00007f44ab537000)
      /lib64/ld-linux-x86-64.so.2 (0x00007f44af7ca000)
      libmql1.so => /usr/lib/oracle/12.2/client64/lib/libmql1.so (0x00007f44ab2c0000)
      libipc1.so => /usr/lib/oracle/12.2/client64/lib/libipc1.so (0x00007f44aae8d000)
      libnnz12.so => /usr/lib/oracle/12.2/client64/lib/libnnz12.so (0x00007f44aa744000)
      libons.so => /usr/lib/oracle/12.2/client64/lib/libons.so (0x00007f44aa4f6000)
      libm.so.6 => /usr/lib64/libm.so.6 (0x00007f44aa1f4000)
      libnsl.so.1 => /usr/lib64/libnsl.so.1 (0x00007f44a9fda000)
      librt.so.1 => /usr/lib64/librt.so.1 (0x00007f44a9dd2000)
      libaio.so.1 => /usr/lib64/libaio.so.1 (0x00007f44a9bd0000)
      libresolv.so.2 => /usr/lib64/libresolv.so.2 (0x00007f44a99b7000)
      libclntshcore.so.12.1 => /usr/lib/oracle/12.2/client64/lib/libclntshcore.so.12.1 (0x00007f44a93e9000)
      libgcc_s.so.1 => /usr/lib64/libgcc_s.so.1 (0x00007f44a91d3000)
  $ make tarball
  >> building release tarball
  >   sql_exporter-0.5.linux-amd64.tar.gz
  $

I just ran a simple test with one metric and i noticed that i have to make aliases for the columns (column in lowercase between double quotes) otherwise key_labels and values must be written in uppercase in the yaml file.

I do not know if i broke something else but at least for me is working for oracle database.

[stud0709]

@4dr14ns1, I am not a gopher, and I run into problems with the compilation. I can follow you regarding the changes you make to sql.go. And then I tried to go build the stuff, but it fails with

# _/home/ydzh/Documents/git/sql_exporter
./metric.go:99:28: cannot use mf.config.ValueType() (type "github.com/free/sql_exporter/vendor/github.com/prometheus/client_golang/prometheus".ValueType) as type "github.com/prometheus/client_golang/prometheus".ValueType in return argument

Is it something you address within .promu.yml or a different issue?

[4dr14ns1]

@4dr14ns1, I am not a gopher, and I run into problems with the compilation. I can follow you regarding the changes you make to sql.go. And then I tried to go build the stuff, but it fails with ... Is it something you address within .promu.yml or a different issue?

I am not a gopher also, two ears ago i spend some time on it just enough to be able to build sql_exporter. In case that you did not made any mistake regarding patch details most probably is from godror because there was no update on sql_exporter since 2019.

[stud0709]

@4dr14ns1, no, because if you git clone sql_exporter "as is" and go build it - you get the same error.