Oracle initialization parameter file

Every Oracle instance makes use of a set of initialization parameters that dictate database limits such as

1. the number of users,
2. specify the names and locations of key files and directories,
3. and optimize performance by setting the size of key resources such as memory.

Before we jump into the details of Oracle database creation, it’s important to familiarize ourself with the important Oracle initialization parameters and how Oracle uses them.

TYPES OF INITIALIZATION PARAMETER FILE

Oracle uses a parameter file to store the initialization parameters and their settings for an instance. We can use either of the following two types of parameter files:

1. Server parameter file (SPFILE): Dynamic persistent server parameter file SPFILE, commonly referred as spfile.ora A binary file that contains the initialization parameters
2. Initialization parameter file (pfile): Static parameter file, PFILE, commonly referred as init.ora  A text file that contains a list of all initialization parameters.

Contents Of Parameter File

·         A list of instance parameters

·         The name of the database the instance is associated with

·         Allocation for memory structures of the SGA

·         What to do with filled online redo log files.  (Archive Destination)

·         The names and locations of control files.

·         Information about undo tablespace.

 Text initialization parameter file:  PFILE à   init.ora  (sid) means database name

• Text File
• Modified with an operating system editor
• Modification made manually
• Whenever we need to change the value of the parameter file we must shutdown the database.
• The pfile is read only during instance startup. (Changes take effect on next startup)

For oracle 10g (R2)   à  default location E:\oracle\product\10.2.0\Db_1\database\initorcl.ora

Server parameter file:  SPFILE : à spfile.ora  à   (sid)  means database name

- Binary File

- Maintained by the oracle server

- Always resides on the server side having read and write by database server

- Ability to make changes persistent across SHUTDOWN and STARTUP

- Can self-tune parameter values

- Can have RMAN support for backup the initialization parameter

For oracle 9i à  default location C:\oracle\ora92\database\spfileorcl.ora

For oracle 10g  à  default location E:\oracle\product\10.2.0\Db_1\dbs\spfileorcl.ora

perform  following query to check your database was started with a PFILE or SPFILE

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "File Type"

       FROM sys.v_$parameter WHERE name = 'spfile';

Oracle Background Process

PMON

The Process Monitor checks if a user process fails and if so, does all cleaning up of resources that the user process has acquired such as rolling back the uncommitted transactions and releasing locks. PMON also does service registration with the Oracle listener.

MMAN

MMAN dynamically adjust the sizes of the SGA components. It is a new process added to Oracle 10g as part of automatic shared memory management.

DBWR

The Database Writer writes dirty blocks from the database buffer to the datafiles. Dirty blocks need to be flushed out to disk to make room for new blocks in the cache. The checkpoint process (CKPT) uses DBWR to write dirty buffers to disk. This is normally referred to as scattered or random writes, because the dirty blocks to be written could be anywhere on the disk(s). You can configure more than one DBWR process up to 10. This depends on the number of CPUs allocated to the instance. To have more than one DBWR only make sense if each DBWR has been allocated its own list of blocks to write to disk. This is done through the initialization parameter DB_BLOCK_LRU_LATCHES. If this parameter is not set correctly, multiple DBWRs can end up contending for the same block list.

LGWR

The Log Writer writes the redo log buffer from the SGA to the online redo log file. LGWR does this every three seconds, whenever a user issues a commit, a checkpoint happens or whenever the redo log buffer is 1MB or more than 1/3 full. This means that there is no point in making the redolog buffer more than 3MB. Note that the logs are written via sequential writes as opposed to scattered writes.

CKPT

The Checkpoint Process regularly initiates a checkpoint. A checkpoint process

Flushes the redo log buffer to redo log files by means of LGWR

Writes a checkpoint record to the redo log file

Uses DBWR to write all dirty blocks back to the datafiles, thus synchronizes the database.

Updates the file headers of the data files with information about the last checkpoint performed

Update the control files about the last checkpoint

SMON

The System Monitor carries out a crash recovery when a crashed instance is started up again. SMON cleans temporary segments. It also merges contiguous areas of free space in the datafiles, a process known as coalescing.

RECO

The Distributed Transaction Recovery Process finds pending distributed transactions and resolves them. Pending distributed transactions are two-phase commit transactions involving multiple databases. The database that the transaction started is normally the coordinator. It will send request to other databases involved in two-phase commit if they are ready to commit. If a negative request is received from one of the other sites, the entire transaction will be rolled back. Otherwise, the distributed transaction will be committed on all sites. However, there is a chance that an error (network related or otherwise) causes the two-phase commit transaction to be left in pending state (i.e. not committed or rolled back!). It is the role of the RECO process to liaise with the coordinator to resolve the pending two-phase commit transaction. RECO will either commit or rollback this transaction.

ARC

The Archiver process copies an online redo log file to another location when the redo log file is filled up. Archive log files are used for media recovery (in case of a hard disk failure and for maintaining an Oracle standby database via log shipping). There can be up to ten archiver processes. Archiver is only present if the database is running in archivelog mode and automatic archiving is enabled. LGWR process is responsible for starting multiple ARC processes when the workload increases. Unless archiver completes the copying of a redo log file, it is not released to LGWR for overwriting.

CJQ0

This is the Oracle’s dynamic job queue coordinator. It periodically selects jobs that need to be run, scheduled by the Oracle job queue. The coordinator process dynamically spawns job queue slave processes (J000…J999) to run the jobs. These jobs could be PL/SQL statements or procedures on an Oracle instance. Please note that this is not a persistent process. It comes and goes

Dnnn

The Dispatcher Process is used in a shared server environment. Dnnn supports shared server configuration by allowing user processes to share a limited number of server processes. Shared server configuration is explained later. Oracle 10g comes with a single Dnnn process configured by default.

Snnn

The Shared Server Process is used in a shared server environment. Each shared server process serves multiple client requests in the shared server configuration. Shared server processes and dedicated server processes provide the same functionality, except shared server processes are not associated with a specific user process. That is a shared server process is not a shadow process.

Oracle Startup Process

The Oracle Startup Process

The steps that Oracle takes in starting up are as follows:

1. STARTUP NOMOUNT

1.1. Reads the initialization file

1.2. Allocates the shared memory (SGA)

1.3. Starts the background processes

1.4. Opens the alert and trace files

2. ALTER DATABASE MOUNT

2.1. Opens and reads the control files

2.2. Mounts the database and associates the started instance with the database

3. ALTER DATABASE OPEN

3.1. Opens data files and redo logs

3.2. Performs database consistency and auto recovery