ORA-02095: specified initialization parameter cannot be modified

This is a generic error message that may be returned while setting an initialization parameter. There are a few other error messages that we may face and in this article, I would discuss those error messages as well. The most common reason of this error message is modifying a parameter using ALTER SYSTEM command that is not dynamically modifiable. In this case, we need to set the parameter in spfile, or pfile, and then a re-bounce of the instance would set the value of this parameter in the instance.

In the following example, we can see that an error would be returned if a static parameter PROCESSES would be set online. When we use SCOPE=SPFILE, we can set parameter without any error.

Enter user-name: sys as sysdba Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> alter system set processes=1200; alter system set processes=1200                  * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified SQL> alter system set processes=1200 scope=spfile; System altered.

Another reason of this error could be to provide wrong format for the value of the parameter, or providing an invalid value. See following example.

gcs_server_processes

This parameter sets the number of processes to serve global cache in a RAC environment and can be set at SYSTEM level only. Trying to set it at SESSION level  would also return ORA-02095. Although setting some other parameters at session level would return ORA-02096 as discussed later in this article

SQL> show parameter gc NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ gcs_server_processes                 integer     0 SQL> alter session set gcs_server_processes=3 scope=spfile sid='*'; alter session set gcs_server_processes=3 scope=spfile sid='*'                   * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified

ORA-02097: parameter cannot be modified because specified value is invalid

This error message is caused by an invalid value of parameter that we are trying to set. Following is an example where other errors along with this error may also provide more information about how to set the parameter correctly.

SQL> show parameter log_archive_dest_1 NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1                   string      location=USE_DB_RECOVERY_FILE_                                                  DEST log_archive_dest_10                  string log_archive_dest_11                  string log_archive_dest_12                  string log_archive_dest_13                  string log_archive_dest_14                  string log_archive_dest_15                  string log_archive_dest_16                  string log_archive_dest_17                  string log_archive_dest_18                  string NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_19                  string SQL> alter system set log_archive_dest_1='location=d:\'; alter system set log_archive_dest_1='location=d:\' * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated ORA-09291: sksachk: invalid device specified for archive destination OSD-04018: Unable to access the specified directory or device. O/S-Error: (OS 3) The system cannot find the path specified.

ORA-00922: missing or invalid option

Providing in invalid value to the parameter can also throw ORA-00922 error message. In the following example, the correct value of parameter LOCK_SGA could be TRUE or FALSE. Any value other than this would return error.

SQL> alter system set lock_sga=yes scope=spfile; alter system set lock_sga=yes scope=spfile                           * ERROR at line 1: ORA-00922: missing or invalid option

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

This error is returned if you are trying to change a parameter with ALTER SESSION command, but this parameter can only be modified with ALTER SYSTEM option. To solve the problem, use correction option while setting the parameter. For example, cpu_count cannot be modified at session level, thus setting this parameter at session level would return ORA-02096.

SQL> alter session set cpu_count=10 scope=spfile; alter session set cpu_count=10 scope=spfile                   * ERROR at line 1: ORA-02096: specified initialization parameter is not modifiable with this option SQL> alter system set cpu_count=10 scope=spfile; System altered.