Set No. 0

mangledcobwebSoftware and s/w Development

Dec 14, 2013 (3 years and 8 months ago)

81 views

1

Course : (IS436 / IS337)


Text Book : Oracle Database11g Administration I By John Watson



ISBN 978
-
0
-
07
-
159102
-
7 Published by McGraw Hill.


Time : 3
-
5 ( Sunday, Tuesday)







Welcome to all of you.

2

Grades assignment plan for the course
:

Assignments and quiz’s


10

Mid
-
Term Exam



15

Lab Test I




15

Lab Test II




20

Final Exam




40

Total





100


3

Name :
Eyas

El
-
Qawasmeh


Email :eyasa@usa.net


URL :
www.sdiwc.us/eyas


Cell phone : 0564569838


Office number : 1258

4

Your lab midterm exam is Oct.
27
( practical)


Tuesday
-

15%


Your theory exam is Sept. 3 ( during the class
second half)


Sunday


15%



5

6



ORACLE DATABASE

ARCHITECTURE

7

Tasks of an Oracle Database Administrator


A prioritized approach for designing, implementing, and
maintaining an Oracle database involves the following tasks:

1.

Evaluating the database server hardware

2.

Installing the Oracle software

3.

Planning the database and security strategy

4.

Creating, migrating, and opening the database

5.

Backing up the database

6.

Enrolling system users and planning for

their Oracle Network access

7.

Implementing the database design

8.

Recovering from database failure

9.

Monitoring database performance

8

Objectives


After completing this lesson, you should be able to:


List the major architectural components of Oracle
Database


Explain the memory structures


Describe the background processes


Correlate the logical and physical storage structures

9

Oracle Database 11g



g
” Stands for Grid


Global
Grid Forum (GGF)


Oracle’s grid infrastructure:


Low cost


High quality of service


Easy to manage

Automatic

Storage

Management

Real

Application

Clusters

Oracle

Streams

Enterprise

Manager

Grid Control

Storage

grid

Database

grid

Application

grid

Grid

control

10

Oracle Database 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

11

Oracle Database


The Oracle relational database management
system (RDBMS) provides an open,
comprehensive, integrated approach to
information management

12

Connecting to a Server

Client

Middle tier

Server

Multitier architecture shown

13

Oracle Database
Architecture







Database

Data files

Online redo
log files

Control
files






Database

buffer

cache

Shared pool





Data dictionary

cache

Library

cache


PMON

SMON

Others

Server

process

PGA

Archived

log files

User

process

Instance


RECO

ARC
n

SGA


DBW
n

Redo log

buffer


LGWR

CKPT

14

Connecting to the Database


Connection: Communication between a user process
and an instance


Session: Specific connection of a user to an instance
through a user process

SQL> Select …

Session

Connection

User

User

process

Server

process

Session

15

Interacting with an Oracle Database


User






Database

buffer

cache

Shared pool





Data dictionary

cache

Library

cache


PMON

SMON

Others

Instance


RECO

ARC
n

SGA


DBW
n

Redo log

buffer


LGWR

CKPT

User

process

Server

process

16

Oracle Database Server Structures

Database




PMON

SMON

Others

Data files

Online
redo log
files

Control
files

Instance


RECO

ARC
n


DBW
n


LGWR

CKPT

Storage structures

User

process

Server

process

Memory structures

Processes




Database

buffer

cache

Shared pool





Data dictionary

cache

Library

cache

SGA

Redo log

buffer

17

Oracle Database
Memory Structures

SGA

Database buffer

cache

Redo log buffer

Java pool

Streams
pool

Shared pool

Large pool

PGA

PGA

PGA


Background

process

Server

process 1

Server

process 2

Shared

SQL area

Library cache

Data dictionary
cache

Other

I/O buffer

Response
queue

Request queue

Free memory

18

Database Buffer Cache



Is part of the SGA


Holds copies of data blocks that are read from data files


Is shared by all concurrent users




PMON

SMON

Others

Instance


RECO

ARC
n


DBW
n


LGWR

CKPT




Database

buffer

cache

Shared pool





Data dictionary

cache

Library

cache

SGA

Redo log

buffer

19

Redo Log Buffer



Is a circular buffer in the SGA


Holds information about changes made to the database


Contains redo entries that have the information to redo changes
made by operations such as DML and DDL




PMON

SMON

Others

Instance


RECO

ARC
n


DBWn


LGWR

CKPT




Database

buffer

cache

Shared pool





Data dictionary

cache

Library

cache

SGA

Redo log

buffer

20

Shared Pool




Is a portion of the SGA


Contains:


Library cache


Shared SQL area


Data dictionary cache


Control structures




PMON

SMON

Others

Instance


RECO

ARC
n


DBW
n


LGWR

CKPT

Shared

SQL area

Library
cache

Data dictionary
cache

Other




Database

buffer

cache

Shared pool





Data dictionary

cache

Library

cache

SGA

Redo log

buffer

21

Allocation and Reuse of Memory

in the Shared Pool



Server process checks the shared pool to see if a shared
SQL area already exists for an identical statement.


Server process allocates a private SQL area on behalf of
the session.

Server

process

Shared pool

Shared

SQL area

Library cache

Data dictionary
cache

Other

22

Large Pool




Provides large memory allocations for:


Session memory for the shared server and the Oracle
XA interface


I/O server processes


Oracle Database backup and restore operations


Large pool

I/O buffer

Response queue

Request queue

Free memory

Java pool

Large pool

Shared pool

Database

buffer

cache

Redo log

buffer

Streams
pool

23

Java
Pool and
Streams Pool




Java pool memory is used in server memory for all session
-
specific Java code and data in the JVM.


Streams pool memory is used exclusively by Oracle
Streams to:



Store buffered queue messages



Provide memory for Oracle Streams processes

Java pool

Streams pool

24

Process Architecture


User process


Is started when a database user or a batch process connects to
Oracle Database


Database processes


Server process: Connects to the Oracle instance and is started
when a user establishes a session


Background processes: Are started when an Oracle instance is
started




PMON

SMON

Others

Instance


RECO

ARC
n


DBW
n


LGWR

CKPT

PGA

User

process

Server

process

Background processes




Database

buffer

cache

Shared pool





Data dictionary

cache

Library

cache

SGA

Redo log

buffer

25

Process Structures

Server

n
processes

SGA

Oracle
background
processes


PMON

SMON

Others


RECO

ARC
n


DBW
n


LGWR

CKPT

Server

Server

Server

Server

Server




Database

buffer

cache

Shared pool





Data dictionary

cache

Library

cache

SGA

Redo log

buffer

27

Database Writer Process (
DBWn
)



Writes modified (dirty) buffers in the database buffer
cache to disk:


Asynchronously while performing other processing


Periodically to advance the checkpoint

Database buffer cache

Database writer process

Data files


DBW
n

28

LogWriter

Process (LGWR)


Writes the redo log buffer to a redo log file on disk


Writes:


When a user process commits a transaction


When the redo log buffer is one
-
third full


Before a
DBW
n

process writes modified buffers to disk

Redo log buffer

LogWriter process

Redo log files


LGWR

29

Checkpoint Process (CKPT)



A checkpoint is a data structure that defines a system change number
(SCN) in the redo thread of a database.


Records
checkpoint information in


Control file


Each data file header

Checkpoint
process

Data files

Control file

CKPT

30

System Monitor Process (SMON)



The System Monitor process (SMON) performs
recovery at instance startup if necessary.


Cleans
up unused temporary segments

Temporary
segment

Instance

System Monitor process

SMON

31

Process Monitor Process (PMON)



Performs process recovery when a user process fails


Cleans up the database buffer cache


Frees resources that are used by the user process


Monitors sessions for idle session timeout


Dynamically registers database services with listeners

Process Monitor
process

Database buffer cache

Failed user process

User

PMON

32

Recoverer

Process


Used with the distributed database configuration


Automatically connects to other databases involved in
in
-
doubt distributed transactions


Automatically resolves all in
-
doubt transactions


Removes any rows that correspond to in
-
doubt
transactions


Recoverer process

in database A

In
-
doubt transaction

in database B

RECO

33

Archiver

Processes (
ARCn
)


Copy redo log files to a designated storage device after a
log switch has occurred


Can collect transaction redo data and transmit that data
to standby destinations

Archiver process

Archive destination

Copies of redo log files

ARC
n

34

Other Processes



MMON: Performs manageability
-
related
background tasks


MMNL: Performs frequent and lightweight
manageability
-
related tasks


MMAN: Performs automatic memory
management tasks


CJQ0: Runs user jobs used in batch processing


QMNC: Monitors the Streams Advanced
Queuing message queues

35

Server Process and Database

Buffer Cache


Buffers:

1.
Pinned

2.
Clean

3.
Free or unused

4.
Dirty

Database

writer
process




Database

buffer

cache

SGA

Data
files

DBW
n

Server

process

36

Database Storage Architecture

Online redo log files

Password file

Parameter file

Archived redo log
files

Control files

Data files

Alert log and trace files

Backup files

37

Database Storage Architecture


The files that constitute an Oracle database are organized into the following:

1.
Control files: Contain data about the database itself. Without them, you
cannot open data files to access the data in the database.

2.
Data files: Contain the user or application data of the database, as well as
metadata and the data dictionary

3.
Online redo log files: Allow for instance recovery of the database.

The following additional files are important to the successful running of the
database:


Parameter file: Is used to define how the instance is configured when it
starts up


Password file: Allows
sysdba
,
sysoper
, and
sysasm

to connect remotely to
the instance and perform administrative tasks


Backup files: Are used for database recovery.


Archived redo log files: Contain an ongoing history of the data changes
(redo) that are generated by the instance.

38

Logical and Physical Database Structures

Database

Logical

Physical

Tablespace

Data file

OS block

Segment

Extent

Oracle data

block

Schema

39

Tablespaces

and Data Files


A database is divided into logical storage units
called
tablespaces
.


Tablespaces

consist of one or more data files.


Data files belong to only one
tablespace
.

USERS tablespace

Data file 1

Data file 2

40

SYSTEM and SYSAUX
Tablespaces



The SYSTEM and SYSAUX
tablespaces

are
mandatory
tablespaces
.


They are created at the time of database creation.


They must be online.


The SYSTEM
tablespace

is used for core
functionality (for example, data dictionary tables).


The auxiliary SYSAUX
tablespace

is used for
additional database components (such as the
Enterprise Manager Repository).

41

Segments, Extents, and Blocks

a)
Segments exist within a
tablespace
.

b)
Segments are made up of a collection of extents.

c)
Extents are a collection of data blocks.

d)
Data blocks are mapped to disk blocks.

Segment

Extents

Data
blocks

Disk
blocks

42

Course Examples: The HR Schema

REGIONS

REGION_ID (PK)

REGION_NAME

COUNTRIES

COUNTRY_ID (PK)

COUNTRY_NAME

REGION_ID (FK)

LOCATIONS

LOCATION_ID (PK)

STREET_ADDRESS

POSTAL_CODE

CITY

STATE_PROVINCE

COUNTRY_ID (FK)

DEPARTMENTS

DEPARTMENT_ID (PK)

DEPARTMENT_NAME

MANAGER_ID

LOCATION_ID (FK)

JOBS

JOB_ID (PK)

JOB_TITLE

MIN_SALARY

MAX_SALARY

EMPLOYEES

EMPLOYEE_ID (PK)

FIRST_NAME

LAST_NAME

EMAIL

PHONE_NUMBER

HIRE_DATE

JOB_ID (FK)

SALARY

COMMISION_PCT

MANAGER_ID (FK)

DEPARTMENT_ID (FK)

JOB_HISTORY

EMPLOYEE_ID (PK)

START_DATE (PK)

END_DATE

JOB_ID (FK)

DEPARTMENT_ID (FK)

REGIONS

COUNTRIES

LOCATIONS

DEPARTMENTS

JOBS

EMPLOYEES

JOB_HISTORY

43

Dynamic Performance Views


Dynamic performance views
provide access to information
about changing states and
conditions in the database.

Session data

Wait events

Memory allocations

Running SQL

UNDO usage

Open cursors

Redo log usage

And so on

Oracle instance

44

Dynamic Performance Views: Considerations


These views are owned by the SYS user.


Different views are available at different times:


The instance has been started.


The database is mounted.


The database is open.


You can query V$FIXED_TABLE to see all the view
names.


These views are often referred to as “v
-
dollar views.”


Read consistency is not guaranteed on these views
because the data is dynamic.

45

Dynamic Performance Views
: Usage
Examples

SQL> SELECT
sql_text
, executions FROM
v$sql

WHERE
cpu_time

> 200000;

SQL> SELECT * FROM
v$session

WHERE machine =
'EDRSR9P1' and
logon_time

> SYSDATE
-

1;

SQL> SELECT sid, ctime FROM v$lock WHERE block > 0;

a

b

c

46

Summary of Structural Components


Memory structures:


System Global Area (SGA): Database buffer cache, redo
buffer, and various pools


Program Global Area (PGA)


Process structures:


User process and Server process


Background processes: SMON, PMON,
DBW
n
, CKPT,
LGWR,
ARC
n
, and so on


Storage structures:


Logical: Database, schema,
tablespace
, segment, extent, and
Oracle block


Physical: Files for data, parameters, redo, and OS block

47