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

[karabsze]

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

[icyfeverr]

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]

Thanks! this work for me totally!

[pmdba · Accepted solution]

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]

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

[mmensch]

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

[karabsze]

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}]