Oracle architecture

A Descriptive Presentation About Oracle.....

1. Introduction to OracleIntroduction to Oracle

Physical StructurePhysical Structure

Logical StructureLogical Structure

SGA / PGASGA / PGA

Background ProcessesBackground Processes

Backup MethodsBackup Methods

Computer Science Database – CS01Computer Science Database – CS01

Administrative TasksAdministrative Tasks

3. Physical StructuresPhysical Structures

Datafiles (*.dbf)Datafiles (*.dbf)

The datafiles contain all the database data. The data of logical database The datafiles contain all the database data. The data of logical database
structures, such as tables and indexes, is physically stored in the datafiles structures, such as tables and indexes, is physically stored in the datafiles
allocated for a database. allocated for a database.

Control Files (*.ctl)Control Files (*.ctl)

Every Oracle database has a control file. A control file contains entries that Every Oracle database has a control file. A control file contains entries that
specify the physical structure of the database such as Database name and the specify the physical structure of the database such as Database name and the
Names and locations of datafiles and redo log files.Names and locations of datafiles and redo log files.

Redo Log Files (*.log)Redo Log Files (*.log)

The primary function of the redo log is to record all changes made to data. If a The primary function of the redo log is to record all changes made to data. If a
failure prevents modified data from being permanently written to the datafiles, failure prevents modified data from being permanently written to the datafiles,
then the changes can be obtained from the redo log, so work is never lost. then the changes can be obtained from the redo log, so work is never lost.

4. Physical Structures (cont’d)Physical Structures (cont’d)

Archive Log Files (*.log)Archive Log Files (*.log)

Oracle automatically archives log files when the database is in ARCHIVELOG Oracle automatically archives log files when the database is in ARCHIVELOG
mode. This prevents oracle from overwriting the redo log files before they have mode. This prevents oracle from overwriting the redo log files before they have
been safely archived to another location.been safely archived to another location.

Parameter Files (initSID.ora)Parameter Files (initSID.ora)

Parameter files contain a list of configuration parameters for that instance and Parameter files contain a list of configuration parameters for that instance and
database.database.

Alert and Trace Log Files (*.trc)Alert and Trace Log Files (*.trc)

Each server and background process can write to an associated trace file. When an internal Each server and background process can write to an associated trace file. When an internal
error is detected by a process, it dumps information about the error to its trace file. The alert error is detected by a process, it dumps information about the error to its trace file. The alert
log of a database is a chronological log of messages and errors.log of a database is a chronological log of messages and errors.

5. Logical StructuresLogical Structures

TablespacesTablespaces

A database is divided into logical storage units called tablespaces, which group A database is divided into logical storage units called tablespaces, which group
related logical structures together. One or more datafiles are explicitly created for related logical structures together. One or more datafiles are explicitly created for
each tablespace to physically store the data of all logical structures in a each tablespace to physically store the data of all logical structures in a
tablespace.tablespace.

Oracle Data BlocksOracle Data Blocks

At the finest level of granularity, Oracle database data is stored in data blocks. At the finest level of granularity, Oracle database data is stored in data blocks.
One data block corresponds to a specific number of bytes of physical database One data block corresponds to a specific number of bytes of physical database
space on disk. The standard block size is specified by the DB_BLOCK_SIZE space on disk. The standard block size is specified by the DB_BLOCK_SIZE
initialization parameter. initialization parameter.

6. Logical Structures (cont’d)Logical Structures (cont’d)

ExtentsExtents

The next level of logical database space is an extent. An extent is a specific The next level of logical database space is an extent. An extent is a specific
number of contiguous data blocks, obtained in a single allocation, used to store a number of contiguous data blocks, obtained in a single allocation, used to store a
specific type of information.specific type of information.

SegmentsSegments

Above extents, the level of logical database storage is a segment. A segment is Above extents, the level of logical database storage is a segment. A segment is
a set of extents allocated for a certain logical structure. The different types of a set of extents allocated for a certain logical structure. The different types of
segments are :segments are :

Data segment – stores table dataData segment – stores table data

Index segment – stores index dataIndex segment – stores index data

Temporary segment – temporary space used during SQL executionTemporary segment – temporary space used during SQL execution

Rollback Segment – stores undo informationRollback Segment – stores undo information

7. Logical Structures (cont’d)Logical Structures (cont’d)

Schema OverviewSchema Overview

A schema is a collection of database objects. A schema is owned by a database A schema is a collection of database objects. A schema is owned by a database
user and has the same name as that user. Schema objects are the logical user and has the same name as that user. Schema objects are the logical
structures that directly refer to the database's data. Schema objects include structures that directly refer to the database's data. Schema objects include
structures like tables, views, and indexes. structures like tables, views, and indexes.

9. Oracle InstanceOracle Instance
An Oracle database server consists of an An Oracle database server consists of an
Oracle database and an Oracle instance. Oracle database and an Oracle instance.
Every time a database is started, a system Every time a database is started, a system
global area (SGA) is allocated and Oracle global area (SGA) is allocated and Oracle
background processes are started. The background processes are started. The
combination of the background processes combination of the background processes
and memory buffers is called an Oracle and memory buffers is called an Oracle
instance. instance.

10. System Global Area (SGA)System Global Area (SGA)
The System Global Area (SGA) is a shared memory region that contains data and The System Global Area (SGA) is a shared memory region that contains data and
control information for one Oracle instance. Users currently connected to an Oracle control information for one Oracle instance. Users currently connected to an Oracle
database share the data in the SGA. The SGA contains the following memory database share the data in the SGA. The SGA contains the following memory
structures :structures :

Database Buffer CacheDatabase Buffer Cache

Database buffers store the most recently used blocks of data. The set of Database buffers store the most recently used blocks of data. The set of
database buffers in an instance is the database buffer cache. The buffer cache database buffers in an instance is the database buffer cache. The buffer cache
contains modified as well as unmodified blocks. Because the most recently (and contains modified as well as unmodified blocks. Because the most recently (and
often, the most frequently) used data is kept in memory, less disk I/O is often, the most frequently) used data is kept in memory, less disk I/O is
necessary, and performance is improved.necessary, and performance is improved.

11. System Global Area (cont’d)System Global Area (cont’d)

Redo Log Buffer of the SGARedo Log Buffer of the SGA

The redo log buffer stores redo entries—a log of changes made to the database. The redo log buffer stores redo entries—a log of changes made to the database.
The redo entries stored in the redo log buffers are written to an online redo log, The redo entries stored in the redo log buffers are written to an online redo log,
which is used if database recovery is necessary. The size of the redo log is which is used if database recovery is necessary. The size of the redo log is
static.static.

Shared Pool of the SGAShared Pool of the SGA

The shared pool contains shared memory constructs, such as shared SQL areas. The shared pool contains shared memory constructs, such as shared SQL areas.
A shared SQL area is required to process every unique SQL statement submitted A shared SQL area is required to process every unique SQL statement submitted
to a database. A shared SQL area contains information such as the parse tree to a database. A shared SQL area contains information such as the parse tree
and execution plan for the corresponding statement. and execution plan for the corresponding statement.

12. Program Global Area (PGA)Program Global Area (PGA)
PGA is a memory buffer that contains data and control information for a PGA is a memory buffer that contains data and control information for a
server process. A server process is a process that services a client’s server process. A server process is a process that services a client’s
requests. A PGA is created by oracle when a server process is started. The requests. A PGA is created by oracle when a server process is started. The
information in a PGA depends on the oracle configuration. The PGA area is information in a PGA depends on the oracle configuration. The PGA area is
a non-shared area of memory created by oracle when a server process is a non-shared area of memory created by oracle when a server process is
started. started.
The basic difference between SGA and PGA is that PGA cannot be shared The basic difference between SGA and PGA is that PGA cannot be shared
between multiple processes in the sense that it is used only for between multiple processes in the sense that it is used only for
requirements of a particular process whereas the SGA is used for the whole requirements of a particular process whereas the SGA is used for the whole
instance and it is shared. instance and it is shared.

13. Oracle Background ProcessesOracle Background Processes
An Oracle database uses memory structures and processes to manage and access An Oracle database uses memory structures and processes to manage and access
the database. All memory structures exist in the main memory of the computers that the database. All memory structures exist in the main memory of the computers that
constitute the database system. Processes are jobs that work in the memory of these constitute the database system. Processes are jobs that work in the memory of these
computers.computers.
Oracle creates a set of background processes for each instance. The background Oracle creates a set of background processes for each instance. The background
processes consolidate functions that would otherwise be handled by multiple Oracle processes consolidate functions that would otherwise be handled by multiple Oracle
programs running for each user process. They asynchronously perform I/O and programs running for each user process. They asynchronously perform I/O and
monitor other Oracle processes to provide increased parallelism for better monitor other Oracle processes to provide increased parallelism for better
performance and reliability.performance and reliability.
The most common background processes are :The most common background processes are :

System Monitor – SMONSystem Monitor – SMON

This database background process performs instance recovery at the start of the This database background process performs instance recovery at the start of the
database. SMON also cleans up temporary segments that are no longer in use database. SMON also cleans up temporary segments that are no longer in use
and recovers dead transactions skipped during crash and instance recovery and recovers dead transactions skipped during crash and instance recovery
because of file-read or offline errors. It coalesces i.e. combines contiguous free because of file-read or offline errors. It coalesces i.e. combines contiguous free
extents into larger free extents.extents into larger free extents.

14. Background Processes (cont’d)Background Processes (cont’d)

Process Monitor - PMONProcess Monitor - PMON

This database background process cleans up failed user processes. PMON is This database background process cleans up failed user processes. PMON is
responsible for releasing the lock i.e. cleaning up the cache and freeing responsible for releasing the lock i.e. cleaning up the cache and freeing
resources that the process was using. Its effect can be seen when a process resources that the process was using. Its effect can be seen when a process
holding a lock is killed.holding a lock is killed.

Database Writer - DBWRDatabase Writer - DBWR

This background process is responsible for managing the contents of the data This background process is responsible for managing the contents of the data
block buffer cache and dictionary cache. DBWR performs batch writes of block buffer cache and dictionary cache. DBWR performs batch writes of
changed block. Since Oracle uses write-ahead logging, DBWR does not need to changed block. Since Oracle uses write-ahead logging, DBWR does not need to
write blocks when a transaction commits. In the most common case, DBWR write blocks when a transaction commits. In the most common case, DBWR
writes only when more data needs to be read into the system global area and too writes only when more data needs to be read into the system global area and too
few database buffers are free. The least recently used data is written to the few database buffers are free. The least recently used data is written to the
datafiles first.datafiles first.

Although there is only one SMON and one PMON process running per database Although there is only one SMON and one PMON process running per database
instance, one can have multiple DBWR processes running at the same time. instance, one can have multiple DBWR processes running at the same time.
Note the number of DBWR processes running is set via the Note the number of DBWR processes running is set via the
DB_WRITER_PROCESSES.DB_WRITER_PROCESSES.

15. Background Processes (cont’d)Background Processes (cont’d)

Log Writer - LGWRLog Writer - LGWR

This background process manages the writing of the contents of the redo log This background process manages the writing of the contents of the redo log
buffer to the online redo log files. LGWR writes the log entries in batch form. The buffer to the online redo log files. LGWR writes the log entries in batch form. The
Redo log buffers entries always contain the most up-to-date status of the Redo log buffers entries always contain the most up-to-date status of the
database.database.

Archiver - ARCHArchiver - ARCH

The Archiver process reads the redo log files once Oracle has filled them and The Archiver process reads the redo log files once Oracle has filled them and
writes a copy of the used redo log files to the specified archive log destination(s). writes a copy of the used redo log files to the specified archive log destination(s).
Actually, for most databases, ARCH has no effect on the overall system Actually, for most databases, ARCH has no effect on the overall system
performance. On some large database sites, however, archiving can have an performance. On some large database sites, however, archiving can have an
impact on system performance. impact on system performance.

16. Background Processes (cont’d)Background Processes (cont’d)

Checkpoint - CKPTCheckpoint - CKPT

All modified information in database buffer in the SGA is written to the datafiles All modified information in database buffer in the SGA is written to the datafiles
by a database write process (DBWR). This event indicates a checkpoint. The by a database write process (DBWR). This event indicates a checkpoint. The
checkpoint process is responsible for signaling DBWR at checkpoints and checkpoint process is responsible for signaling DBWR at checkpoints and
updating all of the datafiles and control files of the database.updating all of the datafiles and control files of the database.

Recover - RECORecover - RECO

The recover process automatically cleans up failed or suspended distributed The recover process automatically cleans up failed or suspended distributed
transactions.transactions.

Job Queue ProcessesJob Queue Processes

Job queue processes are used for batch processing. They run user jobs. They Job queue processes are used for batch processing. They run user jobs. They
can be viewed as a scheduler service that can be used to schedule jobs as can be viewed as a scheduler service that can be used to schedule jobs as
PL/SQL statements or procedures on an Oracle instance. Given a start date and PL/SQL statements or procedures on an Oracle instance. Given a start date and
an interval, the job queue processes try to run the job at the next occurrence of an interval, the job queue processes try to run the job at the next occurrence of
the interval.the interval.

18. Computer Science DatabaseComputer Science Database
Server InformationServer Information

Sun e4500Sun e4500

8GB Ram8GB Ram

8 x 400mhz CPU8 x 400mhz CPU

32GB Disk for 32GB Disk for
OracleOracle

4mm DAT DDS3 4mm DAT DDS3
Tape BackupTape Backup

19. Computer Science DatabaseComputer Science Database

Instance Name : CS01 (v$database)Instance Name : CS01 (v$database)

Instance Version : 8.1.6.0.0Instance Version : 8.1.6.0.0

Tablespaces : (dba_tablespaces)Tablespaces : (dba_tablespaces)

SYSTEM – holds all system tablesSYSTEM – holds all system tables

INDEX01 – user indexesINDEX01 – user indexes

USERS01 – user tablesUSERS01 – user tables

USERS02 – user tables (faculty)USERS02 – user tables (faculty)

RBS – rollback segmentsRBS – rollback segments

21. Backup MethodsBackup Methods

Cold Backup (aka Consistent Backups)Cold Backup (aka Consistent Backups)

The only way to make a consistent whole database The only way to make a consistent whole database
backup is to shut down the database with the backup is to shut down the database with the
NORMAL, IMMEDIATE, or TRANSACTIONAL NORMAL, IMMEDIATE, or TRANSACTIONAL
options and make the backup while the database is options and make the backup while the database is
closed.closed.

Advantage : No recovery is required after datafiles are Advantage : No recovery is required after datafiles are
restored – quicker restorerestored – quicker restore

Disadvantage : No access to database during backup Disadvantage : No access to database during backup
time (depends on size/system speed)time (depends on size/system speed)

22. Backup Methods (cont’d)Backup Methods (cont’d)

Hot Backup (aka Inconsistent Backups)Hot Backup (aka Inconsistent Backups)

If the database must be up and running 24 hours a If the database must be up and running 24 hours a
day, seven days a week, then you have no choice but day, seven days a week, then you have no choice but
to perform inconsistent backups of the whole to perform inconsistent backups of the whole
database. A backup of online datafiles is called an database. A backup of online datafiles is called an
online backup. This requires that you run your online backup. This requires that you run your
database in ARCHIVELOG mode. database in ARCHIVELOG mode.

Advantage : Database remains open during backupAdvantage : Database remains open during backup

Disadvantage : Large databases may have Disadvantage : Large databases may have
performance impact during backup, recovery takes performance impact during backup, recovery takes
longer and is longer and is slightlyslightly more complex more complex

23. Backup Methods (cont’d)Backup Methods (cont’d)

Logical backup (Export)Logical backup (Export)

Logical backups are exports of schema objects, like Logical backups are exports of schema objects, like
tables and stored procedures, into a binary file. tables and stored procedures, into a binary file.
Oracle utilities are used to move Oracle schema Oracle utilities are used to move Oracle schema
objects in and out of Oracle.objects in and out of Oracle.

Not recommended for backup of a whole database, Not recommended for backup of a whole database,
but useful for backing up individual objects or but useful for backing up individual objects or
schemas or moving data into another database schemas or moving data into another database

24. Administrative TasksAdministrative Tasks

Daily ChecksDaily Checks

Check database availabilityCheck database availability

Check logs / trace filesCheck logs / trace files

Check free space / resourcesCheck free space / resources

Check for invalid objectsCheck for invalid objects

Check for broken jobsCheck for broken jobs

Verify backupVerify backup

25. Administrative Tasks (cont’d)Administrative Tasks (cont’d)

Weekly TasksWeekly Tasks

Collect statistics (database job)Collect statistics (database job)

Archive / delete log filesArchive / delete log files

Run performance reports (statspack)Run performance reports (statspack)

26. Administrative Tasks (cont’d)Administrative Tasks (cont’d)

OthersOthers

Applying patchesApplying patches

Database upgradesDatabase upgrades

New Database installationsNew Database installations

Creating user accountsCreating user accounts

27. More InformationMore Information

Oracle 10g Release 2 Database Documentation Oracle 10g Release 2 Database Documentation
http://www.oracle.com/pls/db102/http://www.oracle.com/pls/db102/

Oracle Database / SQL HelpOracle Database / SQL Help
http://asktom.oracle.com/http://asktom.oracle.com/
http://www.oracle.com/technology//index.htmlhttp://www.oracle.com/technology//index.html

*FREE* Oracle Software Downloads*FREE* Oracle Software Downloads
http://www.oracle.com/technology/software/index.htmlhttp://www.oracle.com/technology/software/index.html

Oracle Database 10g Express EditionOracle Database 10g Express Edition

Oracle SQL DeveloperOracle SQL Developer

This DocumentThis Document

Computer Science Homepage -> On-Line helpComputer Science Homepage -> On-Line help