Adventures With Oracle: ORA-12519 and ORA-01012 Errors

I received a call today from one of the developer groups that I support. They could not make any new connections to oracle (oracle 10g). Existing connections seemed to be working OK.

I tried sqlplus to the server from a remote box and got:

ORA-12519: TNS:no appropriate service handler found

I then checked listener with:

lsnrctl status

There was no problem with listener. Just for kicks, I restarted the listener with:

lsnrctl reload

At this point, I ran sqlplus from the server, thus bypassing the listener. I was greeted with:

ORA-01012: not logged in

Weird. At this point I called the users and had one of them log out. I quickly ran sqlplus and was able to get in to the server (rlogin server -l oracle ; sqlplus / as sysdba). I ran this query to see what was going on:

select username, sid, serial# from v$session

There were a large number of connections from one particular user (a developer, testing code on the production box...). I was able to kill the offending connections one by one:

alter system kill session 'SID, SERIAL#';
IE: alter system kill session '62,19625'; getting sid and serial# from the v$session select, above

It turns out that there's a server parameter called 'processes' that limits the number of connections, and the server had run out of connections. (apparently it would be too easy for oracle to print a HELPFUL error message like 'ORA-12345 Out of connections: kill some connections or increase the 'processes' parameter). You can see the current setting by running:

show parameter processes
------------------------------------ ----------- ------------------------------
aq_tm_processes   integer 0
db_writer_processes   integer 2
gcs_server_processes   integer 0
job_queue_processes   integer 10
log_archive_max_processes   integer 2
processes   integer 150

You might also be able to see this in the init.ora:
processes = 50
/* note: this probably means we're using an spfile, not a pfile (?) */

I later realized that I could have looked in the error logs on the server. You can find the location of your log files (assuming you can get in to your server) by doing the following:

sqlplus / as sysdba
show parameter 'background_dump_dest'

------------------------------------ ----------- ------------------------------
background_dump_dest string /opt/oracle/admin/_your_sid_name_/bdump
cd /opt/oracle/admin/_your_sid_name_/bdump

look at most recent .trc file (ls -altr *.trc):

Oracle Database 10g Enterprise Edition Release - 64bit
With the Partitioning, OLAP and Data Mining Scoring Engine options
ORACLE_HOME = /opt/oracle/product/db
System name: SunOS
Node name: ora-server1
Release: 5.10
Version: Generic_118822-27
Machine: sun4u
Instance name: ORAPROD
Redo thread mounted by this instance: 1
Oracle process number: 0
Unix process pid: 15216, image: oracle@ora-server1

Died during process startup with error 20 (seq=85701)
OPIRIP: Uncaught error 20. Error stack:
ORA-00020: maximum number of processes (150) exceeded