pfile and spfile Overview

Oracle stores initialization parameters in configuration files that are loaded during instance startup. These files define the database's physical structure, memory allocation, system constraints, and various default values. Oracle supports two types of parameter files:

pfile (Initialization Parameters File): Text-based configuration files used before Oracle 9i. The default naming convention is init<SID>.ora. These files reside in $ORACLE_HOME/dbs and can be edited with any text editor.

spfile (Server Parameter File): Binary parameter files introduced in Oracle 9i. The default naming convention is spfile<SID>.ora, also located in $ORACLE_HOME/dbs. These files cannot be manually edited with text editors—parameter modifications require SQL commands.

You can distinguish between them using the file command:

[oracle@dbserver dbs]$ ls -la
-rw-r--r-- 1 oracle oinstall 1792 Jan 15 09:30 initPROD.ora
-rw-rw---- 1 oracle dba 3584 Jan 15 09:30 spfilePROD.ora

[oracle@dbserver dbs]$ file initPROD.ora
initPROD.ora: ASCII text

[oracle@dbserver dbs]$ file spfilePROD.ora
spfilePROD.ora: data

Benefits of Using spfile

Switching to spfile provides several advantages:

1. Centralized management: In clustered or RAC environments, pfile copies proliferate across nodes, making it difficult to track which version is current. spfile serves as a single authoritative source for parameter values.

2. Online modifications: With pfile, parameter changes require instance restart. spfile enables dynamic parameter adjustments, and many parameters take effect immediately without restarting. If a problematic change prevents startup, you can recreate the pfile from spfile in nomount state to recover.

Locating spfile and pfile

Query the data dictionary to find parameter file locations:

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'spfile';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
spfile
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilePROD.ora

Alternatively, use the shorthand syntax:

SHOW PARAMETER spfile;

Determining Startup Method

Method 1: Query v$spparameter

SELECT DECODE(COUNT(*), 1, 'spfile', 'pfile') AS INIT_FILE
FROM V$SPPARAMETER
WHERE ROWNUM = 1 AND ISSPECIFIED = 'TRUE';

INIT_FILE
----------
spfile

Method 2: Check parameter display

When starting with spfile, both SHOW PARAMETER pfile and SHOW PARAMETER spfile display the parameter file path. When starting with pfile, neither command shows a path.

Converting Between pfile and spfile

-- Create spfile from pfile
CREATE SPFILE FROM PFILE = '/path/to/initPROD.ora';

-- Create pfile from spfile
CREATE PFILE FROM SPFILE;

The following example demonstrates creating a pfile from the current spfile:

CREATE PFILE FROM SPFILE;

File created.

Resulting file structure:

[oracle@dbserver dbs]$ ls -la
-rw-r--r-- 1 oracle oinstall 1792 Jan 15 09:30 initorcl.ora
-rw-rw---- 1 oracle dba 3584 Jan 15 09:30 spfileorcl.ora

Startup Behavior

The startup command searches for parameter files in this order:

1. spfile<SID>.ora
2. spfile.ora
3. init<SID>.ora
4. init.ora

To start with a specific pfile:

STARTUP PFILE = '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initPROD.ora';

ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 8894688 bytes
Variable Size 520093696 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7877888 bytes
Database mounted.
Database opened.

Important considerations:

• The startup command can only reference pfile, not spfile directly
• When both files exist in $ORACLE_HOME/dbs, spfile takes precedence automatically
• Parameter files outside the default directory require explicit path specification

Understanding scope Options

When modifying parameters with ALTER SYSTEM, three scope options determine persistence:

Parameter Type	scope=spfile	scope=memory	scope=both
Static	Yes (effective after restart)	No	No
Dynamic	Yes (effective after restart)	Yes (immediate, lost on restart)	Yes (immediate, persists after restart)

scope=spfile: Changes are written to the server parameter file and take effect on the next database restart. Works for both parameter types.

scope=memory: Changes apply only to the current instance's memory. Dynamic parameters take effect immediately but revert upon restart.

scope=both: Changes are written to both memory and spfile simultaneously. Dynamic parameters apply immediately and persist across restarts. This is the default when using spfile.

Attempting invalid scope combinations produces errors:

ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE = BOTH;
ERROR: ORA-02095: specified initialization parameter cannot be modified

Practical Examples

Modifying a dynamic parameter with scope=both

SQL> SHOW PARAMETER memory_target;

NAME                                 TYPE           VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer    1G

SQL> ALTER SYSTEM SET memory_target = 1500M SCOPE = BOTH;

System altered.

SQL> SHOW PARAMETER memory_target;

NAME                                 TYPE           VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer    1500M

Modifying a static parameter

Static parameters require scope=spfile because they cannot be applied to memory:

SQL> SELECT value FROM v$parameter WHERE name = 'open_cursors';

VALUE
--------------------------------------------------------------------------------
300

SQL> ALTER SYSTEM SET open_cursors = 400 SCOPE = SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 8894688 bytes
Variable Size 520093696 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7877888 bytes
Database mounted.
Database opened.

SQL> SELECT value FROM v$parameter WHERE name = 'open_cursors';

VALUE
--------------------------------------------------------------------------------
400

Using scope=memory for temporary changes

SQL> ALTER SYSTEM SET sort_area_size = 1048576 SCOPE = MEMORY;

System altered.

pfile limitations

When using pfile, all scope options except memory produce errors because the system cannot persist changes to a text-based file:

SQL> STARTUP PFILE = '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initPROD.ora';

ORACLE instance started.

SQL> ALTER SYSTEM SET open_cursors = 500;
ERROR: ORA-02095: specified initialization parameter cannot be modified

SQL> ALTER SYSTEM SET open_cursors = 500 SCOPE = SPFILE;
ERROR: ORA-32001: write to SPFILE requested but no SPFILE is in use

SQL> ALTER SYSTEM SET open_cursors = 500 SCOPE = BOTH;
ERROR: ORA-02095: specified initialization parameter cannot be modified

Common Initialization Parameters

# Block and Buffer Configuration
db_block_size=8192
db_cache_size=536870912
db_file_multiblock_read_count=32

# Cursor Management
open_cursors=300

# Database Identification
db_name=PROD
db_domain=company.com

# Diagnostic Destinations
background_dump_dest=/u01/app/oracle/admin/PROD/bdump
core_dump_dest=/u01/app/oracle/admin/PROD/cdump
user_dump_dest=/u01/app/oracle/admin/PROD/udump

# Control Files
control_files=("/u01/oradata/PROD/control01.ctl", 
               "/u01/oradata/PROD/control02.ctl")

# Memory Settings
sga_target=1073741824
pga_aggregate_target=536870912
shared_pool_size=268435456
large_pool_size=67108864
java_pool_size=33554432

# Process Configuration
processes=300

# Undo Management
undo_management=AUTO
undo_tablespace=UNDOTBS1
undo_retention=10800

# Recovery Settings
fast_start_mttr_target=300
log_archive_dest_1="LOCATION=/u01/archivelogs"

# Security
remote_login_passwordfile=EXCLUSIVE

# Compatibility
compatible=19.0.0