Oracle : SPFILE AND PFILE

                                SPFILE

 

SPFILE IS SERVER PARAMETER FILE

• SPFILE IS A SEMI BINARY FILE.
• WE CANNOT EDIT MANUALLY
• WE CAN EDIT AS DYNAMICALLY BY USING ALTER SYSTEM
• ITS SPECIALY MADE FOR RAC (REAL APPLICATION CLUSTER)
• WE CAN CREATE SPFILE AT ANY STAGE(nomount,mount,open)
• IF YOUR DATABASE IS RUNNING WITH SPFILE 80% OF THE PARAMETERS WILL BE DYNAMIC
• IN SPFILE WE HAVE THREE DIFFERENT TYPES OF SCOPES ARE THERE
• 1)SCOPE=MEMORY(PARAMETERS WILL BE EFFECT FOR CURRENT

INSTANCE)

2)SCOPE=SPFILE(PARAMETERS WILL BE EFFECT FOR UPCOMING

INSTANCE)

3)SCOPE=BOTH(PARAMETERS WILL BE EFFECT FOR CURRENT AND

UPCOMING INSTANCE)

 

SPFILE	PFILE
1)   Its an semi binary file 2)   First preference 3)   80 % dynamic parameters 20% static parameters 4)   It resides server side	1)   Its an ASCII file 2)   Second preference 3)   80% static parameters 20% dynamic parameters   4)   It resides client side

how to create spfile?

Sys> create spfile from pfile;

 

how to create pfile with spfile?

Sys>create pfile from spfile;

 

How to use spfile with scopes?

For example my resource_limit value is false

 

Alter system set resource_limit=true scope=memory;

( now the value will be true only for the current instance)

 

Alter system set resource_limit=true scope=spfile)

(now the value will be true for upcoming instance)

 

Alter system set rersource_limit=true scope=both)

(now the value will be true for current instance as well as upcoming instance)

 

If you have both pfile as well as spfile when you say startup oracle will start the database using spfile. If you want to start with pfile we need to specify manually like this

Sys>startup pfile=$ORACLE_HOME/dbs/init$ORACLE_SID.ora