Oracle DB architecture study can be divided into following four groups:
• Physical Database Architecture
• Logical Database Architecture
• Memory Architecture
• Process Architecture
Physical Database Architecture:
Physical Database involves Operating System files of following files:
a) Parameter Files:
Parameter file is not a database file but it is very important. It is a simple text file that can be edited by any text editor. It contains a number of settings that affects the oracle database instance. One or more Parameter files are maintained. Parameter files contain following important settings:
• Location of database control file,
• Data Buffer Size to pool data into RAM,
• Buffer Size to keep SQL and PL/SQL reusable codes, etc
b) Control File:
Oracle uses control files to keep information about the state of the database. One or more Control files are maintained. A control file keeps following information:
• Database Name,
• Information about Tablespaces,
• Names and Locations of all data files,
• Names and Locations of all redo files,
• Checkpoint information, etc
c) Data Files:
The most important types of files are Data Files. One or more data files are maintained to keep complete database. They keep following types of data:
• Table Data,
• Index Data,
• Data Dictionary Definition,
• Rollback Data (Information used to undo transaction),
• PL/SQL Codes/Programs,
• Temporary Data,
d) Redo Files (Log Files):
Log Files are used to keep sequential record of changes to a database. As soon as an SQL statement changes the data in database, Oracle generates one or more redo log entries to record that change. There are two types of redo files: Online Redo Files and Offline Redo Files (Archived Redo Files). All Oracle databases use Online Redo Files but Offline Redo Files are optional.
Logical Database Architecture:
Logical Database architecture involves different objects which are abstraction of data from physical into logical. At the top of the object hierarchy is Database. Following main objects are important for studying logical database architecture:
a)Database:
A database is collection of Tablespaces identified by a unique ID. Database has no direct link with any of the physical files. A database is top level abstraction of data.
b)Tablespace:
A tablespace is collection of logical objects like tables, views, synonyms, etc. These objects are also grouped under another logical object named Schema/User. There is no direct relation between a Tablespace and a Schema. Tablespace has direct relation with physical data file. A tablespace may have one or more data files. Size of all data files collectively is the size of a tablespace.
c)Schema:
Schema is pure logical abstraction of data. It collects related objects required for one application or environment. Objects grouped under schema are table, View, Sequence, Index, Synonym, etc.
d)Data Objects:
These are the objects that represent logical data directly or indirectly like, Tables, Views, Synonyms, Index, etc.
Memory Architecture:
When an Oracle Instance starts up, it allocates a large block of memory known as SGA (System Global Area). All Oracle background processes use the SGA. In addition, each process associated with Oracle Instance will have its own private area or memory known as a Program Global Area (PGA). SGA is composed of following components:
a)Database Buffer Cache:
This area of SGA keeps the frequently used data from database. It is further divided into three parts:
i) Keep Buffer Pool: Keeps frequently accessed schema objects that must be kept
in memory all the time. Data read into this pool is retained until the Oracle Instance
is shut down.
ii) Recycle Buffer Pool: Keeps the object data that should be flushed out of memory as
quickly as possible.
iii) Default Buffer Pool: Keeps the data that does not fall into above two categories.
b)Redo Log Buffer:
It is an area in SGA where Oracle places Redo Log Entries that need to be written to the disk. Redo Log entries are written immediately to the disk to avoid data loss in case of power failure or instance crash.
c)Shared Pool:
The two main components of shared pool are Library Cache and Dictionary Cache. Library cache is further divided into Shared PL/SQL Area and Shared SQL Area. Dictionary Cache keeps the Data Dictionary information from database that is used during parsing of SQL statements. Other two caches keep the PL/SQL code and SQL statements that are used frequently by Oracle Instance.
d)Fixed SGA:
It keeps the number of values that Oracle Instance needs to keep the track of different process and status internally. Fixed SGA can not be tuned.
Process Architecture:
An Oracle instance (Oracle Server) is composed of processes and memory structures. An Oracle instance is composed of a number of processes called background processes. They are called background process because they are always running, whether or not any users are connected to the database. Some of the important processes are:
a)Database Writer (DBWR):
This process writes modified data blocks back to the Data Files.
b)Log Writer (LGWR):
Log Writer writes Redo Log entries to the Redo Log Files.
c)System Monitor (SMON):
System Monitor process performs crash recovery and coalesces free space.
d)Process Monitor (PMON):
This process watches for processes that are prematurely disconnected. It releases any lock that they hold and take care of any other necessary cleanup tasks.
e)Recoverer (RECO):
This process resolves distributed transaction.
f)Lock (LCK0):
These processes are used by parallel server option to manage inter-instance locking.
g)Checkpoint (CKPT):
This processes periodically checkpoint the database. Checkpointing is the process of recording the current system change number in all of the database files.
h)Archiver (ARC0):
These process copies filled redo log files to the archive log destination that can be Hard Disk, Tape, etc.
• Physical Database Architecture
• Logical Database Architecture
• Memory Architecture
• Process Architecture
Physical Database Architecture:
Physical Database involves Operating System files of following files:
a) Parameter Files:
Parameter file is not a database file but it is very important. It is a simple text file that can be edited by any text editor. It contains a number of settings that affects the oracle database instance. One or more Parameter files are maintained. Parameter files contain following important settings:
• Location of database control file,
• Data Buffer Size to pool data into RAM,
• Buffer Size to keep SQL and PL/SQL reusable codes, etc
b) Control File:
Oracle uses control files to keep information about the state of the database. One or more Control files are maintained. A control file keeps following information:
• Database Name,
• Information about Tablespaces,
• Names and Locations of all data files,
• Names and Locations of all redo files,
• Checkpoint information, etc
c) Data Files:
The most important types of files are Data Files. One or more data files are maintained to keep complete database. They keep following types of data:
• Table Data,
• Index Data,
• Data Dictionary Definition,
• Rollback Data (Information used to undo transaction),
• PL/SQL Codes/Programs,
• Temporary Data,
d) Redo Files (Log Files):
Log Files are used to keep sequential record of changes to a database. As soon as an SQL statement changes the data in database, Oracle generates one or more redo log entries to record that change. There are two types of redo files: Online Redo Files and Offline Redo Files (Archived Redo Files). All Oracle databases use Online Redo Files but Offline Redo Files are optional.
Logical Database Architecture:
Logical Database architecture involves different objects which are abstraction of data from physical into logical. At the top of the object hierarchy is Database. Following main objects are important for studying logical database architecture:
a)Database:
A database is collection of Tablespaces identified by a unique ID. Database has no direct link with any of the physical files. A database is top level abstraction of data.
b)Tablespace:
A tablespace is collection of logical objects like tables, views, synonyms, etc. These objects are also grouped under another logical object named Schema/User. There is no direct relation between a Tablespace and a Schema. Tablespace has direct relation with physical data file. A tablespace may have one or more data files. Size of all data files collectively is the size of a tablespace.
c)Schema:
Schema is pure logical abstraction of data. It collects related objects required for one application or environment. Objects grouped under schema are table, View, Sequence, Index, Synonym, etc.
d)Data Objects:
These are the objects that represent logical data directly or indirectly like, Tables, Views, Synonyms, Index, etc.
Memory Architecture:
When an Oracle Instance starts up, it allocates a large block of memory known as SGA (System Global Area). All Oracle background processes use the SGA. In addition, each process associated with Oracle Instance will have its own private area or memory known as a Program Global Area (PGA). SGA is composed of following components:
a)Database Buffer Cache:
This area of SGA keeps the frequently used data from database. It is further divided into three parts:
i) Keep Buffer Pool: Keeps frequently accessed schema objects that must be kept
in memory all the time. Data read into this pool is retained until the Oracle Instance
is shut down.
ii) Recycle Buffer Pool: Keeps the object data that should be flushed out of memory as
quickly as possible.
iii) Default Buffer Pool: Keeps the data that does not fall into above two categories.
b)Redo Log Buffer:
It is an area in SGA where Oracle places Redo Log Entries that need to be written to the disk. Redo Log entries are written immediately to the disk to avoid data loss in case of power failure or instance crash.
c)Shared Pool:
The two main components of shared pool are Library Cache and Dictionary Cache. Library cache is further divided into Shared PL/SQL Area and Shared SQL Area. Dictionary Cache keeps the Data Dictionary information from database that is used during parsing of SQL statements. Other two caches keep the PL/SQL code and SQL statements that are used frequently by Oracle Instance.
d)Fixed SGA:
It keeps the number of values that Oracle Instance needs to keep the track of different process and status internally. Fixed SGA can not be tuned.
Process Architecture:
An Oracle instance (Oracle Server) is composed of processes and memory structures. An Oracle instance is composed of a number of processes called background processes. They are called background process because they are always running, whether or not any users are connected to the database. Some of the important processes are:
a)Database Writer (DBWR):
This process writes modified data blocks back to the Data Files.
b)Log Writer (LGWR):
Log Writer writes Redo Log entries to the Redo Log Files.
c)System Monitor (SMON):
System Monitor process performs crash recovery and coalesces free space.
d)Process Monitor (PMON):
This process watches for processes that are prematurely disconnected. It releases any lock that they hold and take care of any other necessary cleanup tasks.
e)Recoverer (RECO):
This process resolves distributed transaction.
f)Lock (LCK0):
These processes are used by parallel server option to manage inter-instance locking.
g)Checkpoint (CKPT):
This processes periodically checkpoint the database. Checkpointing is the process of recording the current system change number in all of the database files.
h)Archiver (ARC0):
These process copies filled redo log files to the archive log destination that can be Hard Disk, Tape, etc.
