pfile vs spfile?

  p-file(parameter file)
                                    It is located in cd $ORACLE_HOME/dbs
                                    It is named as init$ORACLE_SID.ora
                                    It is ASCII file.
                                    we can change the parameters in pfile.

sp-file(server parameter file)
                                     It is located in cd $ORACLE_HOME/dbs
                                     It is named as spfile$ORACLE_SID.ora
                                     sp-file is binary file.
                                     we cannot change the parameters by opening sp-file.
To chack whether the instance is started with p-file or sp-file

SQL> sho parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                                     string      /u01/app/subbu/product/11.2.0.4/dbs/spfilesubbu.ora

If in the value it doesnt showed anything means then the instance is started with p-file.

NOTE:if there are 2 files pfile and spfile then by default instance will start with sp-file

To check whether the parametes is static or dynamic:
SQL> select name,issys_modifiable from v$parameter where name='sga_target';
NAME                           ISSYS_MOD
------------------------------ ---------
sga_target                     IMMEDIATE

If issys_mod = immediate we can modify.so it comes under dynamic parameters.

If issys_mod = false we cannot modify.so it comes under static parameters.

SQL> sho parameter sga_target;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_target                           big integer  412M

since instance is started with pfile and sga_target parameter is static.we cannot change it through alter command.so go to cd $ORACLE_HOME/dbs/init$ORACLE_SID.ora and edit value here and restart the database.

SQL> startup force                    (internally it will shutdown and startup)

ORACLE instance started.

Total System Global Area  523108352 bytes

Fixed Size                  1365740 bytes

Variable Size             138414356 bytes

Database Buffers          377487360 bytes

Redo Buffers                5840896 bytes

Database mounted.

Database opened.

SQL> sho parameter sga_target 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_target                           big integer 500M

If our instance is started with spfile there are 3 scopes.

                         1)both

                         2)memory

                         3)spfile

oracle provides 2 options to change the parameters of spfile

                        1)Alter System

                         2)Alter Session

Alter system set resource_limit=true scope=_________________

   1)Both---changes will be applied for current instance and future instance.

   2)memory---changes will be applied only for current instance.

   3)spfile--changes will be applied after restarting the database.