Donald K. Burleson Oracle Tips

Oracle9i spfile

One exciting new feature of Oracle9i is the removal of the init.ora file.  Oracle has replaced the init.ora file because all parameters can now be changed dynamically with the “alter system” commands.

By default, a new Oracle9i database will be working on a pfile, so the spfile must be created from the pfile at the SQL prompt. The spfile is created using the CREATE SPFILE statement; this requires connecting as SYSDBA.

Connect system/manager as sysdba;

CREATE SPFILE FROM PFILE;

This command creates an spfile in a non-default location ($ORACLE_HOME/database).  However, you can qully-qualigfy the path name is the “create spfile” statement:

CREATE SPFILE='/u01/admin/prod/pfile/file_mydb.ora'

FROM  

PFILE=/u01/admin/prod/pfile/initprod.ora';

Warning - After an spfile is created, when you bounce the database you may encounter an error. To get around this, you have to reconnect as SYSDBA and use the STARTUP command.

The addition of the spfile has changed the search path for the Oracle startup deck. Oracle9i now uses the following path:

• Search for the spfile$ORACLE_SID.ora file in the default location,

• Look for the spfile.ora; and

• Search for the pfile by name init$ORACLE_SID.ora.

Specifying SCOPE in parameters

Once you have an spfile, you can change any initialization parameter with the “alter system” command.  However, there is an important SCOPE parameter that you need to understand.

The SCOPE parameter has three values MEMORY, SPFILE and BOTH. Let’s look at an example of each:

Alter system set db_2k_cache_size=100m SCOPE=SPFILE;

If you want to make a change to a parameter in the spfile without affecting the current instance, you can do so using the SCOPE=SPFILE option of the ALTER SYSTEM statement. This is useful when you want to make a change starting from the next startup and not for the current instance.

Alter system set db_2k_cache_size=100m SCOPE=MEMORY;

In the example above, the SCOPE=MEMORY tells Oracle9i to make the change for the life of the instance, and to change it back to the default value the next time the database is bounced.

Alter system set db_2k_cache_size=100m SCOPE=BOTH;

When you specify SCOPE=BOTH, the change will be made immediately, and Oracle will also make the change permanent, even after the database is bounced.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm