How to modify a parameter in SPFILE

Method 1:

1. Run
   

   ALTER SYSTEM SET processes=(processes + 5) SCOPE=SPFILE SID='*';
   

2. Reboot (Issue shutdown and startup)

if it is not a parameter that can be modified via ALTER SYSTEM then use:

Method 2:

1. Run
   

   CREATE PFILE='$ORACLE_HOME/dbs/initSID.ora' FROM SPFILE='location/spfile.ora';
   

   this will create PFILE called initSID.ora at $ORACLE_HOME/dbs
2. Make changes to initSID.ora file via vi editor



3. Shutdown the database



4. Startup (nomount) the instance where you created and altered the pfile using this pfile only




STARTUP PFILE=$ORACLE_HOME/dbs/initSID.ora

5. Create a new spfile




 CREATE SPFILE='location/spfile.ora' FROM PFILE=‘$ORACLE_HOME/dbs/initSID.ora’;

6. Shutdown this instance again



7. Now startup normally without PFILE or SPFILE option:




STARTUP

8. Remove the PFILE (Optional)



9. To confirm that the parameter has been modified, issue following sql statement:




SHOW PARAMETER