Splunk DB Connect 2: Is it possible to establish an Oracle DB Connection with a tnsnames.ora entry?

[heiko_seip]

Hello,

is there a possibility for a tnsnames.ora entry to establish the connection?

Then through a customized "jdbcUrlFormat = ..." so connect.
jdbcUrlFormat = jdbc: oracle: thin: @ //

Would like to connect via Splunk on a scan Oracle listener.
About sqlplus is not a problem "sqlplus USER_SPLUNK @ TNS_NAMES_ENTRY" works.
Or you can use the tnsnames.ora entry directly into the Splunk config?

Many thanks for your help,
Heiko

[pmdba · Accepted solution]

If you are connecting to a SCAN address, then you don't need the complex TNSNAMES entry. The whole point of SCAN is to move the load balancing and failover complexity to the server and out of the client configuration. SCAN handles the load balancing and failover parameters can be defined a a part of each service name. Then you can simply use the JDBC-Thin driver with the SCAN hostname and the Oracle service name to connect, as described in this post.

Potentially you could also alter the connect string prototype in the db_connection_types.conf file to use a JDBC-Thick (OCI) connection, something like this:

jdbcUrlFormat = jdbc:oracle:oci:@TNSALIAS

This would allow you to use TNSNAMES, say to define a connection string with SOURCE_ROUTE, enable strong network encryption, or to use LDAP resolution instead of tnsnames.ora. I have made similar changes with other applications designed to use the Thin connection with no problem, but I have not tested such a configuration with Splunk and I'm not sure if it would work. Perhaps someone from Splunk could comment on that configuration, and whether or not is has ever been tested...

[heiko_seip]

oh yeah thats works for me, with the servicename.

Thanks so lot and kind regards,
Heiko 🙂

[heiko_seip]

tnsnames.ora:

TNS_NAMES_ENTRY =
(DESCRIPTION_LIST= (LOAD_BALANCE=off) (FAILOVER=on)
    (DESCRIPTION = (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=10)(RETRY_COUNT=3)
                (ADDRESS_LIST=(LOAD_BALANCE=on)
                (ADDRESS = (PROTOCOL = TCP)(HOST = host1a-scan)(PORT = 1234))
                )
        (CONNECT_DATA=
                (service_name=DB_SERVICE)
        ))
    (DESCRIPTION = (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
                (ADDRESS_LIST=(LOAD_BALANCE=on)
                (ADDRESS = (PROTOCOL = TCP)(HOST = host5a-scan)(PORT = 1234))
                )
        (CONNECT_DATA=
                (service_name=DB_SERVICE)
        ))
)

[heiko_seip]

thanks for the support, I am puzzled why I get no connect.