Home » » relocate spfile from ASM to file system

relocate spfile from ASM to file system

Written By Srikrishna Murthy Annam on Thursday, February 3, 2011 | 1:35 AM

In the present article 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>


Hope it helps

SRI
Share this article :

Related Articles By Category



+ comments + 2 comments

November 19, 2012 at 5:38 PM

Hello Sir
how to implement a data guard where both primary and standby databases use ASM filesystem
can you please tell me steps how to implement

Regards,
VIjay

Srikrishna Murthy Annam
December 5, 2012 at 5:18 AM

You can use rman to create standby ...
Please follow the below links which explains the step by step procedure to create a standby database ( No matter which file system you are using as long as you are using rman. It takes care of it ).
http://learnwithme11g.wordpress.com/2011/12/30/creating-physical-standby-database-with-rman-duplicate-from-active-database/
http://learnwithme11g.wordpress.com/2011/12/30/creating-physical-standby-database-with-rman-duplicate-from-active-database-video/

Thanks
SRI

Post a Comment

Thank you for visiting our site and leaving your valuable comment.

 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger