Sometimes, the SPFILE gets damaged. When this happens, the SPFILE can be replaced by a new one, generated from the PFILE file. The PFILE file is made automatically when the database is created and it can be found in the folder ORACLE_HOME\admin\SID\pfile.
The new SPFILE can be generated as follows:
- connect with sqlplus to the instance as sysdba
- use one of the following commands:
SQL> CREATE SPFILE FROM PFILE = 'ORACLE_HOME\admin\SID\pfile \my_pfile.ora';
Using this command Oracle will assume you are creating a default SPFILE. If a default SPFILE already exists Oracle will overwrite it.
Or you can use
SQL> CREATE SPFILE = 'desired_directory\my_spfile.ora'
FROM PFILE = 'ORACLE_HOME\admin\SID\pfile\my_pfile.ora';
Using this, a non-default SPFILE will be created. The name of the new SPFILE must not be the same as the SPFILE that was used to start the current instance.
For replacing the old SPFILE just delete the old one from ORACLE_HOME\ ora92\database\directory, copy here the new one and rename it with the name of the original SPFILE. After that you can start the instance and it will start with the parameters from the new SPFILE.
