Reply
Highlighted
New Contributor
Posts: 3
Registered: ‎11-15-2017
Accepted Solution

How to use JDBC connection string (Oracle TNSNAMES) for connecting to Hive Metastore DB (Oracle)

[ Edited ]

Hello Cloudera Community,

 

We have a primary and a standby Oracle clusters where our Hive Metastore DB is located.

I'd like to be able to specify a FAILOVER JDBC connection string which might access both primary and standby Oracle cluster.

I don't want to change manually values for "Hive Metastore Database Host" and "Hive Metastore Database Name" everytime when a primary and a standby Oracle DB's are switched over (their roles will be swapped).

 

How can i specify a JDBC connection string for the "Hive Metastore Database"?

 

Our CDH version: 5.11.0

 

Thank you!

New Contributor
Posts: 3
Registered: ‎11-15-2017

Re: How to use JDBC connection string (Oracle TNSNAMES) for connecting to Hive Metastore DB (Oracle)

I got a very helpful answer from the Cloudera Support and it did the trick:

 

Look for "Hive Metastore Server Advanced Configuration Snippet (Safety Valve) for hive-site.xml" in the Hive configuration, press (+) [Plus button] and specify the following:

 

in the "Name" field: javax.jdo.option.ConnectionURL

in the "Value" field: jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=off)(FAILOVER=on)(CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-server1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-server2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ora_service_name.ora_domain_name)))

 

 

NOTE: You will have to change the connection string correspondingly - I just showed an example of the failover-able (primary + standby DBs) Oracle TNS connection string.

 

 

The same trick can be used for "Sentry", but the corresponding parameter name is: "sentry.store.jdbc.url"

Announcements