Support Questions
Find answers, ask questions, and share your expertise

Solr import data with Oracle db

Solr import data with Oracle db

Rising Star

Hi,

I am working with Solr 6.5 and trying to index and import data from Oracle db. I've set the following steps up in the configurations :

- Copy the ojdbc6.jar to the dist file and solr/server/lib.

- Add the following line to the Solrconfig file.

<lib dir="${solr.install.dir:../../../..}/dist/" regex=".*\.jar" />

- Add the following line to Solr Config.xml file

<requestHandler name="/dataimport"> <lst name="defaults"> <str name="config">data-config.xml</str> </lst> </requestHandler>

- Add the following lines in data-config.xml

<dataConfig> <dataSource name="jdbc" driver="oracle.jdbc.OracleDriver" url="jdbc:oracle:thin:@XXXXX/YYY" user="XXX" password="XXX"/> <document> <entity name="URI" query="SELECT name from URI"> <field column="URI" name="URI" /> </entity> </document> </dataConfig>

- Add the following line to the managedschema.xml file

<field name="URI" type="string" indexed="true" stored="true" />

- Restart Solr

After the above changes when I tried to index and import the data it gives me the following error.

Doc Builder : Exception while processing PIERS_STATUS document: SolrInputDocument(fields:[]):org.apache.solr.handler.dataimport.DataImportHandlerException : Unable to execute query: SELECT ID from PERIS.STATUS Processing Document #1

Doc Importer : Full Import failed:java.lang.RuntimeException: java.lang.RuntimeException: org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to execute query: SELECT ID from PERIS.STATUS Processing Document #1

MySQL db works perfectly fine but not Oracle. Can you please help me?

3 REPLIES 3

Re: Solr import data with Oracle db

Explorer

Hi Sanaz,

1st- make sure the the Oracle listener is up and running
2nd- make sure that the port 1521 is opened from Solr server

3rd- I've tried the same scenario on two machine: one oracle DB and the other is Solr, after a long troubleshooting, I found my issue was with the "data-config.xml", it was such as following:

----------------------------------------------------------------------------------------------------

<dataConfig>

<dataSource name="jdbc" driver="oracle.jdbc.OracleDriver" url="jdbc:oracle:thin:@//[my-oracle-DB-server-IP]:1521/orcl" user="myusername" password="mypassword"/>

<document>

<entity name="wlslog" query="select * from wlslog">

<field column="ID" name="id" />

<field column="TIME_STAMP" name="time_stamp" />

<field column="CATEGORY" name="category" />

<field column="TYPE" name="type" />

<field column="SERVERNAME" name="servername" />

<field column="CODE" name="code" />

<field column="MSG" name="msg" />

</entity>

</document>

</dataConfig>

-------------------------------------------------------------

I think you might missed the "//" after "@" in the connection URL string.

Best Regards

Mahmoud

Re: Solr import data with Oracle db

Mentor

@msabri

Take note that not all Oracle databases run on port 1521, a smart DBA would change that default port, the best information about the port is from the DBA team :-)

Re: Solr import data with Oracle db

Explorer

Sure.. I've just mentioned the port used by Sanaz in his question :)