SQOOP Connection Issue ( Sqoop to Oracle)

[#1]

Could you please help on below connection issue:

I have already placed the JAR file in the (/usr/lib/sqoop/lib) folder. And I have verified ping/tnsping/telnet .. etc in database side and nc -z (127.XX.XX.XX 1521) in Cloudera and everything looks fine but I'm unable to connect sqoop.

sqoop list-tables \
> --connect jdbc:oracle:thin:@localhost:1521/pdborcl \
> --username hr \
> -P \
> --verbose
Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
20/02/23 09:59:00 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.13.0
20/02/23 09:59:00 DEBUG tool.BaseSqoopTool: Enabled debug logging.
Enter password:
20/02/23 09:59:03 DEBUG sqoop.ConnFactory: Loaded manager factory: org.apache.sqoop.manager.oracle.OraOopManagerFactory
20/02/23 09:59:03 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
20/02/23 09:59:03 DEBUG sqoop.ConnFactory: Trying ManagerFactory: org.apache.sqoop.manager.oracle.OraOopManagerFactory
20/02/23 09:59:04 DEBUG oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop can be called by Sqoop!
20/02/23 09:59:04 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
20/02/23 09:59:04 DEBUG sqoop.ConnFactory: Trying ManagerFactory: com.cloudera.sqoop.manager.DefaultManagerFactory
20/02/23 09:59:04 DEBUG manager.DefaultManagerFactory: Trying with scheme: jdbc:oracle:thin:@localhost
20/02/23 09:59:04 DEBUG manager.OracleManager$ConnCache: Instantiated new connection cache.
20/02/23 09:59:04 INFO manager.SqlManager: Using default fetchSize of 1000
20/02/23 09:59:04 DEBUG sqoop.ConnFactory: Instantiated ConnManager org.apache.sqoop.manager.OracleManager@727f6c7d
20/02/23 09:59:05 DEBUG manager.OracleManager: Creating a new connection for jdbc:oracle:thin:@localhost:1521/pdborcl, using username: hr
20/02/23 09:59:05 DEBUG manager.OracleManager: No connection paramenters specified. Using regular API for making connection.
20/02/23 09:59: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:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
at org.apache.sqoop.Sqoop.main(Sqoop.java:252)

[#2]

@Prabhu_Muppala 
Can you please try to specify the the "--driver" param in your Sqoop as following:

--driver oracle.jdbc.driver.OracleDriver

Also can you pleas everify if the DB credentials are correctly being entered and DB is accessbile on localhost 1521?

# netstat -tnlpa | grep 1521

Also this user "hr" has enough privileges to list tables in Oracle DB?

[#3]

Hi Jsensharma,

Thank you for your updates

**********************************

With netstat :

[cloudera@quickstart ~]$ netstat -tnlpa | grep 1521
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)

[cloudera@quickstart ~]$ sudo netstat -tnlpa | grep 1521
[cloudera@quickstart ~]$

*****************************************************************************

I have added '--driver' and facing a new issue now. 

And we are able to list the tables in sql developer for 'HR' user.

[cloudera@quickstart ~]$ sqoop list-tables \
> --driver oracle.jdbc.driver.OracleDriver \
> --connect jdbc:oracle:thin:@localhost:1521:pdborcl \
> --username hr \
> -P \
> --verbose
Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
20/02/24 11:41:28 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.13.0
20/02/24 11:41:28 DEBUG tool.BaseSqoopTool: Enabled debug logging.
Enter password:
20/02/24 11:41:31 DEBUG sqoop.ConnFactory: Loaded manager factory: org.apache.sqoop.manager.oracle.OraOopManagerFactory
20/02/24 11:41:31 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
20/02/24 11:41:31 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.
20/02/24 11:41:31 INFO manager.SqlManager: Using default fetchSize of 1000
20/02/24 11:41:32 DEBUG manager.SqlManager: No connection paramenters specified. Using regular API for making connection.
20/02/24 11:41:32 ERROR manager.SqlManager: Error reading database metadata: java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:743)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:662)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:560)
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.listTables(SqlManager.java:539)
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:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:470)
at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:506)
at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:595)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:230)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
... 15 more
Caused by: java.net.ConnectException: Connection refused
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at java.net.Socket.connect(Socket.java:579)
at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:161)
at oracle.net.nt.ConnOption.connect(ConnOption.java:159)
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:428)
... 20 more
Could not retrieve tables list from server
20/02/24 11:41:32 ERROR tool.ListTablesTool: manager.listTables() returned null

[#4]

@Prabhu_Muppala 

As we see that the netstat command shows no oracle port 1521 opened.     (no output means Oracle is not running on default listerer port  1521.

[cloudera@quickstart ~]$ sudo netstat -tnlpa | grep 1521

.

Also the following error indicates that your Oracle Database is not successfully running on "localhost:1521"

Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:470)

So please verify why the oracle DB is not running and configured to use 1521 listener port?
Please try to restart Oracle and then recheck if the port 1521 is opened? Or if you have configured to run Oracle on some other port?

# netstat -tnlpa | grep $ORACLE_PROCESS_ID

.