Oracle Technologies Blog

By ASKM

relocate spfile from ASM to file system

Posted by Srikrishna Murthy Annam on February 3, 2011

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

Advertisements

2 Responses to “relocate spfile from ASM to file system”

  1. vijay said

    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

  2. Srikrishna Murthy Annam said

    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 ).
    https://learnwithme11g.wordpress.com/2011/12/30/creating-physical-standby-database-with-rman-duplicate-from-active-database/
    https://learnwithme11g.wordpress.com/2011/12/30/creating-physical-standby-database-with-rman-duplicate-from-active-database-video/

    Thanks
    SRI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: