Editing Your Oracle spfile

Introduction
This article will describe how to modify your Oracle spfile configuration file in Oracle 9i. It assumes that you are currently running your database using your spfile and not your pfile.

Creating a Pfile from your Spfile
First, if you want, copy your file in /dbs/init.ora (where is your oracle instance name) which will be overwritten by this process. Then log into your oracle database as sysdba. Usually from a privileged operating system account you can do this like this:

[oracle@mynode oracle]$ sqlplus /nolog


SQL> connect / as sysdba;
Connected.

Once connected, it's time to create a pfile, which you can edit with a text editor, from your current spfile. You can do this like this:

SQL> CREATE pfile FROM spfile;

File created.

SQL> quit;

Modifying Your Pfile
This created a pfile called init.ora in /dbs/, which in my case was called initORCL.ora. Now you can edit your init.ora file with a plain text editor like VI or Notepad. Once you're done editing the pfile, it's recommended that you start the database with the pfile to make sure it works and that the syntax is correct before making a spfile from it:

[oracle@mynode oracle]$ sqlplus /nolog

SQL> connect / as sysdba;
Connected.

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


SQL> startup pfile=initORCL.ora;
ORACLE instance started.

Total System Global Area  404194808 bytes
Fixed Size                   742904 bytes
Variable Size             201326592 bytes
Database Buffers          201326592 bytes
Redo Buffers                 798720 bytes
Database mounted.
Database opened.

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

Creating Your New Spfile
If all went well, then your database started up with the pfile. Next, we want to create the spfile from the pfile. Leaving your sqlplus session open you can create the new spfile, shutdown the database, and then bring it back up without the pfile parameter so that it uses the spfile like so:

SQL> connect / as sysdba;
Connected.


SQL> CREATE spfile FROM pfile;

File created.



SQL> startup;
ORACLE instance started.

Total System Global Area  404194808 bytes
Fixed Size                   742904 bytes
Variable Size             201326592 bytes
Database Buffers          201326592 bytes
Redo Buffers                 798720 bytes
Database mounted.
Database opened.

SQL>

Your oracle instance should now be running with your new spfile. Check out your database to make sure that the changes you made took effect.

As always, if you have trouble, please post your comments on this web page.
share on: facebook