Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Sqoop, mail # user - Transactional isolation issue with OraOop during import


Copy link to this message
-
RE: Transactional isolation issue with OraOop during import
David Robson 2011-09-30, 06:48
Hi Ken,

Quest Data Connector for Oracle and Hadoop 1.4 has been released which allows you to specify hints via the oraoop.import.hint parameter. There is more information in the documentation about the parameter - but this should solve your issue.

You can download it here:

http://toadforcloud.com/entry.jspa?categoryID=677&externalID=4298

Regards,

David

From: Peter Hall [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 22 September 2011 4:30 PM
To: [EMAIL PROTECTED]
Subject: RE: Transactional isolation issue with OraOop during import

Hi Ken,

SCN_ASCENDING should help. We are currently developing a patch to allow adding hints to the SQL. No release date for it yet, but should be soon. Once that's out you can try SCN_ASCENDING.

In the mean time you could try increasing the size of the rollback segments. David should be able to provide more details when he's available.

Cheers,
Peter
________________________________
From: Ken Krugler [[EMAIL PROTECTED]]
Sent: Wednesday, 21 September 2011 6:35
To: [EMAIL PROTECTED]
Subject: Transactional isolation issue with OraOop during import
Hi all, and especially Quest devs,

We ran into a failing Sqoop issue recently, while importing lots of data from an Oracle DB.

>From the error below it looks Oracle is using transactional isolation for read consistency - but in our case, when pulling isolated rows (no inter-row dependencies) from a single table, that's not necessary, right?

Is there any way to disable that?

One alternative solution would be to provide the SCN_ASCENDING hint, as per:

http://berxblog.blogspot.com/2009/01/avoid-ora-1555-in-long-running-queries.html

This apparently causes Oracle to ignore the error and keep going, returning potentially inconsistent data - but for our case, that wouldn't matter.

Any comments on this approach? Of course we'd need support from OraOop to be able to (optionally) specify that hint, I assume via oraoop-site.xml.

Thanks,

-- Ken

===========================================================================================11/09/17 17:56:08 INFO oraoop.OracleConnectionFactory: Initializing Oracle session with SQL : alter session set time_zone = 'GMT' 11/09/17 17:56:08 INFO oraoop.OracleConnectionFactory: Initializing Oracle session with SQL : alter session disable parallel query 11/09/17 17:56:08 INFO oraoop.OracleConnectionFactory: Initializing Oracle session with SQL : alter session set "_serial_direct_read"=true 11/09/17 17:56:08 INFO oraoop.OracleConnectionFactory: Initializing Oracle session with SQL : alter session set tracefile_identifier=oraoop 11/09/17 17:56:08 INFO oraoop.OracleConnectionFactory: Initializing Oracle session with SQL : alter session set optimizer_index_cost_adj=10000
11/09/17 17:56:14 INFO oraoop.OraOopDataDrivenDBInputFormat: The table being imported by sqoop has 11403264 blocks that have been divided into 1392 chunks which will be processed in 16 splits. The chunks will be allocated to the splits using the method : ROUNDROBIN
java.io.IOException: SQLException in nextKeyValue at com.cloudera.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:246) at com.quest.oraoop.OraOopDBRecordReader.nextKeyValue(OraOopDBRecordReader.java:312) at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:455) at org.apache.hadoop.mapreduce.MapContext.nextKeyValue(MapContext.java:67) at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:143) at com.cloudera.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:187) at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:646) at org.apache.hadoop.mapred.MapTask.run(MapTask.java:322) at org.apache.hadoop.mapred.Child$4.run(Child.java:268) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:396) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1115) at org.apache.hadoop.mapred.Child.main(Child.java:262) Caused by: java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 3054 with name "_SYSSMU3054_4184535728$" too small at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) at oracle.jdbc.driver.T4CPreparedStatement.fetch(T4CPreparedStatement.java:1084) at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:359) at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:263) at com.cloudera.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235) ... 12 more
Ken Krugler
+1 530-210-6378
http://bixolabs.com
custom big data solutions & training
Hadoop, Cascading, Mahout & Solr