topic too many oracle processes in Operating System - HP-UX

too many oracle processes

Mohajervepessaran — Thu, 12 Aug 2004 04:38:15 GMT

In our HPUX 11.0 ( 2GB RAM ) runs Oracle 1.8.7.3.0. In addition we use omniback for online backup. It happens that no application can be started any more with a message of "too many oracle processes". The database parameter Processes is increased to 400, but the problem can happen every now and then. The kernel parameter nfile is set to 15000. What could be the reason? I attached also the RDA output.

Re: too many oracle processes

Jean-Luc Oudart — Thu, 12 Aug 2004 04:47:04 GMT

Hi,

what is the maxuprc value in your system ?
kmtune | grep -i maxuprc
(maximum number of simultaneous user processes)

Regards,
Jean-Luc

Re: too many oracle processes

Mohajervepessaran — Thu, 12 Aug 2004 05:28:59 GMT

maxuprc is set to 1024.

Re: too many oracle processes

Massimo Bianchi — Thu, 12 Aug 2004 05:47:49 GMT

Hi,
check also the number of semaphores.

For each process, you must be sure to have also a free semaphore.

Chek in the /stand/system file the sem* parameters.

Also,
what is the number of sessions ? Is it maintained to with the 1.2 ratio with process

HTH,
Massimo

Re: too many oracle processes

Mohajervepessaran — Thu, 12 Aug 2004 06:15:43 GMT

The semaphore variables are set as following:
semmnu=1660
semmns=4096
semmni=4096
semaem=16384
semmap=(SEMMNI+2)

Where can I check the session number?

Re: too many oracle processes

Jean-Luc Oudart — Thu, 12 Aug 2004 06:18:23 GMT

Also, coul you post the definition for the user who owns/starts the listener

echo $SHELL
ulimit -a

Regards
Jean-Luc

Re: too many oracle processes

Mohajervepessaran — Thu, 12 Aug 2004 06:41:31 GMT

I don't have direct access to this system, but in one lab system which should have similar configuration I get this output for oracle user:
% [2] % ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 524288
stack(kbytes) 8192
memory(kbytes) unlimited
coredump(blocks) 4194303
nofiles(descriptors) 1024

Re: too many oracle processes

Jean-Luc Oudart — Thu, 12 Aug 2004 06:58:47 GMT

Hi,

could you check on the target system which shell is used and also the oraenv script file.

Please check following threads for more information .

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=62595

http://forums1.itrc.hp.com/service/forums/bizsupport/questionanswer.do?threadId=602771
http://forums1.itrc.hp.com/service/forums/bizsupport/questionanswer.do?threadId=49882

Regards,
Jean-Luc

Re: too many oracle processes

Massimo Bianchi — Thu, 12 Aug 2004 07:29:44 GMT

Semaphore values look good

Session can be derived from initSID.ora.

Or, if you have sqlnet access,
simply issue a

show parameters

an please post the output.

Regards,
Massimo

Re: too many oracle processes

Mohajervepessaran — Thu, 12 Aug 2004 08:15:38 GMT

Here is the contents of init.ora:
##############################################################################
#
# initoss.ora for Nokia NetAct Database.
#
# First 8.1.7.1.0b version on 30.5.2001
#
##############################################################################

db_name = "oss"
instance_name = oss
service_names = oss

db_files = 250

control_files = ("/d/db01/oradata/oss/control01.ctl", "/d/db/oradata/oss/control02.ctl", "/d/db/oradata/oss/control03.ctl")

max_dump_file_size = 10240

log_archive_start = true
log_archive_dest = /d/db01/app/oracle/admin/oss/arc/arch

# Global Naming -- enforce that a dblink has same name as the db it connects to
# global_names = false

# Uncomment the following line if you wish to enable the Oracle Trace product
# to trace server activity. This enables scheduling of server collections
# from the Oracle Enterprise Manager Console.
# Also, if the oracle_trace_collection_name parameter is non-null,
# every session will write to the named collection, as well as enabling you
# to schedule future collections from the console.
# oracle_trace_enable = true

# define directories to store trace and alert files
background_dump_dest = /d/db01/app/oracle/admin/oss/bdump
core_dump_dest = /d/db01/app/oracle/admin/oss/cdump
user_dump_dest = /d/db01/app/oracle/admin/oss/udump

#Uncomment this parameter to enable resource management for your database.
#The SYSTEM_PLAN is provided by default with the database.
#Change the plan name if you have created your own resource plan.
resource_manager_plan = oss_multilevel_plan

db_block_size = 8192

remote_login_passwordfile = shared

# P4
optimizer_mode = choose
# Changed 10.12.01
utl_file_dir = /tmp, /d/db01/app/oracle/admin/oss/pfile/mdbchk

# The default date format to use with the TO_CHAR and TO_DATA
# functions.
nls_date_format = dd.mm.rrrr

# The default language of the database.
nls_language = american

# The name of the territory whose conventions are to be followed
# for day and week numbering.
nls_territory = france

# The characters to use as the group separator and decimal.
# This overrides those defined implicitly by NLS_TERRITORY.
nls_numeric_characters = ". "

# Previous open_cursors=255, changed 06.11.2001
open_cursors = 300
session_cached_cursors = 100

db_block_buffers = 60000

shared_pool_size = 80000000

large_pool_size = 0

# os_authent_prefix = ""
# remote_os_authent = true

log_checkpoint_timeout = 0
log_checkpoint_interval = 250000

processes = 300

log_buffer = 786432

sort_area_size = 655360
sort_area_retained_size = 65536

db_block_lru_latches = 3
buffer_pool_keep = ("buffers:18000","lru_latches:1")

cursor_space_for_time = true

db_file_multiblock_read_count = 8

db_writer_processes = 2

dbwr_io_slaves = 4

enqueue_resources = 1492

max_enabled_roles = 100

disk_asynch_io = false

tape_asynch_io = false

timed_statistics = true

compatible = "8.1.7"

job_queue_processes = 2

job_queue_interval = 60

#Added for OmniBack
backup_tape_io_slaves = true

java_pool_size = 7500000

# Added 06.11.2001 (8.1.7.1.2 version)
cursor_sharing = force
##############################################################################
#
# initoss.ora for Nokia NetAct Database. Intended for systems having 1.5 GB RAM.
#
##############################################################################

db_block_buffers = 112000
buffer_pool_keep = ("buffers:33600","lru_latches:1")

Re: too many oracle processes

Massimo Bianchi — Thu, 12 Aug 2004 08:33:59 GMT

Hi,
some notes on your init:

db_block_buffer: must occur only once, otherwise the last entry will be the real one; the same for buffer_pool_keep. Please, comment out the unused ones.

In the initoss.ora you should put a line like this:

#sessions=1.2*process
in your case
sessions=360

Do you get any error, when no more oracle process can start ? Like ORA- ....

Here is a good discussion about process and sessions...

http://asktom.oracle.com/pls/ask/f?p=4950:8:628233631431204707::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:5671284058977,

Regards,
Massimo

Re: too many oracle processes

Rory R Hammond — Thu, 12 Aug 2004 18:56:19 GMT

This looks like a unix kernel nproc problem. It has noting todo with number of files.

Rory