Thursday, July 21, 2011

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>

Wednesday, July 20, 2011

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.

Thursday, July 14, 2011

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>