Program error combined with Oracle error

[Jakes Louw]

Greeting all forum addicts!

I've got a cute problem here: a batch program is falling over, with the following message:

"Feb 13 06:40:06 tcenh046 Operational[18073]: (log:ARINATBDAY_ATBDAY_84_TSA200402
13) Fatal Error occured in file /tsahome/tsa/cc/cctsa/bb/opign/v04_1n/os/src/OPS
YS_GenRtn.c, in function Opsys_runUnixCommand, line 1085 : Command sh -c "arat
b_day_pr ARINATBDAY ATBDAY_84 TSA 3" can not be executed."

The program mentioned (ARINATBDAY) has been running well for a long time.

After scrutinizing the job logs more carefully, I found this:

"ORA-12223: TNS:internal limit restriction exceeded"

and at this point it would appear as if the connection to the Oracle database fails.

Metalink says we should check kernel params and swap-space. After analysis of dmesg, syslog, and perfview, I can safely say we have NOT exceeded any limits.

What I do know, is that over 100 instances of this program were started almost simultaneously with various run parameters.

I also saw a very short peak in the memory queue (7.51), priority queue (5.46), IPC subsystem queue (248), and sleep queue (3534), all within the same 5 minute snapshot.
Syscalls jumped to 556000. But the run queue was stable at 1.3, there was no paging or swapping, and memory usage peaked at 36%. Swap use was minimal at 18%.
A strange stat was that alive processes jumped from 935 to 3606 at the time of the error, and a huge jump in the request for virtual memory (swap).

Analysis of Oracle using Precise shows no problems with init*.ora limits.

Now my question: is there a practical limit to the number of concurrent, simaltaneous job requests that the scheduler can handle?
Is there also a limit when it comes to other resource allocation, like RSS/VSS, semaphores, queues, and other what-nots that are requested in a very shoer period?
Now my question is

[Steve Steel]

ORA-12223: TNS:internal limit restriction exceeded
Cause: Too many TNS connections open simultaneously.

Action: Wait for connections to close and re-try.

see

http://www.dbaclick.com/forums/archive/11/3118.html

Maybe maxuprc in the kernel has run out.

All these things are user oracle

May be errors in syslog.log


Steve STeel

[Tim Sanko]

There is another possibility that if maxuprcs hasn't run out, you may have run into either a maxssiz, maxdsiz or maxmsiz or shmseg,shmmni. These might have been exceeded, also. but The message I got with maxuprcs being exceeded was "unable to fork..."

Tim

[Jakes Louw]

Steve:

Yes, I'm aware what the Oracle error description is. Analysis indicates that they had not exceeded their max number of processes as defined by init.ora. And as I said, there is nothing in syslog or dmesg, which would clearly show if maxuprc or nproc was exceeded.
Tim: stack size limitations would be prevalent for EVERY call to this program with a specific set of params. In this case, the jobs ran perfectly after a short wait. That implies that Steve is partially correct, but I'm not picking up the specific internal limit that is being exceeded: it's definitely not one of the published limits that I can see.

[Steve Steel]

Hi

Getting An ORA-12223 When Trying To Connect GSX:1014739.6
ORA-12223 Using SQL*NET V2 GSX:1008370.6

Are logged on oracle references

look also at

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=92217

Steve Steel

[Jakes Louw]

Hi Steve

As you can see, that ITRC thread was also unresolved....

I'll check the Oracle references.

[Hein van den Heuvel]

> Yes, I'm aware what the Oracle error description is.
> Analysis indicates that they had not exceeded their max number of processes as defined by init.ora.

If Oracle indicates that an internal limit was exceeded one has to assume it was right.
How did you proof otherwise? Through V$RESOURCE_LIMIT (of course after the proble, before restart? Some connection logging? Alert.log? Please help us understand hwo you can write this of.

What is the relation between Unix limits like nproc and sockets versus Oracle limits? IMHO, the Oracle limits shoudl trigger first for the most concise error messages.

Sounds like the application 'has gone wild'.
Is it issueing forks and got carried away?
Is it tied to end users (that rarely jump in usage numbers) or tight to an automated queue / process that perhpas locked up for a while and got released?


Here is a tiny query the I use for a quick v$resource_limit check in readable/narrow form:


column NAME format A25
column INIT format A10
column LIMIT format A10
SELECT RESOURCE_NAME "Name", CURRENT_UTILIZATION "Current",
MAX_UTILIZATION "Max", INITIAL_ALLOCATION "Init", LIMIT_VALUE "Limit"
FROM V$RESOURCE_LIMIT;


SQL> @resources.sql

Name Current Max Init Limit
------------------------- ---------- ---------- ---------- ----------
processes 10 11 40 40
sessions 10 12 60 60
enqueue_locks 26 31 810 810
:



Groetjes,
Hein.

[Jakes Louw]

Vielen dank, Hein.

Unfortunately, the DB was bounced yesterday due to an unrelated problem (some 8.0.6 bug that Oracle says will remain a bug due to the termination of support......:-( )

But I'll keep the query for later use.....