Managing the Redo Log

1.What Is the Redo Log?

 

store all changes made to the database as they occur. 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, describe changes to the data segment block for the table, the undo segment data block, and the transaction table of the undo segments.

Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

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.

The minimum size permitted for a redo log file is 4 MB.

Redo Threads

When speaking in the context of multiple database instances, the redo log for each database instance is also referred to as a redo thread. In typical configurations, only one database instance accesses an Oracle Database, so only one thread is present. In an Oracle Real Application Clusters environment, however, two or more instances concurrently access a single database and each instance has its own thread of redo. A separate redo thread for each instance avoids contention for a single set of redo log files, thereby eliminating a potential performance bottleneck.

 

2.system change number (SCN):Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction.

 

3.How Oracle Database Writes to the Redo Log

LGWR writes to redo log files in a circular fashion. When the current redo log file fills, LGWR begins writing to the next available redo log file. When the last available redo log file is filled, LGWR returns to the first redo log file and writes to it, starting the cycle again,Filled redo log files are available to LGWR for reuse depending on whether archiving is enabled.

• If archiving is disabled (the database is in NOARCHIVELOG mode), a filled redo log file is available after the changes recorded in it have been written to the datafiles.

• If archiving is enabled (the database is in ARCHIVELOG mode), a filled redo log file is available to LGWR after the changes recorded in it have been written to the datafiles and the file has been archived.

4. status of logfile  group and logfile member

current:Oracle Database uses only one redo log files at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file.

Active:Redo log files that are required for instance recovery are called active redo log files.

Inactive:Redo log files that are no longer required for instance recovery are called inactive redo log files.

• INVALID - File is inaccessible

• STALE - File's contents are incomplete

• DELETED - File is no longer used

• null - File is in use

•  

5.Log Switches and Log Sequence Numbers

A log switch is the point at which the database stops writing to one redo log file and begins writing to another. Normally, a log switch occurs when the current redo log file is completely filled and writing must continue to the next redo log file. However, you can configure log switches to occur at regular intervals, regardless of whether the current redo log file is completely filled. You can also force log switches manually.

Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it. When the database archives redo log files, the archived log retains its log sequence number. A redo log file that is cycled back for use is given the next available log sequence number.

Each online or archived redo log file is uniquely identified by its log sequence number. During crash, instance, or media recovery, the database properly applies redo log files in ascending order by using the log sequence number of the necessary archived and redo log files.

 

6.Planning the Redo Log

Multiplexing Redo Log Files:To protect against a failure involving the redo log itself, Oracle Database allows a multiplexed redo log, meaning that two or more identical copies of the redo log can be automatically maintained in separate locations.When redo log files are multiplexed, LGWR concurrently writes the same redo log information to multiple identical redo log files, thereby eliminating a single point of redo log failure.Multiplexing is implemented by creating groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of the group.

Legal and Illegal Configurations:In most cases, a multiplexed redo log should be symmetrical: all groups of the redo log should have the same number of members. However, the database does not require that a multiplexed redo log be symmetrical.The only requirement for an instance redo log is that it have at least two groups. The second configuration is illegal because it has only one group.

Placing Redo Log Members on Different Disks:If you archive the redo log, spread redo log members across disks to eliminate contention between the LGWR and ARCn background processes. For example, if you have two groups of multiplexed redo log members (a duplexed redo log), place each member on a different disk and set your archiving destination to a fifth disk. Doing so will avoid contention between LGWR (writing to the members) and ARCn (reading the members).Datafiles should also be placed on different disks from redo log files to reduce contention in writing data blocks and redo records.

Setting the Size of Redo Log Members:When setting the size of redo log files, consider whether you will be archiving the redo log. Redo log files should be sized so that a filled group can be archived to a single unit of offline storage media (such as a tape or disk), with the least amount of space on the medium left unused.

Choosing the Number of Redo Log Files: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.MAXLOGFILES.MAXLOGMEMBERS

 

7.add logfile group/members

 

   alter database add logfile group 1 ('','')  size 100m;

   alter database add logfile member '/u01/oracle/lsh/log.dbf' to group 1;

8.drop logfile group/members

 

   alter database drop logfile group 1;

   alter database drop logfile member '/u01/oracle/lsh/log.dbf' ;

 

 

An instance requires at least two groups of redo log files, regardless of the number of members in the groups. (A group comprises one or more members.)

You can drop a redo log group only if it is inactive. If you need to drop the current group, first force a log switch to occur.

Make sure a redo log group is archived (if archiving is enabled) before dropping it. To see whether this has happened, use the V$LOG view.

It is permissible to drop redo log files so that a multiplexed redo log becomes temporarily asymmetric. For example, if you use duplexed groups of redo log files, you can drop one member of one group, even though all other groups have two members each. However, you should rectify this situation immediately so that all groups have at least two members, and thereby eliminate the single point of failure possible for the redo log.

An instance always requires at least two valid groups of redo log files, regardless of the number of members in the groups. (A group comprises one or more members.) If the member you want to drop is the last valid member of the group, you cannot drop the member until the other members become valid. To see a redo log file status, use the V$LOGFILE view. A redo log file becomes INVALID if the database cannot access it. It becomes STALE if the database suspects that it is not complete or correct. A stale log file becomes valid again the next time its group is made the active group.

You can drop a redo log member only if it is not part of an active or current group. If you want to drop a member of an active group, first force a log switch to occur.

Make sure the group to which a redo log member belongs is archived (if archiving is enabled) before dropping the member. To see whether this has happened, use the V$LOG view.

 

9.Relocating and Renaming Redo Log Members

   shutdown immediate;

   !cp/mv '/u01/oradata/lsh/redo01.dbf' to '/u02/oradata/lsh/redo01.dbf'

   startup mount;

   alter database rename file '/u01/oradata/lsh/redo01.dbf' to '/u02/oradata/lsh/redo01.dbf'

   alter database open;   

10.Forcing Log Switches

alter system switch logfile;

 

11.Clearing a Redo Log File

ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database.

This statement overcomes two situations where dropping redo logs is not possible:

• If there are only two log groups

• The corrupt redo log file belongs to the current group

12.log contention

    a large wait time on the “log file sync”,you may be experiencing contention on the redo logs;they are not being written fast enough.

    Redo is designed to be written with sequential writes and to be on dedicated devices.

    1.One application reason (one the DBA cannot fix, but the developer must fix) is that you are committing too frequently—committing inside of a loop doing INSERTs,

    2.Putting redo on a slow device.

    3.Putting redo on the same device as other files that are accessed frequently

    4.Mounting the log devices in a buffered manner.

    5.Putting redo on a slow technology, such as RAID-5: RAID-5 is great for reads, but it is terrible for writes.

 

13.BLOCK CLEANOUT

     we may have to “clean it out”—in other words, remove the transaction information.this action generate redo and may cause lots of blocks to be written to disk with the next checkpoint.

     1.when commint,execute BLOCK CLEANOUT

     2.deferred  BLOCK CLEANOUT,CREATE TABLE AS SELECT, direct path loaded data, and direct path inserted data will all create
“clean” blocks. An UPDATE, normal INSERT, or DELETEmay create blocks that need to be cleaned
with the first read(query).

 

14.the Redo/Undo of Temporary Tables

       an operation on a tempo-
rary table is not “recoverable.” When you modify a block in a temporary table, no record of this
change will be made in the redo log files. However, temporary tables do generate undo, and
the undo is logged. Hence, temporary tables will generate some redo.

       • An INSERT will generate little to no undo/redo activity.
       • A   DELETE will generate the same amount of redo as a normal table.
       • An UPDATE of a temporary table will generate about half the redo of an UPDATE of a normal table.