Oracle forms disconnects from server when idle

We are running an Oracle Application on Forms 6 (win XP) and Oracle 8.
The problem is that when the user leaves the formsruntime for a while (10 minutes or so) he gets an error: connection to database lost.
Then he re-opens the forms and work on it.
It is also happening when the machines locks.

This is not on all our clients, just few but is annoying.
What could be the problem?
LVL 14
Ehab SalemIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

schwertnerCommented:
Using Forms server or connected directly?
Seems there is a timeout parameter.
I have met this with Oracle forms
using Forms server.
0
slightwv (䄆 Netminder) Commented:
I'm not a forms person so I can't speak to specific forms related timeouts.

What's different about those few annoying clients?  I'm thinking Firewall timeout for inactive ports between those few clients.

0
schwertnerCommented:
Slights idea about firewalls sounds realistic!
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ehab SalemIT ManagerAuthor Commented:
Actually we have 2 problems:
1 computer is connected directly thru a LAN, this one closes the forms always when computer locks (screen saver pw protection). Once the computer is unlocked, forms close and give the windows error screen.
We also computers connected directly but thru a WAN (remote location w VPN), and there is a PIX 506e in the traffic, all these computers suffer from the error "connection to the database lost" after being idle for some time and has no relation to computer lock, If the user leaves the form open for some time and is doing some other work on the computer).
Could that be the reason for remote users? I never thought of that!
0
schwertnerCommented:
When you loose connection to the database Forms will close.
The user needs new connection.
There was a timeout parameter in the registry that closes the connection after some idle time.
0
slightwv (䄆 Netminder) Commented:
I'll leave the forms expertise to schwertner but I'd definatly get with the network folks about your VPN connections.  I ran into the timeout problem on our internal network when using SQL*Plus.  Our network staff set up an idle disconnection of 2 hours.  My workstation went through a firewall to get to the DB server.  I would get dropped all the time.

For large DML operations, the solution was to remote desktop into the DB server.  This way my SQL*Plus window was running local to the server.

Not sure about the LAN computer.  The screen saver shouldn't interfere with Oracle.
0
schwertnerCommented:
Surprisingly often the change of the Network Card and the cable helps.

This shows the statistics on Experts Exchange.
0
Ehab SalemIT ManagerAuthor Commented:
This is not one computer, these are 5 PCs on the remote site having the same problem.
0
schwertnerCommented:
This is VPN and network problem.
Either lost connection due bad infrastructure and/or timeout in the network infrastructure.
0
Ehab SalemIT ManagerAuthor Commented:
Infrastructure is good as other services are working fine (mail, VoIP, FAXoIP, Intranet).
If it is related to a timeout, what should I check? Is there any setting for timeout in the PIX506?
0
slightwv (䄆 Netminder) Commented:
I'm not 100% sure how VOIP and FAXoIP work but I doubt they need to sustain idle connections for long.  My guess is they open a pipe, use it constantly then close it when finished.

Oracle doesn't do this.  Once you create a connection it creates the pipe.  If the user doesn't do anything in the forms, the connection sits idle.

I'm not a Network person but I do know that you can set the idle timeout in the Firewall.  I had to have our network guys do that for me.  Get with your network folks.  They should be able to help you.

I think there is an easy way to help test this, see if you can get a connection exactly like your remote clients.  Then from a SQL*Plus window do the following:
begin
   exec dbms_lock.sleep(600);
   dbms_output.put_line('all done');
end;
/

This should start a transaction at the database and shouldn't generate any IP traffic between the client and server.  See if you see the output: all done.  If not, your connection has been timed out.
0
Ehab SalemIT ManagerAuthor Commented:
Should I test this command in SQLplus or in Forms?
In SQL it is given error.
0
schwertnerCommented:
I agree with slightwv!
He is correct.

Additionally you have to check what Oracle user/schema the
reports use. Are they same or not?
Please kindly take in account that Oracle users can have profiles that limited the idle connection time and simply terminate the session.
0
schwertnerCommented:
Possibly this doesn't work in Oracle 8/8i, I am not sure.

Please tell us if you use Forms Server (3 tier) or use the old good Client/Server architecture.
0
slightwv (䄆 Netminder) Commented:
>>In SQL it is given error.

My little sleep stub?  This is from SQL*Plus.  My guess is you need to grant execute on DBMS_LOCK to the user.  If this isn't it:  What is the error?

I'm thinking it's not a profile problem.  I would hope the remote users don't have a different profile from the local users.  If they do, schwertner may be correct.
0
Ehab SalemIT ManagerAuthor Commented:
old client server
0
Ehab SalemIT ManagerAuthor Commented:
sqlplus 8.
I granted dbms_lock to the user but still getting the error:
    exec dbms_lock.sleep(600);
         *
ERROR at line 2:
ORA-06550: line 2, column 10:
PLS-00103: Encountered the symbol "DBMS_LOCK" when expecting one of the following:
:= . ( @ % ;
0
schwertnerCommented:
begin
   execute dbms_lock.sleep(600);
   dbms_output.put_line('all done');
end;
/
0
Ehab SalemIT ManagerAuthor Commented:
 1  begin
  2     execute dbms_lock.sleep(600);
  3     dbms_output.put_line('all done');
  4* end;
SQL> /
   execute dbms_lock.sleep(600);
           *
ERROR at line 2:
ORA-06550: line 2, column 12:
PLS-00103: Encountered the symbol "DBMS_LOCK" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "DBMS_LOCK" to continue.
0
schwertnerCommented:
This will create the package:

{ORACLE_HOME}/rdbms/admin/dbmslock.sql

run it so:


execute dbms_lock.sleep(600.0);
0
slightwv (䄆 Netminder) Commented:
I apologize and am claiming a blonde moment here.  I typed it in w/o testing myself and I know better than to trust my memory. ]

There's no need for exec in PL/SQL.

Try this.  Note: to locate the timeout you may need to play with the sleep time.  It's in seconds.
--------------
begin
   dbms_lock.sleep(600);
   dbms_output.put_line('all done');
end;
/

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ehab SalemIT ManagerAuthor Commented:
Yes, this is working on the LAN, I will test it on the WAN today and let you know.
0
Mark GeerlingsDatabase AdministratorCommented:
I just noticed this posting today, and I'm responsding partly because we have a similar problem of disconnects with some (but not all) of our Windows client/server Form6i clients connecting to Oracle.  Our problem though only started after we moved our database from Oracle9 on a Windows server to Oracle10.1 on a Linux server.  When we had an Oracle8 database, then later an Oracle9 database on Windows servers (both with the same Forms6i clients that we still use) we never noticed this problem with timeouts.

I will be out on vacation for the next 10 days, but if anyone has any good ideas about this, I hope to find out about them when I get back.
0
schwertnerCommented:
The configuration in this case is
Forms 6 (win XP) and Oracle 8.
I am not sure that Forms 6i is certified to work with Windows XP,
but definitelly it works good with Windows 2000.

Also try to change the network card - may be this can help.
Also may be the hub or the switch these computer are connected
is not good ... enough.
0
Ehab SalemIT ManagerAuthor Commented:
slightwv: Thank you for your SQL.
It ran ok on the remote machine, so I started to increase the time. When dbms_lock.sleep(time) reached 3000, the query failed. So it is a timeout propably in the PIX, because in the LAN, I increased to 6000 and did not disconnect.
Can anyone help how to fix this from the PIX?
0
slightwv (䄆 Netminder) Commented:
Sorry.  I'm not a network person.  You'll need to get with your network folks and negotiate the timeout.  I say negotiate because they will NEVER let it go unlimited.

I'm also not a forms guy so I don't know if this is possible or not but I wanted to throw it out:

If you can have Forms perform an autonomous task without user intervention it should keep the pipe alive.  Something like pulling the sysdate from the DB server every 5 minutes.  This way if the users don't touch any of the forms, at least data is being sent back and forth every 5 minutes.
0
Ehab SalemIT ManagerAuthor Commented:
I am 100% sure this is a PIX issue. I will close the case and repost in the security section a specific question about PIX timeouts.

Thank you for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.