Wednesday, February 4, 2009

Oracle Physical Structure

hi friends,

In last topic we discussed about basic oracle architecture. In this discussion we will discuss physical structure of oracle database. But before that, we have to learn the logical architecture which is involved in physical structure.

confused....???? let's see.

Oracle physical structure which include logical structure is as follows:-

Logical Structures

Oracle database is divided into smaller logical units to manage, store, and retrieve data efficiently. The logical units are tablespace, segment, extent, and data block. The following figure will explain it more clearly. Have a look:



Tablespace

Tablespace is a logical structure which holds other logical structure of database. Tablespace is further broken into segments. segment is further broken into extents. This way tablespace holds the segments , extents and datafiles for a particular purpose. Like we have system tablespace, users tablespace, index tablespace and etc., All tablespace segments and extents for that particular purpose. Tablespace can be named, as we seen in above example.

Segments

A Tablespace is further broken into segments. A segment is used to stores same type of objects. That is, every table in the database will store into a specific segment (named Data Segment) and every index in the database will also store in its own segment (named Index Segment). The other segment types are Temporary Segment and 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. Unlike a tablespace or a segment, an extent cannot be named.

Data Block

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

Now lets have a look at actual physical structure of oracle database. It is a structure which holds your actual data. It consist of some files, which resides on disk. These files are datafiles, control files, redo log files and password file.

Datafiles

Datafiles holds actual data of oracle database.A datafile is a file that correspondens with a tablespace. One datafile can be used by one tablespace, but one tablespace can has more than one datafiles. When ever user request data, oracle retrieve that data from these datafiles with the help of some processes in the SGA (System Global Area). And also manipulated data is written back to these datafiles, so that changed data is available to all users.

Redo Log files

Redo log files are the files that store the redo entries generated by DML statements. It can be used for recovery processes. These files also stores data, but contain only changes made to database. These changes are useful when recovering database. These file can be archived, that is can be copied offline for recovery purpose. this process is called archiving. These files belongs to certain group. We will discuss redo log files in more details later.

Control Files

Control files are used to store information about physical structure of database, such as datafiles size and location, redo log files location, etc. Control files are very crucial for database operation.
An Oracle database cannot be started without at least one control file.It contains (but is not limited to) the following types of information:

* Database information (RESETLOGS SCN and their time stamp)
* Archive log history
* Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not)
* Redo threads (current online redo log)
* Database's creation date
* database name
* current archive log mode
* Log records (sequence numbers, SCN range in each log)
* RMAN catalog
* Database block corruption information
* Database ID, which is unique to each DB

Hope you have got a bit of information about from these blogs. We will definitely discuss all structures in details in our future topics.

7 comments:

  1. Hi Vikrant,

    My self i am pavan.I am interested learning oracle technology.

    I have a bit of knowledge on sql, now that i am focused to learn the technology.

    Mr.Aman has advised me to go through you blog.

    I really appreciate your idea of sharing your knowledge.

    For novices like me it is really useful.

    Looking forward for further updates.

    pavan

    ReplyDelete
  2. @Pavan,
    Good to see you here buddy :-).
    Aman....

    ReplyDelete
  3. hey pavan sir,

    nice to see your here.

    i will be honored if you can share your experience with us here.

    and i know you are expert, let us learn us something from you.

    thanks and regards
    VD

    ReplyDelete
  4. hi vikranth,

    You are under a great mis consumption..

    I have just started learning the technology..

    Please dont tag me with sir.

    thanks,
    pavan

    ReplyDelete
  5. let me share my experience with regard to the service of how do i reinstall my sql server?, it automatically eliminates data corruption issues in selected databases

    ReplyDelete
  6. Hi, Vikrant

    Recently i have passed SQL and now i am preraring for 10 g administration-1 and genuinely your knowledge helped me a lot......

    THANKS....

    CHINMAY CHOUDHARY

    ReplyDelete
  7. I think all of you have basic knowledge about Oracle. This post explains the physical and logical structure of Oracle. This is described in very easy and understandable pattern. I think one can get clear view after reading the post. thanks for the work.
    sap pp

    ReplyDelete