All Apps and Add-ons

Oracle and DBConnect

michael_herbert
Explorer

I am trying to setup Splunk DB Connect to an Oracle database that has a service name but no SID. I can't get past the verification because I keep getting this error ...

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

This happens whether I try to use the service name or the database name in the "Database/SID" field. I have no issues connecting to the database using other clients. Is there another setting somewhere that is missing to use the database name or service name? Here is the format for the tnsnames.ora entry that I use on other clients ...


NAME_SN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = some.hostname1.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = some.hostname2.com)(PORT = 1521))
(FAILOVER = yes)
(LOAD_BALANCE = no)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NAME_SN)
)
)

Tags (1)

skender27
Contributor

Hi,

I resolved it this way:

  • got to path /u01/app/oracle/product/12.1.0/dbhome_1/network/admin
  • then copy the name of the service like (this is an example) (SERVICE_NAME = xxxxx.domainxyz.oraclecloud.internal)

And so paste the name of the service (the part on the right of equal symbol) as database name in dbx configuration!

Hope it helped you and everybody else,
Skender

0 Karma

gathiu
Engager

Hi,

if listed example is the definition used for other clients then part "(SERVICE_NAME = NAME_SN)" is the one you look for ... especially the "NAME_SN".
But as mentioned, if using the Oracle JDBC driver, then SID and SERVICE_NAME are effectively interchangeable. If not add ".world" to your SID ... at least this works for me in most cases 🙂

BR.

0 Karma

pmdba
Builder

If using the Oracle JDBC driver, then SID and SERVICE_NAME are effectively interchangeable. Make sure your service_name is registered with the database listener(s) ("lsnrctl status" should show all service names). The ./etc/apps/dbx/local/database.conf file entry should look like the following when using SID:

[orcl]
database = orcl
host = localhost
password = xxxxxxxxxxxxxxxx
port = 1521
readonly = 1
type = oracle
username = splunk

Or like this if using a service_name:


[splunk.world]
database = splunk.world
host = localhost
password = xxxxxxxxxxxxxxxx
port = 1521
readonly = 1
type = oracle
username = splunk

If using Oracle RAC 11g, the host should be the SCAN hostname. If using Oracle RAC 10g, use one of the RAC virtual IP addresses.

Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!