Solved

Oracle Client hangs when running a long pl/sql procedure

Posted on 2008-10-10
5
2,067 Views
Last Modified: 2012-05-05
We have a problem running a long pl/sql procedure on our UPGRADED 11.1.0.6 database on solaris 9 64bit machine. ( it was upgraded from 9, which the same logic run properly on ).
the client side just hangs and waits for a return signal from the database, although it seems like the database logic run and ended, after a little more then an hour.

the call is a simple call for a procedure :
newimport.processstagingtodestination(parentidin => :parentidin,
                                                   publishertoppccidin => :publishertoppccidin,
                                                   subparentidin => :subparentidin,
                                                   subcategoryname => :subcategoryname,
                                                   companygroupidin => :companygroupidin,
                                                   vendorsyncidprefixin => :vendorsyncidprefixin,
                                                   importidin => :importidin,
                                                   instanceidin => :instanceidin)

The database alert log shows the following :

Fatal NI connect error 12170.

  VERSION INFORMATION:
    TNS for Solaris: Version 11.1.0.6.0 - Production
    Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.1.0.6.0 - Production
    TCP/IP NT Protocol Adapter for Solaris: Version 11.1.0.6.0 - Production
  Time: 10-OCT-2008 13:29:19
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 145
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xx.xx.xx.115)(PORT=3185))

the host ID is the host that ran the process.
there is no other error/ORA message on the database trace files.

the sqlnet.ora file looked like that :

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

and we tried to add/change the following parameters without any success :
NAMES.CONNECT_TIMEOUT=0  ( also 30 did not work)
SQLNET.EXPIRE_TIME=1 ( also 10 and 0 did not work )
TCP.NODELAY=yes

the listener.ora file looked like that :
LISTENER11 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = XX)(PORT = 1523))
    )
  )

SID_LIST_LISTENER11 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = UPGTEST)
      (ORACLE_HOME = /u5/app/oracle/product/11.1.0)
      (SID_NAME = UPGTEST)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = FPROD1)
      (ORACLE_HOME = /u5/app/oracle/product/11.1.0)
      (SID_NAME = FPROD1)
    )
)


we started the listener with trace level 16 :
TRACE_LEVEL_LISTENER11=16
TRACE_FILE_LISTENER11=listener11.trc
TRACE_DIRECTORY_LISTENER11=/u5/app/oracle/product/11.1.0/network/trace

and to add this parameter as well : ( with no success )
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER11=OFF

the initial call for the procedure was from the application server which used oracle client 9.
then we tried it with 10 and 11 as well ( from the application server and from the sqlplus directly ) -
again we were getting the same error.

I will mention again that the database is upgraded from 9 to 11.
we have another fresh 11g database installation on linux and we don't get those errors.
the listener is listening on port 1523 ( and not the standard 1521 port, because the old listener that still serve version 9 database is listening on 1521 port ).

from the listener trace file, the only thing that looks a little suspicious is ( but i don't know if its related ) :

2008-10-10 14:02:46.831771 : nsnainit:"or" info flags: 0xd  Translations follow:
    native service(s) is (are) wanted
    NA disabled remotely for this connection
    NA services unavailable on both processes - negotiation not needed

2008-10-10 14:02:46.831805 : nsnainit:"or" info flags: 0xd  Translations follow:
    native service(s) is (are) wanted
    NA disabled remotely for this connection
    NA services unavailable on both processes - negotiation not needed
"and" info flags: 0xd   Translations follow:
    native service(s) is (are) wanted
    NA disabled remotely for this connection
    NA services unavailable on both processes - negotiation not needed

if anyone encountered this kind of behavior, or have a thought on how to find the problem, it will be highly appreciated.
0
Comment
Question by:structuredweb
  • 2
5 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 22700284
Are you accessing any DB links?
If so, have you checked whether they are working fine?
0
 
LVL 1

Author Comment

by:structuredweb
ID: 22702842
Hi sujith, thanks for the comment.
we found the  parameter that solves the issue....

In sqlnet.ora file we defined SQLNET.EXPIRE_TIME=1 which solve the disconnections.
The firewall probably disconnected "hanged" sessions, and although we tried this parameter with value of 10 and 0, it did not solve the problem till we changed it to 1.

I will be happy if someone can provide explanation for that, just for every ones knowledge.

Thanks.
0
 
LVL 1

Author Comment

by:structuredweb
ID: 22706670
The parameter that we are talking about above (EXPIRE_TIME in the sqlnet.ora file) was not defined for our oracle 9i version ( which mean the default was suppose to be 0 ). this should also work, but it didn't.

if someone can provide an explanation for this behavior, cause it seems that there is a change of behavior between the Oracle 9i and 11g enterprise database.

We are about to upgrade the production, but do not want to take any risk with this issue.

we have a firewall configured to disconnect TCP connection after 30 minutes of idle time.

Thanks
0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 23269153
Question PAQ'd, 500 points refunded, and stored in the solution database.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Netgear switch to Cisco switch VLAN not passing traffic 8 33
history tablespace temp usage 2 31
policy routing to fw2 18 51
Web Service from a stored procdure oracle 10 49
Please see preceding article here: http://www.experts-exchange.com/Networking/Operating_Systems/A_11209-Root-Bridge-Election.html Figure 1 After Root Bridge has been elected, then what?..... Let's start by defining a Root Port in la…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Viewers will learn how to properly install and use Secure Shell (SSH) to work on projects or homework remotely. Download Secure Shell: Follow basic installation instructions: Open Secure Shell and use "Quick Connect" to enter credentials includi…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

911 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

20 Experts available now in Live!

Get 1:1 Help Now