Transcript Oracle9i

Chapter 5
The Redo Log Files
Oracle9i Database Administrator: Implementation and Administration
1
Objectives
 Learn to describe redo log files,
groups, and members
 Manage redo log groups and
members
Oracle9i Database Administrator: Implementation and Administration
2
Introduction to Online
Redo Log Files
Online redo log files store details as
the database is used including:

Checkpoints

Data Changes (DML)

Structural changes (DDL)

Data file changes
Oracle9i Database Administrator: Implementation and Administration
3
Oracle9i Architecture: A Typical Server
Oracle9i Database Administrator: Implementation and Administration
4
Introduction to Online
Redo Log Files
Components:
•Redo log group
•Online redo log
file or member
•Archived redo
log file
•ARCn
Oracle9i Database Administrator: Implementation and Administration
5
Introduction to Online
Redo Log Files
Components:
•Redo log buffer
•LGWR
•CKPT
Oracle9i Database Administrator: Implementation and Administration
6
Introduction to Online
Redo Log Files
File 1 is written to
File A by the
ARCn process
after the log
switch is
complete
Oracle9i Database Administrator: Implementation and Administration
7
Introduction to Online
Redo Log Files
File 1 and File 3
are written to by
the LGWR
process
simultaneously
until both are full
Oracle9i Database Administrator: Implementation and Administration
8
The Purpose of Redo Log Files
Redo log files aid in recovery from short term
data loss such as brief power outage:

Store changes that may not be written to the
data files yet

Store checkpoints to allow recovery to
resynchronize data files with correct changes

Cannot completely handle recovery from
major loss such as an entire data file
Oracle9i Database Administrator: Implementation and Administration
9
The Purpose of Redo Log Files
Recovery procedures (overview):
Prior to failure:

Perform full database backup regularly

Run database in ARCHIVELOG mode
After failure:

Restore from full backup

Apply archived redo log files (oldest to newest)

Apply online redo log file
Oracle9i Database Administrator: Implementation and Administration
10
The Structure of Redo Log Files
Redo log buffer is flushed to the redo log
file when:

A transaction COMMITS

The redo log buffer becomes 1/3 full

The redo log buffer contains >1 M of
updated records

A checkpoint occurs
Oracle9i Database Administrator: Implementation and Administration
11
The Structure of Redo Log Files
Redo log file components:

Redo record (also called redo entry)

Relates to one data block

Made up of one or more change vectors
Oracle9i Database Administrator: Implementation and Administration
12
Managing Redo Log Files
The COMMIT
command tells
the database to
log the
changes to the
redo log file
Oracle9i Database Administrator: Implementation and Administration
13
Log Switches and Checkpoints

To manually signal a log switch:
ALTER SYSTEM SWITCH LOGFILE;

A log switch triggers a checkpoint

Checkpoint is a signal to write all dirty
buffers to the appropriate files

Checkpoint increments the System
Change Number (SCN)
Oracle9i Database Administrator: Implementation and Administration
14
Log Switches and Checkpoints
How the SCN is used:

SCN is recorded in the redo log buffer

SCN is recorded in the header of each
data file that was written to

During recovery, SCN of each datafile is
compared to the SCN in the redo log file


If a match, the data file is up to date
If redo log file has higher SCN, changes are
reapplied to the data file from the redo log file
Oracle9i Database Administrator: Implementation and Administration
15
Multiplexing and Other
Maintenance

Multiplexed redo log files requires multiple
members in each group

A log group continues to function as long as
it has at least one good member

If all members of the current group become
damaged, the database forces a log switch

The database shuts down immediately if a
log switch fails
Oracle9i Database Administrator: Implementation and Administration
16
Adding a Member to a Group

Add members to groups while the database is
running

Do not specify SIZE because new members
are automatically assigned the same size as
other members in the group

Command syntax:
ALTER DATABASE ADD LOGFILE MEMBER '<X:\xxx>\<filename>'
TO GROUP <n>;
Oracle9i Database Administrator: Implementation and Administration
17
Adding a New Group

Add a new group while the database is
running

Specify SIZE and group number

List all members to be created with the
group (must be at least one)

Command syntax:
ALTER DATABASE ADD LOGFILE GROUP <n>
( '<X:\xxx>\<filename>', '<X:\xxx>\<filename>')
SIZE <n>;
Oracle9i Database Administrator: Implementation and Administration
18
Renaming or Moving
a Redo Log File

Must be done while the group is not
ACTIVE

Steps:
1.
Shut down the database:
SHUTDOWN IMMEDIATE
2.
3.
Rename or relocate files using the operating
system
Mount the database
STARTUP MOUNT
Oracle9i Database Administrator: Implementation and Administration
19
Renaming or Moving
a Redo Log File
Steps (continued):

4.
Inform database of changed file name:
ALTER DATABASE
RENAME FILE '<X:\xxx>\<oldfilename1>',
'<X:\xxx>\<oldfilename2>'
TO '<X:\xxx>\<newfilename1>',
'<X:\xxx>\<newfilename2>';
5.
Open the database:
ALTER DATABASE OPEN;
Oracle9i Database Administrator: Implementation and Administration
20
Dropping Redo Log Members
or Groups
Reasons for dropping members or
groups:

Bad disk, so must recreate member

Tuning recommendation calls for
reducing members or groups

Corrupted members can be deleted
and replaced later
Oracle9i Database Administrator: Implementation and Administration
21
Dropping Redo Log Members
or Groups


Rules when dropping redo log members:

Each group must have at least one member

The group affected must not be ACTIVE and (if in
ARCHIVELOG mode) must be archived
Rule when dropping groups:

Database requires at least two groups

The group affected must not be ACTIVE and (if in
ARCHIVELOG mode) must be archived
Oracle9i Database Administrator: Implementation and Administration
22
Dropping Redo Log Members or
Groups
Steps to drop redo log member:
1.
2.
Query V$LOG to confirm that group is
inactive
Drop redo log member:
ALTER DATABASE DROP LOGFILE MEMBER
'<X:\xxx>\<filename>';
3.
Delete associated file in the operating
system
Oracle9i Database Administrator: Implementation and Administration
23
Dropping Redo Log Members or
Groups
Steps to drop redo log group:
1.
2.
Query V$LOG to confirm that group is
inactive: SELECT * FROM V$LOG;
Drop redo log group:
ALTER DATABASE DROP LOGFILE GROUP <n>;
3.
Delete associated file(s) in the operating
system
Oracle9i Database Administrator: Implementation and Administration
24
Dropping Redo Log Members or
Groups
Additional notes:

If group is ACTIVE, force log switch:
ALTER SYSTEM SWITCH LOGFILE;

If group still active, force checkpoint:
ALTER SYSTEM CHECKPOINT;

An alternative to dropping a corrupt group and
recreating the members:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <n>;
Oracle9i Database Administrator: Implementation and Administration
25
Archiving a Redo Log Group
Advantages of archiving redo log groups:

Point-in-time recovery to a point earlier
than online redo log files contain

The ability to query archived log files with
LogMiner

The ability to set up and maintain a
standby database
Oracle9i Database Administrator: Implementation and Administration
26
Finding Redo Log Information in
Data Dictionary Views
States of a redo log group:






UNUSED
CURRENT
ACTIVE
CLEARING
CLEARING_CURRENT
INACTIVE
Oracle9i Database Administrator: Implementation and Administration
27
Viewing Control File Data
Oracle9i Database Administrator: Implementation and Administration
28
Chapter Summary

Redo log files primarily contain information on
database changes

A database has at least two redo log groups

A redo log group has at least one member

Redo log files support automatic recovery
from minor failures

A redo log group with multiple files is
multiplexed
Oracle9i Database Administrator: Implementation and Administration
29
Chapter Summary

The SCN is incremented at a checkpoint

Maintenance you can do with redo log files
and groups:

Add a new file (member) to a group

Add a new group

Rename or relocate a member

Drop a member

Drop a group

Clear a group
Oracle9i Database Administrator: Implementation and Administration
30
Chapter Summary

Placing a database in ARCHIVELOG
mode causes redo log files to be
archived after a log switch

Many initialization parameters set the
behavior of the archiving function

The V$LOG and V$LOGFILES dynamic
performance views display information
about redo log groups and members
Oracle9i Database Administrator: Implementation and Administration
31