Friday, February 07, 2014

ORA-12500

I had ORA-12500 errors each time I tried to connect to a database on a particular server:
 
C:\>sqlplus system@livsost1
 
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 7 11:05:18 2014
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Enter password:
ERROR:
ORA-12500: TNS:listener failed to start a dedicated server process
 
Enter user-name: system@ebesost1
Enter password:
ERROR:
ORA-12500: TNS:listener failed to start a dedicated server process
 
Enter user-name:
 
Logging in to the server as the oracle UNIX user gave a clue to the cause of the problem:
 
login as: oracle
oracle@sge-mktred-dev1's password:
Last login: Thu Feb  6 09:13:51 GMT 2014 from 10.80.14.248
Compaq Tru64 UNIX V5.1B (Rev. 2650); Tue Jun 13 17:14:04 BST 2006
No mail.
task_create() failed for pid 479503: max_proc_per_user (=1024) exceeded for uid 200.
task_create() failed for pid 479503: max_proc_per_user (=1024) exceeded for uid 200.
task_create() failed for pid 479503: max_proc_per_user (=1024) exceeded for uid 200.
task_create() failed for pid 479503: max_proc_per_user (=1024) exceeded for uid 200.
 
I logged in as root and saw several defunct processes all associated with process 431167:
 
root > ps -ef|grep oracle|grep defunct|grep 431167|more
oracle         2311     431167  0.0 -        ??           0:00.00 <defunct>
oracle         8250     431167  0.0 -        ??           0:00.00 <defunct>
oracle         9459     431167  0.0 -        ??           0:00.00 <defunct>
oracle        11819     431167  0.0 -        ??           0:00.00 <defunct>
oracle        12526     431167  0.0 -        ??           0:00.00 <defunct>
oracle        12814     431167  0.0 -        ??           0:00.00 <defunct>
oracle        13421     431167  0.0 -        ??           0:00.00 <defunct>
etc
 
So I counted them:
 
root > ps -ef|grep oracle|grep defunct|grep 431167|wc -l
       427
root >
 
Then I found out where they came from:
 
root > ps -ef|grep 'oracle       431167'
oracle       431167     210307  0.0   Feb 01 ??        1-07:14:58 /oracle/app/oracle/product/grid/agent10g/bin/emagent
root >
 
I decided to kill the process. It’s just the Oracle Enterprise Manager agent so I can start it again afterwards:
 
root > kill 431167
 
… and the problem went away:
 
C:\>sqlplus system@livsost1
 
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 7 11:36:28 2014
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Enter password:
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
 
SQL>

No comments: