ORA-32001: write to spfile requested but no SPFILE specified at startup !!!!
2 min readORA-32001: How to identify if the database was started with spfile or pfile?
There are couple of ways of finding if the database was started with spfile or pfile.
First we can check the value of parameter spfile, if it returns blank then database was started by pfile.
SQL> show parameter spfile;
NAME TYPE VALUE
—— —— ————————————————
spfile string +GRID_DG/ORCL/spfileorcl.ora
Similar to using SHOW PARAMETER, you can query the V$PARAMETER view to check the value of the spfile parameter. If the query returns a path, the database was started with an spfile. If it returns NULL, it was started with a pfile.
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME = 'spfile';
If started with an spfile:
SQL> SHOW PARAMETER spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileORCL.ora
If started with a pfile:
SQL> SHOW PARAMETER spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
Second way to find would be setting the parameter with scope=spfile.
If database was started with spfile one will be able to set the value if it’s started with pfile you will see the “ORA-32001: write to spfile requested but no SPFILE specified at startup”.
Note: You can set the new value to be the same as current value of the parameter
SQL> ALTER SYSTEM set open_cursors=300 scope=spfile;
ALTER SYSTEM set open_cursors=300 scope=spfile;
*
ERROR at line 1:
ORA-32001: write to spfile requested but no SPFILE specified at startup
Hope it helped !! 🙂
