Solved

Oracle forms disconnects from server when idle

Posted on 2007-03-19
27
2,559 Views
Last Modified: 2013-12-19
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?
0
Comment
Question by:Ehab Salem
  • 10
  • 10
  • 6
  • +1
27 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 18748468
Using Forms server or connected directly?
Seems there is a timeout parameter.
I have met this with Oracle forms
using Forms server.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18748499
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
 
LVL 47

Expert Comment

by:schwertner
ID: 18748576
Slights idea about firewalls sounds realistic!
0
 
LVL 14

Author Comment

by:Ehab Salem
ID: 18754317
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
 
LVL 47

Expert Comment

by:schwertner
ID: 18754556
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18756281
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
 
LVL 47

Expert Comment

by:schwertner
ID: 18762398
Surprisingly often the change of the Network Card and the cable helps.

This shows the statistics on Experts Exchange.
0
 
LVL 14

Author Comment

by:Ehab Salem
ID: 18787509
This is not one computer, these are 5 PCs on the remote site having the same problem.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 18791316
This is VPN and network problem.
Either lost connection due bad infrastructure and/or timeout in the network infrastructure.
0
 
LVL 14

Author Comment

by:Ehab Salem
ID: 18792250
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18792437
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
 
LVL 14

Author Comment

by:Ehab Salem
ID: 18792779
Should I test this command in SQLplus or in Forms?
In SQL it is given error.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 18793005
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
ID: 18793149
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18793901
>>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
 
LVL 14

Author Comment

by:Ehab Salem
ID: 18798140
old client server
0
 
LVL 14

Author Comment

by:Ehab Salem
ID: 18798170
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
 
LVL 47

Expert Comment

by:schwertner
ID: 18798534
begin
   execute dbms_lock.sleep(600);
   dbms_output.put_line('all done');
end;
/
0
 
LVL 14

Author Comment

by:Ehab Salem
ID: 18798553
 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
 
LVL 47

Expert Comment

by:schwertner
ID: 18798761
This will create the package:

{ORACLE_HOME}/rdbms/admin/dbmslock.sql

run it so:


execute dbms_lock.sleep(600.0);
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 18799681
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
 
LVL 14

Author Comment

by:Ehab Salem
ID: 18806203
Yes, this is working on the LAN, I will test it on the WAN today and let you know.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18818168
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
 
LVL 47

Expert Comment

by:schwertner
ID: 18822076
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
 
LVL 14

Author Comment

by:Ehab Salem
ID: 18850394
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18850567
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
 
LVL 14

Author Comment

by:Ehab Salem
ID: 18850770
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now