Skip to Main Content
  • Questions

  • How to prevent some firewall from terminating SQL*Plus sessions with long-running statements?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ralf.

Asked: November 06, 2018 - 4:07 pm UTC

Last updated: November 26, 2018 - 9:51 am UTC

Version: 12.2.0.1

Viewed 10K+ times! This question is

You Asked

Hi Tom,

In know that I should talk to the firewall admins to get things fixed but this is - unfortunately - out of scope.

So here we go: We have a database running on some Linux server and some client application running on some W2k16 server. The machines are on separate subnets and between the subnets there is some firewall. The firewall is configured in a way that "idle connections" are terminated after, say, 15 minutes. Idle here refers to a connection in SQL*Plus that either is not used to do anything or to some connection in SQL*Plus that runs an UPDATE on some big table on the server and has no bits to transfer between server and client until the UPDATE is finished.

I talk about tables with several 100 million rows that are altered by 1st adding columns and 2nd updating the new columns to contain NOT NULL data and 3rd modifying the table and set the new columns to NOT NULL. There might be other long-running statements to be executed like creating flat tables from huge partitioned tables (2 billion rows), and so on.
All of this is part of a transition from some old data model to a new data model while updating the third party application.

So the obvious question is:
Is there some way to keep the connection busy while the UPDATE is executing such that the firewall does not classify it as an idle connection and terminates it?

I have been reading about dead connection detection and I did set SQLNET.EXPIRE_TIME = 10 on the server. But this does not help since the server-side of my connection is busy with the UPDATE and will not be able to send probes to the client.

I currently see only these alternatives to solve the problem:

1. Copy the SQL script from the client-side to the server and execute it there. This is not
supported by the vendor of the application and it is a bit cumbersome since that statement
is created on the fly and contains a couple of thousand lines.

2. Instead of creating the SQL file and executing it create a task using DBMS_SCHEDULER and
have the task execute that script. The processing would happen on server-side so no
firewall would be involved.

3. Chop up the work into smaller pieces that are finished before the firewall terminates the
connection. This would be a real pain so I forget about it immediately.

So beside moving the work from the remote machine to the database server directly is there some way to trick the firewall into considering the connection being alive for however long some long-running statement might run?

Best regards,
Ralf



and Connor said...

Yeah I'm not sure sqlnet.expire_time is going to help you here. As per Doc ID 257650.1

"PLEASE NOTE:
DCD was never designed to be used as a "virtual traffic generator" as we are wanting to use it for here.
In fact, some later firewalls and updated firewall firmware may not see DCD packets as a valid traffic possibly because the packets that DCD sends are actually empty packets. Therefore, DCD may not work as expected and the firewall / switch may still terminate TCP sockets that are idle for the period monitored, even when DCD is enabled and working.
In such cases, the firewall timeout should be increased or users should not leave the application idle for longer than the idle time out configured on the firewall."

One thing to try out (although I'm pessimistic) is that maybe your firewall is simply checking *client* existence as opposed to particular session. So perhaps an additional SQLPlus session on the same client that is just doing "select * from dual" every 30 seconds would be enough to "fool" your firewall.

If you can't do that, then your option 2 looks like a reasonable way forward.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Another alternatice exists and seems to work

Ralf, November 07, 2018 - 7:02 pm UTC

Hi Connor,

Thanks for your answer and your time spent on the issue.
I was pointed to some other alternative: Tweaking Linux kernel parameters to enforce TCP keep alive on OS level:

Issuing
# echo 600 > /proc/sys/net/ipv4/tcp_keepalive_time
# echo 60 > /proc/sys/net/ipv4/tcp_keepalive_intvl
# echo 20 > /proc/sys/net/ipv4/tcp_keepalive_probes

enables keep alive on all new IPv4 TCP connections. This was enough to trick the firewall in our case.
The changes can be persisted by adding corresponding entries to /etc/sysctl.d scripts.

Hope this helps other folks who fight with their IT security departments over firewall policies. :-)

Best regards,
Ralf
Connor McDonald
November 08, 2018 - 12:37 am UTC

Thanks for coming back to us with the info. Useful for others.

on IDLE status

Rajeshwaran Jeyabal, November 12, 2018 - 11:36 am UTC

Team,

Idle here refers to a connection in SQL*Plus that either is not used to do anything or to some connection in SQL*Plus that runs an UPDATE on some big table on the server and has no bits to transfer between server and client until the UPDATE is finished.

Having an big update going on, then the connection would be waiting for an event to complete - perhaps db file scattered read/direct path read/direct path write/log file switch etc - in that case will that be in IDLE status?

Here is a test case for that.

before the update starts teh session is 'inactive' status.

demo@ORA12C> select sid,status
  2  from v$session
  3  where sid = 20 ;

       SID STATUS
---------- --------
        20 INACTIVE

while the update in progress, it is active.

demo@ORA12C> @sid

SESSION_ID
----------
        20

demo@ORA12C> set timing on
demo@ORA12C> update big_table set owner = lower(owner);


montioring from another session shows this.

demo@ORA12C> select sid,status,event
  2  from v$session
  3  where sid = 20 ;

       SID STATUS   EVENT
---------- -------- -------------------------------
        20 ACTIVE   db file scattered read

demo@ORA12C>

Connor McDonald
November 26, 2018 - 9:51 am UTC

The status is active from the database point of view. I think our poster is claiming that the firewall software layer is interpreting such sessions as idle.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database