Oracle Database Physical & Logical storage architecture

Oracle storage design of database architecture is interesting and good. The concepts of having Physical & Logical components are really helpful. For example, DBAs can maintain the physicals structures while the logical instance are still running.

Clearly isolating this two tiers are helpful to DBA & developers. Developer do not need to remember which data file that the database is assigned. Administrator can scale the Tablespaces as and when needed to expand or reduce by adding or reducing the data files.

PHYSICAL

Datafiles

• Take note : A datafile can only be assigned to a tablespace, but a tablespace can have many datafiles

• When ever user request data, oracle retrieve that data from these datafiles with the help of processes in the SGA (System Global Area). Manipulated data is written back to these datafiles, so that changed data is available to all users.

Beside the physical data files, there also other physical files like the redo log files, and control files.

Redo Log files

• The redo log files store the change data entries generated by DML. 
• It will be used during database recovery processes. 
• Ofter  redo log files  are  also archived, and it is copied offline for recovery purpose. (archived redo logs)

Control Files

• Control files is important file to start the DB instances.It contatin the initiatisation info of the DB(init.ora)
• It record information about physical structure of database, such as datafiles size and location, redo log files location.

LOGICAL

Tablespace

• Tablespace is a logical structure which holds other logical structure of database. 
• This is further broken into segments. segment is further broken into extents, and extent intodata block. 
• Tablespaces have two main type: Data & Index TS and Undo TS.

Segments

• Table in the database will store into a  Data Segment.
• Index in the database will  be store in Index Segment. 
• Temporary Segment
• Rollback Segment.

Extents

• A segment is further broken into extents. 
• An extent consists of one or more data block. 
• When the database object is enlarged, an extent will be allocated. 
• An extent cannot be named.

Data Block

• Smallest unit of storage in the database. 
• The data block size is a specific number of bytes within tablespace and it has the same number of bytes. 
• It consist of multiple operating system blocks.