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