Thursday, June 3, 2010

Question on restoring SPFILE after a complete database server crash


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?


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.


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


connect target /
connect catalog catuser/catuser@catdb
set DBID 123456789
allocate channel t1 type 'SBT_TAPE';
restore spfile;


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


Restore CONTROLFILE and update PFILE with new controlfile location

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;

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

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;

No comments:

Related Posts Plugin for WordPress, Blogger...