ORACLE ARCHITECTURE -4(PHYSICAL REPRESENTATION OF DATABASE)

ORACLE ARCHITECTURE -4(PHYSICAL REPRESENTATION OF DATABASE )

In this article we will cover Physical representation of Database.

Physical representation is nothing but Physical files(CRD).

From Oracle version 6  to 12c every database has three files. They are :

1.Controlfile

2.Redolog files

3.Datafiles

Please check below to get knowledge on Physical files

CONTROL FILE :

• Control file is a physical file.
• Control file contains all the crucial information of database like

–>DBname and ID

–>Name  & locations of datafiles

–> SCN# Number

–>Name  & locations of redolog files

–>Rman backup information

–>Archive information

• Control file is used to start the database.
• The size of control file is in lower MB’s
• We can multiplex the control files to 8 different location(Min-1   Max-8)
• We will give .ctl extension for the controlfile ,but the extension is not mandatory.It is understanding and only for our purpose.

 

REDO LOG FILE :

• Redolog file contains the latest transaction information
• Redolog files are used for recovery purpose.
• The size of redologfiles are lower in MB’s.
• The extension of redolog file is.log(Not mandatory).
• A group is a collection of members.
• A member belongs to one group.
• In a single database we can have of min-2 and max -16  groups.
• In each group we can have min-1 and max-5 members

 

DATAFILE :

• These are largest in your database.
• Datafiles  contains data
• We have two types of Data

1.Metadata

2.Business data

Metadata : 

A data about data is known as Metadata.

Using metadata we can find out how many users and objects are existing and everything

Business data(Actual data) :

The actual data is known as Business data.

If we select anything .It will shows the data that is actual data.

There are three mandatory datafiles in Oracle database :

1.SYSTEM

2.SYSAUX

3.UNDO

Optional datafiles

1.TEMPORARY FILE

2.USERDATA

3.USERINDEX etc….,

 

SYSTEM DATAFILE :

System datafile contains the base tables.

What is base table ?

A table contains the metadata is known as Base table.

All the base tables will be end with $ symbol

Ex : TAB$

COl$

VIEW$

SYN$

PRIV$ etc…..,

System datafiles contains metadata of base tables.

SYSAUX DATAFILE :

It has workload repositories tables like AWR,STATSPACK,STREAMS etc..,

System auxiliary uses 10G onwards.

Before 10G we used to store these workload repository tables in system datafile.

From 10G this is mandatory datafile.

UNDO DATAFILE :

• Undo tablespace contains before image of DML tx’s.
• In Oracle 9i this method of manual undo management is still available in addition to a new automatic method which frees DBAs from routine undo management tasks and tuning.
• Before 9i we used to store before image of DML tx’s  under Rollback segments.

• Enabling Automatic Undo Management :

• Since the default undo management mode is MANUAL, the instance must be told to use AUTO mode at instance startup. To do this the following initialization parameters can be set.

TEMPORARY DATAFILE :

Temporary tablespaces or datafiles are used to manage space for database sort and joining operations and for storing global temporary tables.

Space will be allocated in a temporary tablespace for doing these types of operations. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, SELECT DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

USERDATA  :

Userdata file contains actual data or business data.

USERINDEX :

Userindex is used to store all the index related information.