PROBLEM :
While altering parameter in parameter file got below error :
ALTER SYSTEM SET processes = 800 SCOPE=SPFILE;
ALTER SYSTEM SET processes = 800 SCOPE=SPFILE
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use
SOLUTION :
This error comes, if the database is running with pfile instead of spfile.
1. Check whether DB is running with pfile or spfile:
SQL> show parameter pfile
NAME TYPE VALUE
------ -------- ------------------------------
spfile string
Here value is showing BLANK, Means database is running with pfile. So with pfile , if we are trying to alter any init parameter in database, it will throw error.
To fix it, create an spfile from the pfile and restart the database.
2. Create spfile from pfile ;
create spfile from pfile;
3. Check whether spfile has been created in $ORACLE_HOME/dbs location :
cd $ORACLE_HOME/dbs
ls -ltr spfile*
4. Restart the database :
shutdown immediate;
startup
When we start the database, if both pfile(init$ORACLE_SID.ora) and spfile(spfile$ORACLE_SID.ora) are present at dbs location, Then bydefault spfile will be used for db startup.
5. Check whether spfile is used or not :
show parameter pfile :
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/oracle/product/12chome/spfiledbaclass.ora
6. Now run alter statement :
ALTER SYSTEM SET processes = 800 SCOPE=SPFILE
System altered.