Info : Quick Process to edit any parameter of SPFILE

************************
Steps to edit spfile parameter. SPFILE should not be modified directly, follow below procedure...
************************

Note: Need to convert pfile, Edit the file and replace with Spfile


a) Creating the PFILE From SPFILE
SQL> create pfile='pfile' from spfile;

b) Taking backup of existing SPFILE as OS File.
SQL>! cp /kiranginni/oradata/data01/spfilekiranginni.ora /kiranginni/oradata/data01/spfilekiranginni.ora_one

c) COMMAND to open newly created pfile to edit
SQL> !vi $ORACLE_HOME/dbs/pfile

d) SAVE & QUIT the modified information in PFILE.

e) Bounce the Database to get latest parameter getting effective.
shutdown immediate;

f) Now creating SPFILE back from PFILE (At this step we are getting modified entries to SPFILE).

g) startup

h) Verification

i) Remove the temporary pfile


-----------
Ex:- Here we are going to see that 102 parameter in spfile should be changed to 112 after Upgrade.
=========

Query V$PARAMETER

select name, value
from v$parameter
where value like '%/102%' ; SQL> 2 3
core_dump_dest
/kiranginni/oracle/product/102/admin/kiranginni_auohsppol03/cdump


SQL> !ls -l /kiranginni/oracle/product/102/admin/kiranginni_auohsppol03/cdump
ls: /kiranginni/oracle/product/102/admin/kiranginni_auohsppol03/cdump: No such file or directory

SQL> create pfile='pfile' from spfile;

File created.

SQL> sho parameter pfile
spfile string /kiranginni/oradata/data01/spfileP
PPOLI.ora
SQL> !cp /kiranginni/oradata/data01/spfilekiranginni.ora /kiranginni/oradata/data01/spfilekiranginni.ora_one

SQL> !ls -ltrd /kiranginni/oradata/data01/spfilekiranginni.ora /kiranginni/oradata/data01/spfilekiranginni.ora_one
-rw-r----- 1 orkiranginni dbkiranginni 6656 Jul 16 21:12 /kiranginni/oradata/data01/spfilekiranginni.ora
-rw-r----- 1 orkiranginni dbkiranginni 6656 Jul 17 00:58 /kiranginni/oradata/data01/spfilekiranginni.ora_one




Modified:
SQL> !vi $ORACLE_HOME/dbs/pfile



SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile='pfile';

File created.

SQL> startup
ORACLE instance started.
Total System Global Area 2421977088 bytes
Fixed Size 2228800 bytes
Variable Size 1140854208 bytes
Database Buffers 1258291200 bytes
Redo Buffers 20602880 bytes
Database mounted.
Database opened.


Verify:
SQL> select name, value
from v$parameter
where value like '%/102%'
2 3 4
SQL> /

no rows selected


>> Removed the temporary pfile
rm $ORACLE_HOME/dbs/pfile

SQL> sho parameter pfile
spfile string /kiranginni/oracle/product/112/dbs
/spfilekiranginni.ora


Done with the action plan. Now parameter got changed as well as SPFILE is kept as default parameter file.



Thnks
kiran ginni