Oracle 10g Database Architecture............

Overview of the System Global Area

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.
An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.
The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.
The SGA contains the following data structures:

• Database buffer cache
  
• Redo log buffer
  
• Shared pool
  
• Java pool
  
• Large pool (optional)
  
• Streams pool
  
• Data dictionary cache
  
• Other miscellaneous information
  

Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.
If the system uses shared server architecture, then the request and response queues and some contents of the PGA are in the SGA.

The Oracle server architecture
The Oracle server architecture is basically categorized as follows:

• Logical layer
• Physical layer

The logical layer comprises one or more tablespaces and the database schema. The database schema consists of tables, clusters, indexes, views, procedures, triggers, sequences, and so on. The database schema is a collection of schema objects.
The relationship between segments, extents, and data blocks
The hierarchy of Oracle disk-space management is as follows:

• Tablespaces: These are at the highest level of Oracle disk-space management.
• Segments
• Extents
• Data blocks: These are at the lowest level of Oracle disk-space management.

The physical layer comprises the following files:

• The control file: Though it is the smallest file (1MB-5MB) of the database, it is the most crucial and critical file. The criticality of this file is ascribed to the fact that if it is damaged then the database recovery operation becomes tedious. It contains the requisite information to start the database. The names and locations of all the control files of the database can be obtained from the V$CONTROLFILE dynamic performance view.
• Data files: These hold data that is stored in tables of a database. They are usually the largest files in the database, ranging in size from MB, GB, to TB.
• Redo Log files: These files contain information that helps in recovery in the event of system failure. It contains information stored in the Redo Log Buffer. The information in the Redo Log Buffer is written into Redo Log files by the LGWR background process. The Redo Log files are generally multiplexed and copied for recovery purposes. Sets of redo Log files are known as Redo Log groups. Each database has minimum of two Redo Log groups. Redo Log groups are used in a circular fashion

Note:

1. The V$LOGFILE dynamic performance view is used to obtain information about the names and locations of the Redo Log groups and their members.
2. LGWR writes every three seconds to a Redo Log group in the case of the following events:
   • Whenever a user commits a transaction
   • Whenever the Redo Log Buffer is one-third (1/3) full

The Oracle memory structure
The most basic components of Oracle memory are:

• SGA- It stands for System Global Area. SGA is allocated whenever an Oracle instance starts and gets de-allocated when the Oracle instance is shutdown. Components of SGA:
  
  1. The Database Buffer Cache
  2. The Redo Log Buffer
  3. The Shared Pool


• PGA- It stands for Program Global Area. It is a region in memory that contains data and control information for a single process. This can be either a server or a background process. A PGA is allocated whenever an Oracle database user connects to a database and a session is created for him/her.

Oracle processes
There are two categories of processes that run with an Oracle database. They are mentioned below:

• User processes
• System processes

  The first interaction with the Oracle-based application comes from the user computer that creates a user process. The user process then communicates with the server process on the host computer. Here, PGA is used to store session specific information.
Oracle background processes
Oracle has a large number of background processes. The background processes are categorized into:

• Required Oracle background processes
• Optional Oracle background processes

Some of the background processes are:

1. DBWR – It writes data blocks from SGA to data files.
2. LGWR – It writes data from the Log Buffer to the redo log.
3. CKPT – It timestamps all the data files and the control files whenever a checkpoint occurs.
4. PMON – It stands for process monitor. It keeps track of database processes. It also cleans up the process that has died pre-maturely. The result is that all the cache and the resources are freed up. It also restarts those dispatcher processes that might have failed. Some of the main functions of background processes are:
   
   • Communication among Oracle instances
   • Performing computer maintenance
   • Writing the dirty blocks to disk
5. SMON – It stands for system monitor. It performs instance recovery at instance startup.

The Oracle instance
The Oracle instance consists of SGA and all the Oracle background processes. To manage the size of SGA, two initialization parameter files known as PFILE and SPFILE are used. There are a total of 250-initialization parameters. PFILE holds 30 of those 250 initialization parameters. Oracle does not recommend modifying the rest of the 220 initialization parameters.
This article has described the various facts involved in order to understand the underlying Oracle architecture. After reviewing this article, the database user is now well equipped to understand several components of Oracle architecture.