Re: Some problem about ora-12519 error

Havel Zhang wrote:
> hi guys:
>
> I have a problem about Oracle 10g and a patched bug 1377901.
> Our system using Oracle 10g Win64 version and running a windows
> 2003 64bit HP. When I using this system, it sometimes report ora-12519
> error, and when this error occurred, users can not connect to DB
> Server. This error occurred randomly not always. After I analyze the
> alter_log file, i found many PMON process died when ora-12519 occur.
> And, then system will re-spawn a new PMON process, as the time pass by,
> system will reached the 150 process limited. And when many users
> connect to system, this error occurred frequently.
> I googled this ora-12519 error and hope to find a way to solve this
> problem. The error message of ora-12519 is:TNS:no appropriate service
> handler found, oracle said the cause of this error is:The listener
> could not find any available service handlers that are appropriate for
> the client connection. But I found another thread said it's is a
> oracle's bug, to solve this problem just change the following line in
> tnsnames.ora file to another:
> ...
> (CONNECT_DATA =
> (SID = nsapwebt)
> (SERVER = DEDICATED)
> )
> to
> (CONNECT_DATA =
> (SERVICE_NAME = nsapwebt)
> (SERVER = DEDICATED)
> )
>
> Just replace SID with SERVICE_NAME! Really?! I'm changed right now.
> Then the problem seems solved. Now the 10g haven't report this error
> til now. It's very strange!! And this bug number is 1377901 and have
> been fixed on Oracle 8.1.7.1, but re-occured on 10g?!
> The url explain this fixed problem is:
> http://oracle-docs.dartmouth.edu/dba-docs/patchset_8.1.7.2.htm#1377901
>
> Is it true?! Have you or your friends who using 10g have been puzzled
> with this problem? Can u share with me and other persons? Can u help
> me?
>
> Thank you.
>
> Havel
>
> ----------------------------------------------------------------------
> below is alter_log file messages:
>
> Wed Apr 19 14:38:54 2006
> Process P075 died, see its trace file
> Wed Apr 19 14:39:15 2006
> Process P078 died, see its trace file
> Process P078 died, see its trace file
> Wed Apr 19 14:39:17 2006
> Process P079 died, see its trace file
> Wed Apr 19 14:39:18 2006
> Process P080 died, see its trace file
> Wed Apr 19 14:39:22 2006
> Thread 1 cannot allocate new log, sequence 6605
> Checkpoint not complete
> Current log# 3 seq# 6604 mem# 0:
> C:\ORACLE\ORADATA\NSAPPWEB\REDO\REDO21.LOG
> Current log# 3 seq# 6604 mem# 1:
> C:\ORACLE\ORADATA\NSAPPWEB\REDO\REDO22.LOG
> Current log# 3 seq# 6604 mem# 2:
> C:\ORACLE\ORADATA\NSAPPWEB\REDO\REDO23.LOG
> Thread 1 advanced to log sequence 6605
> Current log# 2 seq# 6605 mem# 0:
> D:\ORACLE\ORADATA\NSAPPWEB\REDO\REDO11.LOG
> Current log# 2 seq# 6605 mem# 1:
> D:\ORACLE\ORADATA\NSAPPWEB\REDO\REDO12.LOG
> Current log# 2 seq# 6605 mem# 2:
> D:\ORACLE\ORADATA\NSAPPWEB\REDO\REDO13.LOG
> Wed Apr 19 14:39:43 2006
> Thread 1 cannot allocate new log, sequence 6606
> Checkpoint not complete
> Current log# 2 seq# 6605 mem# 0:
> D:\ORACLE\ORADATA\NSAPPWEB\REDO\REDO11.LOG
> Current log# 2 seq# 6605 mem# 1:
> D:\ORACLE\ORADATA\NSAPPWEB\REDO\REDO12.LOG
> Current log# 2 seq# 6605 mem# 2:
> D:\ORACLE\ORADATA\NSAPPWEB\REDO\REDO13.LOG
> Thread 1 advanced to log sequence 6606
> Current log# 1 seq# 6606 mem# 0:
> E:\ORACLE\PRODUCT\10.2.0\ORADATA\NSAPPWEB\REDO01.LOG
> Current log# 1 seq# 6606 mem# 1:
> E:\ORACLE\PRODUCT\10.2.0\ORADATA\NSAPPWEB\REDO02.LOG
> Current log# 1 seq# 6606 mem# 2:
> E:\ORACLE\PRODUCT\10.2.0\ORADATA\NSAPPWEB\REDO03.LOG
> Wed Apr 19 14:39:57 2006
> Process P080 died, see its trace file
> Process P080 died, see its trace file
> Wed Apr 19 14:40:01 2006
> Process P080 died, see its trace file
> Wed Apr 19 14:40:04 2006
> Process P080 died, see its trace file
> Wed Apr 19 14:40:07 2006
> Process P081 died, see its trace file
> Wed Apr 19 14:40:11 2006
> Process P082 died, see its trace file
> Wed Apr 19 14:40:12 2006
> Process P083 died, see its trace file
> Wed Apr 19 14:40:13 2006
> Process P080 died, see its trace file
> Wed Apr 19 14:40:25 2006
> Process P080 died, see its trace file
> Wed Apr 19 14:40:26 2006
> Process P082 died, see its trace file
> Wed Apr 19 14:40:27 2006
> Process P080 died, see its trace file
> Wed Apr 19 14:40:28 2006
> Process P082 died, see its trace file
> Wed Apr 19 14:40:29 2006
> Process P083 died, see its trace file
> Wed Apr 19 14:40:30 2006
>
>

First of all, those Pnnn processes are not PMON - dead PMON inevitably leads to instance crash. Pnnn processes are Parallel Query slaves and there seem to be all too many of them (over 80!) Alert log suggests inspecting their trace files for the cause of their death (in bdump, see <sid>_Pnnn_<thread#>.trc files.) How come there are so many PQ slaves running and what their trace files say? There also seem to be a problem with redo log sizes - they seem to be too small for the load: logs were switched in just 20 seconds, obviously not enough for checkpoint to complete, so the system grinds to a halt until DBWR can finish the checkpoint and the log switch can be completed.

As of SID vs SERVICE_NAME - what you did is probably not the fix for the problem (the bug you mentioned was indeed fixed back in 8i days,) but the change is welcome anyway, because use of SID in connection descriptors was long deprecated in favor of service names.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Thu Apr 20 2006 - 13:54:33 CDT