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
SQL
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
📌 Key point: Parameters define how the Oracle instance should operate.

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 File

4. 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.
SQL
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.

SQL
ALTER SYSTEM SET processes=500 SCOPE=SPFILE;

Oracle stores the parameter change in the SPFILE.

7. PFILE vs SPFILE

PFILESPFILE
Text fileBinary file
Can be manually editedShould not be manually edited
Traditionally called init.oraServer Parameter File
Changes require appropriate restart/reload depending on parameterSupports ALTER SYSTEM SET
Useful for troubleshootingPreferred for normal database configuration
Can be used with STARTUP PFILEUsually used automatically during startup
📌 Easy way to remember: PFILE = Text. SPFILE = Server-managed Binary Parameter File.

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:

SQL
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:

SQL
SELECT value
FROM v$parameter
WHERE name = 'spfile';
10. Check All Parameters

To display parameters using SQL*Plus:

SQL
SHOW PARAMETER;

You can also search for a specific parameter. For example:

SQL
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.

SQL
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.

SQL
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:

SQL
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.

SQL
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:

SQL
ALTER SYSTEM SET processes=500 SCOPE=SPFILE;

The value is stored in the SPFILE but does not become active in the current instance. Then:

SQL
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.

SQL
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 Active
18. SCOPE=SPFILE

The change is written to the SPFILE.

SQL
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.

SQL
ALTER SYSTEM SET some_parameter=value SCOPE=BOTH;
             ALTER SYSTEM
                  |
          +-------+-------+
          |               |
          v               v
       MEMORY           SPFILE
          |               |
          v               v
   Active Immediately  Persistent

SCOPE=BOTH can be used only when the parameter supports dynamic modification and the instance was started with an SPFILE.

20. SCOPE Comparison
ScopeCurrent InstanceSPFILERestart Required
MEMORYYesNoNo
SPFILENoYesUsually yes
BOTHYesYesNo for dynamic parameters
📌 Easy way to remember: MEMORY = Temporary. SPFILE = Persistent. BOTH = Current + Persistent.

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:

SQL
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.

SQL
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:

SQL
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:

SQL
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:

SQL
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:

SQL
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:

SQL
ALTER SYSTEM SET parameter_name=value SCOPE=scope;

Examples:

SQL
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.

SQL
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:

SQL
CREATE PFILE='/tmp/initORCL.ora'
FROM SPFILE;

Edit the PFILE:

vi /tmp/initORCL.ora

Then start the database using:

SQL
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 using ALTER 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 using ALTER 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 + Persistent

42. 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:

📌 PFILE — text-based parameter file
📌 SPFILE — server parameter file managed by Oracle

As an Oracle DBA, you should be comfortable with:

  • SHOW PARAMETER
  • V$PARAMETER
  • V$SPPARAMETER
  • PFILE
  • SPFILE
  • Dynamic parameters
  • Static parameters
  • SCOPE=MEMORY
  • SCOPE=SPFILE
  • SCOPE=BOTH
  • CREATE PFILE
  • CREATE SPFILE
  • ALTER SYSTEM SET
  • ALTER 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.