Sunday, February 14, 2010

Oracle Database Architecture

The Oracle database has a logical layer and a physical layer.The physical layer consists of the files that reside on the disk and logical layer map the data to these files of physical layer

Oracle physical Structure
The physical database structure comprises of datafiles, redo log files and control files

Datafiles contain database's data. The data of logical data structures such as tables and indexes is stored in datafiles of the database. One or more datafiles form a logical unit of database storage called a tablespace.

Redo log files:
The purpose of these files is to record all changes made to data. These files protect database against failures.

Control files:
Control files contain entries such as database name, name and location of datafiles and redo log files and time stamp of database creation.

Oracle Logical Database Structure
Logical structures include tablespaces, schema objects, data blocks, extents and segments.

Database is logically divided into one or more tablespaces. Each tablespace creates one or more datafiles to physically store data.

SYSTEM tablespace is automatically created when the database is created. It contains data dictionary for the entire database.

Schema objects:
Schema objects are the structure that represents database's data. Schema objects include structures such as tables, views, sequences, stored procedures, indexes, synonyms, clusters and database links.
These schema objects consist of structures such as:
  • tables
  • clusters
  • indexes
  • views
  • stored procedures
  • database triggers
  • sequences
Data Blocks:
Data block represents specific number of bytes of physical database space on disk.

An extent represents continuous data blocks that are used to store specific data information.

A segment is a set of extents allocated for a certain logical structure.

Define Data-Dictionary Cache:
Data-Dictionary Cache keeps information about the logical and physical structure of the database.
The data dictionary contains information such as the following:
User information, such as user privileges Integrity constraints defined for tables Names and data types of all columns in database tables Information on space allocated and used for schema objects.

SGA (System Global Area)

Shared Pool:
This memory structure is divided into two sub-structures which are Library Cache and Data Dictionary Cache for storing recently used PL/SQL statements and the recent data definitions. The maximum size of the Shared Pool depends on the SHARED_POOL_SIZE parameter.

Database Buffer Cache:
This memory structure improves the performance while fetching or updating the recently used data as it stores the recently used datafiles. The size of this block is decided by DB_BLOCK_SIZE.

Redo Log Buffer:
This memory structure is used to store all the changes made to the database and it's primarily used for the data recovery purposes. The size of this block is decided by LOG_BUFFER.

Java Pool:
This memory structure is used when Java is installed on the Oracle server. Size that can be used is stored in parameter named JAVA_POOL_SIZE.

Large Pool:
This memory structure is used to reduce the burden of the Shared Pool, as the Session memory for the Shared Server, as the temporary storage for the I/O and for the backup and restore operations or RMAN. Parameter that stores the maximum size is LARGE_POOL_SIZE.

Program Global Area (PGA)

The PGA is a memory area that contains data and control information for the Oracle server processes. This area consists of the following components:
Stack space--This holds the session's variables and arrays.
Session information--If you are not running the multithreaded server, the session information is stored in the PGA. If you are running the multithreaded server, the session Private SQL area--This area keeps information about binding variables and runtime buffers.

Types of Segments

Data Segments:
There is a single data segment to hold all the data of every non clustered table in an oracle database. This data segment is created when you create an object with the CREATE TABLE/SNAPSHOT/SNAPSHOT LOG command. Also, a data segment is created for a cluster when a CREATE CLUSTER command is issued.
The storage parameters control the way that its data segment's extents are allocated. These affect the efficiency of data retrieval and storage for the data segment associated with the object.

Index Segments:
Every index in an Oracle database has a single index segment to hold all of its data. Oracle creates the index segment for the index when you issue the CREATE INDEX command. Setting the storage parameters directly affects the efficiency of data retrieval and storage.

Rollback Segments:
Rollbacks are required when the transactions that affect the database need to be undone. Rollbacks are also needed during the time of system failures. The way the roll-backed data is saved in rollback segment, the data can also be redone which is held in redo segment.
A rollback segment is a portion of the database that records the actions of transactions if the transaction should be rolled back. Each database contains one or more rollback segments. Rollback segments are used to provide read consistency, to rollback transactions, and to recover the database.
Types of rollbacks:
- statement level rollback
- rollback to a savepoint
- rollback of a transaction due to user request
- rollback of a transaction due to abnormal process termination
- rollback of all outstanding transactions when an instance terminates abnormally
- rollback of incomplete transactions during recovery.

Temporary Segments:
The SELECT statements need a temporary storage. When queries are fired, oracle needs area to do sorting and other operation due to which temporary storages are useful.

Locking in oracle
Locking protect table when several users are accessing the same table. Locking is a concurrency control technique in oracle. It helps in data integrity while allowing maximum concurrency access to data. Oracle offers automatic locking whenever situation requires. This is called implicit locking.

Shared Lock
This type is placed on a record when the record is being viewed.

Exclusive lock
This is placed when Insert, Update or Delete command is performed. There can be only one exclusive lock on a record at a time.
What are background processes in oracle?

No comments:

Post a Comment