What if lost both the PFILE and SPFILE???

It’s been two months I didn’t write any articles. It’s been a continuous festive season, here in India, and a job change for me ๐Ÿ™‚

Hope I’ll continue my interest with the same pace as earlier.

Loosing SPFILE and the PFILE might be a typical scenario faced by lot of DBAs (mostly the junior DBAs): Loosing PFILE and SPFILE both.

If the Database is already running and if you lost these files, you won’t face any problems until you come across a situation to bounce the Database ๐Ÿ˜‰

If the Database is down and your task is to bring it up, you’re already dead ๐Ÿ™‚

Don’t be panic in this kind of situations, as I do ๐Ÿ™‚

I’m going to tell you a trick by following which you can make your instance alive by having at least some amount of settings of your running instance recovered.

You can find the details about the various parameters with which the instance is started in the alert log.ย Yes, the alert log collects all the parameters before the instance get started. So you can use them to construct a pfile and start your instance with that pfile like below

start pfile='<path to PFILE>’

after that you can generate the SPFILE as well using

create spfile from pfile

One caveat to this is, you couldn’t recover the setting you made ‘on the fly’. i.e. the settings you made to the spfile directly while the instance is running.

If you’re smart and already configured SPFILE auto backup, you don’t need all the things mentioned above. You could just restore the SPFILE from the auto backup.

See the instructions below to do that (courtesy Oracle Documentationย )

To restore the server parameter file:

  1. If the database is up at the time of the loss of the SPFILE, connect to the target database. For example, run:
    % rman TARGET /

    If the database is not up when the SPFILE is lost, and you are not using a recovery catalog, then you must set the DBID of the target database. See“Determining your DBID”ย for details on determining your DBID.

  2. Shut down the instance and restart it without mounting. When the SPFILE is not available, RMAN starts the instance with a dummy parameter file. For example:
    RMAN> STARTUP FORCE NOMOUNT;
  3. Restore the server parameter file. If restoring to the default location, then run:
      RMAN> RESTORE SPFILE FROM AUTOBACKUP;

If restoring to a nondefault location, then you could run commands as in the following example:

      RMAN> RESTORE SPFILE TO '/tmp/spfileTEMP.ora' FROM AUTOBACKUP;

Hope you enjoyed it.

I always welcome your feedback and suggestions.

Thanks,

CSM

Advertisements

2 Responses to What if lost both the PFILE and SPFILE???

  1. Pingback: Can we start the Database with both the spfile and pfile lost?? | queryCSM

  2. Siva says:

    Nice Artical,. keep it up…

    Regards,
    Siva.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: