ORACLE DATABASE PARAMETERS AND PARAMETER FILES
Introduction
Oracle Database uses initialization parameters to control how the database instance operates.
These parameters control areas such as:
- Memory allocation
- Database name
- Control files
- Processes
- Sessions
- Archive log configuration
- Optimizer behavior
- Audit configuration
- Recovery settings
- Network-related behavior
- Instance configuration
For an Oracle DBA, understanding parameters and parameter files is very important because many database configuration and troubleshooting activities depend on them.
1. What is an Oracle Parameter?
An Oracle initialization parameter is a configuration value that controls the behavior of an Oracle instance. For example:
DB_NAME MEMORY_TARGET SGA_TARGET PGA_AGGREGATE_TARGET PROCESSES CONTROL_FILES UNDO_TABLESPACE
SHOW PARAMETER DB_NAME;
Output may look like:
NAME TYPE VALUE --------- ------ ------- db_name string ORCL
The parameter tells Oracle which database name is associated with the instance.
2. Why Do We Need Parameters?
Parameters are used to configure and control the Oracle instance. For example:
Memory
SGA_TARGET PGA_AGGREGATE_TARGET MEMORY_TARGET
Processes
PROCESSES SESSIONS TRANSACTIONS
Database Files
CONTROL_FILES DB_CREATE_FILE_DEST DB_RECOVERY_FILE_DEST
Database Identity
DB_NAME DB_UNIQUE_NAME INSTANCE_NAME
Recovery
LOG_ARCHIVE_DEST_1 LOG_ARCHIVE_DEST_2 LOG_ARCHIVE_FORMAT
3. What is a Parameter File?
A parameter file is a file that contains initialization parameters used when starting an Oracle instance.
Oracle mainly uses two types of parameter files:
1. PFILE
2. SPFILE
Parameter Files
|
+---------+---------+
| |
v v
PFILE SPFILE
Text File Server Parameter File4. PFILE
PFILE stands for Parameter File. It is also called an init.ora file. A PFILE is a normal text file containing Oracle initialization parameters.
*.db_name='ORCL' *.control_files='/u01/oradata/ORCL/control01.ctl' *.memory_target=4G *.processes=500
The DBA can open and edit a PFILE using normal Linux commands. For example:
vi initORCL.ora
Characteristics of PFILE
- It is a text file.
- It can be manually edited.
- Changes made to the file are not automatically applied to a running instance.
- The instance can be started using a PFILE.
- It is useful for troubleshooting when the SPFILE cannot be used.
STARTUP PFILE='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initORCL.ora';
6. SPFILE
SPFILE stands for Server Parameter File. It is Oracle's binary parameter file.
Unlike a PFILE, an SPFILE should normally be modified using SQL commands rather than manually editing the file with an operating-system text editor.
ALTER SYSTEM SET processes=500 SCOPE=SPFILE;
Oracle stores the parameter change in the SPFILE.
7. PFILE vs SPFILE
| PFILE | SPFILE |
|---|---|
| Text file | Binary file |
| Can be manually edited | Should not be manually edited |
| Traditionally called init.ora | Server Parameter File |
| Changes require appropriate restart/reload depending on parameter | Supports ALTER SYSTEM SET |
| Useful for troubleshooting | Preferred for normal database configuration |
Can be used with STARTUP PFILE | Usually used automatically during startup |
8. Default Location of Parameter Files
For Oracle Database on Linux, the default parameter file location is normally:
$ORACLE_HOME/dbs
For example:
/u01/app/oracle/product/19.0.0/dbhome_1/dbs
You may find files such as:
spfileORCL.ora initORCL.ora
The exact location can vary depending on the Oracle installation and environment.
9. How to Check Which Parameter File is Being Used
One useful query is:
SHOW PARAMETER SPFILE;
Example:
NAME TYPE VALUE ------- ------- -------------------------------- spfile string /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileORCL.ora
If the VALUE is populated, the instance was started using an SPFILE. You can also query:
SELECT value FROM v$parameter WHERE name = 'spfile';
10. Check All Parameters
To display parameters using SQL*Plus:
SHOW PARAMETER;
You can also search for a specific parameter. For example:
SHOW PARAMETER SGA; SHOW PARAMETER PGA; SHOW PARAMETER MEMORY; SHOW PARAMETER CONTROL_FILES;
11. V$PARAMETER
The dynamic performance view V$PARAMETER can be used to view the initialization parameters currently in effect for the instance.
SELECT name, value FROM v$parameter WHERE name = 'processes'; SELECT name, value FROM v$parameter WHERE name LIKE '%sga%';
12. V$SPPARAMETER
V$SPPARAMETER displays parameter values from the server parameter file.
SELECT name, value FROM v$spparameter WHERE name = 'processes';
This is useful when you want to compare the parameter currently being used by the instance with the value stored in the SPFILE.
13. Current Value vs SPFILE Value
This is an important DBA concept. Suppose:
SHOW PARAMETER PROCESSES;
shows VALUE = 300, but the SPFILE contains 500. The two values may differ depending on how the parameter was changed and whether a restart is required. You can investigate both using:
SELECT name, value FROM v$parameter WHERE name = 'processes'; SELECT name, value FROM v$spparameter WHERE name = 'processes';
14. Static and Dynamic Parameters
Oracle initialization parameters can broadly be classified as:
1. Dynamic Parameters 2. Static Parameters
15. Dynamic Parameter
A dynamic parameter can be changed while the database instance is running.
ALTER SYSTEM SET SGA_TARGET=4G SCOPE=BOTH;
If the parameter supports dynamic modification, the new value can take effect without restarting the instance. However, the exact behavior depends on the parameter and the selected SCOPE.
16. Static Parameter
A static parameter cannot be changed for the running instance. A restart is required. For example, if a parameter requires a restart:
ALTER SYSTEM SET processes=500 SCOPE=SPFILE;
The value is stored in the SPFILE but does not become active in the current instance. Then:
SHUTDOWN IMMEDIATE; STARTUP;
After startup, the new value becomes active.
17. SCOPE — MEMORY, SPFILE and BOTH
One of the most important Oracle DBA interview topics is:
SCOPE=MEMORY SCOPE=SPFILE SCOPE=BOTH
SCOPE=MEMORY
The change is applied only to the running instance memory.
ALTER SYSTEM SET some_parameter=value SCOPE=MEMORY;
The change does not persist in the SPFILE. After restarting the database, the parameter returns to its previous persistent value.
Running Instance
|
v
MEMORY
|
v
Change Active18. SCOPE=SPFILE
The change is written to the SPFILE.
ALTER SYSTEM SET some_parameter=value SCOPE=SPFILE;
The current running instance is not changed. The new value becomes effective after the appropriate restart.
SPFILE | v New Value Stored | v Restart Database | v New Value Active
This is commonly used for static parameters.
19. SCOPE=BOTH
The change is applied to the running instance and also persisted in the SPFILE.
ALTER SYSTEM SET some_parameter=value SCOPE=BOTH;
ALTER SYSTEM
|
+-------+-------+
| |
v v
MEMORY SPFILE
| |
v v
Active Immediately PersistentSCOPE=BOTH can be used only when the parameter supports dynamic modification and the instance was started with an SPFILE.
20. SCOPE Comparison
| Scope | Current Instance | SPFILE | Restart Required |
|---|---|---|---|
| MEMORY | Yes | No | No |
| SPFILE | No | Yes | Usually yes |
| BOTH | Yes | Yes | No for dynamic parameters |
21–22. SCOPE Examples
Example — Changing SGA Target
Suppose:
SHOW PARAMETER SGA_TARGET;
Output: sga_target = 2G. We want to change it to 4 GB. If supported:
ALTER SYSTEM SET sga_target=4G SCOPE=BOTH;
Now: Memory → 4G, SPFILE → 4G. The setting remains after restart.
Example — Static Parameter
Suppose we need to change a parameter that requires a restart.
ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;
Oracle stores the new value in the SPFILE. Check:
SELECT name, value FROM v$spparameter WHERE name='processes';
Then restart:
SHUTDOWN IMMEDIATE; STARTUP;
Check: SHOW PARAMETER PROCESSES; — the new value is now active.
23–24. Creating a PFILE from SPFILE / SPFILE from PFILE
This is an important DBA operation. You can create a text PFILE from the current SPFILE using:
CREATE PFILE='/tmp/initORCL.ora' FROM SPFILE;
SPFILE | v CREATE PFILE | v /tmp/initORCL.ora
Now the PFILE can be viewed:
cat /tmp/initORCL.ora
The reverse operation is also possible:
CREATE SPFILE FROM PFILE='/tmp/initORCL.ora'; -- Or specify a destination: CREATE SPFILE='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileORCL.ora' FROM PFILE='/tmp/initORCL.ora';
Flow:
PFILE | v CREATE SPFILE | v SPFILE
25. Starting Database Using PFILE
If you want to explicitly start the database using a PFILE:
STARTUP PFILE='/tmp/initORCL.ora';
Oracle reads the initialization parameters from the specified PFILE. This is particularly useful when troubleshooting an SPFILE problem.
26. Startup Without Specifying PFILE
Normally, when you execute STARTUP;, Oracle looks for the appropriate initialization parameter file in the standard location, for example $ORACLE_HOME/dbs. If a suitable SPFILE is present, Oracle normally uses it. If not, Oracle can use a suitable traditional initialization parameter file if available.
27. Parameter File Startup Flow
STARTUP
|
v
Find Initialization
Parameters
|
+--------+--------+
| |
v v
SPFILE PFILE
| |
+--------+--------+
|
v
Allocate Memory
|
v
Start Background
Processes
|
v
Mount Database
|
v
Open Database
28. Important Memory Parameters
SGA_TARGET
Controls the target size of the SGA when automatic shared memory management is used.
SHOW PARAMETER SGA_TARGET;
SGA_MAX_SIZE
Specifies the maximum SGA size that can be allocated for the instance under the applicable configuration.
SHOW PARAMETER SGA_MAX_SIZE;
PGA_AGGREGATE_TARGET
Specifies the target aggregate PGA memory for the instance.
SHOW PARAMETER PGA_AGGREGATE_TARGET;
MEMORY_TARGET
Controls the total target memory for the Oracle instance when Automatic Memory Management is used.
SHOW PARAMETER MEMORY_TARGET;
MEMORY_MAX_TARGET
Specifies the maximum value to which MEMORY_TARGET can be set.
29. Important Database Identity Parameters
DB_NAME
Identifies the database name.
SHOW PARAMETER DB_NAME;
DB_UNIQUE_NAME
Provides a unique database identifier, especially important in environments such as Data Guard.
SHOW PARAMETER DB_UNIQUE_NAME;
INSTANCE_NAME
Identifies the instance name.
SHOW PARAMETER INSTANCE_NAME;
Example:
DB_NAME = ORCL DB_UNIQUE_NAME = ORCL INSTANCE_NAME = ORCL
In RAC, DB_NAME is associated with the database while each instance has its own instance identity.
30. CONTROL_FILES Parameter
The CONTROL_FILES parameter specifies the locations of the control files. Check:
SHOW PARAMETER CONTROL_FILES;
Example:
/u01/oradata/ORCL/control01.ctl /u02/oradata/ORCL/control02.ctl
Oracle uses the control files to maintain important database metadata, for example:
- Database structure
- Datafile information
- Redo log information
- Checkpoint information
- Backup and recovery-related metadata
31. PROCESSES Parameter
The PROCESSES parameter controls the maximum number of operating system user processes that can connect to the Oracle instance, subject to Oracle's process management architecture.
SHOW PARAMETER PROCESSES;
Example:
ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;
If the parameter requires a restart in your Oracle version/configuration:
SHUTDOWN IMMEDIATE; STARTUP;
32. SESSIONS Parameter
The SESSIONS parameter controls the maximum number of sessions that can be connected to the instance.
SHOW PARAMETER SESSIONS;
Oracle's relationship between PROCESSES and SESSIONS depends on the Oracle version and configuration. Do not assume a fixed formula without checking the specific database version.
33. UNDO_TABLESPACE Parameter
The UNDO_TABLESPACE parameter identifies the default undo tablespace for an instance.
SHOW PARAMETER UNDO_TABLESPACE;
Example: undo_tablespace = UNDOTBS1
Undo is required for operations such as:
- Transaction rollback
- Read consistency
- Flashback-related operations
- Recovery-related processing
34. ARCHIVE LOG Parameters
In an Oracle DBA environment, archive log parameters are especially important. For example:
SHOW PARAMETER LOG_ARCHIVE_DEST;
You can check:
SHOW PARAMETER LOG_ARCHIVE;
Important parameters may include:
LOG_ARCHIVE_DEST_1 LOG_ARCHIVE_DEST_2 LOG_ARCHIVE_FORMAT LOG_ARCHIVE_MAX_PROCESSES
These parameters are important when configuring and troubleshooting Oracle ARCHIVELOG mode and Data Guard.
35–36. How to Check and Change a Parameter
Using SHOW PARAMETER:
SHOW PARAMETER PROCESSES;
Using V$PARAMETER:
SELECT name, value FROM v$parameter WHERE name = 'processes';
Using V$SPPARAMETER:
SELECT name, value FROM v$spparameter WHERE name = 'processes';
This gives the DBA multiple ways to investigate parameter configuration.
To change a parameter, the general syntax is:
ALTER SYSTEM SET parameter_name=value SCOPE=scope;
Examples:
ALTER SYSTEM SET pga_aggregate_target=4G SCOPE=BOTH; ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;
Always verify whether the parameter is dynamic or static before selecting the appropriate scope.
37. How to Reset a Parameter
If a parameter was explicitly set in the SPFILE and you want Oracle to use its default behavior, you can reset it.
ALTER SYSTEM RESET parameter_name SCOPE=SPFILE; -- For example: ALTER SYSTEM RESET some_parameter SCOPE=SPFILE;
A restart may be required depending on the parameter.
38. PFILE Troubleshooting Scenario
Scenario
The database does not start because the SPFILE contains an incorrect parameter. For example:
ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file
or another parameter-related startup error. A DBA can use a PFILE for troubleshooting. First, create or obtain a valid PFILE:
CREATE PFILE='/tmp/initORCL.ora' FROM SPFILE;
Edit the PFILE:
vi /tmp/initORCL.ora
Then start the database using:
STARTUP PFILE='/tmp/initORCL.ora';
After successfully starting and validating the configuration, the DBA can recreate the SPFILE if required.
39. Important Difference: Parameter vs Parameter File
This is a common interview question.
Parameter
A parameter is a configuration value. Example: PROCESSES=500
Parameter File
A parameter file is the file that stores initialization parameter values. Example: spfileORCL.ora
Parameter | v PROCESSES=500 Parameter File | v spfileORCL.ora | +-- PROCESSES=500 +-- SGA_TARGET=4G +-- CONTROL_FILES=... +-- DB_NAME=ORCL
40. PFILE and SPFILE — Interview Questions
- Q1. What is a PFILE?
A PFILE is a text-based initialization parameter file used to provide instance startup parameters. - Q2. What is an SPFILE?
An SPFILE is a server parameter file maintained by Oracle and normally modified usingALTER SYSTEM. - Q3. What is the difference between PFILE and SPFILE?
PFILE → Text → Manually Editable. SPFILE → Binary → Oracle Managed. - Q4. How do you check whether the database is using an SPFILE?
SHOW PARAMETER SPFILE;If a path is displayed, the instance is using an SPFILE. - Q5. How do you create a PFILE from SPFILE?
CREATE PFILE='/tmp/initORCL.ora' FROM SPFILE; - Q6. How do you create an SPFILE from PFILE?
CREATE SPFILE FROM PFILE='/tmp/initORCL.ora'; - Q7. What is SCOPE=MEMORY?
Changes the parameter only in the running instance memory. - Q8. What is SCOPE=SPFILE?
Stores the new value in the SPFILE. The current instance is not changed. - Q9. What is SCOPE=BOTH?
Changes the current instance and persists the value in the SPFILE, when the parameter supports dynamic modification. - Q10. What happens if a static parameter is changed?
The change is normally stored in the SPFILE usingALTER SYSTEM SET parameter=value SCOPE=SPFILE;. A database restart is then required for the new value to become active.
41. Quick DBA Cheat Sheet
PARAMETER
|
+-- Configuration value
|
+-- Controls Oracle behavior
PFILE
|
+-- Text file
+-- init.ora
+-- Manually editable
+-- STARTUP PFILE
SPFILE
|
+-- Server Parameter File
+-- Binary
+-- Oracle managed
+-- ALTER SYSTEM
SCOPE
|
+-- MEMORY → Current instance only
|
+-- SPFILE → Persistent configuration
|
+-- BOTH → Current + Persistent42. Complete Parameter Management Flow
Oracle DBA
|
v
Check Parameter
|
+----------+----------+
| |
v v
V$PARAMETER V$SPPARAMETER
| |
+----------+----------+
|
v
Decide Change
|
v
ALTER SYSTEM
|
+----------+----------+
| | |
v v v
MEMORY SPFILE BOTH
| | |
v v v
Runtime Persistent Runtime +
Persistent
|
v
If Required
Restart
|
v
Verify Parameter
|
v
Database Ready
Conclusion
Oracle initialization parameters are the configuration settings that control the behavior of an Oracle instance.
The two important parameter file types are:
📌 SPFILE — server parameter file managed by Oracle
As an Oracle DBA, you should be comfortable with:
SHOW PARAMETERV$PARAMETERV$SPPARAMETER- PFILE
- SPFILE
- Dynamic parameters
- Static parameters
SCOPE=MEMORYSCOPE=SPFILESCOPE=BOTHCREATE PFILECREATE SPFILEALTER SYSTEM SETALTER SYSTEM RESET- Starting the database using a PFILE
These concepts form the foundation for advanced Oracle DBA administration, including startup/shutdown, RAC, Data Guard, RMAN, performance tuning, memory management, and troubleshooting.