What Is the Redo Logfile ?
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.
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.
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. 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 undo segment data block, and the transaction table of the undo segments.
Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.
Redo records are buffered in a circular fashion in the redo log buffer of the SGA and are written to one of the redo log files by the Log Writer (LGWR) database background process. 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. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.
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.
How Oracle Database Writes to the Redo Log
The redo log of a database consists of two or more redo log files. The database requires a minimum of two files to guarantee that one is always available for writing while the other is being archived (if the database is in ARCHIVELOG mode).
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. Figure 12-1 illustrates the circular
writing of the redo log file. The numbers next to each line indicate the sequence in which LGWR writes to each redo log file.
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.
Figure A Reuse of Redo Log Files by LGWR
Active (Current) and Inactive Redo Log Files
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.
Redo log files that are required for instance recovery are called active redo log files. Redo log files that are no longer required for instance recovery are called inactive redo log files.
If you have enabled archiving (the database is in ARCHIVELOG mode), then the database cannot reuse or overwrite an active online log file until one of the archiver background processes (ARCn) has archived its contents. If archiving is disabled (the database is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting the next log file in the sequence when it becomes inactive.
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.
Creating Redo Log Groups and Members
Plan the redo log of a database and create all required groups and members of redo log files during database creation. However, there are situations where you might want to create additional groups or members. For example, adding groups to a redo log can correct redo log group availability problems.
To create new redo log groups and members, you must have the ALTER DATABASE system privilege. A database can have up to MAXLOGFILES groups.
Creating Redo Log Groups
To create a new group of redo log files, use the SQL statement ALTER DATABASE with the ADD LOGFILE clause.
The following statement adds a new group of redo logs to the database:
ALTER DATABASE ADD LOGFILE ('/u01/prd/log1c.rdo', '/u01/prd/log2c.rdo') SIZE 100M;
Provide full path names of new log members to specify their location. Otherwise, the files are created in either the default or current directory of the database server, depending upon your operating system.
You can also specify the number that identifies the group using the GROUP clause:
ALTER DATABASE ADD LOGFILE GROUP 10 ('/u01/prd/log1c.rdo', '/u01/prd/log2c.rdo') SIZE 100M BLOCKSIZE 512;
Using group numbers can make administering redo log groups easier. However, the group number must be between 1 and MAXLOGFILES. Do not skip redo log file group numbers (that is, do not number your groups 10, 20, 30, and so on), or you will consume unnecessary space in the control files of the database.
In the preceding statement, the BLOCKSIZE clause is optional.
Creating Redo Log Members
In some cases, it might not be necessary to create a complete group of redo log files. A group could already exist, but not be complete because one or more members of the group were dropped (for example, because of a disk failure). In this case, you can add new members to an existing group.
To create new redo log members for an existing group, use the SQL statement ALTER DATABASE with the ADD LOGFILE MEMBER clause. The following statement adds a new redo log member to redo log group number 2:
ALTER DATABASE ADD LOGFILE MEMBER ‘/u01/prd/log2b.rdo’ TO GROUP 2;
Notice that filenames must be specified, but sizes need not be. The size of the new members is determined from the size of the existing members of the group.
When using the ALTER DATABASE statement, you can alternatively identify the target group by specifying all of the other members of the group in the TO clause, as shown in the following example:
ALTER DATABASE ADD LOGFILE MEMBER ‘/u01/prd/log2c.rdo’
TO (‘/u01/prd/log2a.rdo’, ‘/u01/prd/log2b.rdo’);
Relocating and Renaming Redo Log Members
You can use operating system commands to relocate redo logs, then use the ALTER DATABASE statement to make their new names (locations) known to the database. This procedure is necessary, for example, if the disk currently used for some redo log files is going to be removed, or if datafiles and several redo log files are stored on the same disk and should be separated to reduce contention.
To rename redo log members, you must have the ALTER DATABASE system privilege. Additionally, you might also need operating system privileges to copy files to the desired location and privileges to open and back up the database.
Before relocating your redo logs, or making any other structural changes to the database, completely back up the database in case you experience problems while performing the operation. As a precaution, after renaming or relocating a set of redo log files, immediately back up the database control file.
Use the following steps for relocating redo logs. The example used to illustrate these steps assumes:
- The log files are located on two disks: diska and diskb.
- The redo log is duplexed: one group consists of the members /diska/logs/log1a.rdo and /diskb/logs/log1b.rdo, and the second group consists of the members /diska/logs/log2a.rdo and /diskb/logs/log2b.rdo.
- The redo log files located on diska must be relocated to diskc. The new filenames will reflect the new location: /diskc/logs/log1c.rdo and /diskc/logs/log2c.rdo.
Steps for Renaming Redo Log Members
- Shut down the database.
- Copy the redo log files to the new location.Operating system files, such as redo log members, must be copied using the appropriate operating system commands. See your operating system specific documentation for more information about copying files.
Note:You can execute an operating system command to copy a file (or perform other operating system commands) without exiting SQL*Plus by using the HOST command. Some operating systems allow you to use a character in place of the word HOST. For example, you can use an exclamation point (!) in UNIX.The following example uses operating system commands (UNIX) to move the redo log members to a new location:mv /diska/logs/log1a.rdo /diskc/logs/log1c.rdo
mv /diska/logs/log2a.rdo /diskc/logs/log2c.rdo
- Startup the database, mount, but do not open it.
- CONNECT / as SYSDBA
- STARTUP MOUNT
- Rename the redo log members.
Use the ALTER DATABASE statement with the RENAME FILE clause to rename the database redo log files.ALTER DATABASERENAME FILE ‘/diska/logs/log1a.rdo’, ‘/diska/logs/log2a.rdo’
TO ‘/diskc/logs/log1c.rdo’, ‘/diskc/logs/log2c.rdo’;
- Open the database for normal operation.The redo log alterations take effect when the database is opened.ALTER DATABASE OPEN;
Dropping Redo Log Groups and Members
In some cases, you may want to drop an entire group of redo log members. For example, you want to reduce the number of groups in an instance redo log. In a different case, you may want to drop one or more specific redo log members. For example, if a disk failure occurs, you may need to drop all the redo log files on the failed disk so that the database does not try to write to the inaccessible files. In other situations, particular redo log files become unnecessary. For example, a file might be stored in an inappropriate location.
Dropping Log Groups
To drop a redo log group, you must have the ALTER DATABASE system privilege. Before dropping a redo log group, consider the following restrictions and precautions:
- 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 must drop the current group, then 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
- SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
- GROUP# ARC STATUS·
- ——— — —————-·
- 1 YES ACTIVE·
- 2 NO CURRENT·
- 3 YES INACTIVE·
- 4 YES INACTIVE
Drop a redo log group with the SQL statement ALTER DATABASE with the DROP LOGFILE clause.
The following statement drops redo log group number 3:
ALTER DATABASE DROP LOGFILE GROUP 3;
When a redo log group is dropped from the database, and you are not using the Oracle Managed Files feature, the operating system files are not deleted from disk. Rather, the control files of the associated database are updated to drop the members of the group from the database structure. After dropping a redo log group, ensure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped redo log files.
When using Oracle Managed Files, the cleanup of operating systems files is done automatically for you.
Dropping Redo Log Members
To drop a redo log member, you must have the ALTER DATABASE system privilege. Consider the following restrictions and precautions before dropping individual redo log members:
- 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 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. 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
To drop specific inactive redo log members, use the ALTER DATABASE statement with the DROP LOGFILE MEMBER clause.
The following statement drops the redo log ‘/u01/prd/log3c.rdo’;
ALTER DATABASE DROP LOGFILE MEMBER ‘/u01/prd/log3c.rdo’;
When a redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure. After dropping a redo log file, ensure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped redo log file.
To drop a member of an active group, you must first force a log switch.
Forcing Log Switches
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.
You can force a log switch to make the currently active group inactive and available for redo log maintenance operations. For example, you want to drop the currently active group, but are not able to do so until the group is inactive. You may also want to force a log switch if the currently active group must 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 force a log switch, you must have the ALTER SYSTEM privilege. Use the ALTER SYSTEM statement with the SWITCH LOGFILE clause.
The following statement forces a log switch:
ALTER SYSTEM SWITCH LOGFILE;
Clearing a Redo Log File
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.
The following statement clears the log files in redo log group number 3:
ALTER DATABASE CLEAR LOGFILE GROUP 3;
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
If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.
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 clear an unarchived redo log file, you should make another backup of the database.
To clear an unarchived redo log that is needed to bring an offline tablespace 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.
How to add additional online redo logfile to the existing group
SYS>alter database add logfile member ‘/u01/prd/redolog2a.log ‘ to group 1;
SYS>select * from v$logfile;
SYS>alter database add logfile member ‘/u01/prd/redolog2b.log’ to group 2;
SYS>select * from v$logfile;
How to identify log status(current,inactive,actives,unused)
SYS>select * from v$log;
How to create new group to the existing database
SYS>alter database add logfile group 3 ‘/u01/prd/redolog3a.log’ size 10m;
all members in the group must have the same size.
SYS> alter database add logfile group 3 ‘/u01/prd/redolog3b.log’ size 10m;
SYS>select * from v$logfile;
how to identify log sequence number
SYS>archive log list;
How to change the log sequence number manually
SYS>alter system switch logfile;
How to remove a log member from existing group 1
SYS>alter database drop logfile member ‘/u01/prd/redolog1a.log’ ;
NOTE: all groups and members can be dropped except current log group and members.
How to drop a group 3 including log file members
SYS>alter system switch logfile;
SYS>alter database drop logfile group 3;
Note tha Redo Log Data Dictionary Views
The following views provide information on redo logs.
|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/PRD/REDO 04.LOG
2 /U01/PRD/REDO 03.LOG
3 /U01/PRD/REDO 02.LOG
4 /U01/PRD/REDO 01.LOG
If STATUS is blank for a member, then the file is in use.
table spaces taken offline normal do not require recovery.
Source Oracle docs