Transcript Oracle Database 10g: Administration Workshop I

Introduction
Copyright © 2005, Oracle. All rights reserved.
Objectives
•
After completing this course, you should be able
to do the following:
– Install, create, and administer Oracle Database 10g
Enterprise Edition
– Configure the database for an application
– Implement a backup and recovery strategy
– Employ basic monitoring procedures
•
After completing this lesson, you should be able
to do the following:
– Describe course objectives
– Explain the Oracle Database 10g
architecture
1-2
Copyright © 2005, Oracle. All rights reserved.
Oracle Products
•
•
•
•
•
•
1-3
Oracle databases
Oracle Application Server
Oracle Applications
Oracle Collaboration Suite
Oracle Developer Suite
Oracle services
Copyright © 2005, Oracle. All rights reserved.
Oracle Database 10g:
“g” Stands for Grid
•
•
•
1-4
Low cost
High quality of service
Easy to manage
Storage
grid
Database
grid
Application
grid
Grid
control
Automatic
Storage
Management
Real
Application
Clusters
Oracle
Streams
Enterprise
Manager
Grid Control
Copyright © 2005, Oracle. All rights reserved.
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
1-6
Copyright © 2005, Oracle. All rights reserved.
DB structures
- Memory
- Process
- Storage
Database Structures
Memory structures
Instance
System Global Area (SGA)
Process structures
Background processes
Storage structures
1-7
Database files
Copyright © 2005, Oracle. All rights reserved.
Oracle Memory Structures
DB structures
- Memory <<
- Process
- Storage
Server
process
1
PGA
Server
process
2
Background
process
PGA
SGA
1-8
Shared pool
Streams pool
Large pool
Java pool
Database
buffer cache
Redo log
buffer
Copyright © 2005, Oracle. All rights reserved.
PGA
DB structures
- Memory
- Process <<
- Storage
Process Structures
Instance
PGA
SGA
User process
•
•
•
1-10
Server
process
Background
processes
User process: Started at the time a database user
requests a connection to the Oracle server
Server process: Connects to the Oracle instance
and is started when a user establishes a session
Background processes: Started when an Oracle
instance is started
Copyright © 2005, Oracle. All rights reserved.
Oracle Processes
Server
process
Server
process
Server
process
Server
process
System Global Area
(SGA)
System
Monitor
(SMON)
Process
Monitor
(PMON)
Database
Writer
(DBWn)
Checkpoint
(CKPT)
LogWriter
(LGWR)
Background processes
1-11
Copyright © 2005, Oracle. All rights reserved.
Archiver
(ARCn)
Server Process and Database
Buffer Cache
Server
process
SGA
Database
buffer cache
Buffers:
• Pinned
• Clean
• Free or
unused
• Dirty
DBWn
Data files
1-12
Copyright © 2005, Oracle. All rights reserved.
Physical Database Structure
DB structures
- Memory
- Process
- Storage <<
Control files
Data files
Parameter file
Backup files
Password file
1-13
Online redo log files
Archive log files
Alert and trace log files
Copyright © 2005, Oracle. All rights reserved.
Tablespaces and Data Files
•
•
Tablespaces consist of one or more data files.
Data files belong to only one tablespace.
Data file 1
Data file 2
USERS tablespace
1-15
Copyright © 2005, Oracle. All rights reserved.
SYSTEM and SYSAUX Tablespaces
•
•
•
•
•
1-16
The SYSTEM and SYSAUX tablespaces are
mandatory tablespaces.
They are created at the time of database creation.
They must be online.
SYSTEM: For core functionality (for example, data
dictionary tables)
SYSAUX: Is auxiliary, for additional database
components (such as the Enterprise Manager
Repository)
Copyright © 2005, Oracle. All rights reserved.
Segments, Extents, and Blocks
•
•
•
•
Segments exist within a tablespace.
Segments are made up of a collection of extents.
Extents are a collection of data blocks.
Data blocks are mapped to disk blocks.
Segment
1-17
Extents
Data
blocks
Copyright © 2005, Oracle. All rights reserved.
Disk
blocks
Logical and Physical Database Structures
Logical
Physical
Database
Schema
Tablespace
Data file
Segment
Extent
Oracle data
block
1-18
Copyright © 2005, Oracle. All rights reserved.
OS block
Enlarging the Database
You can enlarge the database in the following ways:
• Creating a new tablespace
• Adding a data file to an existing tablespace
• Increasing the size of a data file
• Providing for the dynamic growth of a data file
Database
SYSTEM
tablespace
1-20
INVENTORY
tablespace
Copyright © 2005, Oracle. All rights reserved.
Exploring the Storage Structure
Click the links to view
detailed information.
1-21
Copyright © 2005, Oracle. All rights reserved.
The HR Sample Schema
LOCATIONS
LOCATION_ID (PK)
STREET_ADDRESS
JOB_HISTORY
DEPARTMENTS
POSTAL_CODE
EMPLOYEE_ID
(PK,FK)
DEPARTMENT_ID (PK)
CITY
DEPARTMENT_NAME
STATE_PROVINCE
EMPLOYEES
MANAGER_ID
COUNTRY_ID (FK)
EMPLOYEE_ID (PK)
LOCATION_ID (FK)
START_DATE (PK)
END_DATE
JOB_ID (FK)
DEPARTMENT_ID (FK)
FIRST_NAME
LAST_NAME
COUNTRIES
EMAIL
COUNTRY_ID (PK)
PHONE_NUMBER
COUNTRY_NAME
HIRE_DATE
REGION_ID (FK)
JOB_ID (FK)
SALARY
COMMISION_PCT
JOBS
MANAGER_ID (FK)
JOB_ID (PK)
DEPARTMENT_ID (FK)
JOB_TITLE
MIN_SALARY
MAX_SALARY
1-22
Copyright © 2005, Oracle. All rights reserved.
REGIONS
REGION_ID (PK)
REGION_NAME
Database Architecture:
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, DBWn,
CKPT, LGWR, ARCn, and others
•
Storage structures:
– Logical: Database, schema, tablespace, segment,
extent, and Oracle block
– Physical: Files for data, parameters, redo, and OS
block
1-23
Copyright © 2005, Oracle. All rights reserved.
Database Architecture and Activities
The following topics are covered in the remaining part
of this lesson:
• Control files
• Oracle instance management
• Data files and Database Writer (DBWn)
• Checkpoints (CKPT)
• Redo log files and LogWriter (LGWR)
• Archiver (ARCn)
• System Monitor (SMON)
• Process Monitor (PMON)
• Example: How the Oracle database works
1-24
Copyright © 2005, Oracle. All rights reserved.
Control Files
•
•
•
•
Contain physical database structure information
Are read at mount stage
Should be multiplexed to protect against loss
Are required to access the database
Example
Instance
SGA
Database files
spfileorcl.ora
CONNECT / AS SYSDBA
STARTUP
1-25
Copyright © 2005, Oracle. All rights reserved.
Oracle Instance Management
SGA
Shared pool
Streams pool
Large pool
Java pool
Database
buffer cache
Redo log
buffer
System
Monitor
(SMON)
Process
Monitor
(PMON)
Database
Writer
(DBWn)
LogWriter
(LGWR)
Checkpoint
(CKPT)
1-26
Archiver
(ARCn)
Copyright © 2005, Oracle. All rights reserved.
Data Files
SGA
Database
buffer cache
Database
Writer
(DBWn)
•
•
•
•
•
Contain all database data
Can be associated with only
one database
Form the logical unit of
database storage called
“tablespace”
Are read into memory (in
blocks) on request
Should be secured by
following a backup strategy
Data files
1-27
Copyright © 2005, Oracle. All rights reserved.
Database Writer (DBWn)
Background Information
SGA
Database
buffer cache
Database
Writer
(DBWn)
Data files
1-28
DBWn writes when one of the
following events occurs:
• Checkpoint
• Dirty buffers’ threshold
• No free buffers
• Timeout
• RAC ping request
• Tablespace OFFLINE
• Tablespace READ ONLY
• Table DROP or TRUNCATE
• Tablespace BEGIN BACKUP
Copyright © 2005, Oracle. All rights reserved.
Checkpoint (CKPT)
Responsible for:
• Signaling DBWn at checkpoints
• Updating data file headers with
checkpoint information
• Updating control files with
checkpoint information
Checkpoint
(CKPT)
1-29
Copyright © 2005, Oracle. All rights reserved.
SGA
Database
buffer cache
Database
Writer
(DBWn)
Redo Log Files and LogWriter
SGA
Redo log
buffer
Log
Writer
(LGWR)
Group 1
1-30
Group 2
Redo log files:
• Record changes to the
database
• Should be multiplexed to
protect against loss
LogWriter writes:
• At commit
• When one-third full
Group 3 • Every three seconds
• Before DBWn writes
Copyright © 2005, Oracle. All rights reserved.
Archiver (ARCn)
•
•
•
Is an optional
background process
Automatically archives
online redo log files
when ARCHIVELOG mode
is set for the database
Preserves the record of
all changes made to the
database
SGA
Redo log
buffer
LogWriter
(LGWR)
Archiver
(ARCn)
1-31
Copyright © 2005, Oracle. All rights reserved.
System Monitor (SMON)
•
•
•
Performs instance recovery at startup
Coalesces free space
Deallocates temporary segments
Instance
SGA
Other
processes
System
Monitor
(SMON)
1-32
Copyright © 2005, Oracle. All rights reserved.
Process Monitor (PMON)
PMON cleans up the database buffer cache and frees
resources after failed processes by:
• Rolling back the transaction
• Releasing locks
• Releasing other resources
Instance
PGA
User process
1-33
Server
process
Process
Monitor
(PMON)
Other
processes
Copyright © 2005, Oracle. All rights reserved.
SGA
Oracle Instance Management: Summary
SGA
Shared pool
Streams pool
Large pool
Java pool
Database
buffer cache
Redo log
buffer
System
Monitor
(SMON)
Process
Monitor
(PMON)
Database
Writer
(DBWn)
LogWriter
(LGWR)
Checkpoint
(CKPT)
1-34
Archiver
(ARCn)
Copyright © 2005, Oracle. All rights reserved.
How the Oracle Database Works
1
Instance
Starting a database instance
Establishing a connection
3 Server
process
2
User process
1-35
SGA
Database
buffer cache
Redo log
buffer
Database
Writer
(DBWn)
LogWriter
(LGWR)
Copyright © 2005, Oracle. All rights reserved.
How the Oracle Database Works
Instance
SGA
Processing a SQL statement
5
10
Server
process
7 Database
buffer cache
Redo log
buffer
Database
Writer
(DBWn)
LogWriter
(LGWR)
9
User process
6
4
1-36
The user updates a row.
Copyright © 2005, Oracle. All rights reserved.
8
Summary
In this lesson, you should have learned how to:
• Describe the course objectives
• Explain the Oracle Database 10g architecture
1-37
Copyright © 2005, Oracle. All rights reserved.
Database Architecture: Quiz
Mark each of the following statements about an Oracle
database as True or False:
1. Control files store information about the physical
database structure.
2. Transaction changes are stored in redo log files.
3. Tablespaces consist of one or more data files.
4. A data file can be used for more than one
tablespace.
5. LogWriter (LGWR) writes redo log entries to the
disk.
6. Database Writer (DBWn) writes modified blocks
from the buffer cache to the disk.
7. All Oracle background processes are optional.
1-38
Copyright © 2005, Oracle. All rights reserved.