Splunk DB Connect: Error connecting to Oracle Instance ORA-12514

[mjones414]

I've been trying to connect to an oracle instance but I am running into a brick wall and not sure what to try next. I know the Oracle SID, server name, port, and account are all good for read only, but this is the error I continuously get:

Encountered the following error while trying to update: In handler 'dbx-databases': Error connecting to database: java.sql.SQLException: Listener refused the connection with the following error: ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

I'm running Splunk 5.0.1 and Splunk DB Connect 1.0.6..

[amielke]

My Solution is: I delete in the Manager, External Databases the connection. Next Step is create a new Connection to the Oracle DB, but disable the icon "Validate Database Connection". After this you can save the connection whitout errors.

the second step ist change the config-file database.conf to the result:

[INEK_ORACLE]
database.sid = true
database = INEK
host = 'full-host'
password = password
port = 1521
readonly = 1
type = oracle
username = SPLUNK

FINSIH

[amielke]

Hello,

my configuration is Splunk Version 5.0.3, Build 163460, Splunk DB Connect Version 1.0.11. I connect to a MySQL DB all works fine. I trying connect to an Oracle DB 11g. I setup the driver ojdbc6.jar in the folder //&SplunkHome/splunk/etc/apps/dbx/bin/lib. In the File &splunk_Home/splunk/etc/apps/dbx/local/databases.conf i setup database.sid = true, the Connection Manager tell no errors, if i'm select something, a error appear:

command="dbquery", Error getting database connection: Listener refused the connection with the following error: ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
Where is my mistake?

The database.conf file:

database.sid = true
[INEK]
disabled = 0
database = INEK
host = 'full-host'
password = passowrd
port = 1521
readonly = 1
type = oracle
username = SPLUNK

[MySQL ] database = splunk
host = hostname
password = password
port = 3306
readonly = 1
type = mysql
username = splunk

[mjones414 · Accepted solution]

Looks like I was right about the cache.. for whatever insane reason, one of the trial attempts using a fully qualified instance name was set in ../launcher/local/database.conf. Once I blew this away and bounced splunk all was peaceful.. Thanks Ziegfried for all of your help!

[ziegfried]

Just to clarify: The database stanza in launcher/local/database.conf is not a cache. It's there because it has been created in the context of the launcher app. This happens when you navigate from Launcher to manager instead of navigating there from the DB Connect app. The behavior for other configs in Splunk like inputs or props is similar.

[ziegfried]

If you are sure that you are using the correct SID and you are using an older version of Oracle, you might be able to connect by changing the following setting database.conf for you database connection:

database.sid = true

This setting is not exposed to the UI and you'll have to restart Splunk after you changed it.

If you're also have a service name instead of the SID for the same oracle instance, please use this one instead.

[mjones414]

Six = dbx...

[mjones414]

Ah now here comes the mystery. It's determining the fully qualified name with just the Sid itself. I've grep'ed the whole six directory for any reference of it and can't find it but when it connects its showing up... Is there some sort of cache someplace it may be grabbing this from another attempt?

[ziegfried]

Well, the error message means that the server doesn't recognize the given service name. Maybe try it with the "not-fully-qualified" one?

[mjones414]

jumped up to ojdbc6.jar but still getting the same issue. 😞

[mjones414]

debug info:

2013-01-16 11:25:46.769 dbx2065:ERROR:DatabaseQueryCommand - Error while executing command: Error getting database connection: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

11:25:46.673 AM
2013-01-16 11:25:46.673 dbx2065:DEBUG:Oracle - Built Oracle JDBC connection URL: "jdbc:oracle:thin:@//FULLY.QUALIFIED.SERVER.NAME:1521/FULLY.QUALIFIED.INSTANCE.NAME"
host= Options| sourcetype=dbx_debug Options| source=/opt/splunk/var/log/splunk/dbx.log Options

[ziegfried]

Please use the ojdbc6.jar if possible. Here's a good explaination on the difference between SID and Service Name: http://stackoverflow.com/a/43901

An Oracle client is not required for DB Connect.

[mjones414]

ok that got me a little further. I can now pass the connection validation, but I still get this error whenever I try to connect to the database via the database info page in the app

[mjones414]

So just to clarify, there is a fully qualified oName that gets used, is this what you are referring to by service name? Are there any further requirements of this splunk app on the oracle client to make this connection or is it oracle client independent? I do have ojdbc5.jar in the appropriate directory and am trying to establish the connection this way... Thanks for your help, I will try the database.sid entry and let you know.