A few basic notes on Oracle Database Administration.

Friday, May 2, 2008

Moving back to a lost spfile

We had an unexpected outage last saturday morning, and the person who restarted the database inadvertantly did so with an old pfile instead of the spfile - which was lost (not because of the restart). How to get back to the correct set of parameters. EM to the rescue. Looking at all the parameters in EM it shows which have been changed and when. I could then alter the parameters back to where they should be. The dynamic parameters were easy - i.e.


ALTER SYSTEM SET parallel_max_servers = 280;

I then created an spfile from the pfile with only the dynamic parameters corrected.

create pfile from spfile;

But I was looking at an out of date article which put the had an error about the default location of the spfile.

SELECT name, value FROM v$parameter WHERE name = 'spfile';

gave me the actual location of my spfile, which being in the default location was in
$ORACLE_HOME/dbs/spfileSID.ora



Then bounced the database and brought it up with the spfile.

But, of course, the static parameters need a bounce to the database.

I altered the parameters scope = spfile eg:
ALTER SYSTEM SET processes = 300 SCOPE=SPFILE;

and then rebounced the database to apply the dynamic changes also to the current instance.

THEN I BACKED UP THE SPFILE!




(now if only I could control the fonts on this blog, all would be well.)

2 comments:

Tonguç said...

we had a similar case recently, for us alert.log was the savior, we copied the parameters from the last successful startup of alert.log and pasted into a pfile, created an spfile from that pfile and started the instance.

girlgeek said...

Thanks Tonguc, I'll remember that one. -Claudia