How to edit or fix corrupted spfile of Oracle database ?

Once you’re reading this post, means your database is using SPFILE (Server Parameter File) to store parameters. It might be a case, that your DB is using PFILE (Parameter File) instead. You can check this by selecting value of parameter spfile:

select value from sys.v_$parameter where name='spfile';

If you get path provided in value, means your database is using SPFILE to keep parameters. Otherwise its – PFILE case.

Technically the main difference between PFILE and SPFILE is that the latter has extra binary values for header and footer and in between. If you try to edit it and just save, most probably will experience errors, indicating that SPFILE can not be read by Oracle database instance. So correct way to change data inside SPFILE (i.e. spfile_mydb.ora) is first to edit PFILE (i.e. pfile_mydb.ora) then create it by executing command:

create spfile='/opt/oracle/database/product/12.1.0/dbhome_1/dbs/spfile_mydb.ora' from pfile='/opt/oracle/database/product/12.1.0/dbhome_1/dbs/pfile_mydb.ora'; 

What if I do not have PFILE?

Try just to create empty text file, copy data from SPFILE into new file by removing all extra binary values which will be found in SPFILE. Your aim is just to get a clean “parameter=value” list out of it. Then try to execute preceding command to create SPFILE.

What if I tried to edit SPFILE and it got corrupted?

Yes, most probably it will happen. Depends on which text editor you’re using. If you touch at least one of special binary values, file will be corrupted. But not a big deal. For fixing, scenario is actually the same as for preceding case “What if I do not have PFILE?”.

Oracle Database 12c Enterprise Edition Release

About Danas Tarnauskas