ORA-02095: specified initialization parameter cannot be modified

##########################
#  Error
##########################

ORA-02095: specified initialization parameter cannot be modified

ORA-02095 Error may mislead, check the below post,

http://stepintooracledba.blogspot.com/2013/02/ora-02095-misleading.html

##########################
#  Error Occurred
##########################

When i tried to change processes parameter as SYS user.

Command Executed

alter system set processes=300;

(or)

alter system set processes=300 scope=both;

##########################
## Cause
##########################

Processes parameter is a STATIC Parameter which cannot be changed at instance level.

##########################

## Solution
##########################

ALTER SYSTEM command with,

SCOPE=both  --- Applies the changes in the instance level as well as the spfile used for Startup

If SCOPE is not used, then it applies the changes at instance level.

• Check Database is Using SPFILE

SQL>sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DB1_DATA/db1/spfiledb1.ora

From above command we can confirm that database is using spfile, so next we need to check whether the parameter we are going to change can be modified at instance level.


set lines 200
col name for a35
col value for a25

Select name,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='processes';

NAME                                VALUE                     ISDEFAULT ISSES ISSYS_MOD
----------------------------------- ------------------------- --------- ----- ---------
processes                           600                       FALSE     FALSE FALSE

From the Above output we can find that whether a parameter can be modified at INSTANCE level.

ISSES_MODIFIABLE	VARCHAR2(5)	TRUE - the parameter can be changed with ALTER SESSION FALSE - the parameter cannot be changed with ALTER SESSION
ISSYS_MODIFIABLE	VARCHAR2(9)	IMMEDIATE - the parameter can be changed with ALTER SYSTEM DEFERRED - the parameter cannot be changed until the next session FALSE - the parameter cannot be changed with ALTER SYSTEM

So our processes parameter cannot be modified at instance level. So we need to update only the spfile, which will be reflected in the next instance startup.


SQL>alter system set processes=300 scope=spfile;

System altered.



SQL>sho parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     150


See, Here parameter value is not changed at the INSTANCE level, but its updated in SPFILE. Once we bounce the database it will be reflected.


SQL>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1426065608 bytes
Database Buffers          671088640 bytes
Redo Buffers               38502400 bytes
Database mounted.
Database opened.
SQL>sho parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     300


For Eg :


NAME                                VALUE                     ISDEFAULT ISSES ISSYS_MOD
----------------------------------- ------------------------- --------- ----- ---------
log_archive_max_processes           4                         TRUE      FALSE IMMEDIATE

log_archive_max_processes parameter can be changed immediately via ALTER SYSTEM command and it will be applicable at instance level.