ORA-273xx & ORA-04030 Errors

One of our client is facing the errors for ORA-273xx and ORA-04030 repeatedly in his alert/trace logs. After  so many errors instance is crashed and nothing is logged for this crash. This situation is not consistent, some times it  happens otherwise  everything goes smooth. I had been sent to visit the client side for investigations .
After visit i wrote a small writeup for the problems, cause and solutions which is as under. After reading writeup and the test cases you will able to create the scenarios for these errors.

Relevant Facts:
OS ->Windows Server 2003 Version V5.2 Service Pack 2 (32bit)
DB-> ORACLE V10.2.0.1.0
IIS is also running on the same DB Server

Errors reported:
There are various errors  being reported in alert and trace logs like below.

Process q000 died, see its trace file
ksvcreate: Process(q000) creation failed
Process startup failed, error stack:
ORA-27300: OS system dependent operation:spcdr:9261:4200 failed with status: 997
ORA-27301: OS failure message: Overlapped I/O operation is in progress.
ORA-27302: failure occurred at: skgpspawn
ORA-04030: out of process memory when trying to allocate 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)

Cause Determined:
OS resource issue - After careful study/analysis from the logs it seems that problem pertains with the OS not directly related with database it self.
The maximum amount of addressable memory by a process (eg; oracle.exe) running on 32bit Windows has been reached. In a 32bit Windows environment, total addressable memory by a single process is 4G. The OS reserves half of this memory by default for kernal memory, leaving 2G of addressable memory for a user process. When any process reaches to this limit it crashes/fails.

Test Cases:
I developed few test cases to verify the cause and it proved that problems are occurring due to the OS limits.
Suggested Solutions:
There are various options you can use according to your environment/needs

• add the '/3GB' switch to the boot.ini file at the OS level. It will change the ratio of kernel memory and user memory. Now processes (eg; oracle.exe) can address 3G of memory out of the total 4G of addressable memory. However once the 3G memory is depleted, the  process will fail.
• Tune your application that it will not consume greater than 2.7G of memory (supposing you already have /3GB switch on)
• Reduce the amount of SGA needed to be allocated for the database
• Limit the number of dedicated connections to the database and the amount of each user process will consume.
• Change from the dedicated connections to mutli threaded server (MTS) connections.
• For ORA-04030, tune your application to use less pga or increase pga size.
• I highly recommend that you must move to Windows 64bit instead of 32bit for your DB environment

Other Suggestions:

• IIS should be installed on some other server.
• Create different profiles for different user community to restrict their sessions and resources on DB. As I've observed, users hold the resources         for indefinite time causing    to promote the chances to the oracle.exe process to become bigger un-necessarily
• Reovke DBA privs from the users who don't require it to avoid the dedicated connections un-necessary.
• Listenr.log size is bigger (more than 2G), reset the listener log, check other logs also and reset if necessary.
• Review tablespace sizes
• Arrange the Test environment for your DB

*******************************************************************************************************

Test Cases
FOR ORA-04030
I wrote the following simple plsql to demonstrate the client how out of process error occurs in his environment.
Run this anonymous block and observe the sqlplus and oracle.exe process from windows task manager
1  declare
  2   type mytable is table of CHAR(2000) index by binary_integer;
  3   mt mytable;-- := mytable(10000);
  4   CNT NUMBER := 1;
  5  begin
  6     FOR I IN 1..400000 LOOP
  7         MT(CNT) := ''||I;
  8      CNT := CNT+1;
  9     END LOOP;
 10* END;
SQL> /
declare
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu
call ,pl/sql vc2)

If the loop end limit was 300000, it was running successfuly on the client environment.
***********************************

For Other Errors
I created the following simple java stored procedure and PLSQL wrapper to open sqlplusw session on the server

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
  public static void executeCommand(String command) {
    try {
      
        final Process pr = Runtime.getRuntime().exec(command);

    }//try
    catch (Exception ioe) {
            System.out.println("Exception caught printing process output.");
            ioe.printStackTrace();
    }//catch           
 }//execcuteCommand
 
}; //class

CREATE OR REPLACE PROCEDURE host_command (p_command  IN  VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
/

Create Processes/Sessions Dynamically
The following anonymous blocks was used to create sessions on DB server dynamically from one of client machine. At this point oracle.exe process growth was observed
BEGIN
     for i in 1..50 loop 
       host_command (p_command => 'sqlplusw scott/tiger @e:\test.sql');
       dbms_lock.sleep(.5);
     end loop; 
END;
 

Note: I created a test.sql file containing some long running queries/plsqlblock, so that when a new session is started on DB Server it should start doing some activity.

Kill Sessions Dynamically
The following anonymous block kills all the sessions in DB created earlier
declare
cursor c1 is
select  * from v$session where PROGRAM ='sqlplusw.exe' and username != 'SYS';
rec v$session%rowtype;
stmt varchar2(1000);
BEGIN
     open c1;
     loop 
         fetch c1 into rec;
         exit when c1%notfound;
         stmt := 'Alter SYSTEM Kill Session '||''''||rec.sid||','||rec.serial#||''' '||'IMMEDIATE';
         dbms_output.put_line(stmt);
         execute immediate stmt;
     
     end loop; 
END;
Kill Processes on Windows
The following windows command was used to kill all the sqlplus session on the DB Server at once.
C:\Documents and Settings\oracle>taskkill /F /IM sqlplusw.exe
SUCCESS: Sent termination signal to the process "sqlplusw.exe" with PID 7092.