ORA-32001 write to SPFILE requested but no SPFILE is in use

While trying to set few “ALTER SYSTEM SET” command we started hitting ORA-32001 errors and none of alter statement could run.We even tried to remove SPFILE from command bug got below error.

ORA-02095: specified initialization parameter cannot be modified

Example:

SQL> ALTER SYSTEM SET disk_asynch_io=TRUE ;

ALTER SYSTEM SET disk_asynch_io=TRUE

*

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

 

So we started debuigging something like below and finally were able to run all the commands without any problem.

Before we start debugging we shall know what is a SPFILE and PFILE and what are the differences?

There are hundreds of instance parameters that define the way an instance operates. As an administrator you have to set each of these parameters correctly. All these parameters are stored in a file called parameter file. These parameter files are also called initialization files as they are needed for an instance to startup.

There are two kinds of parameter file. Parameter file (pfile) and server parameter file (spfile).

Differences between an spfile and pfile

1.  Spfiles are binary files while pfiles are plain text files.

2.   If you are using spfile, instance parameters can be changed permanently using SQL*Plus commands. If you are using pfile, you have to edit pfile using an editor to change values permanently.

3. Spfile names should be either spfile<SID>.ora or spfile.ora. Pfile names must be init<SID>.ora

 

So coming back to the issue we faced errors like below:

SQL> conn / as sysdba

Connected.

SQL> ALTER SYSTEM SET memory_target = 0 SCOPE=SPFILE;

ALTER SYSTEM SET memory_target = 0 SCOPE=SPFILE

*

ERROR at line 1:

ORA-32001: write to SPFILE requested but no SPFILE is in use

SQL> ALTER SYSTEM SET sga_target = 14G SCOPE=SPFILE;

ALTER SYSTEM SET sga_target = 14G SCOPE=SPFILE

*

ERROR at line 1:

ORA-32001: write to SPFILE requested but no SPFILE is in use

Once we saw errors regarding SPFILE we started looking out for SPFILE location and if SPFILE exists or not. If SPFILE doesnot exist does DB instance uses PFILE or not, etc.

Below commands can be used to check:

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string

SQL> show parameter pfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string

 

From above commands output you can tell that SPFILE or PFILE is not in use in the database which is causing the alter system statements to fail.

So to see the parameters we ran below command which also did not show much info:

SQL> show parameter dump

 

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

background_core_dump string partial

background_dump_dest string /slot/oracle/11.2.0/log/diag/rdbms/oracledb1/oracledb1/trace

core_dump_dest string /slot/oracle/11.2.0/log/diag/rdbms/oracledb1/oracledb1/cdump

max_dump_file_size string 10M

shadow_core_dump string PARTIAL

user_dump_dest string /slot/oracle/11.2.0/log/diag/rdbms/oracledb1/oracledb1/trace

After seeing all above we decided to create a SPFILE first using below command:

SQL> CREATE SPFILE FROM PFILE;

File created.

Once file created we tried to see where is the location of the SPFILE using below command but it did not show anything all outputs were blank and all alter system commands failed again with same error of SPFILE not found.

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string

SQL> show parameter pfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string

 

Ex:

SQL> ALTER SYSTEM SET memory_target = 0 SCOPE=SPFILE;

ALTER SYSTEM SET memory_target = 0 SCOPE=SPFILE

*

ERROR at line 1:

ORA-32001: write to SPFILE requested but no SPFILE is in use

 

Then we restarted the database and were able to see the SPFILE location using below commands:

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area 2154590208 bytes

Fixed Size 2230112 bytes

Variable Size 1325402272 bytes

Database Buffers 805306368 bytes

Redo Buffers 21651456 bytes

Database mounted.

Database opened.

SQL> show parameter pfile

 

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /slot/oracle/11.2.0/dbs/spfileoracledb1.ora

 

Once we got the above output as SPFILE is now present in DB all command like below went successful without any issues.

SQL> ALTER SYSTEM SET memory_target = 0 SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET sga_target = 14G SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET pga_aggregate_target = 8G SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET processes = 5000 SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET parallel_max_servers = 24 scope=SPFILE;

System altered.

SQL> ALTER SYSTEM SET disk_asynch_io=TRUE scope=SPFILE;

System altered.

 

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.