Sqoop: Oracle JDBC Driver - Connection reset 에러

증상: 에러 로그: DB Server의 Oracle 리스너가 내려가지 않음에도 불구하고 불특정하게 다음과 같이 Connection Reset 에러가 클라이언트 사이드에서 발생: 클라이언트(Oracle JDBC Driver)와 Oracle Database Server와 연결이 되지 않는 현상 - 불특정하게 발생. 될때도 있고 안될때도 있고...

- 리눅스에서만 발생 

//-------------------------------------------------------

17/03/27 18:15:02 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.10.0 

17/03/27 18:15:04 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override 

17/03/27 18:15:04 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc. 

17/03/27 18:15:04 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time. 

17/03/27 18:15:04 INFO manager.SqlManager: Using default fetchSize of 1000 

17/03/27 18:15:04 INFO tool.CodeGenTool: Beginning code generation 

17/03/27 18:16:16 ERROR manager.SqlManager: Error executing statement: java.sql.SQLRecoverableException: IO Error: Connection reset 

java.sql.SQLRecoverableException: IO Error: Connection reset 

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:498) 

at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553) 

at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254) 

at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32) 

at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528) 

at java.sql.DriverManager.getConnection(DriverManager.java:571) 

at java.sql.DriverManager.getConnection(DriverManager.java:215) 

at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:904) 

at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52) 

at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:763) 

at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:786) 

at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:289) 

at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:260) 

at org.apache.sqoop.manager.SqlManager.getColumnTypesForQuery(SqlManager.java:253) 

at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:336) 

at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1858) 

at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1658) 

at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107) 

at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:488) 

at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615) 

at org.apache.sqoop.Sqoop.run(Sqoop.java:143) 

at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70) 

at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179) 

at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218) 

at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227) 

at org.apache.sqoop.Sqoop.main(Sqoop.java:236) 

Caused by: java.net.SocketException: Connection reset 

at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:118) 

at java.net.SocketOutputStream.write(SocketOutputStream.java:159) 

at oracle.net.ns.DataPacket.send(DataPacket.java:210)

//------------------------------------------

원인: 

Linux 파일 시스템 때문에 발생 및 이에 대한 가이드 존재

https://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html#_oracle_connection_reset_errors

하지만, Sqoop 가이드의 옵션이 잘못되어 있음.

Solution: This problem occurs primarily due to the lack of a fast random number generation device on the host where the map tasks execute. On typical Linux systems this can be addressed by setting the following property in the java.security file: securerandom.source=file:/dev/../dev/urandom The java.security file can be found under $JAVA_HOME/jre/lib/security directory. Alternatively, this property can also be specified on the command line via: -D mapred.child.java.opts="-Djava.security.egd=file:/dev/../dev/urandom" Please note that it’s very important to specify this weird path /dev/../dev/urandom as it is due to a Java bug 6202721, or /dev/urandom will be ignored and substituted by /dev/random.

위에 언급되어 있지만, Oracle Bug 6202721의 솔루션이 있음.

//-----------------------------------------------------------------

Oracle Bug :

WORK AROUND

Alternatively, set securerandom.source to file:/dev/./urandom 

With that setting in JDK 5.0, the behavior will be exactly the same as with file:/dev/urandom in 1.4.2.

//-----------------------------------------------------------------

즉, Java에서는 file:/dev/urandom 및 file:/dev/../urandom 설정 모두 file:/dev/random으로 인식하는 것이 문제

즉, Sqoop의 가이드가 잘못되어 있음.

해결책:

- 옵션이 정상적으로 설정되어 있는지 확인하는 Debug 옵션: 

      "-Djava.security.debug="provider,engine=SecureRandom""

- Workaround 

//-------------------------------------------

1. java.security file 수정 : securerandom.source=file:/dev/./dev/urandom 또는
2. Java option: -Dmapred.child.java.opts="-Djava.security.egd=file:/dev/./dev/urnadom"으로 설정

• 참고 : file:/dev/./dev/urandom 대신 file:///dev/urandom도 가능

https://security.stackexchange.com/questions/14386/what-do-i-need-to-configure-to-make-sure-my-software-uses-dev-urandom

General advice

Any program written in Java

Add

-Djava.security.egd=file:///dev/urandom switch

or

-Djava.security.egd=file:/dev/./urandom

to the command line invocation used to start the Java process. (Without this, Java uses /dev/random to seed its SecureRandom class, which can cause Java code to block unexpectedly.)

Alternatively, in the $JAVA_HOME/jre/lib/security/java.security configuration file, add the line

securerandom.source=file:/dev/./urandom

Footnote: In the above examples, you need the crazy-looking filename, e.g., the extra /./, to trick Java into accepting your filename. If you just use /dev/urandom, Java decides you didn't really mean it and replaces what you wrote with /dev/random. Craziness!

//--------------------------------------------------------------------

기타 문서 및 Debug

http://stackoverflow.com/questions/2327220/oracle-jdbc-intermittent-connection-issue

As per Bug https://bugs.openjdk.java.net/browse/JDK-6202721

Java will not consder -Djava.security.egd=file:/dev/urandom

It should be -Djava.security.egd=file:/dev/./urandom

//-------------------------------------------

참고 자료:

• Oracle Documentation: http://bugs.java.com/bugdatabase/view_bug.do?bug_id=6202721
• Linux Java의 urandom / random : https://gist.github.com/svrc/5a8accc57219b9548fe1
• 설정 옵션 Debug : https://tersesystems.com/2015/12/17/the-right-way-to-use-securerandom/