Solved

Oracle Client hangs when running a long pl/sql procedure

Posted on 2008-10-10
5
2,077 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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…

791 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