Greetings Tom,

I have come across the following scenario. I would appreciate if you can give a detailed explanation of what exactly is happening.

We have requests from 5 different webservers coming into a single database which uses both JDBC & ODBC.The Web connections use one common username/password to access the database. Every night we have a load process which runs against the database during which the application team doesn`t want any requests coming to the database. So what they do is lock the user by changing the password.

Sometimes a number of users try to feed in data during this loading time. Now each time a request from any of the Webserver comes to the database during the loading process, a server process is created and just sits there in an idle state.Each request might even create multiple processes.

The database is configured for a maximum processes of 100 in a dedicated server mode. Atleast once in a month they reach the max processes for the database and the load process fails because it is unable to create a new process when required.The load process runs in four parallel sessions and goes to a maximum of 15 process at any given time.

According to Oracle, a Server process is created only when a connection is established. I reconfirmed this with the following shell script.

#!/bin/ksh
cmd1="/usr/local/oracle/product/8.1.7/bin/sqlplus"
cmd2="test1/testconn@DAD1"
count=0
echo " Script Started" >/var/tmp/xyz
while test $count -lt 10
do
let count=$count+1
echo $count
$cmd1 $cmd2 >>/var/tmp/xyz 2>&1 &
echo "$cmd1 $cmd2 --->step $count "
done
echo " Script Ended" >>/var/tmp/xyz

I could`nt see any server processes created for the above script, because the connection was not established.


Here is my listener.ora file
################
# Filename......: listener.ora
# Name..........: test1.world
# Date..........: 13-AUG-02 15:14:25
################
SQLNET.AUTHENTICATION_SERVICES = (NONE)

USE_PLUG_AND_PLAY_LSNR234 = OFF
USE_CKPFILE_LSNR234 = OFF
LSNR234 =
(ADDRESS_LIST =
(ADDRESS=
(PROTOCOL=IPC)
(KEY= DAD1.world)
)
(ADDRESS=
(PROTOCOL=IPC)
(KEY= DAD1)
)
(ADDRESS=
(PROTOCOL=IPC)
(KEY= DAD2.world)
)
(ADDRESS=
(PROTOCOL=IPC)
(KEY= DAD2)
)
(ADDRESS =
(COMMUNITY = TCP.world)
(PROTOCOL = TCP)
(Host = TEST1)
(Port = 1522)
)
)
STARTUP_WAIT_TIME_LSNR234 = 0
CONNECT_TIMEOUT_LSNR234 = 10
TRACE_LEVEL_LSNR234 = OFF
SID_LIST_LSNR234 =
(SID_LIST =
(SID_DESC =

(GLOBAL_DBNAME = DAD1.world)
(SID_NAME = DAD1)
(ORACLE_HOME = /usr/local/oracle/product/8.1.7)
(PRESPAWN_MAX = 10)
)
(SID_DESC =

(GLOBAL_DBNAME = DAD2.world)
(SID_NAME = DAD2)
(ORACLE_HOME = /usr/local/oracle/product/8.1.7)
(PRESPAWN_MAX = 10)
)
)


Here is my Sqlnet.ora

################
# Filename......: sqlnet.ora
# Name..........: TCP.world
# Date..........: 05-AUG-02 14:22:30
################
AUTOMATIC_IPC = ON
TRACE_LEVEL_CLIENT = OFF
SQLNET.EXPIRE_TIME = 10
NAMES.DEFAULT_DOMAIN = world
NAME.DEFAULT_ZONE = world
SQLNET.CRYPTO_SEED = "xyz"
BEQUEATH_DETACH=yes
~

I know this problem can be fixed by the application/web team by providing time out sessions to the request.I suppose they are already working on it.

But my question is

# 1: "WHY" are the server processes created when requests to the
database are coming from the Webserver, eventhough that particular
user`s password is changed and there is no connection established
to the database. ( Tom i would appreciate if you can give a
detailed explanation for this question )

# 2: What would be the ideal way for a dba to restrict or clean up
such idle server processes.


Thanks in advance,
Suren