How to change DB Initialization Parameters?

 

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…

$ORACLE_HOME/dbs/init<SID>.ora
$ORACLE_HOME/dbs/spfile<SID>.ora

 

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;

             or

       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!  =)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s