Displaying Online Redo Log Information- Managing Control Files, Online Redo Logs,and Archivelogs
Use the V$LOG and V$LOGFILE views to display information about the online redo log groups and corresponding members:
Here is some sample output:
When you are diagnosing online redo log issues, the V$LOG and V$LOGFILE views are particularly helpful. You can query these views while the database is mounted or open.
The STATUS column of the V$LOG view is especially useful when you are working with online redo log groups.
• CURRENT. This is the log group currently being written to by the log writer.
• ACTIVE: This log group is required for crash recovery and may or may not have been archived.
• CLEARING: This log group is being cleared out by an ALTER DATABASE CLEAR LOGFILE command.
• CLEARING_CURRENT: This current log group is being cleared of a closed thread.
• INACTIVE: This log group is not required for crash recovery and may or may not have been archived.
• UNUSED: This log group has never been written to; it was recently created.
The STATUS in V$LOG refers to the log group, and V$LOGFILE reports on the status of the physical online redo log file member.
Determining the Optimal Size of Redo Logs
The redo logs need to write out to the archive logs for backup and recovery purposes, so switching redo logs is important. The object is to try to size the online redo logs so that they switch anywhere from two to six times per hour, and also make sure there are not waits on the writing out the archive logs. The V$LOG_HISTORY contains a history of how frequently the online redo logs have switched. Execute this query to view the number of log switches per hour:
Note the group by clause in using an alias is a 23c new feature in sQl.
Here is part of the output:
From the previous output, you can see that a great deal of log switch activity occurred from approximately 4 a.m. to 7 a.m. This could be because of a nightly batch job or users in different time zones updating data. For this database, the size of the online redo logs should be increased. You should try to size the online redo logs to accommodate peak traction loads on the database.
As stated, a general rule of thumb is that you should size your online redo log files so that they switch approximately two to six times per hour. You do not want them switching too often because there is overhead with the log switch; however, leaving transaction information in the redo log without archiving will create issues with recovery. If a disaster causes a media failure in your current online redo log, you can lose those transactions that haven’t been archived. If a disaster causes a media failure in your current online redo log, you can lose those transactions that haven’t been archived.
Oracle initiates a checkpoint as part of a log switch. During a checkpoint, the database-writer background process writes modified (also called dirty) blocks to disk, which is resource intensive. Checkpoint messages in the alert log will also be a way of looking at how fast logs are switching or if there are waits associated with archiving.
Tip Use the ARCHIVE_LAG_TARGET initialization parameter to set a maximum amount of time (in seconds) between log switches.a typical setting for this parameter is 1,800 seconds (30 minutes).a value of 0 (default) disables this feature.this parameter is commonly used in oracle data guard environments to force log switches after the specified amount of time elapses.
You can also query the OPTIMAL_LOGFILE_SIZE column from the V$INSTANCE_ RECOVERY view to determine whether your online redo log files have been sized correctly:
The column reports the redo log file size (in megabytes) that is considered optimal, based on the initialization parameter setting of FAST_START_MTTR_TARGET. Oracle recommends that you configure all online redo logs to be at least the value of OPTIMAL_LOGFILE_SIZE. However, when sizing your online redo logs, you must take into consideration information about your environment (such as the frequency of the switches).
