Logstash jdbc using tns results in error "Unknown host specified"

Elastic Stack Logstash
1

Hello,

I try to use jdbc to connect to my oracle server using tns.
logstash.conf:

input {
jdbc {
jdbc_driver_library => ""
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
jdbc_connection_string => "jdbc:oracle:thin:@DONSC01"
jdbc_user => "user"
jdbc_password=> "password"
schedule => "* * * * *"
statement => "SELECT * from TBL_CRE"
}
}
output{
stdout{codec => rubydebug}
}

the error:

[2020-04-30T10:14:00,726][ERROR][logstash.inputs.jdbc ][main] Unable to connect to database. Tried 1 times {:error_message=>"Java::JavaSql::SQLRecoverableException: IO Error: Unknown host specified "}

And when i do tnsping DONSC01 I have a good response therefore I really don't know what the issue can be. I hope you can help me with that :wink:

2

Can you please provide the exact configuration?

It seems you're not providing the jdbc_driver_library path to the jar.

One example (replace <db_name> and <path to>):

input {
    jdbc {
        jdbc_connection_string => "jdbc:oracle:thin:@DONSC01:1521/<db_name>"
        jdbc_user => "user"
        jdbc_password => "password"
        jdbc_driver_library => "<path to>/ojdbc8.jar"
        jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
        statement => "SELECT * from TBL_CRE"
        schedule => "* * * * *"
       }
}
4

Hello,
Yes sorry about that, the jdbc that i am using is ojdbc7.jar I did put it direcly in logstash as is specified in the doc (https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-jdbc_driver_library) .
By the way I thought I did not need to adda anything after the jdbc:oracle:thin:@DONSC01 cause it is a tns connection as explainned here: http://www.orafaq.com/wiki/JDBC#Thin_driver

5

Can you enable the debug logs in Logstash and see if you have more details? Have you tried providing the IP instead?

6

Thanks for the reply, unfortunatly I do not have access to the IP for security reason(my company policy . . . ), I only know the tns name of the database. As soon as I can I will enable the debug to try to have more details.

7

you need to copy ojdbc8.jar to /usr/share/logstash/logstash-core/lib/jars dir.
and remove jdbc_driver_library.
you only need jdbc_driver_class

    #jdbc_driver_library => "/usr/lib/oracle/12.2/client64/lib/ojdbc8.jar"
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
1 Like
8

I already did put ojdbc8.jar to /usr/share/logstash/logstash-core/lib/jars dir. Removing the jdbc_driver_library or leaving it empty doesn't seem to change anything.

9

HI , I enabled debug fot jdb input but I don't think thats of any help :

> [2020-05-05T10:01:20,882][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@jdbc_user = "NSC_TOM"
[2020-05-05T10:01:20,883][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@schedule = "* * * * *"
[2020-05-05T10:01:20,886][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@jdbc_password = <password>
[2020-05-05T10:01:20,887][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@statement = "SELECT * from TBL_CRE"
[2020-05-05T10:01:20,888][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@jdbc_connection_string = "jdbc:oracle:thin:@DONSC01"
[2020-05-05T10:01:20,889][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@id = "5fdf26f7ea5b4333d4fab5d495f8066ac6acd1f0d95b985d69caaced9e4f914d"
[2020-05-05T10:01:20,890][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@jdbc_driver_class = "Java::oracle.jdbc.driver.OracleDriver"
[2020-05-05T10:01:20,894][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@enable_metric = true
[2020-05-05T10:01:20,902][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@codec = <LogStash::Codecs::Plain id=>"plain_3681cce0-5030-4a08-93d4-c45d49d2f782", enable_metric=>true, charset=>"UTF-8">
[2020-05-05T10:01:20,904][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@add_field = {}
[2020-05-05T10:01:20,905][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@jdbc_paging_enabled = false
[2020-05-05T10:01:20,906][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@jdbc_page_size = 100000
[2020-05-05T10:01:20,907][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@jdbc_validate_connection = false
[2020-05-05T10:01:20,908][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@jdbc_validation_timeout = 3600
[2020-05-05T10:01:20,909][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@jdbc_pool_timeout = 5
[2020-05-05T10:01:20,910][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@sequel_opts = {}
[2020-05-05T10:01:20,911][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@sql_log_level = "info"
[2020-05-05T10:01:20,912][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@connection_retry_attempts = 1
[2020-05-05T10:01:20,913][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@connection_retry_attempts_wait_time = 0.5
[2020-05-05T10:01:20,914][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@plugin_timezone = "utc"
[2020-05-05T10:01:20,915][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@parameters = {}
[2020-05-05T10:01:20,916][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@last_run_metadata_path = "/projets/nsc/home/nscusrm1/.logstash_jdbc_last_run"
[2020-05-05T10:01:20,917][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@use_column_value = false
[2020-05-05T10:01:20,918][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@tracking_column_type = "numeric"
[2020-05-05T10:01:20,919][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@clean_run = false
[2020-05-05T10:01:20,920][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@record_last_run = true
[2020-05-05T10:01:20,921][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@lowercase_column_names = true
[2020-05-05T10:01:20,922][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@columns_charset = {}
[2020-05-05T10:01:20,922][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@use_prepared_statements = false
[2020-05-05T10:01:20,923][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@prepared_statement_name = ""
[2020-05-05T10:01:20,924][DEBUG][logstash.inputs.jdbc     ] config LogStash::Inputs::Jdbc/@prepared_statement_bind_values = []
10

are you able to ping DONSC01 from your logstash cli ? have you tried replacing the DONSC01 with the ip address ? unknown host error seems to be name resolution rather than sql error

11

I don't know how to ping with logstash cli, I did use tsnping and that worked.

12

afaik, tnsping uses oracle configurationtry replacing DONSC01 with the ip address of DONSC01 and see if that helps

what’s the content of tnsnames.ora for DONSC01?

1 Like
13

I have limited access to the machine and I do not know the ip of the oracle database. In $ORACLE_HOME/network/admin there is no TSNnames.ora, only a sqlnet.ora containing:

# sqlnet.ora Network Configuration File: /soft/oracle/product/client/12.2/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
14

if you have access to the shell where logstash is installed, are you able to ping the dbserver using hostname (DONSC01)

15

When I tnsping inside logstash directory it works(returns : OK (10 msec) )

16

what is the error message?

are you running this from command line as test?
/usr/share/logstash/bin/logstash -f

this I dont think has anything to do with ip/name

but you can find out ip by just typing
host donsc01 or nslookup donsc01

17

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.