20
Jul
06

How to switch between SPFile and PFile

Any DBA who worked on oracle 8i knows where to find init[sid].ora.

The SPFile is located in the same folder just like the Pfile.

Except… It’s not manually managed. It’s managed by oracle. This is done to avoid human error in parameters.

There can be problems in parameters though. During the migration, a parameter (DB_BUFFER_SIZE), from the 8i era was left in the parameter file and was conflicting with another one (DB_CACHE_SIZE) in the parameter file.

When restarted, the database couldn’t even mount…

Don’t worry, when you get that error, there’ll be a list of conflicting parameters.

Usually, you should be able to get into OEM and remove the bad parameter then it would be possible to start again. But it was my luck and OEM was not working too.

There’s only one way out of it. It is possible to get back to the old 8i style and use an init.ora file which can be modified manually.

To do so, connect as sysdba on command line:

C:\SET ORACLE_SID = [Your Sid]

C:\SQLPLUS / as sysdba

SQL>CREATE PFILE=’C:\WHEREVER_YOU_WANT_IT_TO_BE]\INIT[SID]’ FROM SPFILE=’C:\WHERE_IT_IS\SPFILE[SID].ORA’

As it says, it will create the Pfile from the SPfile. Then it is manually manageable. When the culprit is removed, simply do the opposite thing:

SQL>CREATE SPFILE=’C:\[WHERE_IT_SHOULD_BE]\INIT[SID]’ FROM SPFILE=’C:\WHERE_IT_SHOULD_BE_TOO\SPFILE[SID].ORA’

This will create a new spfile from the modified init[sid].ora

Now all what is left, is to start the database

SQL>STARTUP

Advertisements

7 Responses to “How to switch between SPFile and PFile”


  1. 1 Mahesh
    June 28, 2007 at 2:06 am

    Hi Nicolas,

    I could not get last create statement.
    You have mentioned to create
    SPFILE=INIT[SID] from SPFILE=SPFILE[SID].ora
    and after that you are saying,
    this will create a new spfile from init[sid].ora
    But it is looking that init[sid].ora file will be created.

    Or is it because of copy/paste from earlier command ?

  2. August 16, 2007 at 7:07 am

    There is an error in there.

    You should read from PFILE to SPFILE or opposite.

  3. May 5, 2008 at 7:07 pm

    6bSgp0 Hello! I’m Samuel Smith, i’m from Switqerland i and find your site really brilliant!

  4. 4 Vijay
    May 12, 2008 at 10:30 am

    Hi,

    I am Vijay, While creating spfile from pfile in oracle 8i i got an error like below:

    ERROR:
    ORA-01034: ORACLE not available
    ORA-27101: shared memory realm does not exist

    I try it after shutdown the database. I am unable to open sqlplus from command prompt. I am only able to connect from . How can i create spfile from pfile in this situation…. Can you give the solution for this….

    Regards
    Vijay

  5. May 22, 2008 at 2:39 pm

    Hi.

    Shared memory Real does not exist means that your database is not working at all.

    Try run the command

    SQL>Startup mount

    then you’ll be able to start doing things with it.

    If you can’t startup your DB then you have more than merely a SPFILE problem.

    Nico

  6. 6 The Other Guy
    September 27, 2011 at 12:51 pm

    Awesome! This post saved my bacon


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: