?
Solved

Oracle session termination

Posted on 2010-11-15
4
Medium Priority
?
1,070 Views
Last Modified: 2012-05-10
Hello experts,
I need your help to fix one issue.  I’m using SQL*plus 10.2.0.1.0 as oracle client and Oracle 10.2.0.3.0 as a database. In our organization, we have a separate LINUX box (AEDB01) for batch jobs and which will connect database using TNS name.  
I have a one process which invokes one procedure. Basically procedure contains 3 MERGE statements to insert/update data in 3 target tables based on staging table.  We have seen a couple of instances of the process running from AEDB01 have run for a long time due to the size of the uploads and, after a while, Oracle would report the session as TERMINATED. There was no indication in the job logs or in the database logs (alert.log) that this is a termination instigated by the database such as a transaction rollback or some error in the code. The database session was eventually cleaned up by Oracle but remained on AEDB01 as a shell script, so I had to clean that up manually.
FYI, I had invoked job using NOHUP command in AEDB01 box even though the oracle session will terminate after a while.
I’m suspecting that this is caused by some network timeout parameter which is killing the session after a period of time. I would like to know what parameter do I need to setup to in order to session alive until job completes.  
Any help greatly appreciated.

0
Comment
Question by:KuldeepReddy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 7

Expert Comment

by:jocave
ID: 34142335
What was the error returned to the batch job?

What profile associated with the Oracle user (SELECT profile FROM dba_users WHERE username = <<user name>>)?  What resource limits are defined for that profile (SELECT resource_name, limit FROM dba_profiles WHERE profile = <<profile name from dba_users>> AND resource = 'KERNEL' AND LIMIT != 'UNLIMITED')

Oracle cannot time out a session.  It is possible that Oracle has been configured to kill sessions that use too many resources (i.e. via a profile).  It is also possible that some network component is causing the timeout (firewalls are a popular source of unexpected timeouts).  If you do have a network component that is timing out the session, it is unlikely that any Oracle configuration change could resolve the problem.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 34158305
You are using NOHUP, so possibly the listener is asked to send probes to the client, but it doesn't answer. thisl eads to closing of the session. This is controlled via SQLNET.EXPIRED_TIME parameter in SQLNET.ORA file on the server. If this parameter is not present ignore my posting.
0
 
LVL 6

Expert Comment

by:sridharv9
ID: 34177673
There is no reason Oracle will kill an active session.   If the addition of the SQLNET.EXPIRED_TIME parameter (above comment) has no affect on the problem. Then enable client trace in the client side sqlnet.ora and server trace in the server sqlnet.ora file. Connect using the problem application to produce the error condition.

To enable client side trace:

Add the following to the client sqlnet.ora file

Trace_level_client=16
Trace_directory_client=<path_to_the_trace_directory> # use the full path to the trace directory
Trace_unique_client=on
Trace_timestamp_client=on
Diag_adr_enabled=off

Upload the resulting trace (.trc) file or files generated in the trace directory. Remember to remove the trace parameters from the sqlnet.ora file after capturing the event.


To enable Server Trace:
add the following to the sqlnet.ora file on the server
NOTE: only enable server trace during periods of low connect activity on the server as all new connections will be traced while trace is enabled. Existing connections are not affected.

trace_level_server=16
trace_directory_server=<O_H/network/trace> # use the full path to the trace directory
trace_timestamp_server=on
Diag_adr_enabled=off

Upload the resulting trace (.trc) file or files generated in the trace directory. Remember to remove the trace parameters from the sqlnet.ora file after capturing the event.


0
 
LVL 48

Accepted Solution

by:
schwertner earned 2000 total points
ID: 34183572
Normally termination of the session is caused by firewalls due timeout setting.
Another investigation you have to do is in profile of the user. Some profiles are limiting the usage of the CPU, have time limits, etc.

But if you find SQLNET.EXPIRED_TIME parameter in SQLNET.ORA file then temporarily remove it and investigate what happens.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
A 2007 NCSA Cyber Security survey revealed that a mere 4% of the population has a full understanding of firewalls. As business owner, you should be part of that 4% that has a full understanding.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

650 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