Oracle Instance and Database

In Oracle to access the data in the database, Oracle uses a set of Background processes that are shared by every user. Also, along with this there is some memory structure that stores the most recently queried data from the database.

A Database Instance (also save as Server) is set of memory structures and background processes that access a set of database files. It is possible for the single database to contain multiple instances, which is known as Real Application Cluster. The parameters that determine the size and composition of an oracle instance are either stored in an initialization file called init.ora or in spfile.ora. The initialization parameter file is read during instance start up and may be modified by the DBA. Any modification made will not be affected until the next start up.

Figure-1 Instance and database

System Global Area (SGA)

Oracle uses an area of shared memory called the system global area (SGA) and a private memory area for each process called the program global area (PGA). The SGA consists of:

System global area:

The SGA is a shared memory region that contains data and control information for one oracle instance. Oracle allocates the SGA when an instance starts and de-allocates it when the instance shut downs. Every instance has the SGA .The entire SGA should be large as possible to increase the system performance and to reduce disk I/O.

The information stored in the SGA is divided into three memory structures,

1) Database buffers cache.

2) Redo log buffers.

3) Shared pool.

Database buffers cache: The database buffer stores the most recently used blocks of data. The set of database buffers in an instance is the database buffer cache. The buffer cache contains modified as well as unmodified blocks. Because the most recently and most frequently used data is kept in memory. It improves the performance of system by reducing the disk I/O operations.

Redo log buffers: The redo log buffer stores redo entries. This is a log of changes made to the database. The redo entries stored in the redo log buffers are written to an online redo log. An online redo log is a set of two or more files that record all the changes made to oracle data files and control files.

Shared pool: The shared pool caches various constructs that can be shared among users, for example SQL shared area. For example’s SQL statements are cached so that they can be reused. Stored procedures can be cached for faster access. Note that in previous versions “out of memory” error were occurs if the shared memory was full. In oracle 10g it does not happen. It provides automatic shared memory tuning.

Program global area

PGA is a memory buffer that contains data and control information for a server process. A server process is a process that services a client’s requests. A PGA is created by oracle when a server process is started. The information in a PGA depends on the oracle configuration. The PGA area is a non-shared area of memory created by oracle when a server process is started.

The basic difference between SGA and PGA is that PGA cannot be shared between multiple processes in the sense that it is used only for requirements of a particular process whereas the SGA is used for the whole instance and it is shared.

Processes

The relationships between the databases’ physical and memory structures are maintained and enforced by the Background Process. These are database’s own background processes that may vary in the number depending in your database configuration. The Trace files are only created when there is any problem. Some of the Background Processes are:

SMON:

SMON stands for System Monitor and this database background process performs instance recovery at the start of the database. In a multiple instance system, SMON of one instance can also perform instance recovery for other instances that have failed. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or offline errors. It coalesces i.e. combines contiguous free extents into larger free extents.

PMON:

PMON stands for the Process Monitor and this database background process cleans up failed user processes. PMON is responsible for releasing the lock i.e. cleaning up the cache and freeing resources that the process was using. Its effect can be seen when a process holding a lock is killed.

DBWR:

The DBWR (Database Writer) background process is responsible for managing the contents of the data block buffer cache and dictionary cache. DBWR performs batch writes of changed block. Since Oracle uses write-ahead logging, DBWR does not need to write blocks when a transaction commits. In the most common case, DBWR writes only when more data needs to be read into the system global area and too few database buffers are free. The least recently used data is written to the datafiles first.

Although there is only one SMON and one PMON process running per database instance, one can have multiple DBWR processes running at the same time. Note the number of DBWR processes running is set via the DB_WRITER_PROCESSES.

LGWR:

The LGWR (Log Writer) background process manages the writing of the contents of the redo log buffer to the online redo log files. LGWR writes the log entries in the batches form. The Redo log buffers entries always contain the most up-to-date status of the database. Note LGWR is the only one process that writes to the online redo log files and the only one that directly reads the redo log buffer during to the normal database operation.


Archiver (ARCH):

The Archiver process reads the redo log files once Oracle has filled them and writes a copy of the used redo log files to the specified archive log destination(s). Actually, for most databases, ARCH has no effect on the overall system performance. On some large database sites, however, archiving can have an impact on system performance. We can specify up to ten ARCn processes for each database instance LGWR will start additional Archivers as needed, based on the load, up to the limit specified by the initialization parameter LOG_ARCHIVE_MAX_PROCESSES.


Checkpoint process (CKPT):

All modified information in database buffer in the SGA is written to the datafiles by a database write process (DBWR). This event indicates a checkpoint. The checkpoint process is responsible for signaling DBWR at checkpoints and updating all of the datafiles and control files of the database.

Recover (RECO)

The recover process automatically cleans up failed or suspended distributed transactions.

Frequently Asked Questions

1. What is a schema?
   
2. What is an instance?
   
3. What is a tablespace?
   
4. What are the differences between SGA and PGA?
   
5. What is the significance of background processes?]
   
6. What is the difference between instance and database?
   
7. Can I have multiple instances of database?
   
8. What is the use of control files?
   
9. What is the use of redo log files?
   
10. What is checkpoint process?
    

First Page: Oracle 10g Free Training: Oracle Concepts and Database Architecture