Oracle redo log group status and switch resolution

Oracle redo log is used to record operation entries for database data recovery. To improve efficiency, we recommend that you set up three redo logs for oracle. This article will parse the status of the redo log group and switch between multiple states, and strive to master this knowledge point.

Change redo log size)

Oracle ONLINE redo LOG FILE)

Restoration of Oracle online redo log loss

Oracle online redo log file

Troubleshooting of version inconsistencies in Oracle redo log files

[Backup and recovery] restores damaged online redo log files.

I. Overview

Oracle redo log groups generally have four states: unused, inactive, active, and current.

Unused is generally a log group that has never been used, that is, a newly added log group.

The current status is LGWR. The process is writing the redo log buffer logs into the log group.

Active is the status after the log switch is completed. At this time, the data changes caused by transactions committed in the log group have not been completely written from the DB buffer cache to the data file, therefore, this log group cannot be overwritten and changes to inactive state after full writing. If the database is in archive mode, archive is completed in this state.

Logs in inactive status can be overwritten and logs can be written. If the archive mode is used, this phase proves that the archive has been completed.

Ii. Experiment

This experiment uses the archive mode as an example:

The log groups are inactive, inactive, and current.

After manual archiving, it is observed that the status of group 1 changes to current, group 3 changes to active, and group 2 remains unchanged. For example:

Note that the currently written log group is group 1, group 3 is archiving and necessary data is written to datafile. After the two tasks are completed, let's look at them again:

Note that group 3 has been archived and can be written for the next time. In non-archive mode, the status of group 3 can also be changed to inactive, indicating that the data has been written from the database buffer cache to DB files.

Iii. Summary

The preceding experiment demonstrates the status of the oracle log Group throughout the lifecycle. Of course, the unused status is not seen. This status is the status when the log group is newly added to the database. This operation is rare in the production environment, so this status is not seen in this experiment.

In addition, some people will think why the command alter system archive log current is used during manual log switching. Here is a description.

The final effect of the two groups of commands is the same. Alter system switch logfile allows you to switch logfile without archiving. It is fast, especially in non-archive mode and takes effect for the current instance. The alter system archive log current operation takes effect for all instances only after the archiving is completed.