All Apps and Add-ons

DB Connect 2: How to configure a connection to an Oracle database with the service name instead of SID?

karabsze
Path Finder

I am trying to connect the oracle db with the service name, but the following warning pops up:

Validating connection with URL [jdbc:oracle:thin:@{host}:1521:{service_name}] failed, please verify that the connection is configured correctly.

May you advise what is the right configuration for using Service name instead of SID?
Thanks

1 Solution

pmdba
Builder

The connect string format is defined in the DBX v2 "db_connection_types.conf" file:

[oracle]
displayName = Oracle
serviceClass = com.splunk.dbx2.OracleJDBC
jdbcUrlFormat = jdbc:oracle:thin:@//<host>:<port>/<database>
jdbcDriverClass = oracle.jdbc.OracleDriver
database = XE
testQuery = SELECT 1 FROM DUAL
port = 1521

Make sure you copy the file from the default folder to the local folder before making changes. See the documentation for more details.

View solution in original post

icyfeverr
Path Finder

I struggled with this myself just recently, the main piece is selecting the correct connection type, as you will see below. For SID use 'oracle', if you are using Service name use 'oracle_service'.

If you are using a SID follow this format:

[SID Example]
connection_type = oracle
database = sid
host = hostname
jdbcUseSSL = 0
port = 1521

If you are using a Service Name follow this format:
[Service Name Example]
connection_type = oracle_service
database = service_name
host = hostname or ip address
jdbcUseSSL = 0
port = 1563

MadiAvalos
Engager

Thanks! this work for me totally!

0 Karma

pmdba
Builder

The connect string format is defined in the DBX v2 "db_connection_types.conf" file:

[oracle]
displayName = Oracle
serviceClass = com.splunk.dbx2.OracleJDBC
jdbcUrlFormat = jdbc:oracle:thin:@//<host>:<port>/<database>
jdbcDriverClass = oracle.jdbc.OracleDriver
database = XE
testQuery = SELECT 1 FROM DUAL
port = 1521

Make sure you copy the file from the default folder to the local folder before making changes. See the documentation for more details.

fdi01
Motivator
Thin-style Service Name Syntax

Thin-style service names are supported only by the JDBC Thin driver. The syntax is:

@//host_name:port_number/service_name

For example:

jdbc:oracle:thin:scott/tiger@//myhost:1521/myservicename

So I would try:

jdbc:oracle:thin:@//oracle.hostserver2.mydomain.ca:1522/ABCD
or
[jdbc:oracle:thin:@//{your_host}:1521/{your_service_name}]

for more information see this link:
http://docs.oracle.com/cd/B28359_01/java.111/b31224/urls.htm#BEIDHCBA

0 Karma

mmensch
Path Finder

I am also having this issue. Can someone confirm that Oracle DB 12.1.0.2.0 is supported with this app? The database matrix on the user documentation only lists that Oracle DB 11g works with JDBC driver ojdbc6.

Thanks

0 Karma

karabsze
Path Finder

Hi fdi,
Thanks for you reply.
I know about the thin style but I am not sure how I can setup it in DBConnect V2

In the db_connections.conf

[TEST_CONNECTION]
connection_type = oracle
database = test.services
host = host.com
identity = test_user
port = 1521

readonly = 1

The connection string is generated by above configuration and the ":" inside
jdbc:oracle:thin:@{host}:1521*:*{service_name}
cannot be skipped....i have no way to construct the string like below format

jdbc:oracle:thin:@//oracle.hostserver2.mydomain.ca:1522/ABCD
or
[jdbc:oracle:thin:@//{your_host}:1521/{your_service_name}]

0 Karma
Get Updates on the Splunk Community!

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...

Five Subtly Different Ways of Adding Manual Instrumentation in Java

You can find the code of this example on GitHub here. Please feel free to star the repository to keep in ...

New Splunk APM Enhancements Help Troubleshoot Your MySQL and NoSQL Databases Faster

Splunk Observability has two new enhancements to make it quicker and easier to troubleshoot slow or frequently ...