Oracle Architecture
Logical/Memory Architecture
Oracle Architecture :- Oracle system divided into two category
1) Logical/Memory Architecture (SGA)
2) Physical/File Architecture
Logical/Memory Architecture:- This is also known as SGA (System Global Area). Below are the components of memory architecture.
Shared pool:-It contains machine-language code and execution plans for frequently used SQL commands. Below are the components of shared pool
a) Library Cache :-
b) Data Dictionary Cache :-
c) Result Cache :-
d) UGA (User Global Area)
Database Buffer Cache:-It stores data values which are written later to the data files by the database writer (DBWn).
a) Active Block :-These are blocks where DBWR processes are writing.
b) Dirty Block :- These are blocks whose data need to written in datafiles
c) Free Block :- These are blocks which are used to write data .
Redo Log Buffer:-It stores a copy of the changed data from user transaction. This data is periodically written to
the Redo Log Files by the Log Writer (LGWR).
Large Pool:-It is a work area given for backup and recovery operations.
Java Pool:-It stores the machine-language and execution plans for Java commands used in application programs and database operations
Background Processes :- Below are the main background processes
a) SMON b) PMON c) MMON d) DBWR e) LGWR f) ARCH g) CKPT h) RECO
a) SMON
c) MMON
d) DBWR :- Database writer (DBWn) process is used to write data from buffer cache to the data files and from data files to buffer cache.
DBWn writes when:
e) LGWR :- Log writer (LGWR) process is used to write data from Log buffer cache to redo log files.
f) CKPT
g) ARCH
Archiver (ARCn)
h) RECO
1) Parameter File
a) spfile (server parameter file) :- This is a default file which is read by system when Oracle database is going start. After reading this file, SGA created on the RAM and system enter into nomount stage. This is binary file that's why we can not read the content of this file. For reading the content of spfile, we need to create pfile from spfile by below SQL statement.
SQL>CREATE PFILE FROM SPFILE;
b) pfile (parameter file) :- This is a text file created from spfile and we can changed the value of parameter defined into file.
2) Control File:-
3) Redo Log File
4) Data File
a) Permanent datafile
b) Undo datafile
c) Temporary datafile
5) Archive Log File
6) Password File
Important Data Dictionaries for Files Architecture :-
select * from v$parameter;
select * from v$controlfile;
select * from v$log;
select * from v$logfile;
select * from v$datafile;
2) Physical/File Architecture
Logical/Memory Architecture:- This is also known as SGA (System Global Area). Below are the components of memory architecture.
Shared pool:-It contains machine-language code and execution plans for frequently used SQL commands. Below are the components of shared pool
a) Library Cache :-
b) Data Dictionary Cache :-
c) Result Cache :-
d) UGA (User Global Area)
Database Buffer Cache:-It stores data values which are written later to the data files by the database writer (DBWn).
a) Active Block :-These are blocks where DBWR processes are writing.
b) Dirty Block :- These are blocks whose data need to written in datafiles
c) Free Block :- These are blocks which are used to write data .
Redo Log Buffer:-It stores a copy of the changed data from user transaction. This data is periodically written to
the Redo Log Files by the Log Writer (LGWR).
Large Pool:-It is a work area given for backup and recovery operations.
Java Pool:-It stores the machine-language and execution plans for Java commands used in application programs and database operations
Background Processes :- Below are the main background processes
a) SMON b) PMON c) MMON d) DBWR e) LGWR f) ARCH g) CKPT h) RECO
a) SMON
System Monitor (SMON) Responsibilities:
- Instance recovery
- Rolls forward changes in redo logs
- Opens database for user access
- Rolls back uncommitted transactions
- Coalesces free space
- Deallocates temporary segments.
Process Monitor (PMON) Cleans up after failed processes by:
• Rolling back the transaction
• Releasing locks
• Releasing other resources
• Restarting dead dispatchers
c) MMON
d) DBWR :- Database writer (DBWn) process is used to write data from buffer cache to the data files and from data files to buffer cache.
DBWn writes when:
• Checkpoint occurs
• Dirty buffers reach threshold
• There are no free buffers
• Timeout occurs
• RAC ping request is made
• Tablespace OFFLINE
• Tablespace READ ONLY
• Table DROP or TRUNCATE
• Tablespace BEGIN BACKUP
e) LGWR :- Log writer (LGWR) process is used to write data from Log buffer cache to redo log files.
Log Writer (LGWR) writes:
• At commit
• When 1/3rd full
• When there is 1 MB of redo
• Every 3 seconds
• Before DBWn writes
f) CKPT
Checkpoint (CKPT) Responsible for:
• Signaling DBWn at checkpoints
• Updating datafile headers with checkpoint information
• Updating control files with checkpoint information
g) ARCH
Archiver (ARCn)
• Optional background process
• Automatically archives online redo logs when ARCHIVELOG mode is set
• Preserves the record of all changes made to the database
h) RECO
Physical/File Architecture
Physical/File Architecture:- Below are the components of File Architecture1) Parameter File
a) spfile (server parameter file) :- This is a default file which is read by system when Oracle database is going start. After reading this file, SGA created on the RAM and system enter into nomount stage. This is binary file that's why we can not read the content of this file. For reading the content of spfile, we need to create pfile from spfile by below SQL statement.
SQL>CREATE PFILE FROM SPFILE;
b) pfile (parameter file) :- This is a text file created from spfile and we can changed the value of parameter defined into file.
2) Control File:-
3) Redo Log File
4) Data File
a) Permanent datafile
b) Undo datafile
c) Temporary datafile
5) Archive Log File
6) Password File
Important Data Dictionaries for Files Architecture :-
select * from v$parameter;
select * from v$controlfile;
select * from v$log;
select * from v$logfile;
select * from v$datafile;
No comments:
Post a Comment