Wednesday, April 29, 2009

Identify the usage of pfile or spfile

In oracle 9i and up spfile is the preferred way to start the instance.
In order to see if an instance has started with pfile or spfile as sys using sqlplus run :

select decode(count(*), 1, 'spfile', 'pfile' ) init_file_type
from v$spparameter whe rerownum=1 and isspecified='TRUE';

Also you can use the show parameter spfile.

In order to create a spfile from pfile the instance must be down.
Then run

create spfile from pfile; to use the default location which is $ORACLE_HOME/dbs on Unix and %ORACLE_HOME%/database on Windows.

In order to create the pfile if the instance has started with spfile run:

Create pfile from spfile;

2 comments:

  1. "In order to create a spfile from pfile the instance must be down.
    Then run "


    why do u need to have the instance down. if the instance is down .. where will you run.

    ReplyDelete
  2. You always can connect with sqlplus when instance is down. How you startup the instance ??

    ReplyDelete