Correcting invalid spfile parameters

Correcting invalid spfile parameters
tnsManager - Distribute tnsnames the easy way and for free!



Consider the following situation. An alteration is made to the spfile which results in the instance being unable to start. Because the instance will not start, the mistake can not be corrected:



SQL> show parameter sga_max_size
sga_max_size big integer 537989896

SQL> alter system set sga_max_size=99999999999 scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-27102: out of memory

SQL> startup nomount
ORA-27102: out of memory

SQL> alter system set sga_max_size=537989896 scope=spfile;
alter system set sga_max_size=537989896 scope=spfile
*
ERROR at line 1:
ORA-01034: ORACLE not available
How annoying! The usual way to fix this problem (apart from being more careful in the first place) is to:
•create pfile from spfile
•edit the pfile
•startup nomount
•create spfile from pfile
•shutdown
•startup
•remove the pfile
There is another way however - and one that I prefer. It relies on the fact that a database can have a spfile and a pfile at the same time, and furthermore parameters specified in the pfile override those in the spfile! The spfile location must be specified in the pfile for this to work. Check out the following trace:

SQL> !
oracle@bloo$ vi $ORACLE_HOME/dbs/init${ORACLE_SID}.ora

spfile=/u02/oradata/scr9/spfilescr9.ora
sga_max_size=537989896
:wq
oracle@bloo$ exit

SQL> startup
ORACLE instance started.

Total System Global Area 554767132 bytes
Fixed Size 451356 bytes
Variable Size 402653184 bytes
Database Buffers 150994944 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

SQL> alter system set sga_max_size=537989896 scope=spfile;
System altered.



SQL> !rm $ORACLE_HOME/dbs/init${ORACLE_SID}.ora
SQL>





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


Source :-