When you ALTER SYSTEM to modify an initialization parameter, it sometimes fails with ORA-02097
ORA-02097: parameter cannot be modified because specified value is invalidReproduce ORA-02097
Let's me show you some examples that can reproduce ORA-02097 and I will explain it more detailed along with the context.
Here I would like to change DB_RECOVERY_FILE_DEST location. At first, I tried to set a full-path location with SCOPE=BOTH.
Online Alteration: Value Must be Acceptable
Oracle always Knows what value is acceptable if you try to alter the value online (within BOTH or MEMORY scope). Because needs to take your value effect immediately, a thorough checking is necessary.
For example, changing the path of Fast Recovery Area (FRA) like this:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+DATA/COMPDB/FRA' SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+DATA/COMPDB/FRA' SCOPE=BOTH
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01261: Parameter db_recovery_file_dest destination string cannot be
translated
The error simply told us the value cannot be understood.
Next, I simply used a disk group rather than a location. It succeeded.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+DATA' SCOPE=BOTH;
System altered.
Can you tell the difference? I set a location value '+DATA/DBNAME/FRA' in the first place, the database refused to accept, but when I set a diskgroup value '+DATA', then it succeeded, which means that Oracle knows what a valid value should be.
Let's see another example.
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 804M
memory_target big integer 804M
shared_memory_address integer 0
SQL> alter system set memory_target=900M scope=both sid='primdb1';
alter system set memory_target=900M scope=both sid='primdb1'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
The clue was in the error stack, it explained the error more deeply. In the above case, Oracle threw ORA-00837 to indicate the value cannot be accepted because it's greater than MEMORY_MAX_TARGET.
Offline Alteration: Type Must be Acceptable
Oracle will only check the type match if you try to alter the value within SPFILE scope, an offline alteration. The database must restart to take effect.
Don't set the SCOPE=SPFILE if you have no confidence against the value that you are going to change, because it always succeed if the data type matched. If you did the alteration, the database may refuse to open with a misconfigured spfile after a shutdown.
Solutions
ORA-02097 means that the alteration is failed and nothing changed. The key to prevent it is to assign an acceptable value for your parameter.
If you changed the parameters but the database refused to startup, you have some troubles to deal with. I hope you have a backup or a copy of SPFILE before alteration. You can restore it and start it over. If you don't have any backup of SPFILE, you may refer to the post below:
How to Startup a Misconfigured Database ORA-02097
Some very similar error codes like ORA-02095 or ORA-02096 may be another story, which indicates the initialization parameter that you're changing can only be modified within a proper scope.
More about changing initialization parameters, you may refer to Oracle documentation: Changing Parameter Values in a Parameter File