How to change database intialization parameter ?

posted 30 Sep 2010, 02:52 by Manish Gupta   [ updated 30 Sep 2010, 07:18 ]
This article is generic in nature, but is very helpful to understand the concept for running the 11g rcu schema creation/management utility. Oracle fusion middle ware 11g rcu has quiet a few system pre-requisites which requires changes in default database initialization parameter.

Directory location on Linux Machine : $ORACLE_HOME/dbs

Initialization parameter file type :
  • Text   (Parameter file or pfile)
    • Can be edited using text editor.
    • Typical file name initSID.ora , where SID is your DB SID.
  • Binary (Server parameter file or spfile)
    • Can be modified using ALTER SYSTEM commands on sqlplus
    • Changes persist across startup/shutdown.
    • Typical file name spfileSID.ora, where SID is your DB SID.

IMPORTANT NOTE : Sequence of running the commands below is important. Please take a backup of all the file present in $ORACLE_HOME/dbs directory. Important to understand that oracle database relies on spfile, and pfile is just a text file version of binary spfile. So whatever you do, it should ultimately be in synch otherwise you will loose the changes made at some point of time.

Option 1 : To modify initialization parameter, run ALTER SYSTEM command. e.g. below is to change open_cursors.

$ sqlplus /nolog

sql>connect / as sysadmin
sql>alter system set open_cursors=500 scope=both;

Option 2 : To modify initialization parameter in text file if ALTER SYSTEM command doesn't support them.

sql>create pfile from spfile

This is to make sure that current pfile is in synch with spfile (binary)
Now Open the initSID.ora file in vi editor and change the processes parameter value to 300, or 400 or ... based on your need.
$ vi initSID.ora

Recreate spfile from this modified pfile.

sql>create spfile from pfile
This will create a new file spfileorcl.ora in $ORACLE_HOME/dbs dir.