Oracle Database Architecture

Oracle Database Architecture:

Overview of the System Global Area:

When instance is started SGA is allocated the memory from RAM. SGA memory allocation can not be more than the RAM size.Once instance is shut down the memory is given back to Operating system.

If more memory required than the allocated memory to SGA, In that case it will use the SWAP memory and this memory is compulsory component when ora
cle database is installed.

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.

SGA and oracle processes(DBWR, LGWR, CHKPT, SMON, PMON REC, ARCH etc.) collectively called as instance of an oracle database.

The SGA components are as given below:

• Shared pool (Data dictionary cache and Library cache)
• Database buffer cache
• Redo log buffer
• Java pool
• Large pool 
• Streams pool

Instance is responsible for fulfilling the request through server process. Server process can be either shared server or dedicated server architecture. Server process takes the required data from database to SGA and then provide to user request.

Logical 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:

• Tablespaces: These are at the highest level of Oracle disk-space management.
• Segments: Collection of Extents.
• Extents: One or more data block collectively called as Extents.
• Data blocks: These are at the lowest level of Oracle disk-space management.

The physical layer comprises the following files: 

Control File: Though it is very small file, it is the most crucial and critical file. This file is responsible for starting up the database into open mode and availabe for user request. 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 view.

It also contains the name of database, timestamp of database creation, information of data file and redolog files which helps in recovery.

Control file has two section. Fixed section and Rotation section. Rotation section keeps the information till 7 days by default.We can change it by changing the initialization parameter control_file_record_keep_time.

Data files: Data files are used to store data in blocks for each and every tables of a database.Size of datafiles may vary from kilobytes to terabytes(KB,MB,GB and TB). Most of the space is being occupied by data files in any database.

Redo Log files: Any changes made in data block are captured into redo log buffer and then into redo log files. These files are very helpful in recovery in case of system failure. 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. Redo log groups can be added as per our need.

Note: The V$LOGFILE dynamic performance view is used to obtain information about the names and locations of the Redo Log groups and their members.

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. Database Buffer Cache: It contains the data read from data files and data updated.It  has 3 area namely: Free buffer: does not contain any useful data. Dirty buffer: Contains the modified data and ready to be written in data files(by DBWR). Pinned buffer: Currently being used by active user.
2. Redo Log Buffer: Contains the information modified in data buffer and ready to be written to redo log files(By LGWR).
3.  Shared Pool: It has two section as : Library cache: The library cache contains the current SQL execution plan information. It also holds stored procedures and trigger code. Data Dictionary Cache: The dictionary cache stores environmental information, which includes referential integrity, table definitions, indexing information, and other metadata stored within Oracle's internal tables.
4. Large Pool: This area is only used if shared server architecture, also called multi-threaded server (MTS), is used, or if parallel query is utilized. LARGE_POOL_SIZE = integer. Session memory for the shared server and the Oracle XA interface which is used where transactions interact with more than one database.  I/O server processes. Oracle backup and restore operations.
5. Java Pool: Contains all java files.
6. Stream Pool: Used for data stream from one system to another.

PGA- Program Global Area 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.

PGA components:

1. Session Area- memory allocated to hold a session variables (login information) and other information related to the session. For a shared server, the session memory is shared and not private.
2. SQL work area- This is also called as sort area. All SQL operations like joining,sorting,groupby, orderby are done in this area.
3. Private SQL Area- A private SQL area contains data such as bind information and runtime memory structures. Each session that issues an SQL statement has a private SQL area.
4. Cursor area - Explicit cursor and Implicit Cursor

Background Process: These are the process which enable the databse to work continuously and are listed below:

1. DBWR : It writes data blocks from dirty buffer of data buffer cache to data files.
2. LGWR : It writes data from the Log Buffer to the redo log. LGWR writes to a Redo Log files in case of these events: #Whenever a user commits a transaction. #Whenever the Redo Log Buffer is one-third (1/3) full. #Every three seconds.
3. CKPT : It updates the header of data file and control file with current timestamp and SCN.
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.
5. SMON : It stands for system monitor. It performs instance recovery at instance startup. It cleans up the segment which are no longer in use.

Oracle processes:

• User processes
• Server 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 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. 

Brief of Architecture:

SGA = Shared Pool + Data buffer Cache + Redo Log + LArge Pool + Stream Pool + Java Pool 

Oracle Instance = SGA + Background Process

Oracle database = Oracle Instance + datafiles + Controlfile + Redo log files

 

Thanks

Srini