Pages

Sunday, March 15, 2015

Archive log files grow very fast

What can cause Archive logs to grow fast?

=====================
General
=====================
Log Writer Process (LGWR)
Redo Log Contents
Archiver Processes (ARCn)
What can cause Archive logs to grow fast?

=====================
Log Writer Process (LGWR)
=====================
The log writer process (LGWR) is responsible for redo log buffer management—writing the redo log buffer to a redo log file on disk. 
LGWR writes all redo entries that have been copied into the buffer since the last time it wrote.
The redo log buffer is a circular buffer. When LGWR writes redo entries from the redo log buffer to a redo log file, server processes can then copy new entries over the entries in the redo log buffer that have been written to disk. 
LGWR normally writes fast enough to ensure that space is always available in the buffer for new entries.
When a user issues a COMMIT statement, LGWR puts a commit record in the redo log buffer and writes it to disk immediately. 
The corresponding changes to data blocks are deferred until it is more efficient to write them

What about non commited transactions?
Per Oracle documentation, The entire list of redo entries of waiting transactions (not yet committed) can be written to disk in one operation, requiring less I/O than do transaction entries handled individually. 

=====================
Redo Log Contents
=====================
Redo 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
Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments.
Redo records can also be written to a redo log file before the corresponding transaction is committed. 
If the redo log buffer fills, or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to a redo log file, even though some redo records may not be committed. If necessary, the database can roll back these changes.



=====================
Archiver Processes (ARCn)
=====================
The archiver process (ARCn) copies redo log files to a designated storage device after a log switch has occurred. 
ARCn processes are present only when the database is in ARCHIVELOG mode, and automatic archiving is enabled.
An Oracle instance can have up to 10 ARCn processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of ARCn processes is insufficient to handle the workload. 

==================================
What can cause Archive logs to grow fast?
==================================
Generally speaking, heavy DML operations.

Consider following scenario:
Database is running heavy DML Transaction, for example delete millions of rows at once.

This TX fails, with:
 ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

Now, the data for all these changes was already written to redo log, and then archived, as Oracle has to free up space in redo log buffer, and does not wait till the end of transaction.
If that process is run again and again, for example from a scheduled job, the archive destination would fill up quite fast...

In this case the archive log files would reach the maximum limit, and would be all of same size.
The MAX size of the archive log would be size of redo log:

SELECT group#, bytes/1024/1024 AS Mb 
FROM V$LOG;

No comments:

Post a Comment