Problem:
EBS instance running out of processes. Need to increase the database PROCESSES initialization parameter.
Solution:
Change the PROCESSES init parameter in the pfile or scope=spfile. Then restart the database (using addbctl.sh, not using the database stop immediate/startup command as shown below).
[applmgr@uttproddb 10.2.0]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Tue Oct 18 17:21:35 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from v$resource_limit; RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU ------------------------------ ------------------- --------------- ---------- ---------- processes 141 200 200 200 sessions 157 263 400 400 enqueue_locks 143 212 4948 4948 enqueue_resources 102 154 10532 UNLIMITED ges_procs 0 0 0 0 ges_ress 0 0 0 UNLIMITED ges_locks 0 0 0 UNLIMITED ges_cache_ress 0 0 0 UNLIMITED ges_reg_msgs 0 0 0 UNLIMITED ges_big_msgs 0 0 0 UNLIMITED ges_rsv_msgs 0 0 0 0 gcs_resources 0 0 0 0 gcs_shadows 0 0 0 0 dml_locks 23 194 10000 UNLIMITED temporary_table_locks 0 8 UNLIMITED UNLIMITED transactions 17 65 440 UNLIMITED branches 0 0 440 UNLIMITED cmtcallbk 3 20 440 UNLIMITED sort_segment_locks 245 372 UNLIMITED UNLIMITED max_rollback_segments 30 34 440 65535 max_shared_servers 0 0 UNLIMITED UNLIMITED parallel_max_servers 0 8 8 3600 22 rows selected. SQL> alter system set processes=1000 scope=memory; alter system set processes=1000 scope=memory * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified SQL> show parameter processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 1 db_writer_processes integer 2 gcs_server_processes integer 0 job_queue_processes integer 2 log_archive_max_processes integer 2 processes integer 200 SQL> show parameter license NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ license_max_sessions integer 0 license_max_users integer 0 license_sessions_warning integer 0 SQL> create spfile from pfile; create spfile from pfile * ERROR at line 1: ORA-32002: cannot create SPFILE already being used by the instance SQL> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/prod/proddb/oracle/produc t/10.2.0/dbs/spfilePROD.ora SQL> alter system set processes=1000 scope=both; alter system set processes=1000 scope=both * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified SQL> alter system set processes=1000 scope=spfile; System altered. SQL> show parameter processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 1 db_writer_processes integer 2 gcs_server_processes integer 0 job_queue_processes integer 2 log_archive_max_processes integer 2 processes integer 200 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 1271616 bytes Variable Size 788531392 bytes Database Buffers 276824064 bytes Redo Buffers 7114752 bytes Database mounted. Database opened. SQL> show parameter processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 1 db_writer_processes integer 2 gcs_server_processes integer 0 job_queue_processes integer 2 log_archive_max_processes integer 2 processes integer 1000 SQL> select * from v$resource_limit; RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU ------------------------------ ------------------- --------------- ---------- ---------- processes 22 23 1000 1000 sessions 28 29 1105 1105 enqueue_locks 59 65 13598 13598 enqueue_resources 59 59 10532 UNLIMITED ges_procs 0 0 0 0 ges_ress 0 0 0 UNLIMITED ges_locks 0 0 0 UNLIMITED ges_cache_ress 0 0 0 UNLIMITED ges_reg_msgs 0 0 0 UNLIMITED ges_big_msgs 0 0 0 UNLIMITED ges_rsv_msgs 0 0 0 0 gcs_resources 0 0 0 0 gcs_shadows 0 0 0 0 dml_locks 0 7 10000 UNLIMITED temporary_table_locks 0 0 UNLIMITED UNLIMITED transactions 0 4 1215 UNLIMITED branches 0 0 1215 UNLIMITED cmtcallbk 0 1 1215 UNLIMITED sort_segment_locks 0 1 UNLIMITED UNLIMITED max_rollback_segments 30 30 1215 65535 max_shared_servers 0 0 UNLIMITED UNLIMITED parallel_max_servers 0 0 8 3600 22 rows selected. SQL> desc v$resource_limit Name Null? Type ----------------------------------------- -------- ---------------------------- RESOURCE_NAME VARCHAR2(30) CURRENT_UTILIZATION NUMBER MAX_UTILIZATION NUMBER INITIAL_ALLOCATION VARCHAR2(10) LIMIT_VALUE VARCHAR2(10)
0 Comments.