Select Page

Move SPFILE from ASM to File System

Author: Dallas Willett | | July 20, 2011

The following is an example of how to move your SPFILE from ASM to your file system or vice versa.

ASM to File System

In this case, we have our spfile in the +DATA diskgroup and we want to move it to the default location on our file system. The default location will be in our $ORACLE_HOME/dbs directory. In order to accomplish this, we need to create a pfile from our existing spfile and then create the new spfile from this temporary pfile. Afterwords, we bounce the database instance for the change to take effect.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/orcl/spfileorcl.ora

SQL> create pfile='/tmp/orcl.pfile' from spfile;

File created.

SQL> create spfile='/u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora'
from pfile='/tmp/orcl.pfile';

File created.

SQL> startup force
ORACLE instance started.
...
Database mounted.
Database opened.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                /db_1/dbs/spfileorcl.ora

Cleaning up the old ASM spfile

At this point you may want to clean up the old spfile within ASM. To do that, use the asmcmd utility to remove the file. The file system spfile will take precedence over the ASM spfile, but it’s not a bad idea to remove the old spfile in ASM.

$ . oraenv
ORACLE_SID= [orcl] ? +ASM
$ asmcmd
ASMCMD> cd DATA/orcl
ASMCMD> rm spfileorcl.ora
ASMCMD> exit

File System to ASM

The reverse procedure is similar. However, because Oracle will use the file system spfile first if it sees one, we need to remove or rename that file system spfile before bouncing the database instance.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                /db_1/dbs/spfileorcl.ora

SQL> create pfile='/tmp/orcl.pfile' from spfile;

File created.

SQL> create spfile='+DATA/orcl/spfileorcl.ora'
from pfile='/tmp/orcl.pfile';

File created.

$ cd $ORACLE_HOME/dbs
$ mv spfileorcl.ora old_spfileorcl.ora

SQL> startup force
ORACLE instance started.
...
Database mounted.
Database opened.

SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/orcl/spfileorcl.ora

Fixing an OMF spfile name

One common error you may run into is if you didn’t provide a full path to the spfile within your ASM diskgroup (i.e., you created the spfile as ‘+DATA’ instead of ‘+DATA/orcl/spfileorcl.ora’). If that happens, then Oracle is going to assign an Oracle Managed File (OMF) name to your spfile and all you need to do is create an alias for it using the asmcmd tool.

SQL> create pfile='/tmp/orcl.pfile' from spfile;
File created.

SQL> create spfile='+DATA'
from pfile='/tmp/orcl.pfile';
File created.

$ cd $ORACLE_HOME/dbs
$ mv spfileorcl.ora old_spfileorcl.ora

SQL> startup force
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/orcl/spfileorcl.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-15173: entry 'spfileorcl.ora' does not exist in directory 'orcl'
ORA-06512: at line 4

$ . oraenv
ORACLE_SID= [orcl] ? +ASM
$ asmcmd
ASMCMD> cd DATA/orcl/
ASMCMD> ls PARAMETERFILE
spfile.277.756994081
ASMCMD> mkalias +DATA/orcl/parameterfile/spfile.277.756994081
+DATA/orcl/spfileorcl.ora
ASMCMD> exit

SQL> startup force
ORACLE instance started.
...
Database mounted.
Database opened.

SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/orcl/spfileorcl.ora

This code was tested on an Oracle 10g Standard Edition database.

To learn more about Datavail’s Oracle Database Administration services, contact us now. >>>

 

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Best RAID For SQL Server | RAID 0, RAID 1, RAID 5, RAID 10

Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.

Eric Russo | June 8, 2015

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS