User Managed Recovery - spfile

There are three scenarios when you loose parameter file and how to recover using hot backups.

Scenario 1 : When you have parameter file backup


Let us simulate a failure. We assume that we already have taken the hot backup.

Step 1 : DB is up and running


Step 2 : Delete both pfile and spfile under $ORACLE_HOME/dbs location



Step 3 : Connect to sqlplus and shutdown the database



Step 4 : Now start the database and it should throw below error



Step 5 :Exit the sqlplus



Step 6 : Go to hot backup location and Copy parameter file (pfile) to $ORACLE_HOME/dbs location



Step 7 : Connect to sqlplus and start your database!





Scenario 2: When you do not have parameter file backup


Let us assume we do not have hot backup of the parameter files, then follow below method to recovery your parameter file

Step 1 : Go to alert log location
(Generally /u01/app/oracle/diag/rdbms/dev/dev/trace)

Step 2 : Cat the alert log file (cat alert_dev.log)



Step 3 : Find last time database was started


Step 4 : Copy all the non-default parameters into notepad and Create a pfile
under $ORACLE_HOME/dbs location

Step 5 : The file name should be initdev.ora and Paste all contents from notepad


Step 6 : Start the database!



Scenario 3 : When you do not have parameter file backup in 11g


From 11g onwards, you can recreate parameter file in case the database is up and running. Even if you loose parameter file but database instance is still running, we can recreate parameter file from memory :

CREATE PFILE FROM MEMORY;





(User Managed Recovery - redo log file)