chapter 1oracle9i dba ii: backup/recovery and network administration 1 chapter 1 backup and recovery...

Chapter 1 Oracle9i DBA II: Backup/Recovery and Network Administration

1

Chapter 1 Backup and Recovery Overview

MSCD642

Backup and Recovery


2

Chapter Objectives

• Identify the files associated with an Oracle9i database

• List the logical structure of an Oracle9i database

• Identify the components of an Oracle9i instance


3

Chapter Objectives (Cont.)

• Identify the purpose of each background process of an Oracle instance

• Identify the purpose of each component of the system global area (SGA)

• Identify the purpose of the INIT.ORA file and the version available with the Oracle9i database


4


• Define mean-time-between-failures (MTBF) and mean-time-to-recover (MTTR)

• Identify the parameters that can be used to speed up instance recovery

• Identify the various types of failures that can occur in an Oracle9i database


5


• Use the Enterprise Manager Console to view the names and locations of the control file


6

Oracle9i Database Structures

• Database – consists of both a physical and logical structure

– Physical structure – the physical operating system files

– Logical structure – structures referenced and manipulated by

the DBMS


7

Physical Structure

• Three types of database files:– Control file– Redo log file– Data file


8

Control File

• Small binary file containing:– name of database – location and name of all data and redo log files

• Recommended: two mirrored copies

• Minimum: one

• Maximum: eight

• Default: three


9

Redo Log File

• Contains committed transactions

• Changes must be written to a redo log file before updated to data files

• Two files are required, three is the default

• Written to in a circular fashion


10

Redo Log File Write Sequence


11

Data File

• Stores the actual database data

• Have the file extension .dbf

• DOES NOT correspond to an object name


12

Locating Database Files

Location of filePhysical and

Logical Structures


13

Logical Structure

• Database - largest

• Tablespace

• Segment

• Extent

• Oracle9i data block – smallest


14

Tablespace

• Contains related objects: – Indexes– Table data

• Belongs to only one database

• Stored in one or more data files

• Composed of segments


15

Segment

• Four types:– Data segments: store table data

– Index segments: store index data

– Undo segments: store data for rollback operations

– Temporary segments: store data for sort operations

• Consist of extents

• Can belong to only one tablespace at a time


16

Extent

• Consists of contiguous Oracle9i data blocks

• Add more extents to:– Increase size of segment;– Subsequently increases size of tablespace


17

Oracle9i Data Block

• Smallest storage unit that can be referenced by the DBMS

• Size specified during database creation

• Should be multiple of operating system block size

• Maximum size is operating system dependent


18

Relationship Between Physical and Logical Structures


19

Oracle9i Instance

• Consists of:– Background processes– Memory structures

• Started before the database files are opened


20

Background Processes

• System Monitor (SMON) – performs instance recovery

• Process Monitor (PMON) – “cleans up” failed user processes

• Log Writer (LGWR) – writes all changed data blocks from memory to the redo log files


21

Background Processes (Cont.)

• Database Writer (DBWn) – writes changed data blocks from memory to the actual data files

• Checkpoint (CKPT) – triggers writing of modified data

• Archiver (ARCn) – copies filled online redo log files after a log switch (optional process)


22

Memory Structures

• Program global area (PGA)– Contents vary based on database server

configuration

– Allocated when the server process is started

– Deallocated when the server process ends

• System global area (SGA) – – Consists of several substructures

– Allocated when the instance is started


23

System Global Area (SGA) Substructures

SGA substructures include:– Shared pool

• library cache: holds most recently used SQL and PL/SQL statements

• data dictionary cache: holds database object information

– Database buffer cache: contains most recently used data blocks

– Redo log buffer: stores changes made to contents of the data files


24

Initialization File (INIT.ORA)

• Contains parameter values for starting the database

• Two versions:– Pfile

• Can be altered using a text editor• Create from spfile using CREATE pfile FROM spfile;

– Spfile• Binary; generated by Oracle9i server• Can be created from alter pfile using CREATE spfile

FROM pfile;


25

Spfile Example


26

Backup and Recovery Terminology

• Backup – creating valid copies of database files

• Recovery – returning the database to a desired state


27

DBA Recovery Goals

• Mean-time-between-failures (MTBF)– Increase by performing preventive maintenance

• Mean-time-to-recover (MTTR)– Decrease with frequent backups and recovery

preparation


28

Tuning Instance Recovery

• FAST_START_MTTR_TARGET parameter:– Maximum number of seconds worth of data writes

that should be held in memory

– Value range: 0 to 3600 seconds • FAST_START_PARALLEL_ROLLBACK

parameter:– Can enable parallel processes for SMON operations

– Possible values: FALSE, HIGH, or LOW


29

Database Failure

• Two types:– Non-media failure

• Usually recoverable by background processes

– Media failure• Unable to access a database file

• Requires DBA intervention


30

Types of Non-Media Failures

• Process failure– Internal error or abnormal termination – Normally corrected by PMON

• Instance failure– Instance shuts down abruptly – Normally corrected by SMON


31

Types of Non-Media Failures (Cont.)

• Statement failure– Syntax error in submitted SQL or PL/SQL

statement– Corrected by submitting valid statement

• User error– Data or tables are incorrectly altered– May require DBA intervention to correct error


32

Backup and Recovery Strategy Factors

• Determine the strategic importance of the database

• Develop a backup strategy based on availability concerns

• Recovery method depends on cause of database failure

• Always test (and practice) recovery methods


33

Chapter Summary

• An Oracle9i database is composed of three types of physical files: control, data, and redo log files

• Has both a physical and logical structure• Oracle server consists of both a database

and an instance• An instance consists of background

processes and memory structures


34

Chapter Summary (Cont.)

• Background process started by default: SMON, PMON, LGWR, DBWn, and CKPT

• Two primary memory areas: PGA and SGA

• INIT.ORA file consists of initialization parameters used during instance and database start-up

• Two types of failures: non-media and media

chapter 1oracle9i dba ii: backup/recovery and network administration 1 chapter 1 backup and recovery...

Documents

store data

data segments

data filestwo files

changed data blocks

database creationshould

database location

online redo log files

redo log filesrecommended