Instance recovery and Oracle SCN

A simple understanding of oracle's SCN is its own time function, just like the linux system's own time, oracle also has its own set of time. When you close the database cleanly, shutdownimmediate or altersystemcheckpoint will write the SCN value to four locations, of which 3 are in controlfile and 1 is in datafi.

A simple understanding of oracle's SCN is its own time function, just like the linux system's own time, oracle also has its own set of time. When you shut down the database cleanly, shutdown immediate or use alter system checkpoint will write the SCN value to four locations, of which 3 are in controlfile and 1 is in datafi

A simple understanding of oracle's SCN is its own time function, just like the linux system's own time, oracle also has its own set of time.

When you close the database cleanly, shutdown immediate or use alter system checkpoint will write the SCN value to four locations, three of which are in controlfile and one in datafile header.

The three SCN in controlfile are: 1, system checkpoint SCN 2, datafile checkpoint SCN 3, and stop SCN.

--- System checkpoint SCN Query

SQL> select checkpoint_change # from v $ database;

--- Datafile checkpoint SCN Query

SQL> select name, checkpoint_change # from v $ datafile where name like '% user % ';

--- Stop SCN Query

SQL> select name, last_change # from v $ datafile where name like '% user % ';

The stop SCN is NUll when the database is in normal use.

The scn in the datafile header is: start SCN

--- Start SCN Query

SQL> select name, checkpoint_change # from v $ datafile_header were name like '% user % ';

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 open database is used, 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 it is the same, check whether the start scn of the datafile header is the same as the stop scn stored in the control file. If it is still the same, the database will be enabled normally; otherwise, 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.

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, instance crash recovery (instance recovery) is required for the next boot ).

Note:

(1) when starting the database, if the stop scn = NULL is found, instancecrash 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.