cancel
Showing results for 
Search instead for 
Did you mean: 

too many oracle processes

Mohajervepessaran
Occasional Advisor

too many oracle processes

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.
11 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: too many oracle processes

Hi,

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

Regards,
Jean-Luc
fiat lux
Mohajervepessaran
Occasional Advisor

Re: too many oracle processes

maxuprc is set to 1024.
Massimo Bianchi
Honored Contributor

Re: too many oracle processes

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
Mohajervepessaran
Occasional Advisor

Re: too many oracle processes

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?
Jean-Luc Oudart
Honored Contributor

Re: too many oracle processes

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

echo $SHELL
ulimit -a

Regards
Jean-Luc
fiat lux
Mohajervepessaran
Occasional Advisor

Re: too many oracle processes

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
Jean-Luc Oudart
Honored Contributor

Re: too many oracle processes

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
fiat lux
Massimo Bianchi
Honored Contributor

Re: too many oracle processes

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

Mohajervepessaran
Occasional Advisor

Re: too many oracle processes

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")

Massimo Bianchi
Honored Contributor

Re: too many oracle processes

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
Rory R Hammond
Trusted Contributor

Re: too many oracle processes

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

Rory
There are a 100 ways to do things and 97 of them are right