ORA-32001
ORA-32001 means that the database cannot write SPFILE at this moment, you should make sure that it has been using by the instance.
Let's see some error patterns of ORA-32001.
In a multitenant database, it's somewhat complicated.
Non-CDB or Root Container (CDB)
If you are in a non-CDB or root container, you might see this error at times. In this case, we tried to modify a parameter within SCOPE=BOTH, but it failed with ORA-32001.
SQL> alter system set undo_retention=1800 scope=both;
alter system set undo_retention=1800 scope=both
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use
Let's see what SPFILE we are using now.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
OK, there's none. Apparently, the database did not start by a SPFILE, but a PFILE. So the database doesn't know where to modify the parameter. That's why we can't write the new value into SPFILE.
There're 2 ways to solve ORA-32001 for this error pattern.
But first of all, shutdown the database.
SQL> shutdown immediate;
Modify PFILE
If the PFILE has been used to startup the database very well, you can modify the parameter in the PFILE, then restart the database by the file.
[oracle@test ~]$ vi /home/oracle/pfile.ora
...
*.undo_retention=1800
SQL> startup pfile='/home/oracle/pfile.ora';
Startup by SPFILE
If you need to a permanent solution to ORA-32001, you can use SPFILE to startup the database
Before using it, you have to make sure that parameters in the SPFILE are all current. If PFILE has the newest version of parameters, you may restore the SPFILE from PFILE.
SQL> create spfile from pfile='/home/oracle/pfile.ora';
File created.
Then startup the database.
SQL> startup
Since the SPFILE is in use by the database, we can modify the parameter within SCOPE=BOTH.
SQL> alter system set undo_retention=1800 scope=both;
System altered.
Pluggable Database (PDB)
Let's see what we found.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
SQL> alter system set undo_retention=1800 scope=both;
alter system set undo_retention=1800 scope=both
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use
As you can see, we tried to modify a parameter in a PDB, but it failed with ORA-32001. Is SPFILE still in use?
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.3.0
/db_1/dbs/spfileORCLCDB.ora
Yes, the database is using SPFILE, it should be no problem. What's going on?
In fact, SPFILE to pluggable databases (PDB) is only a logical repository, not a real file, so any operation on SPFILE of a PDB behaves differently from a CDB.
In other words, all parameters of a PDB store in the root container (CDB). Therefore, the success of modifying a parameter in a PDB depends on the status of the CDB.
Let's check what status of the database currently is.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
It's mounted.
In MOUNT state, we can modify the physical SPFILE for the whole database, but not a virtual, logical one for the PDB. The only solution is to open the CDB in order to write some data into CDB.
Switch to CDB
SQL> alter session set container=CDB$ROOT;
Session altered.
Open CDB
SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
Then we modify the parameter for the PDB.
Switch PDB
SQL> alter session set container=ORCLPDB;
Session altered.
Modify SPFILE of PDB
SQL> alter system set undo_retention=1800 scope=both;
System altered.
We solved it.