ORACLE : Introduction to Oracle Database

Introduction to Oracle Database

1.Oracle Database Architecture

The Oracle server architecture is basically categorized as follows:
· Instance (memory structure)
· Physical database structure
The Oracle Instance (memory structure)
The most basic components of Oracle memory are:
· SGA
· PGA

2. SGA
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
4. The Java Pool
5. The Streams Pool


3.PGA
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. A PGA is allocated whenever an Oracle database user connects to a database and a session is created for him/her.

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.

4.Background process and internal working mechanism

Oracle has a large number of background processes. The background processes are categorized into:
· Mandatory Oracle background processes
· Optional Oracle background processes
4.a .Mandatory Processes
v DBWR - It writes data blocks from SGA to data files.
v LGWR - It writes data from the Log Buffer to the redo log.
v CKPT - It timestamps all the data files and the control files whenever a checkpoint occurs.
v 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
v SMON - It stands for system monitor. It performs instance recovery at instance startup


5. Overview of Physical Database Structures

The following sections explain the physical database structures of an Oracle database, including datafiles, control files, redo log filesarchive log files, parameter files, alert and trace log files, and backup files.

Ø Datafiles
Ø Control Files
Ø Online Redo Log Files
Ø Archived Redo Log Files
Ø Parameter Files
Ø Alert and Trace Log Files
Ø Backup Files
The following sections explain the physical database structures of an Oracle database, including datafiles, redo log files, and control files.
Datafiles
Every Oracle database has one or more physical datafiles. The datafiles contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.
Control Files
Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database. For example, it contains the following information
· Database name
· Names and locations of datafiles and redo log files
· Time stamp of database creation
Redo Log Files
Every Oracle database has a set of two or more redo log files. The set of redo log files is collectively known as the redo log for the database. A redo log is made up of redo entries (also called redo records).
The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost.

Table Spaces
The physical database storage units, data files, are associated with table spaces according to the logical structure of the database. For example, table spaces may be created to separate different categories of data. Table spaces are divided into smaller logical divisions called segments, which are divided further into extents and data blocks. These levels of data storage allow control over how the data files are allocated for physical storage.
Figure shows how oracle stores table in datafiles

Reference:
http://oraclenz.com/__oneclick_uploads/2008/06/oracle_11_concepts.pdf
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/intro.htm#sthref35
http://www.ucertify.com/article/the-oracle-10g-architecture.html
http://www.oracle.com/technology/tech/migration/isv/docs/OracleArchitectureOverview.pdf