Critical alerts for Hive Metastore. Hive not able to write to Oracle DB.

[#1]

2016-04-25 09:14:29,143 ERROR [main]: metastore.HiveMetaStore (HiveMetaStore.java:startMetaStore(6068)) - javax.jdo.JDOUserException: Exception thrown while loading remaining rows of query at org.datanucleus.api.jdo.JDOAdapter.getUserExceptionForException(JDOAdapter.java:1139) at org.datanucleus.store.rdbms.query.ForwardQueryResult.closingConnection(ForwardQueryResult.java:296) at org.datanucleus.store.query.AbstractQueryResult.disconnect(AbstractQueryResult.java:107) at org.datanucleus.store.rdbms.query.AbstractRDBMSQueryResult.disconnect(AbstractRDBMSQueryResult.java:236) at org.datanucleus.store.rdbms.query.JDOQLQuery$2.transactionPreClose(JDOQLQuery.java:755) at org.datanucleus.store.connection.AbstractManagedConnection.transactionPreClose(AbstractManagedConnection.java:94) at org.datanucleus.store.connection.ConnectionManagerImpl$2.transactionPreRollBack(ConnectionManagerImpl.java:447) at org.datanucleus.TransactionImpl.internalPreRollback(TransactionImpl.java:500) at org.datanucleus.TransactionImpl.rollback(TransactionImpl.java:427) at org.datanucleus.api.jdo.JDOTransaction.rollback(JDOTransaction.java:182) at org.apache.hadoop.hive.metastore.ObjectStore.rollbackTransaction(ObjectStore.java:505) at org.apache.hadoop.hive.metastore.ObjectStore.getMSchemaVersion(ObjectStore.java:6763) at org.apache.hadoop.hive.metastore.ObjectStore.getMetaStoreSchemaVersion(ObjectStore.java:6732) at org.apache.hadoop.hive.metastore.ObjectStore.checkSchema(ObjectStore.java:6690) at org.apache.hadoop.hive.metastore.ObjectStore.verifySchema(ObjectStore.java:6674) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498)

[#2]

While installing HDP 2.4 using Ambari, Oracle 12c is used as the external DB for Hive Metastore. The service shows green on Ambari when started but the logs <hive-metastore-error.txt> show that hive is not able to write to the external DB (Oracle 12c).

[#3]

Hello @Joshua Adeleke, I have seen some issues with Oracle and some of our components depending on the version. I will try to gather more information about this case.

[#4]

Can you check you have tables created in the Oracle instance ?

[#5]

Hello @mlanciaux, the tables were created in the oracle instance.

[#6]

Can you try to INSERT values in one table in Oracle (test) I guess you have GRANT since you have created tables but just to be sure.

[#7 · Accepted solution]

Changing from Oracle 12c to MySQL DB got Hive working without issues.

[#8]

Was this ERROR solved? One of the clients i am working is facing the same error. Is there a patch available for metastore to work with oracle.

[#9]

@Satish Bomma The error was not resolved on Oracle 12c. The workaround was to use MySQL instead of Oracle 12c and it worked and is still working fine.

[#10]

Thanks @Joshua Adeleke, this solution is not acceptable for my client as they want to install metastore db in Oracle. Was there any Support tickets opened for this issue?

[#11]

Pleasure @Satish Bomma. Unfortunately, i did not open a support ticket for this issue. Please update me on the fix if you get it resolved. Thank you. Maybe i should also add that our Oozie workflows were also very epileptic when it was on Oracle 12c and was only stable when it was changed to MySQL.

[#12]

@Satish Bomm,@Joshua Adeleke i am getting same problem. the surprise is i am able to create all hive tables to oracle and that too from hive/bin/schematool but when starting metastore getting this problem.using oracle 11g with java 8 and ojdbc7.jar. any solution will help. i have to setup on oracle only.