sqoop connection to oracle causes an exception

[#1]

sqoop Connection to oracle is giving following exception :

17/09/15 13:47:09 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
java.lang.NullPointerException
at org.apache.sqoop.manager.OracleManager.listTables(OracleManager.java:769)
at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.main(Sqoop.java:243)

checked the connectivity between the cluster and Oracle server using telnet <server Name> <port> and it works.

used the same sqoop command from differnt server which is not behind the firewall and it works too.

[#2]

@kishan vantakala Can you please paste the full logs using --verbose option while running the command here.

[#3]

@kishan vantakala

The error that you are getting indicates that the Connection is not established with the Oracle Database.

ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
java.lang.NullPointerException
    at org.apache.sqoop.manager.OracleManager.listTables(OracleManager.java:769)
    at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:147)

Mostly likely it it due to either the Database hostname is not resolvable from your machine where you are running the sqoop client Or the --connect URL is not in correct format (like missing SID/Service name)

You can try doing a telnet to see if you can reach to the database. (jdbc:oracle:thin:@host:1521/Servicename)

# telnet  dbHost  1521
OR
# nc  -v  dbHost  1521

.

Check if you are able to connect to that host & port? If not then try specifying the IP Address instead and then try again.

Example:

# sqoop list-tables --connect "jdbc:oracle:thin:@dbHost:1521:ORCL" --username usr1 -password yourpwd
(OR)
# sqoop list-tables --connect "jdbc:oracle:thin:@dbHost:1521/XE" --username usr1 -password yourpwd

.

Please see similar discussion: https://community.hortonworks.com/questions/92437/sqoop-connection-to-oracle-from-sandbox-gives-erro...

[#4]

Following is the log with verbose option

$ sqoop list-tables --options-file SqoopConnectParms.txt --verbose
Warning: /usr/hdp/2.6.1.0-129/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
17/09/18 15:37:05 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.6.1.0-129
17/09/18 15:37:05 DEBUG tool.BaseSqoopTool: Enabled debug logging.
17/09/18 15:37:05 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
17/09/18 15:37:05 DEBUG sqoop.ConnFactory: Loaded manager factory: org.apache.sqoop.manager.oracle.OraOopManagerFactory
17/09/18 15:37:05 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
17/09/18 15:37:05 DEBUG sqoop.ConnFactory: Trying ManagerFactory: org.apache.sqoop.manager.oracle.OraOopManagerFactory
17/09/18 15:37:05 DEBUG oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop can be called by Sqoop!
17/09/18 15:37:05 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
17/09/18 15:37:05 DEBUG sqoop.ConnFactory: Trying ManagerFactory: com.cloudera.sqoop.manager.DefaultManagerFactory
17/09/18 15:37:05 DEBUG manager.DefaultManagerFactory: Trying with scheme: jdbc:oracle:thin
17/09/18 15:37:05 DEBUG manager.OracleManager$ConnCache: Instantiated new connection cache.
17/09/18 15:37:05 INFO manager.SqlManager: Using default fetchSize of 1000
17/09/18 15:37:05 DEBUG sqoop.ConnFactory: Instantiated ConnManager org.apache.sqoop.manager.OracleManager@65d09a04
17/09/18 15:37:05 DEBUG manager.OracleManager: Creating a new connection for jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxx.xxx.com)(PORT=1523)))(CONNECT_DATA=(SERVICE_NAME=rtd1perf)(SERVER=DEDICATED))))), using username: HADOOPUSER
17/09/18 15:37:05 DEBUG manager.OracleManager: No connection paramenters specified. Using regular API for making connection.
17/09/18 15:38:05 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
java.lang.NullPointerException
at org.apache.sqoop.manager.OracleManager.listTables(OracleManager.java:769)
at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.main(Sqoop.java:243)