ORA-02096: specified initialization parameter is not modifiable with this option

Cause:

You try to modify a parameter online without saving the changes to spfile

02096, 00000, "specified initialization parameter is not modifiable with this option"
// *Cause: Though the initialization parameter is modifiable, it cannot be
//         modified using the specified command.
// *Action: Check the DBA guide for information about under what scope
//          the parameter may be modified

Solution:

There are two types of parameters

one is static and other is dynamic

Dynamic parameters can be modified,when the instance is up and running without a database bounce and can be modified at memory level

Static parameters can be modified,when the instance is up and running but require a reboot for the changes to get reflected on database and it can be modified only at spfile level not in memory

For example,you can see the parameters which can and cant be modified in ISINSTANCE_MODIFIABLE column of v$parameter view

SQL> set lines 200
SQL> set pages 1000
SQL> select name,ISINSTANCE_MODIFIABLE from v$parameter;

NAME                                                                             ISINS <------
-------------------------------------------------------------------------------- -----
lock_name_space                                                                  FALSE(static)
processes                                                                        FALSE(static)
sessions                                                                         TRUE(dynamic)

If i try to modify the static parameter,i get the error

SQL> show parameter processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     200


SQL> alter system set processes=300;
alter system set processes=300
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified <---------


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

System altered.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1572861600 bytes
Fixed Size                  8910496 bytes
Variable Size            1132462080 bytes
Database Buffers          423624704 bytes
Redo Buffers                7864320 bytes
Database mounted.
Database opened.

In alertlog, you can monitor the number of process gets modified with the value which was set earlier

[oracle@orcl19x ~]$ tail -f /data01/base/diag/rdbms/orcl19x1/orcl19x1/trace/alert_orcl19x1.log|grep processes
Shutting down archive processes
  processes                = 300 <-----

I can modify the dynamic parameter in fact without a reboot!

SQL> select name,ISINSTANCE_MODIFIABLE from v$parameter where name='optimizer_use_invisible_indexes';

NAME                                                                             ISINS
-------------------------------------------------------------------------------- -----
optimizer_use_invisible_indexes                                                  TRUE <---(Dynamic)


SQL> show parameter optimizer_use_invisible_indexes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     TRUE
SQL> alter system set optimizer_use_invisible_indexes=FALSE;

System altered.

SQL> show parameter optimizer_use_invisible_indexes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s