Топ-100
 
CerebroSQL

REDO Log Management in Oracle Database

Information

Redo logs : Redo logs are special files in the Oracle database , thanks to which it is guaranteed that all changes made by the session will be recorded in the logs in case a failure occurs between the time the changes are made and the time they are written to the data files. Thus, redo logs are at the heart of the recovery process .

Oracle organizes its redo log files into log groups, and you need to have at least two different redo log groups and at least one member each. You will need at least two groups because when one redo log is archived, the log writer process must be able to continue writing to the active redo log.

Although an Oracle database will work reasonably well with only one member in each redo log group, Oracle strongly recommends multiplexing the online redo logs. Multiplexing simply means maintaining more than one member in each of the redo log groups. All members of such a group are identical - multiplexing is designed to protect against the loss of one of the copies of the log file. When multiplexing online redo logs, the log writer process writes concurrently to all of the group member files.

The size of the REDO file is selected based on the intensity of data writing to the database, the number of groups and the speed of the disk subsystem.

In most cases, the log size is set in the range from 1 to 4 GB, and the number of groups from 3 to 5.

Log management

CerebroSQL provides a full set of tools for quickly assessing the correctness of the selected parameters of the logs, their number, size, and also allows you to manage them through a simple interface.

Estimating the size of files in a group:

The monitoring core constantly collects information about the average time of switching records to log groups, on the main window the Switching logs metric. A low value of the indicator, less than 5 minutes, indicates potential problems due to the cost of switching the recording process from one group to another. for more details about metrics, see the section "Metrics (main window)"

Viewing information about objects operations with which make the maximum contribution to data generation redo logs is performed through the "General REDO size", there is also available information on the general generation of logs by day.

Viewing information about REDO logs
Redo manager.jpg

 

To view information about a group, select it in the "REDO group list"

Block "Statistics"

general information

  • Number - the number of the currently active group

  • Size - the size of the files in the group

  • Time start - time of switching to the specified group

  • Switching logs (avg) - average activity time of one group

  • General REDO size - REDO data generation for the current day starting from 00:00

Block "Information on the selected group"

Selected group information

  • Group number - group number

  • Status - Group status

  • File size - the size of the REDO files in the group

  • Count member - the number of files in the group

  • Start time - time when the group became active for the last time

Group parameter block

Group parameters

  • Group number - group number

  • Group size - file size in the group

  • Check "OMF (storage files)" - managed automatically. The data is retrieved from the v$parameter system view. The values ​​of the db_create_online_log_dest_N parameters are checked, if at least one of the parameters has a value, the check is issued

  • "Standby group" check - set if the group of logs is a standby group

  • File path - paths to a file in a group

V$INSTANCE_RECOVERY Tab

Information from the system view V$INSTANCE_RECOVERY

Creating a new group

 

  • In the "REDO group list" select the line "New"

Group parameter block

  • Enter the group number in the "Group number" field (the group number should not overlap with existing ones)

  • In the " Group size " field, enter the size of the files in the group

  • If you need to create a standby group, check "Standby group"

  • If OMF is not used, in the "File path" block enter paths to files, maximum 5 if it is necessary to multiplex files in a group

  • Click the "Save" button

To view the command before execution, switch to the "SQL" tab

REDO Manager - add log SQL