Question on restoring SPFILE after a complete database server crash

Introduction

I am posting a database recovery related question asked by one my blog's visitors' last week for restoring an spfile. A few additional steps are necessary if the database is using ASM technology.

[06:19] sunil: i had a catalog db and i m taking the bkp of target db in to tape drive, all of a sudden my target server crashed(means i lost everything includibg spfile,now i want to built a new server and want to restore the bkp from tape drive,how it'll happen.

[06:19] sunil: how i can restore my spfile?

Sunil,

It is not very tough to restore the spfile. Please try the below commands to restore your spfile. For simplicity, I am making the following assumptions.

1) You have used RMAN and a seperate CATALOG database for your backup strategy.
2) You still know the DBID of your "crashed" database. If you don't have your backup logfile, you can still find the DBID by querying the RMAN Catalog. Let it be 123456789
3) You have made the tapes containing your backup accessible to the new server.

A)

rman
connect target /
connect catalog catuser/catuser@catdb
set DBID 123456789
startup force nomount;
exit


B)

rman
connect target /
connect catalog catuser/catuser@catdb
set DBID 123456789
run
{
allocate channel t1 type 'SBT_TAPE';
restore spfile;
}
exit

c)

sqlplus '/ as sysdba'
create pfile from spfile;
shutdown immediate;

D)

Restore CONTROLFILE and update PFILE with new controlfile location

E)
The following additional steps must be performed if you have ASM for your database.
Let +ASMDATA be the diskgroup name for storing the spfile.

sqlplus '/ as sysdba'
create spfile='+ASMDATA' from pfile;
exit

cd $ORACLE_HOME/dbs
mv pfile pfile.old

Create a new pfile with the below contents after verifying the exact name of the restored spfile from v$asm_alias view
SPFILE='+ASMDATA/DBNAME/parameterfile/spfile_name_in_asm_alias_view'

rm the spfile on disk in $ORACLE_HOME/dbs directory.

The below command will startup the database using the above pfile which points to the actual SPFILE

sqlplus '/ as sysdba'
startup mount;

ASM for Oracle 11g with R12 E-Business Suite

Introduction

One of my visitors' posed a question in my weekly online chat in this site.

>> Tom asked

Hi Srinivas, we are planning to utilizing ASM 11gR2 DB for R12 ebusiness, is there any document that you can point me to for best practice for setting the ASM disks? Currently have only 2 disks; 1 is DATA and the other is RECOVERY. Data disk is for transaction and RECOVERY is for redo, archive log, etc. Can you point me to the right direction. thanks, Tom.

I am making a blogpost for this question because it is ASM is such an important consideration for today's enterprise databases, both for manageability and high availability of data.

Tom,

You have taken a good decision to implement ASM for your database. We had very good success with ASM and its also fun to work with ASM. Makes your life easy.

You should put even redo also as part of the DATA diskgroup.

1) DATA diskgroup = Datafiles + online redologs + controlfiles
2) RECOVERY diskgroup = just the archived redologs.

Are you using ASM on Linux platform ? ASM is optimized for Linux platform. Oracle supplies an utility called ASMLib for the Linux platform, an utility that further automates ASM management and administration.


Also when you create the diskgroup, I prefer you to use "EXTERNAL REDUNDANCY". Your organization must already be using SAN Fabric for your database. For example, if you use EMC Symmetrix as the SAN solution, you must utilize its inbuilt redundancy feature. So you let EMC handle redundancy instead of Oracle.