| Lesson 2 | Oracle instance |
| Objective | Review Oracle instance structures. |
Oracle Instance Structures
The diagram below illustrates the processes and memory structures that make
up an Oracle instance. The diagram contains a brief description of what each particular process does. The square boxes represent processes, while the circle in the center represents an area of memory that they all share. Boxes drawn in bold are processes that are mandatory, and that are always running when a database is open. The other processes are optional, and whether or not they will exist depends on the specific mix of Oracle features that you are using.
- SGA: System Global Area is a memory area containing the database buffer cache, the redo log buffer, frequently used SQL statements, and other items critical to the operation of a database.
- DBWR: Database Writer writes modified blocks back to the datafiles.
- LGWR: Log Writer writes the database's redo log.
- SMON: System Monitor performs crash recovery, coalesces free space, and so on.
- PMON: Process Monitor cleans up after user processes.
- RECn: Recoverer resolves distributed transactions.
- SNPn: Snapshot runs the job queues used, among other things, for snapshot refreshes.
- Dnnn: Dispatcher is used with the multi-threaded server configuration.
- Shared Server Process: Shared Server Process is used with the multi-threaded server configuration, and not considered part of the instance.
- QMNn: Queue Monitor is used with Oracle Advanced Queueing.
- LCKn: Lock is used with parallel server for inter-instance locking.
- CKPT: Checkpoint initiates and controls the checkpoint process.
- ARCH: Archiver copies filled redo log files to the archive log destination.
There are a few physical files that control the instance. The first is the instance initialization file, better know as the
INIT.ORA file. This file contains a variety of parameters that control how the instance is created. The second is an alternate optional instance configuration file, better know as CONFIG.ORA. We will discuss these files in a later module. As you can see, the Oracle instance is made up of several components. In the next several lessons, we'll review the memory structures and the
processes as they relate to backup and recovery. The next lesson explores Oracle instance memory structures.
What is the purpose of the INIT.ORA file in Oracle?
The INIT.ORA file in Oracle is a configuration file that contains initialization parameters for an Oracle database instance. The initialization parameters control various aspects of the behavior and performance of the database, including memory allocation, disk I/O, and networking.
The purpose of the INIT.ORA file is to provide a centralized location for storing the configuration settings that are used to start and run an Oracle database instance. The parameters in the INIT.ORA file are read by the Oracle instance when it is started, and used to configure the instance according to the specified settings.
Some of the most common initialization parameters that are stored in the INIT.ORA file
By modifying the parameters in the INIT.ORA file, a DBA can control the behavior and performance of an Oracle database instance. For example, increasing the size of the shared pool can improve the performance of the database by reducing the amount of time that is spent parsing SQL statements. Similarly, increasing the size of the redo log buffer can improve the performance of the database by reducing the amount of time that is spent writing changes to disk.
It is important to note that changes to the INIT.ORA file do not take effect until the database instance is restarted. As a result, changes to the INIT.ORA file should be made with care, and thoroughly tested before being applied in a production environment.
The purpose of the INIT.ORA file is to provide a centralized location for storing the configuration settings that are used to start and run an Oracle database instance. The parameters in the INIT.ORA file are read by the Oracle instance when it is started, and used to configure the instance according to the specified settings.
Some of the most common initialization parameters that are stored in the INIT.ORA file
- DB_NAME: the name of the database
- DB_BLOCK_SIZE: the size of the database blocks used for data storage
- SHARED_POOL_SIZE: the size of the shared pool, which is used to store shared SQL and PL/SQL code
- LOG_BUFFER: the size of the redo log buffer, which is used to store changes to the database before they are written to disk
- PROCESSES: the maximum number of concurrent connections to the database include:
By modifying the parameters in the INIT.ORA file, a DBA can control the behavior and performance of an Oracle database instance. For example, increasing the size of the shared pool can improve the performance of the database by reducing the amount of time that is spent parsing SQL statements. Similarly, increasing the size of the redo log buffer can improve the performance of the database by reducing the amount of time that is spent writing changes to disk.
It is important to note that changes to the INIT.ORA file do not take effect until the database instance is restarted. As a result, changes to the INIT.ORA file should be made with care, and thoroughly tested before being applied in a production environment.