Database Architecture   «Prev  Next»

Lesson 3 Initialization file
Objective Identify what lives in the Oracle initialization file (PFILE/SPFILE), how to manage it safely, and which parameters are legacy.

Oracle Initialization File and Parameters (Modern Guidance)

The initialization configuration for an Oracle instance lives in either:

  • PFILE (text, e.g., initORCL.ora) - edited with a text editor; changes take effect at next startup.
  • SPFILE (binary, e.g., spfileORCL.ora) - preferred; make persistent changes with ALTER SYSTEM.

Parameters broadly cover:

  1. Database identity and files (db_name, control_files).
  2. Memory and process sizing (SGA/PGA, server processes).
  3. Diagnostics (alert/trace destinations).
  4. Availability and recovery (redo/archivelog destinations, Flash Recovery Area).

Example snippets (legacy text PFILE style):

db_name = devl
db_files = 1024
control_files =
 (/m01oracle/oradata/ORCL/control01.ctl,
  /m21/oradata/ORCL/control02.ctl,
  /m57/oradata/ORCL/control03.ctl)
db_block_size = 8192

SPFILE workflow and safe changes

-- See if the instance started with an SPFILE
SHOW PARAMETER spfile

-- Change that persists across restarts
ALTER SYSTEM SET processes = 600 SCOPE=SPFILE;

-- Change now and persist (when allowed)
ALTER SYSTEM SET session_cached_cursors = 200 SCOPE=BOTH;

-- Change for current memory only (lost on restart)
ALTER SYSTEM SET optimizer_index_cost_adj = 50 SCOPE=MEMORY;

Tip: Keep a text export for change control: CREATE PFILE='/tmp/init_backup.ora' FROM SPFILE;

Memory management: modern defaults

  • ASMM - Set SGA_TARGET/SGA_MAX_SIZE; Oracle auto-distributes buffer cache, shared pool, etc.
  • Automatic PGA - Set PGA_AGGREGATE_TARGET (or MEMORY_TARGET for AMM); Oracle sizes sort/hash areas per session automatically (WORKAREA_SIZE_POLICY=AUTO).
SHOW PARAMETER sga_target
SHOW PARAMETER pga_aggregate_target
SHOW PARAMETER workarea_size_policy

Availability and recovery parameters (current)

Use the LOG_ARCHIVE_DEST_n family and (optionally) the Fast Recovery Area (FRA).

ARCHIVE LOG LIST

SHOW PARAMETER db_recovery_file_dest
SHOW PARAMETER log_archive_dest_1
SHOW PARAMETER log_archive_min_succeed_dest

Note: LOG_ARCHIVE_DUPLEX_DEST is deprecated; define multiple LOG_ARCHIVE_DEST_n entries instead.

Figure caption audit (9 legacy images)

The original slide deck used nine short captions. Here’s the correctness status and modernized guidance:

1) The db_name parameter names the database.
1) db_name - Accurate. This is the database name you see in SELECT name FROM v$database;

2) The db_files parameter controls the total number of database files that may be open at any one time
2) db_files - Partially accurate / fix: db_files limits the maximum datafiles for the database. It does not count control files or online redo logs. Set it a bit above current datafile count to allow growth without restart.

3) The control_files parameter points to the database control files.
3) control_files - Accurate. Lists multiplexed control file paths; keep copies on separate storage.

4) The db_block_size parameter controls the physical size of a database block.
4) db_block_size - Accurate with nuance: Sets the standard block size at database creation; cannot change later. Nonstandard block sizes require explicit buffer caches (e.g., DB_16K_CACHE_SIZE).

5) Oracle buffers data in memory in order to reduce disk I/O.
5) Buffer cache - Directionally correct: Oracle caches blocks in the database buffer cache (DB_CACHE_SIZE). With ASMM, size is auto-tuned via SGA_TARGET.

6) The shared pool is an area that Oracle uses to store and parse SQL statements and PL/SQL code.
6) Shared pool - Accurate: Holds library cache (parsed cursors/PL/SQL) and dictionary cache. With ASMM, SHARED_POOL_SIZE can be auto-managed.

7) The log_buffer parameter controls the amount of memory Oracle uses to buffer data that needs to be written to the redo log files.
7) log_buffer - Outdated emphasis / fix: Parameter exists but is typically automatically sized by Oracle on startup (especially with SGA_TARGET). Manual settings are rarely needed.

8) This value controls the maximum amount of memory that Oracle will use to sort data when executing a query.
8) Sort/workarea memory - Outdated / fix: Do not use SORT_AREA_SIZE for normal operations. Prefer automatic PGA management with PGA_AGGREGATE_TARGET (or MEMORY_TARGET) and WORKAREA_SIZE_POLICY = AUTO.

9) After a large sort, Oracle will attempt to release some of the memory used by that sort.
9) Sort retention - Outdated / fix: SORT_AREA_RETAINED_SIZE is legacy. With automatic PGA, workareas expand/contract automatically; manage with PGA targets/limits.

How initialization files “work” today

  • Oracle reads parameters at startup (from SPFILE if present, else PFILE). Changes to many parameters can be made dynamically (SCOPE=MEMORY), but others require a restart (SCOPE=SPFILE).
  • The init file isn’t a database file per se, but it’s critical for availability; back it up with your control file and SPFILE.
  • Use FRA for archived logs and manage retention so archive destinations don’t fill (which can stall the instance).
-- Quick health checks
SHOW PARAMETER open_cursors
SHOW PARAMETER processes
SHOW PARAMETER audit_trail

-- Persisted config snapshot
CREATE PFILE='/tmp/init_&&ORACLE_SID..ora' FROM SPFILE;

Parameter groups worth knowing

  • Naming and files: db_name, control_files, db_create_file_dest (OMF).
  • Capacity/limits: processes, sessions, db_files (datafiles cap).
  • Memory: sga_target/sga_max_size, pga_aggregate_target, optional memory_target/memory_max_target.
  • Workareas: workarea_size_policy=AUTO (don’t rely on the old SORT_* parameters).
  • Recovery: db_recovery_file_dest(_size), log_archive_dest_n, log_archive_min_succeed_dest.

Hands-on exercise

Take a safe snapshot and review key settings:

-- Snapshot SPFILE to a text PFILE for review
CREATE PFILE='/tmp/init_review.ora' FROM SPFILE;

-- Grep common items (OS shell)

# grep -E 'db_name|control_files|sga_|pga_|workarea|log_archive_dest' /tmp/init_review.ora

Initialization File - Exercise

First, click the Exercise link below to find and look at your database's initialization file.
Initialization File - Exercise

SEMrush Software 3 SEMrush Banner 3