Oracle: Oops, my spfile is corrupted !!
By admin on Feb 3, 2008 in Oracle
I was trying to restart my Oracle 10g database server. Oops, the database server can be be started because the spfile is corrupted. Someone must have edited it using a text editor…
I cannnot use the alter system command to regenerate the spfile since the database is not running. What can I do ??
After some trials and errors, I resolved this by
- Connect up to your database as sysdba. You should see the message Connected to an idle instance
- Run the command CREATE pfile FROM spfile;. A new pfile will be generated.
- Edit the pfile to update the parameter you need to update.
- Run the command CREATE spfile FROM pfile; to move the changes you have just made back into the spfile.
- Startup the database normally. It should read the changed spfile and start up correctly.
Other things you can do..
To update values in spfile
E.g.
ALTER SYSTEM SET processes=50 SCOPE=spfile;
Depending on the parameters, the change may only be reflected when the database is restarted or in effect immediately.
To view what is in the spfile
If the database is running, do this
SELECT value FROM v$spparameter;
If not, then view the spfile using a text editor. Be careful not to change it !!!!
Create a pfile from an spfile
Run the following
CREATE PFILE FROM SPFILE;.
Create an spfile from a pfile
Run the following
CREATE SPFILE FROM PFILE;.
Check if you using spfile or pfile
Either the command SHOW PARAMETER spfile or SELECT value FROM v$parameter WHERE name=’spfile’; will return the path to the spfile if you are using one. If the value of spfile is blank you are not using a spfile.
NOTE:
- pfile is static text file which can be edited using a text editor. Changes are reflected only database is restarted. Changes to running database are not written to pfile.
- spfile (server parameter file) is introduced to resolve database restart problem. It cannot be edited, and is updated using ALTER SYSTEM commands.
Sorry, comments for this entry are closed at this time.