Oracle Instance Configuration and Storage Management

PFILE vs. SPFILE: Text and Binary Representations

An Oracle database instance cannot come into existence without a set of configuration directives — collectively known as initialization parameters — that define its fundamental characteristics. These parameters are stored in a dedicated file, called an initialization parameter file, which is read during the very first stage of instance startup: the NOMOUNT stage. Oracle supports two mutually exclusive formats:

PFILE (init.ora) is a plain text file conventionally named init<SID>.ora and located in $ORACLE_HOME/dbs (Unix/Linux) or %ORACLE_HOME%\database (Windows). Its syntax is simple: each line contains a parameter assignment; comments are preceded by #. Because it is editable with any text editor, a PFILE provides complete transparency and manual control. However, changes take effect only after the instance is restarted, and it does not support persistence of changes made by ALTER SYSTEM when using SCOPE=SPFILE.

SPFILE (spfile.ora) is a binary file, maintained exclusively by the Oracle server. The SPFILE's binary format allows a much richer operational model: parameters can be modified dynamically via ALTER SYSTEM and optionally persisted across restarts. The instance loads the parameter file according to a strict search order: it first looks for spfile<SID>.ora, then spfile.ora, and finally init<SID>.ora. Conversion between the two formats is straightforward:

CREATE SPFILE FROM PFILE = '/u01/app/oracle/product/19c/dbs/initORCL.ora';
CREATE PFILE FROM SPFILE = '/u01/app/oracle/product/19c/dbs/spfileORCL.ora';

The ability to generate a PFILE from an existing SPFILE is also a vital recovery tool if the binary file becomes corrupted.

Critical Parameter Groups for Instance Startup

An instance requires a minimal set of parameters to pass the NOMOUNT stage successfully. These can be grouped into three essential categories:

Database Identification: The parameter DB_NAME (often set together with DB_UNIQUE_NAME) must match exactly the name embedded in the control files and data file headers. A mismatch will cause the instance to fail at the MOUNT stage. The service name registered with the listener is typically $\text{SERVICE\_NAME} = \text{DB\_UNIQUE\_NAME.DB\_DOMAIN}$.

Memory Allocation: The SGA and PGA are sized by parameters such as SGA_TARGET, SGA_MAX_SIZE, PGA_AGGREGATE_TARGET, and individual pool sizes (SHARED_POOL_SIZE, DB_CACHE_SIZE, etc.). When automatic memory management is enabled, the total memory is bounded by MEMORY_TARGET and MEMORY_MAX_TARGET. A common design constraint is $\text{SGA\_TARGET} \le \text{SGA\_MAX\_SIZE}$, and similarly $\text{MEMORY\_TARGET} \le \text{MEMORY\_MAX\_TARGET}$. If SGA_TARGET exceeds available shared memory, startup fails with ORA-27102: out of memory.

Processes and Sessions: The parameter PROCESSES defines the maximum number of operating system processes that can simultaneously connect to the instance. From it, Oracle derives the default value for SESSIONS using the formula: $\text{SESSIONS} = (1.1 \times \text{PROCESSES}) + 5$. If SESSIONS is explicitly set, it overrides this derivation. Insufficient PROCESSES will lead to ORA-00020: maximum number of processes exceeded.

Dynamic Parameter Modification and Scope Options

Parameters are classified as dynamic (changeable while the instance is running) or static (requiring a restart). The distinction is checked by querying V$PARAMETER.ISSYS_MODIFIABLE. Dynamic changes are effected with ALTER SYSTEM SET, whose SCOPE clause governs where the change is recorded:

• SCOPE=MEMORY: The change takes effect immediately for the current instance but is not written to the SPFILE. Lost upon restart.
• SCOPE=SPFILE: The change is recorded in the SPFILE and will apply only after a subsequent restart. Does not affect the running instance.
• SCOPE=BOTH: The change is applied immediately and also written to the SPFILE, combining persistence with immediate effect. This is the default when an SPFILE is used.

Setting up a Fast Recovery Area (FRA) involves two parameters that must be ordered: DB_RECOVERY_FILE_DEST_SIZE before DB_RECOVERY_FILE_DEST. Archiving parameters are similarly set to direct archived redo logs to specific destinations, often requiring a restart through the MOUNT stage to issue ALTER DATABASE ARCHIVELOG.

The Control File: Database Metadata Repository

A control file is a small binary file that stores the physical structure and critical state information of a database. Without an intact control file, the database instance cannot mount, as every tablespace, datafile, and redo log file must be mapped through its records. The control file is read during the mount stage and is continuously updated by background processes during normal operation.

The control file is organized into record sections including: Database information block (database name, DBID, creation timestamp, current System Change Number $SCN$), Datafile records (one entry per datafile specifying file name, tablespace number, creation SCN, online/offline status, and checkpoint SCN), Tablespace records (names, numbers, attributes, and datafile lists), Online redo log records (each thread of redo has records describing its log groups — group number, sequence number, size, and state), Log member records (each physical redo log file with its full pathname and group association), and Log history records (the sequence of log switches and archive operations).

The size of the control file is determined by MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and other parameters set at database creation. The dynamic view V$CONTROLFILE_RECORD_SECTION reveals the total slots, used slots, and size of each record section.

Multiplexing Control Files

Oracle strongly recommends maintaining at least two identical copies of the control file on physically separate storage devices. The CONTROL_FILES initialization parameter lists the absolute file paths of all active copies. During database operation, the CKPT process writes updates to all listed copies simultaneously. Should one copy become unavailable due to media failure, the instance will continue to operate as long as at least one copy remains accessible. Multiplexing control files is the most basic form of availability insurance — without it, a single disk failure can render the entire database unmountable.

Online Redo Logs: The Change Journal

Every modification to an Oracle data block is recorded as a redo entry in a logical buffer called the redo log buffer. The LGWR process flushes these entries sequentially to the physical online redo log files. Online redo log files are organized into groups. A group consists of one or more identical members (files). At any moment, LGWR writes to exactly one group, designated as CURRENT. A database must have at least two groups to permit continuous cycling.

The group lifecycle is governed by its state:

• CURRENT: The group to which LGWR is actively writing.
• ACTIVE: The group that was recently current and is still required for instance recovery. Cannot be overwritten until instance recovery would no longer need it.
• INACTIVE: A group whose redo is no longer needed for crash recovery. In ARCHIVELOG mode, the archiver must copy it before the group can be reused.

Log Group Cycling and Checkpointing

When the current group fills or an explicit ALTER SYSTEM SWITCH LOGFILE is issued, a log switch occurs. The algorithm proceeds as follows: LGWR closes the current group, marking it ACTIVE, and attempts to open the next group in sequence. If the target group is INACTIVE, LGWR writes a new log sequence number header and resumes writing. If the target group is still ACTIVE, LGWR must wait — this condition, a checkpoint not complete wait, indicates that CKPT has not advanced the checkpoint SCN sufficiently. Immediately after the switch, CKPT initiates a checkpoint, signaling DBWR to write dirty buffers to disk and updating control file and datafile headers. The archiver process begins archiving the now-INACTIVE group.

Let the database have $N$ redo log groups. If each group of size $S$ bytes can hold approximately $T$ seconds of redo generation at peak throughput $R$ bytes/second, then the total redo retention window is approximately $(N-1) \cdot T$. This window determines how long a backup can lag and still be recoverable using only online logs.

Multiplexing Online Redo Logs

Each redo log group should contain at least two members on separate physical disks. If one member becomes unavailable, LGWR continues writing to the remaining members, and the database remains operational. The probability of losing all copies of a critical log provides a quantitative justification for multiplexing: if a single storage volume has an uncorrelated failure probability $p$ over a given interval, then $n$ independent copies reduce the total loss probability to $p^n$. In practice, correlated failures due to power or storage controller events reduce this independence, so geographic separation becomes essential for true disaster recovery.

The Critical Role of Database Archiving

While online redo log groups capture all modifications to data files in a circular buffer, their finite size and cyclic reuse render them insufficient for protecting against media failures or for enabling point-in-time recovery. The process of archiving transforms this transient record into a durable, sequential history — the archived redo log. Together with a consistent backup, archived logs allow an administrator to reconstruct the database to any moment after the backup, delivering recovery point objective (RPO) flexibility.

Enabling ARCHIVELOG Mode

Transitioning from NOARCHIVELOG to ARCHIVELOG mode is performed with the database mounted but closed:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

The archive mode can be verified with ARCHIVE LOG LIST or by querying V$DATABASE.LOG_MODE. Once enabled, the database immediately expects at least one usable archive destination. Oracle provides two configuration paradigms: the legacy LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters, and the more flexible LOG_ARCHIVE_DEST_n ($n = 1 \dots 31$) parameters that support multiplexing, remote transport, and fine-grained control.

Archive Log Naming and Space Management

Archived log file names are controlled by the format mask LOG_ARCHIVE_FORMAT. A typical setting, %t_%s_%r.arc, incorporates the thread number, log sequence number, and resetlogs ID to guarantee global uniqueness. The fundamental equation governing archive generation: let $R$ be the redo generation rate (MB/s). With an online redo log size $S_{\text{log}}$ per member, the log switch interval is $T_{\text{switch}} = S_{\text{log}} / R$. The archiver must copy the inactive log within this interval to prevent the dreaded ORA-00257: archiver error that freezes database activity.

Suppose the database generates redo at a constant average rate $R$ (MB/day) and the recovery window policy requires $W$ days of point-in-time recovery capability. The minimal archive storage requirement is $S_{\text{min}} = R \times W$. The number of archive files generated per day is $N_{\text{daily}} = \lceil R / S_{\text{log}} \rceil$. Over the retention window, the actual storage consumed is $N_{\text{daily}} \times S_{\text{log}} \times W$, which slightly exceeds $R \times W$ due to rounding up at the last incomplete cycle.