Online 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 describes 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. When you recover the database by using redo log files, Oracle reads the change vectors in the redo records and applies the changes to the relevant blocks.
The LGWR process writes redo information from the redo log buffer to the online redo log files under a variety of circumstances:
LGWR always writes its records to the online redo log file before DBWn writes new or modified database buffer cache records to the datafiles.
Each database has its own online redo log groups. A redo log group can have one or more redo log members (each member is a single operating system file). If you have a RAC configuration, in which multiple instances are mounted to one database, each instance has one online redo thread. That is, the LGWR process of each instance writes to the same online redo log files, and hence Oracle has to keep track of the instance from where the database changes are coming. Single instance configurations will have only one thread, and that thread number is 1. The redo log file contains both committed and uncommitted transactions. Whenever a transaction is committed, a system change number is assigned to the redo records to identify the committed transaction.
The redo log group is referenced by an integer; you can specify the group number when you create the redo log files, either when you create the database or when you create a redo log group after you create the database. You can also change the redo log configuration (add/drop/rename files) by using database commands.
The following example shows a CREATE DATABASE command.
CREATE DATABASE "MYDB01"
. . .
LOGFILE ‘/ora02/oradata/MYDB01/redo01.log’ SIZE 10M,
‘/ora03/oradata/MYDB01/redo02.log’ SIZE 10M;
Two log file groups are created here; the first file is assigned to group 1, and the second file is assigned to group 2. You can have more files in each group; this practice is known as the multiplexing of redo log files. You can specify any group number—the range will be between 1 and the parameter MAXLOGFILES. Oracle recommends that all redo log groups be the same size. The following is an example of creating the log files by specifying the group number:
CREATE DATABASE "MYDB01"
. . .
LOGFILE GROUP 1 ‘/ora02/oradata/MYDB01/redo01.log’ SIZE 10M, GROUP 2
‘/ora03/oradata/MYDB01/redo02.log’ SIZE 10M;
The LGWR process writes to only one redo log file group at any time. The file that is actively being written to is known as the current log file. The log files that are required for instance recovery are known as the active log files. The other log files are known as inactive. Oracle automatically recovers an instance when starting up the instance by using the online redo log files. Instance recovery can be needed if you do not shut down the database cleanly or if your database server crashes.
The log files are written in a circular fashion. A log switch occurs when Oracle finishes writing to one log group and starts writing to the next log group. A log switch always occurs when the current redo log group is completely full and log writing must continue. You can force a log switch by using the ALTER SYSTEM command. A manual log switch can be necessary when performing maintenance on the redo log files by using the ALTER SYSTEM SWITCH LOGFILE command.
Whenever a log switch occurs, Oracle assigns a log sequence number to the new redo log group before writing to it. If there are lots of transactions or changes to the database, the log switches can occur too frequently. Size the redo log files appropriately to avoid frequent log switches. Oracle writes to the alert log file whenever a log switch occurs.
Redo log files are written sequentially on the disk, so the I/O will be fast if there is no other activity on the disk. (The disk head is always properly positioned.) Keep the redo log files on a separate disk for better performance. If you have to store a datafile on the same disk as the redo log file, do not put the SYSTEM, UNDOTBS, SYSAUX, or any very active data or index tablespace file on this disk. A commit cannot complete until a transaction’s information has been written to the redo logs, so maximizing the throughput of the redo log files is a top priority.
LGWR log file switch Database checkpoints are closely tied to redo log file switches. We introduced checkpoints earlier in the chapter in the section "Understanding Checkpoints." A checkpoint is an event that flushes the modified data from the buffer cache to the disk and updates the control file and datafiles. The CKPT process updates the headers of datafiles and control files; the actual blocks are written to the file by the DBWn process. A checkpoint is initiated when the redo log file is filled and a log switch occurs, when the instance is shut down with NORMAL, TRANSACTIONAL, or IMMEDIATE, when a tablespace status is changed to read-only or put into BACKUP mode, or when other values specified by certain parameters (discussed later in this section) are reached You can force a checkpoint if needed, as shown here:
ALTER SYSTEM CHECKPOINT;
Forcing a checkpoint ensures that all changes to the database buffers are written to the datafiles on disk.
Another way to force a checkpoint is by forcing a log file switch:
ALTER SYSTEM SWITCH LOGFILE;
The size of the redo log affects the checkpoint performance. If the size of the redo log is smaller compared with the number of transactions, a log switch occurs often, and so does the checkpoint. The DBWn process writes the dirty buffer blocks whenever a checkpoint occurs. This situation might reduce the time required for instance recovery, but it might also affect the runtime performance. You can adjust checkpoints primarily by using the initialization parameter FAST_START_ MTTR_TARGET. This parameter replaces the deprecated parameters FAST_START_IO_TARGET and LOG_CHECKPOINT_TIMEOUT in previous versions of the Oracle database. It is used to ensure that recovery time at instance startup (if required) will not exceed a certain number of seconds.
Redo Log Troubleshooting In the case of redo log groups, it’s best to be generous with the number of groups and the number of members for each group. After estimating the number of groups that would be appropriate for your installation, add one more. I can remember many database installations in which I was trying to be overly cautious about disk space usage, not putting things into perspective and realizing that the slight additional work involved in maintaining either additional or larger redo logs is small in relation to the time needed to fix a problem when the number of users and concurrent active transactions increase.
The space needed for additional log file groups is minimal and is well worth the effort up front to avoid the undesirable situation in which writes to the redo log file are waiting on the completion of writes to the database files or the archived log file destination.
Multiplexing Redo Log Files You can keep multiple copies of the online redo log file to safeguard against damage to these files. When multiplexing online redo log files, LGWR concurrently writes the same redo log information to multiple identical online redo log files, thereby eliminating a single point of redo log failure. All copies of the redo file are the same size and are known as a group, which is identified by an integer. Each redo log file in the group is known as a member. You must have at least two redo log groups for normal database operation.
When multiplexing redo log files, keeping the members of a group on different disks is preferable so that one disk failure will not affect the continuing operation of the database. If LGWR can write to at least one member of the group, database operation proceeds as normal; an entry is written to the alert log file. If all members of the redo log file group are not available for writing, Oracle hangs, crashes, or shuts down. An instance recovery or media recovery can be needed to bring up the database, and you can lose committed transactions.
You can create multiple copies of the online redo log files when you create the database. For example, the following statement creates two redo log file groups with two members in each:
CREATE DATABASE "MYDB01" . . . LOGFILE GROUP 1 (‘/ora02/oradata/MYDB01/redo0101.log’, ‘/ora03/oradata/MYDB01/redo0102.log’) SIZE 10M, GROUP 2 (‘/ora02/oradata/MYDB01/redo0201.log’, ‘/ora03/oradata/MYDB01/redo0202.log’) SIZE 10M; The maximum number of log file groups is specified in the clause MAXLOGFILES, and the maximum number of members is specified in the clause MAXLOGMEMBERS. You can separate the filenames (members) by using a space or a comma. In the following sections, we will show you how to create a new redo log group, add a new member to an existing group, rename a member, and drop a member from an existing group. In addition, we’ll show you how to drop a group and clear all members of a group in certain circumstances.
You can create and add more redo log groups to the database by using the ALTER DATABASE command. The following statement creates a new log file group with two members:
ALTER DATABASE ADD LOGFILE
GROUP 3 (‘/ora02/oradata/MYDB01/redo0301.log’,
‘/ora03/oradata/MYDB01/redo0302.log’) SIZE 10M;
If you omit the GROUP clause, Oracle assigns the next available number. For example, the following statement also creates a multiplexed group:
ALTER DATABASE ADD LOGFILE (‘/ora02/oradata/MYDB01/redo0301.log’,
‘/ora03/oradata/MYDB01/redo0302.log’) SIZE 10M;
To create a new group without multiplexing, use the following statement:
ALTER DATABASE ADD LOGFILE
You can add more than one redo log group by using the ALTER DATABASE command— just use a comma to separate the groups.
If the redo log files you create already exist, use the REUSE option, and don’t specify the size. The new redo log size will be the same as that of the existing file. Adding a new redo log group is straightforward using the EM Database Control interface. To do so, click the Administration tab on the database home page, and then click the Redo Log Groups link. You can view and add another redo log group.
If you forgot to multiplex the redo log files when creating the database (multiplexing redo log files is the default when you use DBCA) or if you need to add more redo log members, you can do so by using the ALTER DATABASE command. When adding new members, you do not specify the file size, because all group members will have the same size.
If you know the group number, use the following statement to add a member to group 2:
ALTER DATABASE ADD LOGFILE MEMBER
‘/ora04/oradata/MYDB01/redo0203.log’ TO GROUP 2;
You can also add group members by specifying the names of other members in the group, instead of specifying the group number. Specify all the existing group members with this syntax:
ALTER DATABASE ADD LOGFILE MEMBER
‘/ora04/oradata/MYDB01/redo0203.log’ TO (‘/ora02/oradata/MYDB01/redo0201.log’,
You can add a new member to a group in the EM Database Control by clicking the Edit button and then clicking Add.
If you want to move the log file member from one disk to another or just want a more meaningful name, you can rename a redo log member. Before renaming the online redo log members, the new (target) online redo files should exist. The SQL commands in Oracle change only the internal pointer in the control file to a new log file; they do not change or rename the operating system file. You must use an operating system command to rename or move the file. Follow these steps to rename a log member:
Another way to achieve the same result is to add a new member to the group and then drop the old member from the group.
You can drop a redo log group and its members by using the ALTER DATABASE command. Remember that you should have at least two redo log groups for the database to function normally. The group that is to be dropped should not be the active group or the current group— that is, you can drop only an inactive log file group. If the log file to be dropped is not inactive, use the ALTER SYSTEM SWITCH LOGFILE command.
To drop the log file group 3, use the following SQL statement:
ALTER DATABASE DROP LOGFILE GROUP 3;
When an online redo log group is dropped from the database, the operating system files are not deleted from disk. The control files of the associated database are updated to drop the members of the group from the database structure. After dropping an online redo log group, make sure that the drop is completed successfully, and then use the appropriate operating system command to delete the dropped online redo log files.
You can delete an entire redo log group in the EM Database Control by clicking the Edit button and then clicking the Delete button.
In much the same way that you drop a redo log group, you can drop only the members of an inactive redo log group. Also, if there are only two groups, the log member to be dropped should not be the last member of a group. Each redo log group can have a different number of members, though this is not advised. For example, say you have three log groups, each with two members. If you drop a log member from group 2, and a failure occurs to the sole member of group 2, the instance will hang, crash, and potentially cause loss of committed transactions when attempts are made to write to the missing redo log group, as we discussed earlier in this chapter. Even if you drop a member for maintenance reasons, ensure that all redo log groups have the same number of members.
To drop a redo log member, use the DROP LOGFILE MEMBER clause of the ALTER DATABASE command:
ALTER DATABASE DROP LOGFILE MEMBER
The operating system file is not removed from the disk; only the control file is updated. Use an operating system command to delete the redo log file member from disk.
If a database is running in ARCHIVELOG mode, redo log members cannot be deleted unless the redo log group has been archived.
You can drop a member of a redo log group in the EM Database Control by clicking the Edit button, selecting the member to be dropped, and then clicking the Remove button.
Under certain circumstances, a redo log group member (or all members of a log group) can become corrupted. To solve this problem, you can drop and re-add the log file group or group member. It is much easier, however, to use the ALTER DATABASE CLEAR LOGFILE command. The following example clears the contents of redo log group 3 in the database:
ALTER DATABASE CLEAR LOGFILE GROUP 3;
Another distinct advantage of this command is that you can clear a log group even if the database has only two log groups and only one member in each group. You can also clear a log group member even if it has not been archived by using the UNARCHIVED keyword. In this case, it is advisable to do a full database backup at the earliest convenience, because the unarchived redo log file is no longer usable for database recovery.
If you only use online redo log files, your database is protected against instance failure but not media failure. Although saving the redo log files before they are overwritten takes additional disk space and management, the increased recoverability of the database outweighs the slight additional overhead and maintenance costs. In this section, we will present an overview of how archived redo log files work, how to set the location for saving the archived redo log files, and how to enable archiving in the database.
An archived redo log file is a copy of a redo log file before it is overwritten by new redo information. Because the online redo log files are reused in a circular fashion, you have no way of bringing a backup of a datafile up to the latest committed transaction unless you configure the database in ARCHIVELOG mode.
The process of copying is called archiving. ARCn does this archiving. By archiving the redo log files, you can use them later to recover a database, update a standby database, or use the LogMiner utility to audit the database activities.
When an online redo log file is full, and LGWR starts writing to the next redo log file, ARCn copies the completed redo log file to the archive destination. It is possible to specify more than one archive destination. The LGWR process waits for the ARCn process to complete the copy operation before overwriting any online redo log file. As with LGWR, the failure of one of the ARCn backup processes will cause instance failure, but no committed transactions will be lost because the "Commit complete" message is not returned to the user or calling program until LGWR successfully records the transaction in the online redo log file group.
Archive Logging Space Issues After you configure the database for ARCHIVELOG mode, your job is only half complete. You need to continually make sure that there is enough room for the archived log files, otherwise the database will hang. At least once in every DBA’s career, he or she will get a phone call from some users saying that the database has "crashed," while other users are still using the database. It’s not until you check the alert log that you discover the archiving process cannot find disk space for a newly filled log file in the archiving destinations.
There should be enough space available for online archived redo log files to recover and roll forward from the last full backup of each datafile that is also online; the remaining archived logs and any previous datafile backups can be moved to another disk or to tape.
Remembering your zero transaction loss strategy (which should be every DBA’s strategy), make sure that you do not misplace or delete an archived log file before it is backed up to tape, otherwise you will not be able to perform a complete recovery due to a media failure.
If you use RMAN and the Flash Recovery area for all of your backup files, then you can further automate this process by directing RMAN to maintain enough backups to satisfy a recovery window policy (number of days) or a redundancy policy (multiple copies of each backup). Once an archived log or other backup file is no longer needed for the policy, the files are automatically deleted from the Flash Recovery area. When the archiver process is copying the redo log files to another destination, the database is said to be in ARCHIVELOG mode. If archiving is not enabled, the database is said to be in NOARCHIVELOG mode. In production systems, you cannot afford to lose data and should therefore run the database in ARCHIVELOG mode so that in the event of a failure, you can recover the database to the time of failure or to a point in time. You can achieve this ability to recover by restoring the database backup and applying the database changes by using the archived log files.
You specify the archive destination in the initialization parameter file. To change the archive destination parameters during normal database operation, you use the ALTER SYSTEM command. Here are some of the parameters associated with archive log destinations and the archiver process:
LOG_ARCHIVE_DEST_n : Using this parameter, you can specify at most 10 archiving destinations. These locations can be on the local machine or on a remote machine where the standby database is located. The syntax for specifying this parameter in the initialization file is as follows:
LOG_ARCHIVE_DEST_n = "null_string" | ((SERVICE = tnsnames_name |
LOCATION = ‘directory_name’)
[MANDATORY | OPTIONAL]
[REOPEN [= integer]])
LOG_ARCHIVE_DEST_1 = ((LOCATION=’/archive/MYDB01’) MANDATORY REOPEN = 60) specifies a location for the archive log files on the local machine at /archive/MYDB01. The MANDATORY clause specifies that writing to this location must succeed. The REOPEN clause specifies when the next attempt to write to this location should be made, when the first attempt did not succeed. The default value is 300 seconds.
Here is another example, which applies the archive logs to a standby database on a remote computer.
LOG_ARCHIVE_DEST_2 = (SERVICE=STDBY01) OPTIONAL REOPEN;
Here STDBY01 is the Oracle Net connect string used to connect to the remote database. Because writing is optional, the database activity continues even if ARCn could not write the archive log file. It tries the writing operation again because the REOPEN clause is specified.
You can also use the EM Database Control web pages to configure the log filenaming and destinations by clicking Configure Recovery Settings in the Maintenance tab. The first destination is on the file system at /u09/oradata/arch01.
Destination number 10 is the Flash Recovery area using the string USE_DB_RECOVERY_FILE_DEST.
LOG_ARCHIVE_MIN_SUCCEED_DEST : This parameter specifies the number of destinations that the ARCn process should successfully write at a minimum to proceed with overwriting the online redo log files. The default value of this parameter is 1. This parameter cannot exceed the total number of enabled destinations. If this parameter value is less than the number of MANDATORY destinations, the parameter is ignored.
LOG_ARCHIVE_FORMAT : This parameter specifies the format in which to write the filename of the archived redo log files. To ensure that the log files are not overwritten, you use predefined substitution variables to construct the name of each archived redo log file. You can provide a text string and any of the predefined substitution variables.
The variables are as follows:
%s Log sequence number
%t Thread number
%r Resetlogs ID: ensures uniqueness even after using advanced recovery techniques that resets the log sequence numbers
%d Database ID
The format you provide must include at least %s, %t, and %r. If you use the same archived redo log location for multiple databases, you must also use %d.
Specifying these parameters does not start writing the archive log files. To enable archiving of the redo log files, place the database in ARCHIVELOG mode. You can specify the ARCHIVELOG clause while creating the database. However, you might prefer to create the database first and then enable ARCHIVELOG mode. To enable ARCHIVELOG mode, follow these steps:
The dynamic performance view V$DATABASE tells you whether you are in ARCHIVELOG mode, as can be seen in this query:
SQL> select dbid, name, created, log_mode from v$database;
DBID NAME CREATED LOG_MODE
--------- -------- -------- ------------
1387044942 ORD 03-MAR-04 ARCHIVELOG
1 row selected.