13.4. Using Miscellaneous Space Management Tools

Two other automated space management features fall into the advisor category: the Undo Advisor and the Redo Logfile Size Advisor. In both cases, Oracle collects statistics on a continuous basis to help you size the undo tablespace and the online redo logs to enhance both performance and availability.

In the following sections, we'll show you how to manage the size of your undo tablespace using the Undo Advisor. After we present an overview of how online redo log files are configured and managed, we'll explain how the Redo Logfile Size Advisor can help you tune the configuration of your online redo log files.

13.4.1. Controlling Undo Space

Undo tablespaces are monitored just like any other tablespace: If a specific set of space thresholds is not defined, the database default values are used; otherwise, a specific set of thresholds can be assigned.

Running out of space in an undo tablespace, however, may also trigger an ORA-01555 Snapshot too old error. Long-running queries that need a read-consistent view of one or more tables can be at odds with ongoing transactions that need undo space. Unless the undo tablespace is defined with the RETENTION GUARANTEE parameter, ongoing DML can use undo space that may be needed for long-running queries. As a result, a Snapshot too old error is returned to the user executing the query, and an alert is generated. This alert is also known as a long query warning alert.

Regardless of how often the Snapshot too old error occurs, the alert is generated at most once per 24-hour period. Increasing the size of the undo tablespace or changing the value of UNDO_RETENTION does not reset the 24-hour timer. For example, an alert is generated at 10 A.M. and you add undo space at 11 A.M. The undo tablespace is still too small, and users are still receiving Snapshot too old errors at 2 P.M. You will not receive a long query warning alert until 10 A.M. the next day, but chances are you will get a phone call before then!

To help you maintain the best balance between disk space in the undo tablespace and user query performance, EM Database Control Undo Advisor helps you determine how large of an undo tablespace is necessary given adjustments to the undo retention setting.

In Figure 13.25, the Undo Advisor screen shows the autotuned undo retention of 753 minutes and an undo tablespace size of 94MB. If you don't expect your undo usage to increase or you don't expect to need to retain undo information longer than 753 minutes, you can drop the size of the undo tablespace if it is significantly more than 94MB.

On the other hand, if you expect to need undo information for longer than 753 minutes, you can see the impact of this increase by either entering a new value for undo retention and refreshing the page or by clicking a point on the graph corresponding to the estimated undo retention. Figure 13.26 shows the results of increasing the undo retention to 103,204 minutes.

To support an undo retention setting of 103,204 minutes given the current undo usage, the undo tablespace will have to be increased in size to 8,625MB, or 8.625GB.

13.4.2. Controlling Redo Log File Space

Even though individual online redo log files do not use much space, they must still be optimized to provide the best performance for transactions while ensuring the instance recovery capabilities that online redo log files provide. After we provide an in-depth overview of how online redo log files are configured, we will show you how to optimize their performance using the Redo Logfile Size Advisor.

13.4.2.1. Online Redo Log File Configuration

A redo log file records all changes to the database, in most cases before the changes are written to the datafiles. To recover from an instance or a media failure, redo log information is required to roll datafiles forward to the last committed transaction. Ensuring that you have at least two members for each redo log file group dramatically reduces the likelihood of data loss, because the database continues to operate if one member of a redo log file is lost.

In the following sections, we will give you an architectural overview of redo log files, as well as show you how to add redo log groups, add or remove redo log group members, and clear a redo log group in case one of the redo log group's members becomes corrupted.

13.4.2.2. Redo Log File Architecture

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:

• When a user commits a transaction, even if this is the only transaction in the log buffer.

• When the redo log buffer becomes one-third full.

• When the buffer contains approximately 1MB of changed records. This total does not include deleted or inserted records.

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, or 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, which we'll discuss later in this chapter in the section "Multiplexing Redo Log Files." You can specify any group number—the range will be between 1 and the initialization 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;

13.4.2.3. Log Switch Operations

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. Figure 13.27 shows how LGWR writes to the redo log groups in a circular fashion.

Whenever a log switch occurs, Oracle allocates a sequence number to the new redo log group before writing to it. As stated earlier, this number is known as the log sequence number. If there are lots of transactions or changes to the database, the log switches can occur too frequently. Make sure to 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.

Database checkpoints are closely tied to redo log file switches. A checkpoint is an event that flushes the modified data from the buffer cache to the disk and updates the control file and data-files. 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.

• 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 to 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.

Real World Scenario: 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 to the archived log file destination.

13.4.3. 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.

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.

13.4.3.1.

13.4.3.1.1. Creating New Groups

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
     '/ora02/oradata/MYDB01/redo0301.log' REUSE;

You can add more than one redo log group by using the ALTER DATABASE command—just use a comma to separate the groups.

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, as you can see in Figure 13.28 on the Redo Log Groups screen.

13.4.3.1.2. Adding New Members

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',
 '/ora03/oradata/MYDB01/redo0202.log'),

You can add a new member to a group in EM Database Control by clicking the Edit button in Figure13.28 and then clicking Add.

13.4.3.1.3. Renaming Log Members

If you want to move the log file member from one disk to another or you 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, as discussed in the "Adding New Members" section earlier in this chapter and the "Dropping Redo Log Members" section, which you'll read shortly.

You can rename a log group member in EM Database Control by clicking the Edit button in Figure 13.28, then clicking Edit again, and then changing the filename in the File Name box.

13.4.3.1.4. Dropping Redo Log Groups

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 EM Database Control by clicking the Edit button in Figure 13.28 and then clicking the Delete button.

13.4.3.1.5. Dropping Redo Log Members

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 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
'/ora04/oradata/MYDB01/redo0203.log';

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 EM Database Control by clicking the Edit button in Figure 13.28, selecting the member to be dropped, and then clicking the Remove button.

13.4.3.1.6. Clearing Online Redo Log Files

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.

13.4.3.2. Redo Logfile Size Advisor

The Redo Logfile Size Advisor provides an automatic method for sizing redo log files. In general, redo logs should be sized large enough so that checkpoints do not occur too frequently; if the logs switch more often than every 20 minutes, performance of the database may be affected.

On the other hand, redo logs that are too big may impact disk space usage without a measurable benefit.

In addition to the amount of redo generated, the other factor that directly affects the proper sizing of the redo logs is the initialization parameter FAST_START_MTTR_TARGET. A parameter available since Oracle 9i, FAST_START_MTTR_TARGET indicates the time, in seconds, that instance recovery should take after a crash or instance failure. For the Redo Logfile Size Advisor to provide a value for the optimal log file size, this parameter must be non-zero. As one of Oracle's automated advisors, statistics for optimizing the redo log file size are collected automatically and continually.

The initialization parameters FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL can still be specified to control instance recovery, but setting either of these parameters disables FAST_START_MTTR_TARGET.

In the sample order database, the redo log files are sized as follows:

SQL> select member from v$logfile;

MEMBER
----------------------------------------
/u07/oradata/ord/redo03.log
/u07/oradata/ord/redo02.log
/u07/oradata/ord/redo01.log
/u08/oradata/ord/redo01.log
/u08/oradata/ord/redo02.log
/u08/oradata/ord/redo03.log

6 rows selected.

SQL> !ls -l /u07/oradata/ord/redo03.log
-rw-r-----    1 oracle   oinstall 10486272 Apr 20 14:01
                              /u07/oradata/ord/redo03.log

The redo log files are sized at 10MB each, the default size for redo log files when the database was created. The parameter FAST_START_MTTR_TARGET is set for 30 seconds; in other words, you don't want instance recovery to take more than 30 seconds after a crash or instance failure:

SQL> show parameter fast_start_mttr_target

NAME                        TYPE        VALUE
--------------------------- ----------- ---------------
fast_start_mttr_target      integer     30
SQL>

You have two ways to retrieve the optimal log file size calculated by the Redo Logfile Size Advisor: using a new column in the view V$INSTANCE_RECOVERY or using EM Database Control. The view V$INSTANCE_RECOVERY contains a new column, OPTIMAL_LOGFILE_SIZE, which recommends a minimum size for the redo log files:

SQL> select optimal_logfile_size from v$instance_recovery;
OPTIMAL_LOGFILE_SIZE
--------------------
                  49

1 row selected.

Given the current log file size of 10MB, you should probably increase the log file size to at least 49MB to reduce the number of log file switches.

Using EM Database Control, you can retrieve the same information via a graphical interface. In Figure 13.29, review the Redo Log Groups screen containing the number and size of each redo log file.

In the Actions drop-down list on the right, select Sizing Advice and click Go. Figure 13.30 shows the recommendation for the redo log file size, which coincidentally corresponds with the information obtained from the view V$INSTANCE_RECOVERY.