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.
Hope it helps
SRI
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>
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
Posts
+ comments + 2 comments
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
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.