Relocate spfile from ASM to file system

In this Post i am going to show you how to move the spfile located on ASM diskgroup to the file system. This may not be the practical requirement in the RAC as we need common place for the spfile to store. If we move spfile to local file system in RAC, then it is very hard to keep the spfiles on all the nodes in sync. But it will be useful if you are trying to change any non-dynamic parameter in the spfile.

Thats fine, how do we manage spfile, but lets see the process how do we move the spfile located in ASM to the file system.

SQL> select name,user from v$database;

NAME USER
————————————————– ——————————
TESTDB SYS

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string +DATA/testdb/spfiletestdb.ora
SQL>

[oracle@dhcppc1 ~]$ asmcmd
ASMCMD> cd +DATA/testdb/

ASMCMD> pwd
+DATA/testdb
ASMCMD> ls -lt spfile*
Type Redund Striped Time Sys Name
N spfiletestdb.ora => +DATA/TESTDB/PARAMETERFILE/spfile.270.738607517
ASMCMD>

[oracle@dhcppc1 datafiles]$ cd $ORACLE_HOME/dbs

[oracle@dhcppc1 dbs]$ pwd
/u01/app/11.2.0/db/dbs
[oracle@dhcppc1 dbs]$ ls -lrt init* spfile*
ls: spfile*: No such file or directory
-rw-r–r– 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r—– 1 oracle oinstall 39 Dec 24 16:45 inittestdb.ora
[oracle@dhcppc1 dbs]$ mv inittestdb.ora inittestdb.ora_bak
[oracle@dhcppc1 dbs]$ ls -lrt init* spfile*
ls: spfile*: No such file or directory
-rw-r–r– 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r—– 1 oracle oinstall 39 Dec 24 16:45 inittestdb.ora_bak
[oracle@dhcppc1 dbs]$

SQL> create pfile=’/u01/app/11.2.0/db/dbs/inittestdb.ora’ from spfile;

File created.

SQL> !ls -lrt /u01/app/11.2.0/db/dbs/inittestdb.ora
-rw-r–r– 1 oracle dba 862 Dec 31 16:42 /u01/app/11.2.0/db/dbs/inittestdb.ora

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

ASMCMD> ls -lt spfile*
Type Redund Striped Time Sys Name
N spfiletestdb.ora => +DATA/TESTDB/PARAMETERFILE/spfile.270.738607517
ASMCMD> rm spfiletestdb.ora
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
ASMCMD> ls -lt spfile*
ASMCMD-08002: entry ‘spfile*’ does not exist in directory ‘+DATA/testdb/’
ASMCMD>

SQL> create spfile=’/u01/app/11.2.0/db/dbs/spfiletestdb.ora’ from pfile=’/u01/app/11.2.0/db/dbs/inittestdb.ora’;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 723984384 bytes
Fixed Size 1338980 bytes
Variable Size 486539676 bytes
Database Buffers 230686720 bytes
Redo Buffers 5419008 bytes
Database mounted.
Database opened.
SQL> select name,user from v$database;

NAME USER
————————————————– ——————————
TESTDB SYS

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u01/app/11.2.0/db/dbs/spfiletestdb.ora
SQL>