⚙️ SPFILE & PFILE

Configuration via init.ora (PFILE)

init.<SID>.ora was the way to configure Oracle 8/9. It is the database parameter file — without it the database cannot start. Default location: $ORACLE_HOME/dbs (UNIX) or %ORACLE_HOME%\database (Windows).

Examples of parameters:

• BACKGROUND_DUMP_DEST — where the background-process trace files are written.
• USER_DUMP_DEST — where the user trace files are created.
• COMPATIBLE — the server version the instance is compatible with.
• CONTROL_FILES — the control file names.
• DB_BLOCK_BUFFERS — number of blocks cached in the SGA (default/minimum 50).
• DB_NAME — database identifier (5 chars or fewer; the only parameter required to create a database).
• SHARED_POOL_SIZE — shared pool size in bytes (default 3 500 000).
• IFILE — reference another parameter file to nest.
• LOG_BUFFER — bytes allocated to the redo log buffer.
• MAX_DUMP_FILE_SIZE — max trace file size (in OS blocks).
• PROCESSES — number of OS processes that can connect simultaneously.
• SQL_TRACE — enable/disable SQL tracing per session (see TKPROF).
• TIMED_STATISTICS — enable/disable timing in traces and on screens.

Configuration via SPFILE

The SPFILE arrived with 9i. To change it, convert the SPFILE to a text PFILE, edit, then convert back to a binary SPFILE.

1COL name  FOR a30
2COL value FOR a30
3SELECT name, value FROM v$parameter;

1SHOW PARAMETER spfile;      -- version & location of the SPFILE.
2SHOW PARAMETER processes;
3SHOW PARAMETER sessions;
4SHOW PARAMETER open_cursors;

Convert

1CREATE PFILE='/u01/app/oracle/pfile.txt' FROM SPFILE;       -- minimal params needed to start.
2CREATE SPFILE FROM PFILE='/u01/app/oracle/pfile.txt';       -- (restart to take effect)
3CREATE PFILE='/u01/app/oracle/pfile_full.txt' FROM MEMORY;  -- also includes all Oracle defaults.

Modify a parameter

1ALTER SYSTEM SET open_cursors = 300;              -- on the fly (not possible for every param).
2ALTER SYSTEM SET sessions = 300 SCOPE=SPFILE;     -- write to the SPFILE (needs a restart).
3ALTER SYSTEM SET processes = 300 SCOPE=BOTH;      -- SPFILE + memory (not valid for SESSIONS).

SPFILE location

• UNIX: $ORACLE_HOME/dbs/init$ORACLE_SID.ora
• Windows: %ORACLE_HOME%\database\init<SID>.ora