How to Resolve ORA-32002 Oracle Error Message

  • by

ORA-32002: cannot create SPFILE already being used by the instance

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.

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

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

Leave a Reply

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