ORA-32002
Tried to restore the server parameter (SPFILE) to the default location from a parameter file (PFILE), but it failed with ORA-32002 like this.
SQL> create spfile from pfile='/home/oracle/pfile';
create spfile from pfile='/home/oracle/pfile'
*
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance
Indeed, the instance is running.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
ORA-32002 means that the SPFILE that you want to restore is currently working for an instance, so you cannot overwrite its content.
For RAC databases, we could have the same issue.
SQL> create spfile='+DATA/ORCLCDB/spfile' from pfile='/home/oracle/pfile';
create spfile='+DATA/ORCLCDB/spfile' from pfile='/home/oracle/pfile'
*
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance
Solutions
There're two way to think about restoring SPFILE, you can either restore SPFILE to another location or restore it to the current location.
Restore SPFILE to Another Location
You may choose another location to place the new SPFILE.
SQL> create spfile='/home/oracle/spfile' from pfile='/home/oracle/pfile';
File created.
There will be no conflict with the current SPFILE in use. Afterwards, you may copy the file to the current location whenever the database is idle.
However, if you insist to restore SPFILE to the current, mostly the default location, let's continue.
Restore SPFILE to Current Location
The key to solve the problem is to detach the SPFILE from any instance. The direct way is to stop the instance.
Single-instance DB
For a single-instance one, we can just stop the database to release SPFILE from the instance.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Then restore SPFILE.
SQL> create spfile from pfile='/home/oracle/pfile';
File created.
RAC DB
For RAC databases, there're two approaches to treat ORA-32002, which result differently.
When DB is Idle
The first way is to stop the database.
[oracle@standby01 ~]$ srvctl stop database -d orclstb
Then restore SPFILE.
[oracle@standby01 ~]$ sqlplus / as sysdba
...
Connected to an idle instance.
SQL> create spfile='+DATA/ORCLCDB/spfile' from pfile='/home/oracle/pfile';
File created.
The real location of SPFILE in ASM would be at:
ASMCMD> cd +DATA/ORCLCDB
ASMCMD> ls -l spfile
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE AUG 11 18:00:00 N spfile => +DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.278.1080321195
As you may have noticed, the real file fell into DB_UNKNOWN directory. Let's see how we correct it.
When DB is NOMOUNT
To detach SPFILE from the instance, we can also stop and startup the database to NOMOUNT with a PFILE.
[oracle@standby01 ~]$ srvctl stop database -d orclstb
[oracle@standby01 ~]$ sqlplus / as sysdba
...
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/pfile';
ORACLE instance started.
Then restore SPFILE.
SQL> create spfile='+DATA/ORCLCDB/spfile' from pfile='/home/oracle/pfile';
File created.
The only difference is the real location of SPFILE in ASM.
ASMCMD> cd +DATA/ORCLCDB
ASMCMD> ls -l spfile
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE AUG 12 11:00:00 N spfile => +DATA/ORCLSTB/PARAMETERFILE/spfile.278.1080385959
Can you tell the difference? I have talk about it in How to Resolve SPFILE in DB_UNKNOWN.