2.3. Managing Parameter Initialization Files

Oracle uses parameter initialization files to store information about initialization parameters used when an Oracle instance starts. Oracle reads the parameter file to obtain information about how the Oracle instance should be sized and configured upon startup.

As you learned in Chapter 1, the parameter file can be either a plain text file, commonly referred to as a PFILE, or it can be a binary parameter file, commonly referred to as an SPFILE. The default location that Oracle searches to find the PFILE and SPFILE parameter files is $ORACLE_HOME/dbs on Unix systems and %ORACLE_HOME%/database on Windows systems.

Oracle uses a search hierarchy when a startup command is issued without specifying either a PFILE or an SPFILE. Oracle first looks for a parameter file called spfile$ORACLE_SID.ora. If it doesn't find that, it searches for spfile.ora. Finally it searches for a traditional text PFILE with the default name of init$ORACLE_SID.ora.

If the parameter files do not exist in the default location or you want to use a different parameter file to start your database, you can specify a parameter file to use when you issue a startup command to start the Oracle database.

In some instances, you may need to change the initialization parameters. For example, you might need to increase the number of sessions allowed to connect to the database because you are adding users. Whatever the case, you need to know how to make these changes.

You can make changes manually, or you can use the EM Database Control tool to modify the parameters. To use the EM Database Control tool to modify existing database parameters, navigate to the Administration menu. In the Instance section, you can modify your initialization parameters. If you choose All Initialization Parameters, you will see a list of all the parameters that can be modified (see Figure 2.29).

Figure 2.29. The EM Database Control Initialization Parameters screen

The Initialization Parameters screen has two tabs: Current and SPFile. The Current tab displays all the currently active settings for initialization parameters for the database instance. If a parameter is marked Dynamic, you can modify it, and this modification immediately affects the parameter that affects the currently running instance without stopping the database. The changes you make from the Current tab are not permanent, so the next time the database is stopped and restarted, the settings revert to their original values.

If you are using a server parameter file, or SPFILE, you will see the SPFile tab. This screen also lets you change existing database parameters. The difference between changing parameters in this screen and changing parameters in the Current tab is that changes to the SPFILE are persistent across database startups and shutdowns because the changes are saved to the SPFILE definition. You can also apply your changes to the SPFILE only or to the SPFILE and the currently running instance.