Skip to content

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

  • by

ORA-32001

ORA-32001 means that it cannot write the server parameter file at this moment, you should make sure SPFILE is being used by the instance.

Let's see some error patterns of ORA-32001.

  1. Non-CDB or Root Container (CDB)
  2. Pluggable Database (PDB)

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 in this pattern.

  1. Modify PFILE
  2. Startup by SPFILE

But first of all, shutdown the database.

SQL> shutdown immediate;

1. 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';

2. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *