Database Reference
In-Depth Information
If adding more redo log groups doesn't resolve the issue, you should carefully consider lowering the value
of FAST_START_MTTR_TARGET . When you lower this value, you can potentially see more I/O because the database
writer process is more actively writing modified blocks to data files. Ideally, it would be nice to verify the impact of
modifying FAST_START_MTTR_TARGET in a test environment before making the change in production. You can modify
this parameter while your instance is up; this means you can quickly modify it back to its original setting if there are
unforeseen side effects.
Finally, consider increasing the value of the DB_WRITER_PROCESSES parameter. Carefully analyze the impact of
modifying this parameter in a test environment before you apply it to production. This value requires that you stop
and start your database; therefore, if there are adverse effects, downtime is required to change this value back to the
original setting.
Adding Online Redo Log Groups
If you determine that you need to add an online redo log group, use the ADD LOGFILE GROUP statement. In this
example the database already contains two online redo log groups that are sized at 50M each. An additional log group
is added that has two members and is sized at 50MB:
alter database add logfile group 3
('/u01/oraredo/O12C/redo03a.rdo',
'/u02/oraredo/O12C/redo03b.rdo') SIZE 50M;
In this scenario I highly recommend that the log group you add be the same size and have the same number of
members as the existing online redo logs. If the newly added group doesn't have the same physical characteristics as
the existing groups, it's harder to accurately determine performance issues.
For example, if you have two log groups sized at 50MB, and you add a new log group sized at 500MB, this is very
likely to produce the Checkpoint not complete issue described in the previous section. This is because flushing all
modified blocks from the SGA that are protected by the redo in a 500MB log file can potentially take much longer than
flushing modified blocks from the SGA that are protected by a 50MB log file.
Resizing and Dropping Online Redo Log Groups
You may need to change the size of your online redo logs (see the section “Determining the Optimal Size of Online
Redo Log Groups,” earlier in this chapter). You can't directly modify the size of an existing online redo log. To resize an
online redo log, you have to first add online redo log groups that are the size you want, and then drop the online redo
logs that are the old size.
Say you want to resize the online redo logs to be 200MB each. First, you add new groups that are 200MB, using
the ADD LOGFILE GROUP statement. The following example adds log group 4, with two members sized at 200MB:
alter database add logfile group 4
('/u01/oraredo/O12C/redo04a.rdo',
'/u02/oraredo/O12C/redo04b.rdo') SIZE 200M;
Note
you can specify the size of the log file in bytes, kilobytes, megabytes, or gigabytes.
After you've added the log files with the new size, you can drop the old online redo logs. A log group must have an
INACTIVE status before you can drop it. You can check the status of the log group, as shown here:
SQL> select group#, status, archived, thread#, sequence# from v$log;
 
 
Search WWH ::




Custom Search