Oracle log files : An introduction
The Oracle server maintains the redo Oracle log files to minimize the loss of data in the Database in case of an uncontrolled shutdown.
Online redo Oracle log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.
For example, if you change a salary value in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the rollback segment data block, and the transaction table of the rollback segments.
The question here is how are the Oracle log files maintained, and what information do we have?
A couple of interesting Oracle views:
a)To view information on log files:
SELECT * FROM v$log;
b)To view information on log file history:
SELECT thread#, first_change#,
TO_CHAR(first_time,'MM-DD-YY HH12:MIPM'),
next_change#
FROM v$log_history;
The above shows you what log state your system is in. Read more about ARCHIVELOG in the article on Oracle Backup.
Consider the parameters that can limit the number of online redo Oracle log files before setting up or altering the configuration of an instance's online redo log.
The following parameters limit the number of online redo Oracle log files that you can add to a database:
- The MAXLOGFILES parameter used in the CREATE DATABASE statement determines the maximum number of groups of online redo Oracle log files for each database.
Group values can range from 1 to MAXLOGFILES.
The only way to override this upper limit is to re-create the database or its control file. Thus, it is important to consider this limit before creating a database.
If MAXLOGFILES is not specified for the CREATE DATABASE statement, Oracle uses an operating system specific default value. The MAXLOGMEMBERS parameter used in the CREATE DATABASE statement determines the maximum number of members for each group.
As with MAXLOGFILES, the only way to override this upper limit is to re-create the database or control file. Thus, it is important to consider this limit before creating a database.
If no MAXLOGMEMBERS parameter is specified for the CREATE DATABASE statement, Oracle uses an operating system default value.
At any given time, Oracle uses only one of the online redo log files to store redo records written from the redo log buffer.
The online redo log file that Log Writer (LGWR) is actively writing to is called the current online redo log file. Online redo Oracle log files that are required for instance recovery are called active online redo log files. Online redo log files that are not required for instance recovery are called inactive.
If you have enabled archiving (ARCHIVELOG mode), Oracle cannot reuse or overwrite an active online log file until ARCn has archived its contents.
If archiving is disabled (NOARCHIVELOG mode), then the last online redo log file fills writing continues by overwriting the first available active file. The best way to determine the appropriate number of online redo log files for a database instance is to test different configurations.
The optimum configuration has the fewest groups possible without hampering LGWR's writing redo log information.
In some cases, a database instance may require only two groups. In other situations, a database instance may require additional groups to guarantee that a recycled group is always available to LGWR.
During testing, the easiest way to determine if the current online redo log configuration is satisfactory is to examine the contents of the LGWR trace file and the database's alert log.
If messages indicate that LGWR frequently has to wait for a group because a checkpoint has not completed or a group has not been archived, add groups.
LGWR writes to online redo log files in a circular fashion. When the current online redo log file fills, LGWR begins writing to the next available online redo log file.
When the last available online redo log file is filled, LGWR returns to the first online redo log file and writes to it, starting the cycle again. The numbers next to each line indicate the sequence in which LGWR writes to each online redo log file.
Filled online redo log files are available to LGWR for reuse depending on whether archiving is enabled or disabled:
· If archiving is disabled (NOARCHIVELOG mode), a filled online redo log file is available once the changes recorded in it have been written to the datafiles.
· If archiving is enabled (ARCHIVELOG mode), a filled online redo log file is available to LGWR once the changes recorded in it have been written to the datafiles and once the file has been archived.
Operations on Oracle log files :
- Forcing log file switches:
ALTER SYSTEM switch logfile;
or
ALTER SYSTEM checkpoint; - Clear A Log File If It Has Become Corrupt:
ALTER DATABASE CLEAR LOGFILE GROUP group_number; - This statement overcomes two situations where dropping redo logs is not possible: If there are only two log groups and if the corrupt redo log file belongs to the current group:
ALTER DATABASE CLEAR LOGFILE GROUP 4; - Clear A Log File If It Has Become Corrupt And Avoid Archiving:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP group_number; - Use this version of clearing a log file if the corrupt log file has not been archived:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3; - Privileges Related To Managing Log Files:
ALTER DATABASE
ALTER SYSTEM - Init File Parameters Related To Log Files:
log_checkpoint_timeout ... set to 0 - Managing Log File Members:
ALTER DATABASE
ADD LOGFILE MEMBER 'log_member_path_and_name'
TO GROUP group_number; - Adding log file group members:
ALTER DATABASE
ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2; - Droping log file group members:
ALTER DATABASE
DROP LOGFILE MEMBER log_member_path_and_name';
ALTER DATABASE
DROP LOGFILE MEMBER '/oracle/dbs/log3c.rdo'; - To create a new group of online redo log files, use the SQL statement ALTER DATABASE with the ADD LOGFILE clause:
The following statement adds a new group of redo Oracle log files to the database:
ALTER DATABASE ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/
oracle/dbs/log2c.rdo') SIZE 500K;
Return from Oracle log files to Oracle DBA
Introduction :
I noticed mostly DBAs has a confusion regarding redo log switch and checkpoint. With this articles i tried to explain the concept of checkpoint.
· What is the Role of checkpoint ?
· What should be frequency of checkpoint ?
· How do we set the value of LOG_CHECKPOINT_INTERVAL ?
· What does the LOG_CHECKPOINT_TIMEOUT value means ?
· What is the significance of LOG_CHECKPOINT_TO_ALERT ?
What is the Role of checkpoint ?
Checkpoint synchronizes the datablock in memory with the datafiles on disk. When a checkpoint occurs , DBWR writes all modified database blocks from the buffer cache to datafiles.
(BUFFER CACHE)
DBWR ------------------------------
[ Modified database blocks ]
LGWR also updates both the controlfile and datafiles to indicate last SCN (checkpoint)
(Record Last SCN no)
LGWR ------------------------------
CKPT process performs the operation of LGWR if it is enabled. CKPT is enable as default in version 8.0
Checkpoint occurs
1) At redo log switch
2) LOG_CHECKPOINT_TIMEOUT has expired
3) LOG_CHECKPOINT_INTERVAL has reached.
4) Forcefully by DBA
What should be frequency of checkpoint ?
Depending on the number of datafiles in a database, a checkpoint can be highly resource intensive. More number of checkpoint reduce recovery time at the time of crash since less redo need to be reapplied , but causes an impact on performance because of system overhead.
In short if downtime is very vary critical , checkpoint should be raised frequently otherwise your goal should be to reduce its frequency to enhance the better performance.
How do we set the value of LOG_CHECKPOINT_INTERVAL ? :
Log_checkpoint_interval is defined in terms of OS Block size.
Suppose the following setting is defined in your database environment :-
LOG_CHECKPOINT_INTERVAL = 10000
OS block size = 512 bytes
Redo log file Size = 20M
Calculation of checkpoint = (20 * 1024*1024)/(512*10000) = 4
Four checkpoint will be raised per redo log file . One checkpoint will be raised when (10000*512) bytes has to be written by the LGWR from cache to redo log files.
If you will define log_checkpoint_interval = 0 (zero) means you are setting this value to infinity. and causes the parameter to be ignored.
If the value of the (log_checkpoint_interval * OS BLOCKSIZE) > redolog file size , checkpoint will occur at the redolog switch
You can set this parameter dynamically by alter system as
SQL> ALTER SYSTEM SET LOG_CHECKPOINT_INTERVAL=
What does the LOG_CHECKPOINT_TIMEOUT value means ?
Log_checkpoint_timeout value is define in terms of seconds in initsid.ora file.
For example LOG_CHECKPOINT_TIMEOUT = 1800 # 1800 sec default value in 8i and 9i enterprise edition #
In Oracle 9i ,the checkpoint raised from the position where the last write to the redo log was 1800 seconds (defined as above) ago . It also points that no buffer will remain dirty for more than 1800 seconds.
In Oracle 8i or earlier versions , checkpoint raises based upon the number of seconds that have passed since the last checkpoint.
For example if you define LOG_CHECKPOINT_TIMEOUT = 180 causes checkpoint to be raised after every 180 seconds.
Setting this value to 0 disable the parameter.
You can set this parameter dynamically by alter system as
SQL> ALTER SYSTEM SET LOG_CHECKPOINT_TIMEOUT = 2400;
What is the significance of LOG_CHECKPOINT_TO_ALERT ?
Log_checkpoint_to_alert parameter is boolean type and its default value is FALSE. By setting this parameter to TRUE allows you to log checkpoint start and stop times in the alert log to determine the checkpoints activity.
Somtimes there is an error in an alert log file "checkpoint not complete ".What does it indicate ?
It means Oracle is ready to recycle the redo logs but it can not because the checkpoint in the previous log is still in progress
Also you can query the v$sysstat system view to determine the value of
background_checkpoint_
SQL > select name,value from sys.v$sysstate where name like 'background checkpoints%';
if the difference of these two values are grater than 1 you must do
1) Add more redo log groups or increase the size of redo logs.
2) Reduce the frequency of checkpoints by increasing LOG_CHECKPOINT_INTERVAL.
Conclusion :
Checkpoint plays a very important role in database activity. So DBAs must have to monitor its efficiency during peak database activities. Log switches cause a checkpoint, but checkpoint does not cause a log switch
No comments:
Post a Comment