\r\njdbc {\r\n # The path to our downloaded jdbc driver saved mine in \/bin, driver ojdbc6.jar also works. \r\n #Best location can be \/vendor folder\r\n jdbc_driver_library => \"ojdbc7.jar\"\r\n # ORACLE Driver Class\r\n jdbc_driver_class => \"Java::oracle.jdbc.driver.OracleDriver\"\r\n # ORACLE jdbc connection string to our database, ORACLE jdbc:oracle:thin:@hostname:PORT\/SERVICE\r\n jdbc_connection_string => \"jdbc:oracle:thin:@hostname:PORT\/SERVICE\"\r\n # The user and password we wish to execute our statement as\r\n jdbc_user => \"DBUSERNAME\"\r\n jdbc_password => \"DBPASSWORD\"\r\n \r\n # our SQL query\r\n statement => \"select * from AWESOMETABLE\"\r\n\r\n #optional extras I use \r\n type => \"Database\"\r\n tags => [\"awesome\", \"import\"]\r\n }\r\n<\/pre>\nSince I did not have ojdbc7.jar on my computer I chose to use ojdbc6.jar so my Oracle database jdbc logstash query came out like this:<\/p>\n
\r\ninput {\r\njdbc {\r\n jdbc_connection_string => \"jdbc:oracle:thin:@\\\\my-host:1521:my-sid\"\r\n jdbc_user => \"db-user\"\r\n jdbc_password => \"db-pwd\"\r\n jdbc_validate_connection => true\r\n jdbc_driver_library => \"C:\\path\\to\\ojdbc6.jar\"\r\n jdbc_driver_class => \"Java::oracle.jdbc.driver.OracleDriver\"\r\n statement => \"SELECT * from my-table\"\r\n}\r\n}\r\noutput {\r\nelasticsearch {\r\nindex => \"contacts\"\r\ndocument_type => \"contact\"\r\ndocument_id => \"%{uid}\"\r\nhosts => \"localhost:9200\"\r\n}\r\n}\r\n<\/pre>\nAlong the way, I encountered two error messages that are the reason for this post:
\nThe first <\/p>\n
” Error: java::oracle.jdbc.driver.OracleDriver not loaded. Are you sure you’ve included the correct jdbc driver in :jdbc_driver_library?
\nYou may be interested in the ‘–configtest’ flag which you can use to validate logstash’s configuration before you choose to restart a running system. ”
\n<\/strong>
\nRunning the query with the ‘–configtest’ flag did not help much but eventually, it turned out to be a simple typo. The oracle driver was not loaded only because the call was
\n” java::oracle.jdbc.driver.OracleDriver ” instead of ” J<\/strong>ava::oracle.jdbc.driver.OracleDriver “.
\n(this one was really annoying but I hope this will save you time I spent on it ).<\/p>\nAnother message I got along the way was <\/p>\n
Java::JavaSql::SQLRecoverableException: IO Error: Invalid connection string fo
\nrmat, a valid format is: “host:port:sid”
\n<\/strong>
\napparently, while all examples for the connection string are in the following format:
\njdbc:mysql:\/\/localhost:3306\/mydb
\nThe valid format in my case (oracle client 11 on windows 7) was
\njdbc:oracle:thin:@\\\\my-host:1521:<\/strong>my-sid<\/p>\nThis should get you connected but if you still get <\/p>\n
Java::JavaSql::SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
\n<\/strong>Check the following:
\n The database and the listener are running.
\n The specified port in the code is the same port that the database is listening .
\n There is no firewall blocking
\nGood luck<\/p>\n","protected":false},"excerpt":{"rendered":"Lately, I began experimenting with elastic search, logstash and kibana. As a DBA, the first thing I wanted to try is Data base connectivity. Just like any new emerging technology, Oracle database is not the first priority on their implementation (and of course, documentation). There are much more examples for sexier (and free) databases like […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[89],"class_list":["post-308","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle-logstash"],"_links":{"self":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/308","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=308"}],"version-history":[{"count":3,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/308\/revisions"}],"predecessor-version":[{"id":311,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/308\/revisions\/311"}],"wp:attachment":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=308"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=308"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=308"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}