OS: Oracle Enterprise Linux 5.5
DB: Oracle 11g
On Oracle 11g, by default, database startup doesn’t read the ascii based pfile (parameter file) anymore. Instead, it searches for the binary based spfile to startup. It is actually a more secure way!
Which means that if you are tuning the DB and just changing the contents of a pfile is not going to take effect on the next startup. So, somehow we have to “modify” the spfile.
Default location for pfile and spfile of a specific SID is…
Below is an example of changing a memory size:
1. Take a look at all parameters, or a specific one…
SQL> SHOW PARAMETER
SQL> SHOW PARAMETER memory
2. Use ALTER SYSTEM SET command liked
SQL> ALTER SYSTEM SET MEMORY_TARGET = 256M;
It will take effect for the DB system until you run shutdown.
3. So, now you can create the pfile and spfile for the new changes.
SQL> CREATE PFILE = ‘/home/oracle/pfile_new.ora’ FROM MEMORY;
SQL> CREATE SPFILE = ‘$ORACLE_HOME/dbs/spfile_new.ora’ FROM MEMORY;
4. Now if you want to motify the new pfile, feel free to do so. Afterwards, you can run startup and point the DB to the new pfile.
SQL> STARTUP PFILE=’/home/oracle/pfile_new.ora’
5. Then you can backup the original pfile and spfile, create the spfile back to default location as
CREATE SPFILE FROM MEMORY;
CREATE SPFILE = ‘$ORACLE_HOME/dbs/spfile<SID>.ora’ FROM MEMORY;
Note: Of course, this is kind of a dumb way to do it. However, if you really know what you are doing, feel free to modify the original pfile at the default location and startup from that! =)