ORA-02095: specified initialization parameter cannot be modified

Oracle database is fundamentally built on top of system memory structures called oracle instance and physical files often referred to as oracle database. The oracle instance creation upon system start, memory allocation happens by mentioning the values in a file called initialization parameter file. Initialization parameters are the key drivers in determining the size of database, instance allocation to name a few. All the initialization parameters are not the same. Some are static in nature that cant be changed and some are dynamic. Parameters must be changed with proper SCOPE specification in alter statement. ORA-02095: specified initialization parameter cannot be modified is often associated with initialization parameters that can’t be modified
I tried creating multiple copies of control file. I got this error at the point where I tried altering the control_files initialization parameter. The issue got fixed when I specified scope=spfile
SQL> alter system set control_files=”C:\APP\USERNAME\ORADATA\ORASID\CONTROL01.CTL”,”C:\APP\USERNAME\ORADATA\ORASID\CONTROL02.CTL”,”C:\APP\USERNAME\ORADATA\ORASID\CONTROL03.CTL”;
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL>alter system set control_files=”C:\APP\USERNAME\ORADATA\ORASID\CONTROL01.CTL”,”C:\APP\USERNAME\ORADATA\ORASID\CONTROL02.CTL”,”C:\APP\USERNAME\ORADATA\ORASID\CONTROL03.CTL” scope=spfile;
System altered.
Case #2:
I got this error when I tried changing an initialization parameter. I thought that there is some problem with the initialization parameter. But it is a different problem. When we specify SCOPE=SPFILE the problem got fixed.
SQL> alter system set undo_management=’manual’;
alter system set undo_management=’manual’ *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set undo_management=’manual’ scope=spfile;
System altered.
Case #3 :
Initialization parameters are the key drivers which drive the operation and performance of Oracle database instance. Certain parameters can be modified dynamically – changed while instance is up and running(dynamic initialization parameter). While few parameters like log_buffer are static initialization parameters that can’t be changed.
SQL> alter system set log_buffer=32M scope=both;
alter system set log_buffer=32M scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
The above command has a class SCOPE. Scope is same as english word scope which in simple terms means range. This clause can take possible values SPFILE,MEMORY,BOTH.

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner