Solved

Oracle Client hangs when running a long pl/sql procedure

Posted on 2008-10-10
5
2,079 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IPv6 question 1 50
Error querying database link from Oracle 10g to Postgresql 7 96
SQL query for highest sequence 4 59
subtr returning incorrect value 8 63
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
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…

680 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