Solved

Oracle Client hangs when running a long pl/sql procedure

Posted on 2008-10-10
5
2,062 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
Comment Utility
Are you accessing any DB links?
If so, have you checked whether they are working fine?
0
 
LVL 1

Author Comment

by:structuredweb
Comment Utility
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
Comment Utility
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
Comment Utility
Question PAQ'd, 500 points refunded, and stored in the solution database.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to tune this sql query 61 100
Bulk insert into global temporary table 2 40
MPLS Network Question 2 31
HSRP needed? 4 25
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 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.

772 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

11 Experts available now in Live!

Get 1:1 Help Now