Database Writer
The server process records changes to rollback and data blocks in the buffer cache. Database
Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files. It
ensures that a sufficient number of free buffers—buffers that can be overwritten when server
processes need to read in blocks from the data files—are available in the database buffer
cache. Database performance is improved because server processes make changes only in the
buffer cache.
DBWn defers writing to the data files until one of the following events occurs:
• Incremental or normal checkpoint
• The number of dirty buffers reaches a threshold value
• A process scans a specified number of blocks when scanning for free buffers and cannot fine any.
• Timeout occurs.
• A ping request in Real Application Clusters environment.
• Placing a normal or temporary tablespace offline.
• Placing a tablespace in read only mode.
• Dropping or Truncating a table.
• ALTER TABLESPACE tablespace name BEGIN BACKUP
#####################################################################
LOG Writer
LGWR performs sequential writes from the redo log buffer cache to the redo log file under
the following situations:
• When a transaction commits
• When the redo log buffer cache is one-third full
• When there is more than a megabyte of changes records in the redo log buffer cache
• Before DBWn writes modified blocks in the database buffer cache to the data files
• Every 3 seconds.
Because the redo is needed for recovery, LGWR confirms the commit only after the redo is
written to disk.
LGWR can also call on DBWn to write to the data files.
##################################
Checkpoint
A checkpoint occurs in the following situations:
• At every log switch
• When an instance has been shut down with the normal, transactional, or immediate
option
• When forced by setting the initialization parameter FAST_START_MTTR_TARGET.
• When manually requested by the database administrator
• When the ALTER TABLESPACE [OFFLINE NORMAL|READ ONLY|BEGIN BACKUP] cause checkpointing on specific data files.
log_checkpoints_to_alert FALSE|TRUE
to make record of checkpoint in alert
ALTER SYSTEM CHECKPOINT; ---this can be used to force the checkpoint
An event called a checkpoint occurs when the Oracle background process DBWn writes all
the modified database buffers in the SGA, including both committed and uncommitted data,
to the data files.
Checkpoints are implemented for the following reasons:
• Checkpoints ensure that data blocks in memory that change frequently are written to
data files regularly. Because of the least recently used algorithm of DBWn, a data block
that changes frequently might never qualify as the least recently used block and thus
might never be written to disk if checkpoints did not occur.
• Because all database changes up to the checkpoint have been recorded in the data files,
redo log entries before the checkpoint no longer need to be applied to the data files if
instance recovery is required. Therefore, checkpoints are useful because they can
expedite instance recovery.
At a checkpoint, the following information is written:
• Checkpoint number into the data file headers
• Checkpoint number, log sequence number, archived log names, and system change
numbers into the control file.
CKPT does not write data blocks to disk or redo blocks to the online redo logs.
#######################################################
Checkpoint number
Checkpoint number is the SCN number at which all the dirty buffers are written to the disk, there can be a checkpoint at object/tablespace/datafile/database level.
Checkpoint number is never updated for the datafiles of readonly tablespaces.
A checkpoint occurs when the DBWR (database writer) process writes all modified buffers in the SGA buffer cache to the database data files. Data file headers are also updated with the latest checkpoint SCN, even if the file had no changed blocks.
Checkpoints occur AFTER (not during) every redo log switch and also at intervals specified by initialization parameters.
log sequence number
A number that uniquely identifies a set of redo records in a redo log file. When Oracle fills one online redo log file and switches to a
different one, Oracle automatically assigns the new file a log sequence number. For example, if you create a database with two online log files,
then the first file is assigned log sequence number 1. When the first file fills and Oracle switches to the second file, it assigns
log sequence number 2; when it switches back to the first file, it assigns log sequence number 3, and so forth.
SCN system change numbers (high and low SCN number )
the system change number (SCN) is Oracle's clock - every time we commit, the clock
increments. The SCN just marks a consistent point in time in the database.
select dbms_flashback.get_system_change_number scn from dual;
When the Oracle server begins executing a SELECT statement, it determines the current
system change number (SCN) and ensures that any changes not committed before this SCN
are not processed by the statement. Consider the case where a long-running query is executed
at a time when several changes are being made. If a row has changes that were not committed
at the start of the query, the Oracle server constructs a read-consistent image of the row by
retrieving the before image of the changes from the undo segment and applying the changes
to a copy of the row in memory.
the scn increments with every commit, not every second. It is not a "ticker".
It is written to the controlfiles when the control files are updated - which happens as the result of a few things, one being "end of official checkpoint".
the database never sleeps. Most of those other "programs" do transactions and commit.
scn's are written to redo logs continuously - when you commit they are emitted into the redo stream.
and semantically speaking, after the checkpoint COMPLETES, not after the checkpoint is INITIATED
when database has no transactions going on,when do checkpoint be using ?
The database ALWAYS has transactions going on, ALWAYS. SMON and many other background
processes are always doing work, the database (unless it is opened read only) is always
doing transactions.
The system change number (SCN) is an ever-increasing value that uniquely identifies
a committed version of the database. Every time a user commits a transaction,
Oracle records a new SCN. You can obtain SCNs in a number of ways, for example,
from the alert log. You can then use the SCN as an identifier for purposes of
recovery. For example, you can perform an incomplete recovery of a database up to
SCN 1030. Oracle uses SCNs in control files, datafile headers, and redo records. Every redo log
file has both a log sequence number and low and high SCN. The low SCN records
the lowest SCN recorded in the log file, while the high SCN records the highest SCN
in the log file.
No comments:
Post a Comment