The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.
Managing the REDO LOG:
The current redo log is always online, unlike archived copies of a redo log. Therefore, the online redo log is usually referred to as simply the redo log.
This section describes some of the more common redo log management tasks. It contains the following topics:
- About online redo log files
- Multiplexing the redo log
- Switching a log file
- Moving online redo log files
- Adding and dropping the redo log groups
- Verifying blocks in redo log files
- Clearing a redo log file
About online redo log files:
The most crucial structure for recovery operations is the online redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle database has an associated online redo log to protect the database in case of an instance failure.
Every Oracle database has a set of two or more online redo log files. The set of redo log files is collectively known as the redo log for the database. A redo log is made up of redo entries, which are also called redo records.
The redo log stores a copy of the changes made to data. If a failure requires a data file to be restored from backup, then the recent data changes that are missing from the restored datafile can be obtained from the redo log, so work is never lost. The redo log is used to recover a database after hardware, software, or media failure. To protect against a failure involving the redo log itself, oracle database can multiplex the redo log so that two or more identical copies of the online redo log can be maintained on different disks.
The redo log for a database consists of groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is considered to be a member of that group. Each group is defined by a number, such as group 1.
Below figure shows the configuration of a database that has three redo log groups and two members in each group. For each group, the members are stored on separate disks for maximum availability. For example, the members of group 1 are the redo log files a_log1 and b_log1.
Online redo log groups and their members:
You can create groups and members of online redo log files during or after database creation. To create new online redo log groups and members, you must have the ALTER DATABASE system privilege.
The database log writer process (lgwr) writes redo records from the memory buffer to a redo log group until the log files in that group reach their storage size limit, or until you request a log switch operation. The lgwr process then writes to the next log group. The lgwr process performs this action in a circular fashion so that the oldest group is overwritten by the most recent redo records.
Multiplexing the redo log:
Multiplexing provides better protection for data in the case of instance or media failure. 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.
To multiplex your redo log, you must add members to each redo log group. It is not required that redo log groups be symmetrical, but oracle recommends that your groups all have the same number of members. A database must have a minimum of two redo log groups.
Multiplexed redo log files:
The online redo log of a database instance should consist of multiplexed groups of online redo log files as shown below:
In the above figure a_log1 and b_log1 are both members of group 1, a_log2 and b_log2 are both members of group 2, and so forth. Each member in a group must be exactly the same size.
Each member of a log file group is concurrently active—that is, concurrently written to by lgwr—as indicated by the identical log sequence numbers assigned by lgwr. In figure, first lgwr writes concurrently to both a_log1 and b_log1. Then it writes concurrently to both a_log2 and b_log2, and so on. Lgwr never writes concurrently to members of different groups (for example, to a_log1 and b_log2).
To multiplex the redo log:
To multiplex the redo logs, first we must identify how many groups and members exists.
Sql> select group#, member from v$logfile order by group#, member;
The above query helps to find out the existing groups and members information.
For example, if your existing member file name is redo01.log and belongs to group1, then you might name this member as redo01a.log.
Sql> alter database add logfile member ‘/u02/app/oracle/oradata/redo01a.log’ to group1;
You can create ‘/u02/app/oracle/oradata/redo01a.log’ file in the same directory, but it is recommended that you store members on separate disk drives. That way, if there is a drive failure, you still have access to one member.
Switching a log file:
A log switch occurs when LGWR stops writing to one redo log group and starts writing to another. By default, a log switch occurs automatically when the current redo log file group fills.
The below query is helpful to know the status of the groups.
Sql> select * from v$log;
When a log switch occurs, the log writer (lgwr) process stops writing to the current redo log group and starts writing to the next available redo log group. You can force a log switch to make the current redo group inactive and available for redo log maintenance operations. For example, you might want to drop the current redo group, but are not able to do so until the group is inactive. You may also want to force a log switch if the current redo group needs to be archived at a specific time before the members of the group are completely filled. This option is useful in configurations with large redo log files that take a long time to fill.
To switch a log file:
Sql> alter system switch logfile;
The status of the current group changes to active, and the status of the next group in the list changes from inactive to current.
Moving online redo log files:
Online redo log files may be moved while the database is shutdown. Once renamed (or moved), the DBA should use the ALTER DATABASE command to update the data dictionary.
Although it is possible to indirectly move online redo log files by dropping entire redo log groups and re-adding the groups in a different location, this solution will not work if there are only two redo log groups because a database will not open with only one redo log file group. Temporarily adding a third group and dropping the first or second group is an option if the database must be kept open; alternatively, the method shown here will move the redo log file(s) while the database is shutdown.
In the following example, we have three redo log file groups with two members each. One member of each group is on the same volume as the oracle software and should be moved to a different volume to eliminate any connection between log file filling and accessing oracle software components. The method you will use here with alter database command.
Example:
Sql> select group#, member from v$logfile order by group#, member;
Group# member
——– ———–
1 /u01/app/oracle/oradata/redo01.log
1 /u05/oradata/redo01.log
2 /u01/app/oracle/oradata/redo02.log
2 /u05/oradata/redo02.log
3 /u01/app/oracle/oradata/redo03.log
3 /u05/oradata/redo03.log
6 rows selected
Sql> shutdown immediate;
Database closed.
Database dismounted.
Oracle instance shut down.
Sql> ! Mv /u01/app/oracle/oradata/redo0[1-3]/log /u04/oradata Sql>startup mount Sql>alter database rename file ‘/u01/app/oracle/oradata/redo01.log’ to ‘/u04/oradata/redo01.log’;
Database altered.
Sql> alter database rename file ‘/u01/app/oracle/oradata/redo02.log’ to ‘/u04/oradata/redo02.log’;
Database altered.
Sql> alter database rename file ‘/u01/app/oracle/oradata/redo03.log’ to ‘/u04/oradata/redo03.log’;
Database altered.
Sql> alter database open;
Database altered.
Now, we can check out the location of the log files from following query.
Sql> select group#, member from v$logfile order by group#, member;
Group# member
——– ———–
1 /u04/oradata/redo01.log
1 /u05/oradata/redo01.log
2 /u04/oradata/redo02.log
2 /u05/oradata/redo02.log
3 /u04/oradata/redo03.log
3 /u05/oradata/redo03.log
6 rows selected
The i/o for the redo log files no longer contends with the oracle software; in addition, the redo log files are multiplexed between two different mount points, /u04 and /u05.
Adding the redo log groups:
Sql>alter database add logfile group 4 ‘/u01/app/oracle/oradata/redo03.log’ size 10m;
Dropping the existing redo log groups:
Sql>alter database drop logfile group 4; /* (to drop a entire group) */ Sql>alter database drop logfile member ‘/u01/app/oracle/oradata/redo03.log’; /* (to drop a particular member) */
Note:
To drop online redo log group, consider the following points:
- An instance requires at least two groups of online redo log files, regardless of the number of members in the groups.
- You can drop an online redo log group only if it is not the active group. If you need to drop the active group, first force a log switch to occur.
Sql>alter system switch logfile;
To drop a member consider the following points:
- It is OK to drop online redo log files, so that, a mirrored online redo log becomes temporarily unsymmetrical for e.g., if you are using the duplexed groups of online 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 there by eliminate the single point of failure of online redo log.
- You can drop an online redo log group only if it is not the active member. If you need to drop the active member, first force a log switch to occur.
Verifying blocks in redo log files:
You can configure the database to use checksums to verify blocks in the redo log files. If you set the initialization parameter db_block_checksum to typical (the default), the database computes a checksum for each database block when it is written to disk, including each redo log block as it is being written to the current log. The checksum is stored in the header of the block.
Oracle database uses the checksum to detect corruption in a redo log block. The database verifies the redo log block when the block is read from an archived log during recovery and when it writes the block to an archive log file. An error is raised and written to the alert log if corruption is detected.
If corruption is detected in a redo log block while trying to archive it, the system attempts to read the block from another member in the group. If the block is corrupted in all members of the redo log group, then archiving cannot proceed.
The value of the db_block_checksum parameter can be changed dynamically using the alter system statement.
Clearing a redo log file:
Normally we need to clear redo log if its corrupted and oracle is not able to reuse it. It may already be archived or may not be. In both cases, we need to clear it to allow oracle instance to re-use it.
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 clearlogfile statement can be used to reinitialize the file without shutting down the database.
The following statement clears the log files in redo log group number 3:
Sql>alter database clear logfile group 3;
This statement overcomes two situations where a dropping redo log is not possible:
- If there are only two log groups.
- The corrupt redo log file belongs to the current group.
If the corrupt redo log file has not been archived, use the unarchived keyword in the statement.
Sql>alter database clear unarchived logfile group 3;
This statement clears the corrupted redo logs and avoids archiving them. The cleared redo logs are available for use even though they were not archived.
If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. The database writes a message in the alert log describing the backups from which you cannot recover.
If you want to clear an unarchived redo log that is needed to bring an offline tablespace to online, use the unrecoverable datafile clause in the alter database clear logfile statement.
If you clear a redo log needed to bring an offline tablespace online, you will not be able to bring the tablespace online again. You will have to drop the tablespace or perform an incomplete recovery. Note that tablespaces taken offline normal do not require recovery.
Data dictionary views:
Central set of read-only reference tables and views of each Oracle database, known collectively as the data dictionary.
The following views provide information on redo logs.
| View | Description |
| V$log | Displays the redo log file information from the control file |
| V$logfile | Identifies redo log groups and members and member status |
| V$log_history | Contains log history information |
The following query returns the control file information about the redo log for a database.
Select * from v$log;
Group# thread# seq bytes members arc status first_change# first_tim
——- —— —— —– ———- —– —— ————-
1 1 10605 1048576 1 yes active 11515628 16-apr-00
2 1 10606 1048576 1 no current 11517595 16-apr-00
3 1 10603 1048576 1 yes inactive 11511666 16-apr-00
4 1 10604 1048576 1 yes inactive 11513647 16-apr-00
To see the names of all of the member of a group, use a query similar to the following:
Select * from v$logfile;
Group# status member
———- ————————–
1 /u01/app/oracle/oradata/redo01.log
2 /u02/oradata/redo02.log
3 /u03/oradata/redo03.log
4 /u04/oradata/redo04.log
If status is blank for a member, then the file is in use.