This post explains the steps required to recover when the parameter files are lost. The parameter files are read at instance startup time to get instance specific characteristics.
Following are the various scenarios of recovering from the lost parameter file
SPFILE is lost but the pfile is present
1. Rename bad or corrupted spfile from the original location. Then shutdown and restart your database with pfile (init.ora)
SQL> STARTUP PFILE='location/init[SID].ora';
2. Then finally create spfile from this pfile. The below command will automatically create spfile with its original name.
SQL> Create spfile from pfile='location/init[SID].ora';
Both spfile, as well as pfile, is lost or corrupted and your database is up
1. Parameter files are read while starting up the instance. Hence losing parameter files when the database is up and running won’t have a huge impact (till next shutdown).
2. Query v$parameter for the non default parameters:
SQL> select name,value from v$parameter where ISDEFAULT=’FALSE’ order by name;
3. Create pfile with these parameters:
SQL> Create spfile from pfile='location/init[SID].ora';
4. Restart the database with this newly created spfile:
From 11g, you can directly create the pfile or spfile using the FROM MEMORY clause.
CREATE PFILE [= 'pfile_name' ] FROM { { SPFILE [= 'spfile_name'] } | MEMORY } ;
CREATE SPFILE [= 'spfile_name' ] FROM { { PFILE [= 'pfile_name' ] } | MEMORY } ;
Example:
SQL>create pfile='$ORACLE_HOME/dbs/initdb11g.txt' from memory; File created.
Both spfile, as well as pfile, is lost or corrupted and your database is down
1. In that case, you have remaining option to create new pfile using the non-default parameters recorded in the alert.log file. You can find this in the previous successful startup entries.
2. You can then start the database using this pfile (init.ora):
SQL> STARTUP PFILE='location/init.ora';
3. Then finally create spfile from this pfile. The below command will automatically create spfile with its original name.
SQL> Create spfile from pfile='location/init[SID].ora';
4. IF RMAN autobackup is configured in any of the above situations, you can restore the same.
RMAN> RESTORE SPFILE FROM AUTOBACKUP;