slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Database Architecture Overview PowerPoint Presentation
Download Presentation
Database Architecture Overview

Loading in 2 Seconds...

play fullscreen
1 / 59

Database Architecture Overview - PowerPoint PPT Presentation


  • 161 Views
  • Uploaded on

Database Architecture Overview. Oracle 11g Server Architecture. Oracle Instance Oracle Database Database Files Server Processes Memory Structures Transactions Data Dictionary Schema And Users. Oracle Server Architecture. An Oracle server:

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Database Architecture Overview' - mathilde-martinez


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
oracle 11g server architecture
Oracle 11g Server Architecture
  • Oracle Instance
  • Oracle Database
  • Database Files
  • Server Processes
  • Memory Structures
  • Transactions
  • Data Dictionary
  • Schema And Users
oracle server architecture
Oracle Server Architecture

An Oracle server:

  • Is a database management system that provides an open, comprehensive, integrated approach to information management
  • Consists of an Oracle instance and an Oracle database

Oracle Server

overview of primary components
Overview of Primary Components

Instance

Userprocess

SGA

Shared pool

Library

cache

Redo logbuffer cache

Databasebuffer cache

Serverprocess

Data Dict.cache

PGA

PMON

SMON

DBWR

LGWR

CKPT

Others

Data files

Control files

Redo log files

Parameter

file

Archived

log files

Password

file

Database

oracle 11g server architecture1
Oracle 11g Server Architecture
  • Oracle Instance
  • Oracle Database
  • Database Files
  • Server Processes
  • Memory Structures
  • Data Dictionary
  • Schema And Users
oracle instance
Oracle Instance
  • Is a means to access an Oracle database
  • Always opens one and only one database
  • Consists of memory and process structures

Instance

SGA

Shared pool

Memory structures

Library

cache

Database buffer cache

Redo logbuffer cache

Data Dictionarycache

Background structures

PMON

SMON

DBWR

LGWR

CKPT

Others

oracle 11g server architecture2
Oracle 11g Server Architecture
  • Oracle Instance
  • Oracle Database
  • Database Files
  • Server Processes
  • Memory Structures
  • Data Dictionary
  • Schema And Users
physical structure

Header

Data files

(includes

data

dictionary)

Control

files

Online

redo log

files)

Physical Structure

The physical structure of an Oracle database is

determined by the operating system files that

provide the actual physical storage for database

information.

  • Control files
  • Data files
  • Redo log files
oracle 11g server architecture3
Oracle 11g Server Architecture
  • Oracle Instance
  • Oracle Database
  • Database Files
  • Server Processes
  • Memory Structures
  • Data Dictionary
  • Schema And Users
database files
Database Files
  • Parameter Files
  • Control Files
  • Data Files
  • Redo Log Files
  • Undo Data Files
oracle database

Oracle Database

Data files

Control files

Redo log files

Archived

log files

Parameter

file

Password

file

Oracle Database
database files1
Database Files
  • Parameter Files
  • Control Files
  • Data Files
  • Redo Log Files
  • Undo Data Files
parameter files
Parameter Files
  • Entries are specific to the instance being accessed
  • There are two kinds of parameters:
    • Explicit: Having an entry in the file
    • Implicit: No entry within the file, but assuming the Oracle default values
  • Multiple files can be used for a single database to optimize performance in different situations.
spfile spfilesid ora
SPFILE spfileSID.ora
  • Binary file with the ability to make changes persistent across shutdown and startup
  • Maintained by the Oracle server
  • Records parameter value changes made with the ALTER SYSTEM command
  • Can specify whether the change being made is temporary or persistent
  • Values can be deleted or reset to allow an instance to revert to the default value
  • Can be dumped to text file for manual editing
database files2
Database Files
  • Parameter Files
  • Control Files
  • Data Files
  • Redo Log Files
  • Undo Data Files
control file
Control File

The control file is a binary file that defines the

current state of the physical database..

  • Loss of the control file requires recovery
  • Is read at MOUNT stage
  • Is required to operate
  • Is linked to a single database
  • Should be multiplexed
  • Maintains integrity of database
  • Sized initially by CREATE DATABASE

Database

Control files

database files3
Database Files
  • Parameter Files
  • Control Files
  • Data Files
  • Redo Log Files
  • Undo Data Files
database files4
Database Files
  • Parameter Files
  • Control Files
  • Data Files
      • Tablespaces
      • Oracle Managed Files
      • Data Block
      • Data Types
      • Tables
      • Indexes
  • Redo Log Files
  • Undo Data Files
logical structure

Data file

Segment

Segment

Extent

Blocks

Logical Structure

The logical structure of the Oracle architecture

dictates how the physical space of a database is to be used.

A hierarchy exists in this structure that consists of

tablespaces, segments, extents, and blocks.

Tablespace

system and non system tablespaces
SYSTEM and Non-SYSTEM Tablespaces
  • SYSTEM tablespace:
    • Created with the database
    • Contains the data dictionary
    • Contains the SYSTEM undo segment
  • Non-SYSTEM tablespaces:
    • Separate segments
    • Ease space administration
    • Control amount of space allocated to a user
database files5
Database Files
  • Parameter Files
  • Control Files
  • Data Files
      • Tablespaces
      • Oracle Managed Files
      • Data Block
      • Data Types
      • Tables
      • Indexes
  • Redo Log Files
  • Undo Data Files
oracle managed files
Oracle Managed Files
  • Oracle Managed Files (OMF) simplify file administration by eliminating the need to directly manage the files in an Oracle database
  • This feature has two major thrusts:
    • Allows database objects to be created without specifying the underlying operating system files
    • Automatically removes obsolete data files and online redo logs
database files6
Database Files
  • Parameter Files
  • Control Files
  • Data Files
      • Tablespaces
      • Oracle Managed Files
      • Data Block
      • Data Types
      • Tables
      • Indexes
  • Redo Log Files
  • Undo Data Files
database block
Database Block
  • Minimum unit of I/O
  • Consists of one or more OS blocks
  • Set at tablespace creation
  • DB_BLOCK_SIZE is the default block size
database files7
Database Files
  • Parameter Files
  • Control Files
  • Data Files
      • Tablespaces
      • Oracle Managed Files
      • Data Block
      • Data Types
      • Tables
      • Indexes
  • Redo Log Files
  • Undo Data Files
oracle data types
Oracle data types

Data type

User-defined

Built-in

Scalar

Collection

Relationship

CHAR(N), NCHAR(N)VARCHAR2(N),NVARCHAR2(N)

NUMBER(P,S)

DATERAW(N)BLOB, CLOB,

NCLOB, BFILE

LONG, LONG RAW

ROWID

VARRAYTABLE

REF

system provided datatypes
System-Provided Datatypes

Built-In Datatype

Native Datatypes

Extended Datatypes

Support Datatypes

CHARACTER

Text

ANSI

NUMBER

DB2

Image

DATE

SQL/DS

Video

LOB

Audio

PL/SQL Datatypes

RAW

ROWID

BOOLEAN

Spatial

BINARY_INTEGER

XML

Time Series

database files8
Database Files
  • Parameter Files
  • Control Files
  • Data Files
      • Tablespaces
      • Oracle Managed Files
      • Data Block
      • Data Types
      • Tables
      • Indexes
  • Redo Log Files
  • Undo Data Files
tables in oracle

Index-organizedtable

Cluster

Tables in Oracle

Partitionedtable

Regular table

database files9
Database Files
  • Parameter Files
  • Control Files
  • Data Files
      • Tablespaces
      • Oracle Managed Files
      • Data Block
      • Data Types
      • Tables
      • Indexes
  • Redo Log Files
  • Undo Data Files
classification of indexes
Classification of Indexes
  • Logical
    • Single column or concatenated
    • Unique or nonunique
    • Function Based
    • Domain Based
  • Physical
    • Partitioned or nonpartitioned
    • B-tree
      • Normal or reverse key
    • Bitmap
      • Bitmap Join Index
database files10
Database Files
  • Parameter Files
  • Control Files
  • Data Files
  • Redo Log Files
  • Undo Data Files
using redo log files
Using Redo Log Files

Redo log files record all changes made to data and

provide a recovery mechanism from a system or

media failure.

  • Redo log files are organized into groups.
  • An Oracle database requires at least two groups.
  • Each redo log within a group is

called a member.

Database

Redo log files

structure of redo log files
Structure of Redo Log Files

Group 1

Group 2

Group 3

Disk 1

Member

Member

Member

Disk 2

Member

Member

Member

how redo logs work
How Redo Logs Work
  • Redo logs are used in a cyclic fashion.
  • When a redo log file is full, LGWR will move to the next log group.
    • This is called a log switch
    • Checkpoint operation also occurs
    • Information is written to the control file
database files11
Database Files
  • Parameter Files
  • Control Files
  • Data Files
  • Redo Log Files
  • Undo Data Files
undo data overview
Undo Data Overview
  • Before Oracle makes a change to a database block it is copied to the Undo area.
  • Used for Rollback and Read Consistency.
oracle 11g server architecture4
Oracle 11g Server Architecture
  • Oracle Instance
  • Oracle Database
  • Database Files
  • Server Processes
  • Memory Structures
  • Transactions
  • Data Dictionary
  • Schema And Users
process structure
Process Structure

An Oracle process is a program that depending on its

type can request information, execute a series of

steps, or perform a specific task.

On some OS (MS Windows…) these are actually implemented as process threads.

Oracle takes advantage of various types of

processes:

  • User process: Started at the time a database user requests connection to the Oracle server
  • Server process: Connects to the Oracle Instance and is started when a user establishes a session.
  • Background process: Available when an Oracle instance is started
background processes
Background Processes

The relationship between the physical and memory

structures is maintained and enforced by Oracle’s

background processes.

  • Mandatory background processes

DBWn PMON CKPT

LGWR SMON and others

  • Optional background processes

ARCn LMON Snnn

QMNn LMDn RECO

CJQ0 Pnnn and others

LCKn Dnnn

oracle 11g server architecture5
Oracle 11g Server Architecture
  • Oracle Instance
  • Oracle Database
  • Database Files
  • Server Processes
  • Memory Structures
  • Transactions
  • Data Dictionary
  • Schema And Users
memory structure
Memory Structure

Oracle’s memory structure consists of two memory areas known as:

  • System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle Instance
  • Program Global Area (PGA): Allocated when the server process is started
system global area sga
System Global Area (SGA)
  • The SGA consists of several memory structures:
    • Shared pool
    • Database buffer cache
    • Redo log buffer
    • Other structures (e.g. lock and latch management, statistical data)
system global area sga1
System Global Area (SGA)
  • SGA is dynamic and sized using MEMORY_TARGET without shutting down the instance
  • SGA memory allocated and tracked in granules by SGA components
    • Contiguous virtual memory allocation
    • Size based on MEMORY_TARGET
program global area pga

PGA Dedicated server Shared server

Serverprocess

Session information

sort area, cursor information

sort area, cursor information

Stack

space

Stack

space

Userprocess

SGA

SGA

Session

information

Shared SQL areas

Shared SQL areas

Program Global Area (PGA)

The PGA is memory reserved for each user process

that connects to an Oracle database.

Managed as part of the MEMORY_TARGET

oracle 11g server architecture6
Oracle 11g Server Architecture
  • Oracle Instance
  • Oracle Database
  • Database Files
  • Server Processes
  • Memory Structures
  • Transactions
  • Data Dictionary
  • Schema And Users
transactions
Transactions
  • Oracle has an implicit transaction model.
    • A transaction starts immediately after the last one completes
    • Can be made READ-ONLY via ALTER TRANSACTION command
    • COMMIT or ROLLBACK ends transaction
slide49

Oracle’s Multi-Version ConcurrencyRead Consistent Row Locking

With Oracle …

report

  • Updates don’t lockout reports and reports don’t lockout updates
  • Reports see only committed data via Multi-Versioning
  • Queries yield maximum throughput with correct results - no waiting and no dirty reads!
  • Row locks never escalate - the most scaleable solution available

Budget Table

update

Undo

Tablespace

Before

Image

accurate

report

oracle 11g server architecture7
Oracle 11g Server Architecture
  • Oracle Instance
  • Oracle Database
  • Database Files
  • Server Processes
  • Memory Structures
  • Transactions
  • Data Dictionary
  • Schema And Users
data dictionary

Data files

Control files

Redo log files

Database

Data Dictionary Tables

Dynamic Performance Tables

Data Dictionary

During database creation, the Oracle server creates additional object structures within the data files.

  • Data dictionary tables
  • Dynamic performance tables
data dictionary1
Data Dictionary

The data dictionary is a set of read-only tables and

views that record, verify, and provide information

about its associated database.

  • Describes the database and its objects
  • Includes two types of objects:
    • Base tables
      • Store description of database
      • Created with CREATE DATABASE
    • Data Dictionary views
      • Summarize base table information
      • Created using catalog.sql script
data dictionary contents
Data Dictionary Contents

The data dictionary provides information about:

  • Logical and physical database structure
  • Definitions and space allocations of objects
  • Integrity constraints
  • Users
  • Roles
  • Privileges
  • Auditing
data dictionary view categories

DBA_xxx

All of the objects in the database

ALL_xxx

Objects accessible by the current user

USER_xxx

Objects owned by the current user

Data Dictionary View Categories

The data dictionary consists of three main sets of

static views distinguished from each other by their

scope:

  • DBA: What is in all the schemas
  • ALL: What the user can access
  • USER: What is in the user's schema
oracle 11g server architecture8
Oracle 11g Server Architecture
  • Oracle Instance
  • Oracle Database
  • Database Files
  • Server Processes
  • Memory Structures
  • Data Dictionary
  • Schema And Users
slide57

Schema Objects

Tables

Triggers

Constraints

Indexes

Views

Sequences

Stored program units

Synonyms

User-defined data types

Database links

Database Schema

  • A schema is a named collection of objects
  • A user is created, and a corresponding schema can be created
  • User can be associated only with one schema
  • Username and schema are often used interchangeably
  • Users are not necessarily associated with a schema
oracle 11g server architecture9
Oracle 11g Server Architecture
  • An Oracle server consists of an Oracle instance and an Oracle database
  • An Oracle instance consists of several required and some more optional processes (threads)
  • An Oracle database is built using different file types
slide59

Q

&

Q U E S T I O N S

A N S W E R S

A