M 1 DBMS S ORACLE A

jazzydoeSoftware and s/w Development

Oct 30, 2013 (3 years and 11 months ago)

117 views

M
ODULE

1

DBMS
S
TRATEGY

AND

ORACLE
A
RCHITECTURE


Database Administration Techniques and
Practices

1

ITEC 450

Fall 2012

O
RGANIZATION

S

DBMS S
TRATEGY


It’s typical to run two or more DBMS products in
large and medium
-
sized organization


The organization should have the vision to
consolidate and minimize the number


Using an existing DBMS whenever it is possible


Once a DBMS is installed, removal can be
difficult: incompatibilities, conversion, special
features


Organization’s DBMS strategy is a join effort
from groups of DBA, Architect, App Dev, and
Business

2

ITEC 450

Fall 2012

C
HOOSING

A

DBMS: S
ELECTION

D
RIVERS


A commercial off
-
the
-
shelf application package:
ERM, PeopleSoft, CRM


The latest and greatest technology: Web, Java,
.NET


DBMS performance: TPC benchmarks


Operating systems: Unix, Windows, Linux


Hardware platforms: HP, Sun, IBM, Dell


Total cost of ownership
: license, people,
development, and maintenance support

3

ITEC 450

Fall 2012

L
EVELS

OF

DBMS A
RCHITECTURE


Enterprise and Departmental DBMS


Scalability and availability, high performance


Large database, concurrent users, multiprocessors


Examples: Oracle, DB2, SQL Server


Personal DBMS: Access, Visual dBase, Personal
Oracle


Mobile DBMS: a special version of DBMS for
remote users who are not on the network all the
time.

4

ITEC 450

Fall 2012

DBMS A
RCHITECTURE

C
OMPONENTS


Disk: logical and physical database
structures


CPU: Operating system processes


Memory: shared and private memory
structures


Network: user access and process
interactions


Clustering: multiple computing systems
working together

5

ITEC 450

Fall 2012


S
ECTION

I
MPORTANT

N
OTES


This section is Oracle specific.


Terms can be very different among
different DBMS’s (e.g. Oracle vs. SQL)


Database (different)


Instances (different)


Schemas (common)


Segments (Oracle specific)


6

ITEC 450

Fall 2012

O
RACLE

D
ATABASE

AND

I
NSTANCE


An Oracle Database server consists of an Oracle
Database and one or more Oracle Database
instances.


Database: all Oracle related files


Data files


holding tables and indexes


System files


System, Undo, Temp


Physical and logical structures


Instance: the combination of processes and
memory


Processes


background


Memory


allocated to Oracle


Running components


Instance is up



7

ITEC 450

Fall 2012

O
RACLE

D
ATABASE

AND

I
NSTANCE



8

ITEC 450

Fall 2012

P
HYSICAL

D
ATABASE

S
TRUCTURE


Main types of files


data files, control files, redo
log files, archived redo log files.


Data files


database data, such as tables and indexes


Control files


Oracle structure information, such as database
name, data file names, time stamp of database


Redo log files


database change logs for recovery


Archived redo log files


offline copies of online redo log files


Parameter files


SPFILE, init.ora


Administration files


alert.log, trace files, the
password files

9

ITEC 450

Fall 2012

P
HYSICAL

D
ATABASE

S
TRUCTURE

10

ITEC 450

Fall 2012

L
OGICAL

S
TRUCTURE

D
ETAILS

Read Chapter 2 Oracle Database Concept


Data block
-

the standard block size is determined by
the init parameter DB_BLOCK_SIZE.


Extent
-

When you create a database object, you
allocate it an initial extent. When the object grows
more than the initial extent, Oracle will automatically
allocate next extent.


Segment
-

Oracle calls all the space allocated to a
particular database object a segment, such as table or
index.


Tablespace
-

A tablespace contains one or more
physical files; a data file belongs to one and only one
tablespace.

11

ITEC 450

Fall 2012

LOGICAL & P
HYSICAL

D
ATABASE

S
TRUCTURE

12

ITEC 450

Fall 2012

O
NLINE

R
EDO

L
OG

F
ILES

AND

A
RCHIVE

L
OG

F
ILES


Redo log files record changes to database data


Online redo log files are open and available whenever
the database is up and running


They capture details of DB transactions and
information about changes to DB including:


Checkpoints


Changes


Data Manipulation Language (DML)


Data Definition Language (DDL)


Datafile

changes


A database should have at least three redo log
groups containing at least one file each

13

ITEC 450

Fall 2012

O
NLINE

R
EDO

L
OG

F
ILES

AND

A
RCHIVE

L
OG

F
ILES

14

ITEC 450

Fall 2012


PURPOSE OF REDO LOG FILES


Purpose: aid in database recovery


Redo log files keep list of DB changes


If DB loses changes, recovery process restores them


Redo log files receive the change information before the
data files
are updated


In minor failures (e.g., short power outage), redo log
files are automatically checked during DB startup, and
data is restored, from redo log files into
data files


In major failures (e.g., loss of an entire disk), data
would not be saved from the online redo logs alone


You need a full DB backup and archived redo log files that
begin after the date of the backup


15

ITEC 450

Fall 2012

STRUCTURE OF REDO LOG FILES


Redo log files store info as a result of DB activity


Information is recorded in the redo log buffer in SGA


Contents of redo log buffer are written by LGWR
process, to online redo log file, when:


A transaction issues a COMMIT command


Redo log buffer is one
-
third full


Every 3 seconds


A checkpoint occurs


The redo log file contains sets of redo records


A redo record (or redo entry) is made up of a related
group of change vectors that record a description of the
changes to a single block in the DB


A single transaction may generate many redo entries


16

ITEC 450

Fall 2012

REDO LOG MANAGEMENT

17

ITEC 450

Fall 2012

O
RACLE

D
ATABASE

S
TRUCTURE

18

ITEC 450

Fall 2012

O
VERVIEW

OF

O
RACLE

I
NSTANCE

When a database is started, Oracle starts many
background processes and acquires memory
structures.

The combination of the background processes and
memory buffers is called an Oracle instance.


19

ITEC 450

Fall 2012

O
RACLE

P
ROCESSES

Read Chapter 9 Oracle Database Concept

A process is a connection or thread to the operating
system.


Oracle processes


server processes and background
processes.


The background processes are the core of the Oracle
instance. When you start an instance, these processes are
created; when you shutdown an instance, these processes
are terminated.


Database writer


Log writer


Process monitor


System monitor


User processes


connecting users to the database
instance

20

ITEC 450

Fall 2012

O
RACLE

P
ROCESSES


Database Writer (DBW0): Database writer process uses a least
-
recently
-
used (LRU) algorithm to identify dirty buffers and write
them to disk. Dirty buffers are buffers that contain data that has
been modified


Log Writer (LGWR): The log writer process is responsible for
writing the redo entries from the redo log buffer to the disk files.


Check Point (CKPT): The check point process performs check
point on an on
-
going basis. Check point flushes all the buffers
from the buffer cache to the disk and also all the current redo log
sequence number and time stamps are written to the all of the
data files.


Process Monitor (PMON): The process monitor monitors all
server processes. When ever a user connection fails, PMON is
responsible foe cleaning up the buffer cache and freeing up
system resources used by the failed process.


System Monitor (SMON): The system monitor is involved in
instance recovery when the database is started.


21

ITEC 450

Fall 2012

O
RACLE

M
EMORY

S
TRUCTURES

Read Chapter 8 Oracle Database Concept

The memory structures enable Oracle to share
executable code, and produces high performance.

Oracle uses two basic memory structures: shared
and process
-
specific


System global area (SGA)


total memory shared
by all server processes including background
processes


Program global area (PGA)


memory used for
private (application, program) processes

22

ITEC 450

Fall 2012

S
YSTEM

G
LOBAL

A
REA

The SGA main components.


Database buffer cache


recently used blocks of
data


Shared pool


library cache, data dictionary cache


Redo log buffer


a log of database changes


Other Memory areas:


Large pool


optional large memory segments for shared SQL
and better performance


Java pool


memory for java code and data within the JVM


Streams pool


memory for using Streams

Automatic shared memory management: total
amount of SGA memory for the database

23

ITEC 450

Fall 2012

DATABASE BUFFER CACHE


Used to hold data blocks read from
datafiles

by
server processes


Contains ‘dirty’ or modified blocks and ‘clean’ or
unused or unchanged bocks


‘Dirty’ and ‘clean’ blocks are managed in lists
called the dirty list and the LRU


Free space is created by DBWR writing out ‘dirty’
blocks or aging out blocks from the LRU


Size is managed by the parameter
DB_BLOCK_BUFFERS

24

ITEC 450

Fall 2012

REDO LOG BUFFER


A circular buffer that contains redo entries


Redo entries reflect changes made to the database


Redo entries take up contiguous, sequential space
in the buffer


Data stored in the redo log buffer is periodically
written to the online redo log files


Size is managed by the parameter LOG_BUFFER


Default is 4 times the maximum data block size for the
operating system


25

ITEC 450

Fall 2012

SHARED POOL


Consists of multiple smaller memory areas


Library cache


Shared SQL area


Contains parsed SQL and execution plans for statements already
run against the database


Procedure and package storage


Dictionary cache


Names of all tables and views in the database


Names and
datatypes

of columns in the database tables


Privileges of all users


Managed via an LRU algorithm


Size determined by the parameter
SHARED_POOL_SIZE


26

ITEC 450

Fall 2012

PROGRAM G
LOBAL

A
REA


Program Global Area (PGA)


Effectively used in session connection memory


Broken into private chunks for each server
process


It is a
non shared
memory created by Oracle
when a server process is started. Access to it is
exclusive to that server process and is read and
written only by Oracle code acting on behalf of
it. The total PGA memory allocated by each
server process attached to an Oracle instance is
also referred to as the aggregated PGA memory
allocated by the instance.


27

ITEC 450

Fall 2012

SGA & BACKGROUND
PROCESSES

28

ITEC 450

Fall 2012

BACKGROUND PROCESSES
(C
ONTINUED
)

29

ITEC 450

Fall 2012

DATABASE


Install
the software components, create DB files to
store your data, and start a set of background
processes that allocate memory and handle
database activities


Oracle defines a DB as the collection of operating
system files that store your data


Database server: combination of DB software, a
DB (the files), and DB instance (the SGA and the
background processes)


Single
-
instance server (typical configuration)


Multiple
-
instance server


Clustered servers

30

ITEC 450

Fall 2012

SINGLE INSTANCE SERVER

31

ITEC 450

Fall 2012

MULTI INSTANCE SERVER

32

ITEC 450

Fall 2012

CLUSTERED SERVER

33

ITEC 450

Fall 2012

ORACLE SOFTWARE OPTIONS


Enterprise Edition: includes all major components


Enables multiple users to connect concurrently


Optimized for high data volume is common


Supports multiple DB instances and replication


Standard Edition: provides basic support for
multi
-
user database applications on a smaller
scale than that of the Enterprise Edition


Cannot be upgraded with database features


Personal Edition: single
-
user access to DB
instance


Two primary uses: programming and deployment


34

ITEC 450

Fall 2012

ORACLE SOFTWARE SOME ADDITONAL
OPTIONS


Optional features requiring additional license fees:


Oracle Partitioning


Oracle
Cluster ware


Oracle Spatial


Oracle Data Mining


Oracle
Database Extensions for .NET


Oracle Advanced
Security


And Other..


Note:

Some of the slides are from
Oracle 10g Database
Administrator: Implementation and Administration by
Gavin Powell
and Carol
McCullough
-
Dieter

35

ITEC 450

Fall 2012