Skip to Main Content

SQLcl

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQLcl takes almost 2 minutes to connect to remote database

MassimoBAug 16 2017 — edited Mar 14 2018

I just installed SQLcl on a Linux server and when I try to connect to remote databases in our network, it takes about 2 minutes.

After that, SQLcl's response times are OK.

Here are the details of my environment:

Client machine:

OS: Cent0s

Java: 1.8.0.144_b1

Oracle Instant Client 11g

NOTE: I installed SQLcl in my home directory, since I do not have root privileges.

Remote databases:

Both 11.2.0.4 and 12.0.1.2

The issue happens with all connections methods (EZConnect, TNS_ADMIN, TWO_TASK, conn command).

BTW, I could not test the net alias command, since, when I tried, I got

          SQL> net alias host:1521/database;

          NET-001: Net command not found

Here is the verbose output of the TNS_ADMIN test:

[user@host/bin]$ ./sql -verbose schema/******@database

SQLcl: Release 17.2.0 Production on Wed Aug 16 17:25:00 2017

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Aug 16, 2017 5:25:00 PM oracle.dbtools.versions.CheckForUpdates getBanner

INFO: /home/user/.sqlcl/banner.ser (No such file or directory) at java.io.FileInputStream.open0(Native Method)

java.io.FileNotFoundException: /home/user/.sqlcl/banner.ser (No such file or directory)

        at java.io.FileInputStream.open0(Native Method)

        at java.io.FileInputStream.open(FileInputStream.java:195)

        at java.io.FileInputStream.<init>(FileInputStream.java:138)

        at java.io.FileInputStream.<init>(FileInputStream.java:93)

        at oracle.dbtools.versions.CheckForUpdates.getBanner(CheckForUpdates.java:429)

        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.handleUpdates(SqlCli.java:509)

        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:465)

Aug 16, 2017 5:25:00 PM oracle.dbtools.raptor.utils.TNSHelper checkForTns

INFO: Checking for tnsnames.* in :/home/user

Aug 16, 2017 5:25:00 PM oracle.dbtools.raptor.utils.TNSHelper checkForTns

INFO: Checking for tnsnames.* in :/etc

Aug 16, 2017 5:25:00 PM oracle.dbtools.raptor.utils.TNSHelper checkForTns

INFO: Checking for tnsnames.* in :/usr/lib/oracle/11.2/client64/network/admin

Aug 16, 2017 5:25:00 PM oracle.dbtools.raptor.utils.TNSHelper getTNSEntries

INFO: Using TNS info from :/usr/lib/oracle/11.2/client64/network/admin

Aug 16, 2017 5:25:01 PM oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL

INFO: Attempting to connect using URL= "jdbc:oracle:thin:@(DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 1521))    )    (CONNECT_DATA =      (SID = database)    )  )"

Aug 16, 2017 5:25:01 PM oracle.dbtools.jdbc.util.LogUtil log

INFO: oracle.dbtools.jdbc.orest.Driver:<clinit>:27:No Message

Aug 16, 2017 5:25:01 PM oracle.dbtools.jdbc.util.LogUtil log

INFO: oracle.dbtools.jdbc.orest.Driver:<clinit>:34::ORest driver loaded

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Aug 16, 2017 5:27:19 PM oracle.dbtools.raptor.scriptrunner.commands.NLSLANGListener runOnConnect

INFO: NLS_LANG is set: AMERICAN_AMERICA.AL32UTF8

Aug 16, 2017 5:27:19 PM oracle.dbtools.raptor.scriptrunner.commands.NLSLANGListener runOnConnect

INFO: LANG is set: AMERICAN

Aug 16, 2017 5:27:19 PM oracle.dbtools.raptor.scriptrunner.commands.NLSLANGListener runOnConnect

INFO: TERR is set: AMERICA true

Aug 16, 2017 5:27:19 PM oracle.dbtools.raptor.scriptrunner.commands.NLSLANGListener runOnConnect

INFO: ENCO is set: AL32UTF8

SQL>

As you can see from it loaded the ORest driver at 5:25:01 PM, then it hang  until about 5:27:19 PM when i finally got the SQL prompt.

I do not have any problem in sqlplus.

Could you think of any reason for this delay?

Does SQLcl require Instant client 12c?

Should SQLcl be installed by root?

Thanks,

Massimo

PS: BTW, backspace and delete do not seem to work (they actually delete the characters, but I do not see the changes... anyone else with this other issue?

Comments

Clemens Bleile

This is probably caused by the issue with /dev/random being blocked because of insufficient entropy. See e.g.

http://www.usn-it.de/index.php/2009/02/20/oracle-11g-jdbc-driver-hangs-blocked-by-devrandom-entropy-pool-empty/

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

https://oraganism.wordpress.com/2015/09/20/slow-jdbc-connections-strace-and-random-numbers/

Does SQLcl require Instant client 12c? --> No

Should SQLcl be installed by root? --> No

Gaz in Oz

Try with

./sql /nolog

How long?

./sql -noupdates /nolog

How long?

(./sql -h for all commandline options)

Using /nolog stops sqlcl traversing the network looking for the database server.

By default sqlcl will try and find newer updates by connecting to some URL. "-noupdates" stops it doing this.

Does SQLcl require Instant client 12c?

No.

Should SQLcl be installed by root?

No.

Clemens Bleile

./sql /nolog does not help because you do not connect to the database then (same as sqlplus /nolog). I.e. /nolog is always fast. The issue happens when connecting.

It's also not related to -noupdates. Please read the links I provided for the reasons of the slow connect using JDBC. I actually also wrote a Blog about it:

https://blog.dbi-services.com/connect-times-to-the-db-suddenly-become-very-slow-using-sqlcl

Gaz in Oz

It's also not related to -noupdates

....so "-noupdates" didn't help?

Clemens Bleile

yes, "-noupdates" didn't help.

1 - 5

Post Details

Added on Aug 16 2017
5 comments
2,635 views