Sqoop command is failing for Oracle database on CDP 7.1.9 cluster and same command works on different cluster of same version

[#1]

Sqoop command is failing for Oracle database on CDP 7.1.9 cluster and same command works on different cluster of same version

The only difference between working and non-working is storage, it might not be the main issue.

sqoop command: sqoop import -libjars ./ojdbc8.jar -Doracle.jdbc.defaultNChar=true -Doracle.jdbc.OracleConnection.CONNECTION_PROPERTY_SET_ROW_PREFETCH_SIZE=10 -Doracle.jdbc.timezoneAsRegion=false -Doracle.sessionTimeZone=Asia/Kolkata -Doracle.jdbc.NLS_LANG=AMERICAN_AMERICA.AL32UTF8 -Doracle.jdbc.timezoneAsRegion=false --connect jdbc:oracle:thin:@hostname:1522:servicename --password $password --map-column-java CHAR_COL=String,DATE_COL=String,FLOAT_COL=Double,TIMESTAMP_COL=String,VARCHAR_COL=String,VARCHAR2_COL=String,NUMPERPS_COL=String,LONG_COL=String --username $username --as-avrodatafile --split-by FLOAT_COL --mapreduce-job-name SQOOP_SOURCE --columns CHAR_COL,DATE_COL,FLOAT_COL,TIMESTAMP_COL,VARCHAR_COL,VARCHAR2_COL,NUMPERPS_COL,LONG_COL --delete-target-dir --table SQOOP_SOURCE --target-dir hdfs://nameservice1/tmp/SPARK

we tried removing "-Doracle.jdbc.NLS_LANG=AMERICAN_AMERICA.AL32UTF8" from cluster node and still facing same issue.

5/08/01 15:54:13 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7.7.1.9.1000-103
25/08/01 15:54:13 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
25/08/01 15:54:13 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
25/08/01 15:54:13 INFO manager.SqlManager: Using default fetchSize of 1000
25/08/01 15:54:13 INFO tool.CodeGenTool: Beginning code generation
25/08/01 15:54:14 ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-12705: Cannot access NLS data files or invalid environment specified

https://docs.oracle.com/error-help/db/ora-00604/
java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-12705: Cannot access NLS data files or invalid environment specified

https://docs.oracle.com/error-help/db/ora-00604/
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:712)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:607)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:602)
at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1695)
at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:936)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:969)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:237)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:547)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTHWithO5Logon(T4CTTIoauthenticate.java:1746)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:1493)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:1433)
at oracle.jdbc.driver.T4CConnection.authenticateWithPassword(T4CConnection.java:2056)
at oracle.jdbc.driver.T4CConnection.authenticateUserForLogon(T4CConnection.java:1993)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:1086)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:1158)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:106)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:895)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:702)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at org.apache.sqoop.manager.OracleManager.makeConnection(OracleManager.java:353)
at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:59)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:747)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:770)
at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:286)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:257)
at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:243)
at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:334)
at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1883)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1676)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:524)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:680)
at org.apache.sqoop.Sqoop.run(Sqoop.java:151)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:81)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:187)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:241)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:250)
at org.apache.sqoop.Sqoop.main(Sqoop.java:259)
25/08/01 15:54:14 ERROR tool.ImportTool: Import failed: java.io.IOException: No columns to generate for ClassWriter
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1682)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:524)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:680)
at org.apache.sqoop.Sqoop.run(Sqoop.java:151)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:81)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:187)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:241)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:250)
at org.apache.sqoop.Sqoop.main(Sqoop.java:259)

[#2]

@Pratibha123, Welcome to our community! To help you get the best possible answer, I have tagged in our Sqoop experts  @mszurap @ChethanYM  who may be able to assist you further.

Please feel free to provide any additional information or details about your query. We hope that you will find a satisfactory solution to your question.

[#3]

@Pratibha123 

ORA-12705 - This is the key error. The Oracle JDBC driver needs to set up a language and character set session with the Oracle database. To do this, it attempts to read NLS configuration data from files on the local filesystem of the machine where Sqoop is running. This error occurs because it cannot find or access those NLS data files, or the environment variable that points to them is invalid.

ORA-00604: This is a cascading error. It means an internal, recursive SQL statement that Oracle runs during the connection/session setup failed, because the session setup itself was incomplete.

References:

https://docs.oracle.com/en/error-help/db/ora-12705/?r=23ai

https://stackoverflow.com/questions/7700330/ora-12705-cannot-access-nls-data-files-or-invalid-enviro...

https://docs.oracle.com/en/error-help/db/ora-00604/?r=23ai

https://stackoverflow.com/questions/30478070/how-to-solve-sql-error-ora-00604-error-occurred-at-recu...

"oracle.jdbc.NLS_LANG" Is not seems to be valid property.

Can you export it before running the job and check?

> export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"

Ensure libjars has the correct ojdbc8.jar and it's compatible with your Oracle DB version.

Regards,

Chethan YM

[#4]

@Pratibha123, Did the response assist in resolving your query? If it did, please mark the relevant reply as the solution, as it will help others locate the answer more easily in the future. 

[#5]

@Pratibha123 

ORA-12705 - This is the key error. The Oracle JDBC driver needs to set up a language and character set session with the Oracle database. To do this, it attempts to read NLS configuration data from files on the local filesystem of the machine where Sqoop is running. This error occurs because it cannot find or access those NLS data files, or the environment variable that points to them is invalid.

ORA-00604: This is a cascading error. It means an internal, recursive SQL statement that Oracle runs during the connection/session setup failed, because the session setup itself was incomplete.

References:

https://docs.oracle.com/en/error-help/db/ora-12705/?r=23ai

https://stackoverflow.com/questions/7700330/ora-12705-cannot-access-nls-data-files-or-invalid-enviro...

https://docs.oracle.com/en/error-help/db/ora-00604/?r=23ai

https://stackoverflow.com/questions/30478070/how-to-solve-sql-error-ora-00604-error-occurred-at-recu...

"oracle.jdbc.NLS_LANG" Is not seems to be valid property.

Can you export it before running the job and check?

> export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"

Ensure libjars has the correct ojdbc8.jar and it's compatible with your Oracle DB version.

Regards,

Chethan YM