Error while trying to retrieve text for error ORA-01804 (OCIError)

[peteralfafara]

Hi Kubo,

I am getting an ORA-01804 error in my Sensu check. Although when I ran my sensu script in the terminal, I get the result that I want. But when Sensu Client picks it up, I can see in Uchiwa and the log files the following:

{"timestamp":"2017-09-10T12:11:44.300319+0000","level":"info","message":"publishing check result","payload":{"client":"OTM","check":{"type":"metric","command":"/opt/sensu/embedded/bin/metrics-stale-transmissions.rb -s 30","interval":600,"handle_when":{"reset":600},"standalone":true,"payload-type":"json","name":"stale-transmission","issued":1505045504,"executed":1505045504,"duration":0.283,"output":"oci8lib.c:280:in oci8lib_240.so: Error while trying to retrieve text for error ORA-01804 (OCIError)\n\tfrom /opt/sensu/embedded/lib/ruby/site_ruby/2.4.0/rubygems/core_ext/kernel_require.rb:55:in require'\n\tfrom /opt/sensu/embedded/lib/ruby/gems/2.4.0/gems/ruby-oci8-2.2.4.1/lib/oci8.rb:107:in<top (required)>'\n\tfrom /opt/sensu/embedded/lib/ruby/site_ruby/2.4.0/rubygems/core_ext/kernel_require.rb:133:in require'\n\tfrom /opt/sensu/embedded/lib/ruby/site_ruby/2.4.0/rubygems/core_ext/kernel_require.rb:133:inrescue in require'\n\tfrom /opt/sensu/embedded/lib/ruby/site_ruby/2.4.0/rubygems/core_ext/kernel_require.rb:40:in require'\n\tfrom /opt/sensu/embedded/bin/sqlcore.rb:4:in<top (required)>'\n\tfrom /opt/sensu/embedded/bin/metrics-stale-transmissions.rb:25:in require_relative'\n\tfrom /opt/sensu/embedded/bin/metrics-stale-transmissions.rb:25:in

'\n","status":3}}}

I researched and found that I just need to properly set my environment variables. And after looking into them, they are indeed properly set but still this error persists

LD_LIBRARY_PATH=/opt/oracle/11R23/lib:/opt/oracle/11R23/lib:/opt/oracle/11R23/lib: ORACLE_HOME=/opt/oracle/11R23 NLS_LANG=American_America.UTF8

Hoping you can assist on this issue. Thank you

Regards, Peter

[kubo]

Could you post the following information?

What OS and OS version do you use? What version of Oracle client do you use? Output of the following commands:

ldd /opt/sensu/embedded/lib/ruby/gems/2.4.0/gems/ruby-oci8-2.2.4.1/lib/oci8lib_240.so
ls -l /opt/oracle/11R23
ls -l /opt/oracle/11R23/lib
env | grep /opt/oracle

[peteralfafara]

Hi Kubo,

Thank you for your immediate response. Kindly see answers to your questions below:

What OS and OS version do you use? /home/oracle> cat /etc/*-release LSB_VERSION=base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch Oracle Linux Server release 6.6 Red Hat Enterprise Linux Server release 6.6 (Santiago) Oracle Linux Server release 6.6

What version of Oracle client do you use? Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production "CORE 11.2.0.3.0 Production" TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production

/home/oracle> ldd /opt/sensu/embedded/lib/ruby/gems/2.4.0/gems/ruby-oci8-2.2.4.1/lib/oci8lib_240.so linux-vdso.so.1 => (0x00007ffff3dfe000) libruby.so.2.4 => /opt/sensu/embedded/lib/libruby.so.2.4 (0x00007f48d99bb000) libclntsh.so.11.1 => /opt/oracle/11R23//lib/libclntsh.so.11.1 (0x00007f48d702d000) libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f48d6e06000) librt.so.1 => /lib64/librt.so.1 (0x00007f48d6bfe000) libdl.so.2 => /lib64/libdl.so.2 (0x00007f48d69f9000) libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f48d67c2000) libm.so.6 => /lib64/libm.so.6 (0x00007f48d653e000) libc.so.6 => /lib64/libc.so.6 (0x00007f48d61a9000) /lib64/ld-linux-x86-64.so.2 (0x0000003c1e800000) libnnz11.so => /opt/oracle/11R23/lib/libnnz11.so (0x00007f48d5ddd000) libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f48d5bc4000) libaio.so.1 => /lib64/libaio.so.1 (0x00007f48d59c2000) libfreebl3.so => /lib64/libfreebl3.so (0x00007f48d57bf000)

/home/oracle> env | grep /opt/oracle CLASSPATH=/opt/oracle/otm-6.3.7/glog/glog_resources:/opt/oracle/otm-6.3.7/glog/config:/opt/oracle/otm-6.3.7/glog/gc3webapp/WEB-INF/classes:/opt/oracle/otm-6.3.7/glog/gc3webapp/WEB-INF/lib/glogserver.jar:/opt/oracle/middleware/wlserver10.3/server/lib/weblogic.jar GLOG_HOME=/opt/oracle/otm-6.3.7 JAVA_HOME=/opt/oracle/otm-6.3.7/jdk LD_LIBRARY_PATH=/opt/oracle/11R23/lib:/opt/oracle/11R23/lib:/opt/oracle/11R23/lib: ORACLE_BASE=/opt/oracle ORACLE_HOME=/opt/oracle/11R23 PATH=/opt/oracle/otm-6.3.7/jdk/bin:/opt/oracle/11R23/bin:/opt/oracle/otm/otmapp/jdk/bin:/opt/oracle/11R23/bin:/opt/oracle/otm/otmweb/jdk/bin:/opt/oracle/11R23/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/opt/java/jdk1.6.0_25/bin:/usr/sbin:/opt/oracle/dba/bin:/opt/oracle/11R23/bin:/opt/oracle/11R23/OPatch WL_HOME=/opt/oracle/middleware/wlserver10.3

I am attaching the result of the ls commands since it has many files under the directory. ls -l opt oracle 11R23 files.txt ls -l opt oracle 11R23 lib files.txt

Also just to note, I have OCI8 installed in a seperate environment but with a similar OS and oracle and it works there.

Thank you for the assistance.

Regardsm Peter

[kubo]

Thanks for the information. It looks good.

Does sqlplus work fine? Does /opt/sensu/embedded/bin/ruby -roci8 -e "p OCI8" print OCI8?

If all work fine, could you change the 111th line in /opt/sensu/embedded/lib/ruby/gems/2.4.0/gems/ruby-oci8-2.2.4.1/lib/oci8.rb as follows and check ORACLE_HOME printed to the Uchiwa log?

...
begin
  require so_basename
rescue LoadError, OCIError
  require 'oci8/check_load_error'
  OCI8::Util::check_load_error($!)
  # delete one line add the following three lies
  exc = $!.class.new("#{$!.to_s} (ORACLE_HOME=#{ENV['ORACLE_HOME']})")
  exc.set_backtrace($!.backtrace)
  raise exc
ensure
  dll_dir.remove if dll_dir
end
...

[peteralfafara]

Hi Kubo,

Sorry for the late response. It was a bit late in Manila last night :)

Now that you mention it, sqlplus isn't working right now. Though before installation of OCI8, I made sure that I was able to connect to sqlplus and I did. Now I am unable to use it.

It's fine when I access the database through another IDE.

I noticed right now that sqlplus executable in $ORACLE_HOME/bin does not contain anything as per the the '0' size. Probably it got corrupted

-rwxr-x--x. 1 oracle oinstall 0 Sep 10 13:58 sqlplus

Also, /opt/sensu/embedded/bin/ruby -roci8 -e "p OCI8" does print OCI8. So I think sqlplus may be the problem. Any ideas how to fix this? Can't seem to find the solution in the web.

Regards, Peter

[kubo]

Sorry for the late response. It was a bit late in Manila last night :)

No problem. It was night also in Japan.

I have no idea to fix sqlplus except reinstalling Oracle.

I guess that the environment variables in the command line and in the Sensu are different. Ruby-oci8 in the command line works so it in the Sensu will work when the environment variables are same. Could you change /opt/sensu/embedded/lib/ruby/gems/2.4.0/gems/ruby-oci8-2.2.4.1/lib/oci8.rb as in the previous post and check the environment variable ORACLE_HOME in the Sensu?

[peteralfafara]

Hi Kubo,

I have changed oci8.rb but it doesn't say anything in sensu-enterprise-dashboard.log only the following: {"timestamp":"2017-09-11T11:19:12.47643161+01:00","level":"info","message":"Updating the datacenter Datacenter QA"} {"timestamp":"2017-09-11T11:19:12.495628061+01:00","level":"warn","message":"GET http://127.0.0.1:4567/metrics/check_requests returned: 404 Not Found"}

I am not sure what this is as sensu-enterprise-dashboard is currently working fine.

Just for you to be aware with our current structure, we have installed oci8 in one of our client systems and we have sensu-enterprise in a seperate server where sensu-client is sending over the metrics collected in the oracle database.

Regards, Peter

[peteralfafara]

Hi Kubo,

Hope you're doing well.

Upon checking the sensu-client.log, I am now seeing a different error than before: {"timestamp":"2017-09-14T02:21:44.810359+0000","level":"info","message":"publish ing check result","payload":{"client":"OTM","check":{"type":"metric","command":" /opt/sensu/embedded/bin/metrics-stale-transmissions.rb -s 30","interval":600,"ha ndle_when":{"reset":600},"standalone":true,"payload-type":"json","name":"stale-t ransmission","issued":1505355704,"executed":1505355704,"duration":0.229,"output" :"oci8lib.c:280:in oci8lib_240.so: Error while trying to retrieve text for error ORA-01804 (ORACLE_HOME=) (OCIError}

In this log, it seems that the sensu can't recognize the ORACLE_HOME environment variable. Any ideas on how to fix this?

Regards, Peter

[kubo]

It will be correct way to set ORACLE_HOME via sensu settings. But I know nothing about sensu. If it cannot be set by sensu, add ENV['ORACLE_HOME'] = '/opt/oracle/11R23' before require 'oci8' or at the top of /opt/sensu/embedded/lib/ruby/gems/2.4.0/gems/ruby-oci8-2.2.4.1/lib/oci8.rb.

[peteralfafara]

Hi Kubo,

This works for me. I placed the declaration of the variable inside my sensu check script.

Many thanks for assisting!

Regards, Peter