How to understand Oracle SCN

System Checkpoint SCN (V$database (checkpoint_change#))

Data file Checkpoint (V$datafile (checkpoint_change#))

Data file termination SCN (V$datafile (last_change#))

Checkpoints stored in data files

Start SCN (V$datafile_header (checkpoint_change#)

1. System Checkpoint SCN

When a checkpoint action is complete, Oracle stores the SCN of the system checkpoint in the control file.

Select checkpoint_change# from V$database

2. data file checkpoint SCN

When a checkpoint action is complete, Oracle stores the SCN of each data file separately in the control file.

Select name,checkpoint_change# from V$datafile

3. Start SCN

Oracle stores the SCN of this checkpoint in the file header of each data file, which is called the start SCN,

Because it is used to check that database recovery is required when the database instance is started.

Select name,checkpoint_change# from V$datafile_header

4, terminate the SCN

The termination SCN for each data file is stored in the control file.

Select name,last_change# from V$datafile

During normal database operations, all data files that are in the online read-write mode are terminated by a null SCN.

5. SCN value during database operation

After the database is opened and running, the system checkpoints in the control file, the data file checkpoint SCN in the control file, and the start SCN in each data file header are the same. The termination SCN for each data file in the control file is null.

During a secure shutdown of the database, the system performs a checkpoint action, at which point the termination of all data files SCN

will be set to the value of the start SCN in the header of the data file. When the database is restarted,

Oracle compares the boot SCN in the file header with the database file checkpoint SCN.

If the two values match each other, Oracle then compares the start SCN in the header of the data file with the stop SCN for the data file in the control file. If the two values are consistent, it means that all the data blocks have been committed, all database modifications are not lost in the process of shutting down the database, so the process of starting the database does not require any recovery operations, the database can be opened. When all the databases are open, the value of the data file that is stored in the control file to terminate the SCN is again changed to NULL, which means that the data file is open and working properly.