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:

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

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

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.

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

Now try to alter any parameter using spfile:

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