SecureBridges & Oracle 9i

[Daniele Buttarelli]

My environment:
Rad Studio XE4 Update1 + Mobile
ODAC 9.1.4
SecureBridge 6.1.2
Oracle9i Enterprise Edition Release 9.2.0.8.0

I try to connect to Oracle9i via SSH tunnel.

Code example:
ScFileStorage.Path := GetHomePath + PathDelim + 'Documents' + PathDelim;
ScSSHClient.HostName := 'XX.XX.XX.XX';
ScSSHClient.Port := 2224;
ScSSHChannel.DestHost := '127.0.0.1';
ScSSHChannel.SourcePort := 1520;
ScSSHChannel.DestPort := 1521;
OraSession.Server := '127.0.0.1:1520:TDB1';
if not ScSSHClient.Connected then
ScSSHClient.Connect;
if not ScSSHChannel.Connected then
ScSSHChannel.Connect;
OraSession.Open;
quOraQuery.Close;
quOraQuery.Open;

ScSSHClient and ScSSHChannel connect succesfully but when I open OraSession I get this error: ORA-03113: end-of-file on communication channel.

How can I solve this problem?
Thanks.
Daniele

[AlexP]

Hello,

Please check whether you can connect to the Oracle server directly using only ODAC (without SecureBridge).

[Daniele Buttarelli]

Yes, using only ODAC I can connect to the Oracle server direcly, without any problems.

[AlexP]

Hello,

Please add an OnDataToClient event handler in the SSH server, in the TScSSHServer component, with the following implementation:

Code: Select all

var
  t: TBytes;
begin
  SetLength(t, Buffer[1]);
  Move(Buffer[9], t[0], Length(t));
  ShowMessage(StringOf(t));
end;

and provide the message text you get on an attempt to connect to the Oracle server.

[Daniele Buttarelli]

The message is:
4(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=22105))

Best regards.
Daniele Buttarelli

[Daniele Buttarelli]

I tried to connect to another PC with Oracle9i but the same error occurs.

[Daniele Buttarelli]

Do you have any ideas?
Thanks.
Best regards.

[AlexP]

Hello,

This error can occur due to the fact that, when connecting to Oracle, the application, at first, connects to the listener, then it is redirected to a port opened by Oracle for a new connection - and the application attempts to connect via a new port and host avoiding SSH client. To solve the problem, you can disable port redirection and work on the same port with listener. For this, disable the USE_SHARED_SOCKET option on the Oracle server.
In future, we will consider the possibility to add an event on redirection for editing values of SSH client.

[Daniele Buttarelli]

I solved by setting USE_SHARED_SOCKET=TRUE in Windows Registry.
Now I have another problem.
I run a stored procedure, via ssh, which should take about 1 minute.
After a few seconds, however, Oracle returns error ORA-03113 (end-of-file on communication channel).
It seems to be a timeout problem.

SSHServer Timeout Settings:
ClientAliveCountMax=3
ClientAliveInterval=0
MaxStartups=20
ServerTimeout=15

SSHClient Timeout Settings:
Timeout=15
ServerAliveCountMax=3
ServerAliveInterval=0
TCPKeepAlive=True

SSHChannel Timeout Settings:
Timeout=15

How can I solve this problem?
Thanks.
Best regards.
Daniele

[AlexP]

Hello,

Please check messages from the server on execution of your stored procedure using the OnDataToClient event, as it was described above.

[Daniele Buttarelli]

I tried but nothing is displayed.
Some message dialogs (showmessage) appear but are empty.

[Dimon]

Please specify the exact version of SSH server you are using.

Do you run your application on the same machine where SSH server is running, or on another one?

[Daniele Buttarelli]

My SSH Server is implemented with Delphi XE2 Update4 and SecureBridge 6.1.2.
SSH Server is running on my customer's server, on the same machine where is Oracle.
I'm trying to connect to Oracle, remotely, via a SSH client application implemented using Delphi XE4 Update1, SecureBridge 6.1.2 and ODAC 9.1.4.
This client application is running on my IPAD.

[Daniele Buttarelli]

I noticed that the session in oracle has the STATUS field set to INACTIVE.
Why?
I found it with this query:
SELECT * FROM v$session WHERE PROGRAM = 'ODAC Net'

If I have a TOraQuery with FetchAll = False, during the fetch of the record, the session remains INACTIVE.
why?

[Dimon]

We could not reproduce the problem.
The ACTIVE value is set into the v$session.STATUS field during an SQL statement execution only. In case there is the record in the v$session view, the connection with the client application may exist, even in case the v$session.STATUS value is INACTIVE.