Moving Oracle SPFILE Between ASM and File System

This article explains how to move an Oracle SPFILE between ASM and the file system, and vice versa. The process is straightforward and involves creating a temporary PFILE, recreating the SPFILE at the desired location, and restarting the database.

Scenario 1: Move SPFILE from ASM to File System

In this scenario, the SPFILE is stored in the ASM disk group +DATA, and we want to move it to the default file system location under $ORACLE_HOME/dbs.

Step 1: Verify Current SPFILE Location


SQL> SHOW PARAMETER spfile;

Output:


NAME    TYPE    VALUE
------  ------  -----------------------------
spfile  string  +DATA/FINDB1/spfilefindb1.ora

Step 2: Create a PFILE from the Existing SPFILE


SQL> CREATE PFILE='/tmp/findb1.pfile' FROM SPFILE;

Step 3: Create a New SPFILE on the File System


SQL> CREATE SPFILE=
'/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilefindb1.ora'
FROM PFILE='/tmp/findb1.pfile';

Step 4: Restart the Database


SQL> STARTUP;

Step 5: Confirm the New SPFILE Location


SQL> SHOW PARAMETER spfile;

Output:


spfile  string  
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilefindb1.ora

Optional Cleanup: Remove Old ASM SPFILE

Although the database now uses the file system SPFILE, it is a good practice to remove the old ASM copy.


$ . oraenv
ORACLE_SID=+ASM
$ asmcmd
ASMCMD> cd DATA/FINDB1
ASMCMD> rm spfilefindb1.ora
ASMCMD> exit

Scenario 2: Move SPFILE from File System to ASM

In this case, the SPFILE exists on the file system and needs to be moved into ASM.

Important: Oracle always prefers a file system SPFILE if it exists. Therefore, it must be renamed or removed before restarting the database.

Step 1: Confirm Current SPFILE Location


SQL> SHOW PARAMETER spfile;

Step 2: Create a PFILE from the File System SPFILE


SQL> CREATE PFILE='/tmp/findb1.pfile' FROM SPFILE;

Step 3: Create SPFILE in ASM


SQL> CREATE SPFILE='+DATA/FINDB1/spfilefindb1.ora'
FROM PFILE='/tmp/findb1.pfile';

Step 4: Rename the File System SPFILE


$ cd $ORACLE_HOME/dbs
$ mv spfilefindb1.ora spfilefindb1.bak

Step 5: Restart the Database


SQL> STARTUP;

Step 6: Validate SPFILE Usage


SQL> SHOW PARAMETER spfile;

Output:


spfile  string  +DATA/FINDB1/spfilefindb1.ora

Fixing an OMF-Named SPFILE in ASM

If the SPFILE was created using only the disk group name (for example, +DATA), Oracle assigns an OMF name. This can cause startup failures if the expected alias is missing.

Symptoms


ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file
ORA-15173: entry does not exist

Resolution Steps

Create a PFILE from the current SPFILE:
SQL> CREATE PFILE='/tmp/findb1.pfile' FROM SPFILE;

Switch to ASM environment:
$ . oraenv ORACLE_SID=+ASM $ asmcmd

Locate the OMF SPFILE:
ASMCMD> cd DATA/FINDB1/PARAMETERFILE ASMCMD> ls spfile.289.1187639911

Create an alias for the SPFILE:
ASMCMD> mkalias \ +DATA/FINDB1/PARAMETERFILE/spfile.289.1187639911 \ +DATA/FINDB1/spfilefindb1.ora

Restart the database:
SQL> STARTUP;

Verify:
SQL> SHOW PARAMETER spfile;

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Moving Oracle SPFILE Between ASM and File System