ORA-00018: maximum number of sessions exceeded

Again, while running my charbench client against the swingbench Order Entry schema I got the following error message:

ORA-00018: maximum number of sessions exceeded

On googling I found an excellent thread which explains how connections, sessions, and processes are related in Oracle.
http://asktom.oracle.com/pls/asktom/f?p=100:11:413703333046318::::P11_QUESTION_ID:5671284058977

Here is what I did to find out my current value of session, and then increase its value.

SQL> show parameter sessions; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ java_max_sessionspace_size integer 0 java_soft_sessionspace_limit integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 sessions integer 400 shared_server_sessions integer SQL> alter system set sessions=800 scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 3540881408 bytes Fixed Size 2211864 bytes Variable Size 1644171240 bytes Database Buffers 1879048192 bytes Redo Buffers 15450112 bytes Database mounted. Database opened. SQL> show parameter sessions; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ java_max_sessionspace_size integer 0 java_soft_sessionspace_limit integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 sessions integer 800 shared_server_sessions integer SQL>

ORA-12516, TNS:listener could not find available handler with matching protocol stack

My charbench client was running fine with 100 users against an Oracle 11g R2 database created using swingbench, but when I tried to run the client with 200 users I got the following error message:

Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error: ORA-12516, TNS:listener could not find available handler with matching protocol stack

On checking to see the vaule of the process for the database I noticed that it was set to 150.

SQL> show parameter processes; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 0 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 150 SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/testasm/spfiletestasm.or a SQL> alter system set PROCESSES=250 scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 3540881408 bytes Fixed Size 2211864 bytes Variable Size 1644171240 bytes Database Buffers 1879048192 bytes Redo Buffers 15450112 bytes Database mounted. Database opened. SQL> show parameter processes; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 0 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 250 SQL>

The charbench client proceeded file with 200 users after the above change to the PROCESSES parameter.

ORA-09817: Write to audit file failed.

While trying to connect to our 11gR2 Oracle database this morning I got the
ORA-09817: Write to audit file failed. error message.

bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 14 10:29:19 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. ERROR: ORA-09817: Write to audit file failed. IBM AIX RISC System/6000 Error: 28: No space left on device Additional information: 12 ORA-09945: Unable to initialize the audit trail file IBM AIX RISC System/6000 Error: 28: No space left on device Enter user-name:

On doing a "df -m" at I noticed that the / had run out of space. It's on / that I had our ORACLE_HOME, so I seemed that that might have caused the problem.

$ df -m Filesystem MB blocks Free %Used Iused %Iused Mounted on /dev/hd4 25600.00 0.00 100% 70464 95% /

I increased the size of / from 25G to 50G by doing the following:

isvp14_ora> chfs -a size=50G / Filesystem size changed to 104857600 isvp14_ora> df -m Filesystem MB blocks Free %Used Iused %Iused Mounted on /dev/hd4 51200.00 25596.05 51% 70465 2% /

Now, I tried to connect to the database again, and everything worked fine.

bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 14 10:34:32 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL>