Oracle log files : An introductionThe 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 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#,
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:
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:
Operations on Oracle log files :
Free eBookSubscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:
What visitors say...
"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.Read more Testimonials
I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.
Return to top
The name Oracle is a trademark of Oracle Corporation.