Parameter Files in Oracle
3 min readTo start a database instance, Oracle Database must read either a server parameter file, which is recommended, or a text initialization parameter file, which is a legacy implementation.
These files contain a list of configuration parameters.
To create a database manually, you must start an instance with a parameter file and then issue a CREATE DATABASE statement. Thus, the instance and parameter file can exist even when the database itself does not exist.
Server Parameter Files
A server parameter file is a repository for initialization parameters. A server parameter file has the following key characteristics: • Only Oracle Database reads and writes to the server parameter file. • Only one server parameter file exists for a database. This file must reside on the database host. • The server parameter file is binary and cannot be modified by a text editor. • Initialization parameters stored in the server parameter file are persistent. Any changes made to the parameters while a database instance is running can persist across instance shutdown and startup. A server parameter file eliminates the need to maintain multiple text initialization parameter files for client applications. A server parameter file is initially built from a text initialization parameter file using the CREATE SPFILE statement. It can also be created directly by the Database Configuration Assistant.
Text Initialization Parameter Files
A text initialization parameter file is a text file that contains a list of initialization parameters. This type of parameter file, which is a legacy implementation of the parameter file, has the following key characteristics: • When starting up or shutting down a database, the text initialization parameter file must reside on the same host as the client application that connects to the database. • A text initialization parameter file is text-based, not binary. • Oracle Database can read but not write to the text initialization parameter file. To change the parameter values you must manually alter the file with a text editor. • Changes to initialization parameter values by ALTER SYSTEM are only in effect for the current instance. You must manually update the text initialization parameter file and restart the instance for the changes to be known.
| Characteristics | Static | Dynamic |
| Requires modification of the parameter file (text or server) | Yes | No |
| Requires database instance restart before setting takes affect | Yes | No |
| Described as “Modifiable” in Oracle Database Reference initialization parameter entry | No | Yes |
| Modifiable only for the database or instance | Yes | No |
The scope of a parameter change depends on when the change takes effect. When an instance has been started with a server parameter file, you can use the ALTER SYSTEM SET statement to change values for system-level parameters as follows: • SCOPE=MEMORY Changes apply to the database instance only. The change will not persist if the database is shut down and restarted. • SCOPE=SPFILE Changes apply to the server parameter file but do not affect the current instance. Thus, the changes do not take effect until the instance is restarted. • SCOPE=BOTH Oracle Database writes changes both to memory and to the server parameter file. This is the default scope when the database is using a server parameter file.
