Rollback description for Oracle instance recovery

I. When do I need instance recovery?

In shutdown normal or shutdown immediate, the so-called clean shutdown will automatically trigger the checkpoint and write the SCN record back. When a checkpoint occurs, the SCN is written to four places:

Three places are in the control file:

(1) SYSTEM CHECKPOINT SCN

(2) Datafile checkpoint SCN

(3) Stop SCN

One is in the datafile header:

Start SCN

1.1 Clean shutdown

When clean shutdown is enabled, the checkpoint is executed, and the stop scn of datafile is the same as the start scn in the control file. When the database is open, Oracle checks whether the start scn in the datafile header is the same as the scn in the datafile stored in the control file. If the two are the same, it checks whether the start scn and stop scn are the same. If the two are still the same, the database is enabled normally. Otherwise, a recovery is required.

When the database is enabled, the stop scn stored in the control file will be restored to the NULL value, indicating that datafile is open in normal mode.

1.2 abnormal shutdown

If SHUTDOWN is abnormal (shutdown abort), after you mount the database, you will find that the stop scn is not equal to the scn at other locations, but is equal to NULL, which means that Oracle does not perform a checkpoint during shutdown, crash recovery (instance recovery) is required for the next boot ).

Note:

(1) when starting the database, if the stop scn = NULL is found, crash recovery is required;

(2) when starting the database, if the start scn with the datafile header is not equal to the datafile scn stored in CONTROLFILE, Media recovery is required.

1.3 crash recovery sequence Problems

Roll forward is required first (starting from the current start scn in the redo log file, redo the transaction that has been submitted after ). Then, the rollback transaction (dead transaction) is performed from the roll back segment. Check that the SCN in controlfile is equal to the scn of the datafile header.

Ii. Crash Recovery process

When the database suddenly crashes and the dirty data blocks in the buffer cache have not been refreshed to the data file, the running transactions are suddenly interrupted when the instance crashes, the transaction is in the intermediate state, that is, neither commit nor rollback. At this time, the content in the data file cannot reflect the status of the instance crash. In this way, the closed database is inconsistent.

The next time you start an instance, Oracle will be automatically restored by the SMON process. When the instance starts, the SMON process checks the end scn number of each online and readable data file recorded in the control file.

When the database is running normally, the end scn number is always NULL. When the database is shut down normally, a full checkpoint is executed and the checkpoint SCN number is updated.

However, if Oracle does not have time to update this field when it crashes, the field is still NULL. When the SMON process finds that this field is null, it is known that the instance was not properly closed last time, so the SMON process starts to recover the instance.

When the SMON process recovers an instance, it obtains the checkpoint position from the control file. Therefore, the SMON process goes to the online log file, finds the position of the checkpoint, and applies all redo entries from the position of the checkpoint, in this way, the status at the time when the instance crashes is restored in the buffer cache. This process is called roll-forward. After the roll-forward is completed, the buffer cache contains both dirty data blocks that have been committed and not written to the data file at the time of crash, and the transaction is suddenly terminated, as a result, no dirty data blocks are committed and not rolled back.

Once the rollback is completed, the SMON process immediately opens the database. However, the database also contains dirty blocks in the intermediate state that are neither committed nor rolled back. Such dirty blocks cannot exist in the database, because they are not submitted, they must be rolled back. After the database is opened, the SMON process rolls back in the background.

Sometimes, after the database is opened, the SMON process does not have time to roll back the data blocks in the intermediate state, and a user process sends a request to read the data blocks. At this time, before the server process returns these blocks to the user, the server process is responsible for rollback. After the rollback is completed, the data block content is returned to the user.

Iii. Why is the database instance recovered from previous rollback and rollback?

The rollback segment actually exists in the form of a rollback tablespace. Since it is a tablespace, there must be a corresponding data file and an image block will exist in the buffer cache, this is the same as the data files in other tablespaces.

When a DML operation occurs, both the REDO Entry for the DML operation and the UNDO (used to roll back the DML operation and record it in the UNDO tablespace) must be generated ), however, since the UNDO information is stored in the rollback tablespace, the UNDO information corresponding to the DML operation (the undo block generated in the buffer cache) the corresponding REDO information (undo block's REDO Entry) is generated and written into the Log Buffer.

This is because the UNDO tablespace blocks in the Buffer Cache may also be lost due to database faults. To ensure smooth rollback at the next startup, first, you must use the REDO log to restore the UNDO segment (in fact, the dirty data block in the Buffer Cache is first restored, and then the Checkpoint is written into the UNDO segment ), after the database is opened, UNDO information is used for rollback to achieve consistency.

After the undo block's REDO Entry is generated, the REDO Entry corresponding to the DML statement is used. Then, the Block in the Buffer Cache is modified, and the Block becomes a dirty data BLOCK at the same time.

In fact, the REDO function is to record all database changes, including the UNDO tablespace.