Thursday, January 17, 2013

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.


6 comments:

Anonymous said...

you are great !

Mallikarjun reddy kethu said...

I worked for me. Thanks a lot.
Please keep posting

Sri said...

thanks dude

Anonymous said...

Thanks a lot..it worked for me

Bhruguraj Rana said...

hey i want to change the format of my archive log file in such a way that it should depict date and time with the filename, please help to do that....
waiting for your reply

Sri said...


Hi Bhruguraj,

LOG_ARCHIVE_FORMAT doesn't support date and time. Below variables can be used,

The following variables can be used in the format:

%s log sequence number

%S log sequence number, zero filled

%t thread number

%T thread number, zero filled

%a activation ID

%d database ID

%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database

Please Refer Below Link assuming you are using 11g Environment.

http://docs.oracle.com/cloud/latest/db112/REFRN/initparams128.htm#REFRN10089