Transcript INT7016 – Contemporary Issues in Database Development

7202ICT – Database Administration
Lecture Three
Database Architecture








DATABASE WRITER (DBWR) PROCESS
UPDATE OPERATION
REDO LOG BUFFER
DATABASE COMMITS
REDO LOG FILES
CHECK POINTS
ARCH PROCESS
TRANSACTIONS OVERVIEW
Reading Oracle Concept Manuel Chapter 8
http://www.cit.griffith.edu.au/~s106441/7202/concept.pdf
Database writer process
SGA
Shared
Pool
Database Buffer Cache
DBWR
Server
User
Data
Files
DATABASE WRITER (DBWR) PROCESS
DBWR manages the database buffer cache so that user
processes can always find free buffers as needed.(picture)
The DBWR Process
 Writes all changed (modified) data buffers to the
appropriate data files
 Uses an LRU algorithm to keep the most recently used
data blocks in memory while aging out the least recently
used blocks
• NB: You can start more than one DBWR process. This would
be required if one DBWR process is incapable of handling the
demands of the database.
DATABASE WRITER (DBWR) PROCESS
The DBWR process will wake up and begin writing dirty
buffers to disk when:
 The dirty list reaches a pre-defined threshold (set using
database parameters DB_BLOCK_WRITE_BATCH)
 A server process scans a specified number of buffers (set
using database parameters DB_BLOCK_MAX_SCAN) in
the LRU list without finding a free buffer (server process
then signals DBWR process)
 A time-out occurs (these occur about every 3 seconds)
 A checkpoint occurs
LOG TRANSACTIONS
• Oracle will record all changes made to the database in the redo
log buffer. A background process called the LOG WRITER
(LGWR) will write the information recorded in the redo log
buffer to disk. Another background process called the
ARCHIVER (ARCH) may optionally be used to archive all
redo information.(Picture)
• The redo log buffer is a circular buffer containing information
about changes made to the database. This information is stored
in redo entries.
LOG TRANSACTIONS
Redo Entries
– Stores all changes made to the database in the redo entries
of the redo log buffer.
– Used to reconstruct or rollback changes made to the
database when database recovery is necessary due to a
database crash.
• NB: ORACLE does not consider a transaction to be complete
until the LGWR has written the appropriate redo logs for that
transaction to disk. It is this action that determines the success
of a commit statement.
Logwriter Process
SGA
Shared Pool
Redo Log
Buffer
Database Buffer Cache
Shared SQL
Area
ARCH
Server
DBWR
LGWR
User
Data
Files
Redo
Log
Files
LOGWRITER PROCESS (LGWR)
The Log Writer (LGWR) process writes redo log entries to
disk.(Picture)
• LGWR writes redo log buffer entries to the redo log files
when:
 A Commit occurs
 The redo log buffer pool reaches one third full
threshold
 The DBWR needs to clean the buffer blocks for a
checkpoint
 A time-out occurs
LOGWRITER PROCESS (LGWR)
• N.B
 There is only one redo log writer per instance
 A commit confirmation is not issued to the user until
the transaction is recorded in the log file
 LGWR cannot write if the ARCH process is active on
the group that LGWR needs to write to. In this case all
database actions shall queue will until the ARCH
process is completed.
 The user must commit a transaction to make the
changes permanent in the database.
DATABASE COMMITS
Commit Operation
– A user issues the commit command
– A commit record is placed in the redo log buffer
– LGWR flushes the redo log buffers to current log file
– The user is notified that the transaction has been
committed
– Resource locks are released on data and rollback blocks
– The data block is marked as “pinned” (clean but out of
synch with disk)
– DBWR will eventually write database block to disk
REDO LOG FILES
• Redo log files record all changes made to the database, and are used
for data recovery. If the redo log files are mirrored (multiplexed), then
the same redo log information is written to multiple online redo log
files.(Picture)
• Redo Log Files
 Redo log files are written to in a circular fashion
 There must be at least 2 redo log groups
• Mirrored Redo Log Files
 Group members are updated simultaneously
 Mirrored redo log files protect against loss of a redo log file
REDO LOG FILES
LGWR
Redo Log Files
Log File 1
Log File 2
Log File 3
Multiplexed Redo Log Files
Disk 1
Disk 2
Group 1
Group 2
Group 3
Member A
Member A
Member A
Member B
Member B
Member B
Log Switches
 A log switch is when LGWR switches from one redo
log file to another
 A log switch occurs when LGWR has filled on log file
group
 A log switch can be forced by the DBA if current redo
file needs to be archived
 A log switch occurs when the database is shut down
 A checkpoint automatically occurs at a log switch
CHECKPOINTS
• During a checkpoint, DBWR writes all dirty buffers in the database
buffer cache to disk, guaranteeing that all data blocks modified since
the previous checkpoint are actually written to disk.
• Checkpoints Occur
 At every log switch
 A specified number of seconds
(LOG_CHECKPOINT_TIMEOUT) after the last checkpoint
 When a predetermined number of redo log blocks have been
written to disk since the last checkpoint
 At instance shutdown
 When forced by a DBA
 When a tablespace is taken off-line (ie: taking a table off-line)
CHECKPOINTS
• During a checkpoint and after the associated log switch, LGWR will
update the headers of database and control files, unless the optional
checkpoint (CKPT) process has been started, in which case it will
perform this task.
• The parameter LOG_CHECKPOINT_TIMEOUT is used to determine
the interval of time between checkpoints.
• The parameter LOG_CHECKPOINT_INTERVAL is used to determine
how many redo file blocks must be filled since the previous
checkpoint, before another checkpoint is initiated.
• Database checkpoints ensure that all modified database buffers are
written to the database files. The database files are marked current as
of a given time and the checkpoint is recorded in the control
file.(Picture)
Checkpoints
– Require that instance recovery need only apply changes that have
been made since the last checkpoint
– Allow an on-line redo log file to be reused by guaranteeing that all
changes stored in the redo log file are also written to the
appropriate data file
– Checkpoints do not halt activity on the database, nor are current
transactions affected.
• CKPT Process
 Updates headers of data and control files after a checkpoint has
been completed
 More frequent checkpoints will reduce the time necessary to
recover a database from instance failure, but will degrade overall
performance (tradeoff)
Checkpoints
SGA
Shared Pool
Redo Log
Buffer
Database Buffer Cache
Shared SQL
Area
ARCH
Server
DBWR
LGWR
User
Data
Files
Control Files
Redo Log
Files
CKPT
ARCH PROCESS
• The Archiver Process (ARCH) is an optional process, which when
enabled, will copy online redo log files to a designated storage area or
device once they become full.(Picture)
• Archiver Process
– Redo log files are copied to tape or disk for media failure recovery
(damaged disk)
 ARCH operates only when a log file group switch occurs
 ARCH is optional and is present only when automatic archiving is
enabled, or when manually requested
 ARCH may write to a tape device or to a disk
Savepoints
•
mark an interim point within a transaction to which a
transaction can later be rolled back.
• Syntax – SAVEPOINT
– SAVEPOINT savepoint_name
• Syntax – ROLLBACK
– ROLLBACK TO savepoint_name
Savepoint Features
• Useful during interactive trial and error situations
• Useful within structured programming where:
– Main program calls sub-routine
– Sub-routine sets savepoint
– Sub-routine processes statements
– Sub-routine encounters error and rolls back to savepoint
– Sub-routine returns control to main program and reports error
• Maximum number of savepoints per transaction is determined by
SAVEPOINTS parameter
• Rolling back to a savepoint`
– Rolls back changes since savepoint_name
– Release locks acquired since savepoint_name