What if you lost both spfile and pfile of the instance when it is up and running. Suppose you don’t have any type of rman or autobackup also. How can you restore that. Let’s see the below example.

We will recreate the scenario by deleting the spfile:

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/app/oracle/product/12.
                                                 1.0/dbhome_1/dbs/spfileSBIP18D
                                                 B.ora
SQL> exit



ls -ltr /oracle/app/oracle/product/12.1.0/dbhome_1/dbs/spfileSBIP18DB.ora
-rw-r-----   1 oracle   oinstall    3584 Jun  6 11:04 /oracle/app/oracle/product/12.1.0/dbhome_1/dbs/spfileSBIP18DB.ora

# rm -rf /oracle/app/oracle/product/12.1.0/dbhome_1/dbs/spfileSBIP18DB.ora

At this point, any attempt to do changes in spfile will throw an error.

SQL> alter system set open_cursors=500 scope=both;
alter system set open_cursors=500 scope=both
*
ERROR at line 1:
ORA-01565: error in identifying file
'/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/spfileSBIP18DB.ora'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

From 11g onward, we can restore the spfile or pfile from the memory.

SQL> create spfile='/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/spfileSBIP18DB.ora' from memory;
create spfile='/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/spfileSBIP18DB.ora' from memory
*
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance

It is throwing error. Because those the spfile is physically present, Database engine still thinks it is using that.

So we will restore the spfile to a temp location.

SQL> create spfile='/tmp/spfileSBIP18DB.ora' from memory;

File created.

Now just copy the new spfile to the original location $ORACLE_HOME/dbs locaiton.

cp /tmp/spfileSBIP18DB.ora /oracle/app/oracle/product/12.1.0/dbhome_1/dbs/spfileSBIP18DB.ora

Now try to alter any parameter using spfile:

SQL> alter system set open_cursors=800 scope=both;

System altered.

Voila!!! We have restored the spfile , without having a rman backup and without any downtime. 🙂