In-depth analysis of Oracle manual recovery Control Files

V $ database. CONTROLFILE_CHANGE # (indicates the last scn Number of the control file recovered from the backup. If the control file is not

V $ database. CONTROLFILE_CHANGE # (indicates the last scn Number of the control file recovered from the backup. If the control file is not

V $ database. CHECKPOINT_CHANGE # (last scn checkpointed)
V $ database. CONTROLFILE_SEQUENCE # (control file sequence number incremented by control file transactions)
V $ database. CONTROLFILE_CHANGE # (last scn in backup control file; null if the control file is not a backup)
V $ datafile. CHECKPOINT_CHANGE # (scn at last checkpoint)
V $ datafile_header. CHECKPOINT_CHANGE # (datafile checkpoint change #
V $ log. FIRST_CHANGE # (lowest system change number (SCN) in the log

V $ database. CHECKPOINT_CHANGE # indicates the scn Number of the last full checkpoint of the database. Update is performed only when full checkpoints are performed. The updated value is the current_scn value of the current database.
V $ database. CONTROLFILE_CHANGE # (indicates the last scn Number of the control file recovered from the backup. If it is just recovered from the backup and the control file is not recovered from the backup, the value is null, but when the control file is opened in the database, the value is set to the current current_scn value as the incremental check point and full check point or the database structure changes.

SYS @ orcl # select CHECKPOINT_CHANGE #, CONTROLFILE_SEQUENCE #, CONTROLFILE_CHANGE # from v $ database;

CHECKPOINT_CHANGE # CONTROLFILE_SEQUENCE # CONTROLFILE_CHANGE #
----------------------------------------------------------
4921905 14285 4922868
23:13:45 SYS @ orcl # select CHECKPOINT_CHANGE #, CONTROLFILE_SEQUENCE #, CONTROLFILE_CHANGE # from v $ database;

CHECKPOINT_CHANGE # CONTROLFILE_SEQUENCE # CONTROLFILE_CHANGE #
----------------------------------------------------------
4935440 14311 4935548

At 23:13:46 SYS @ orcl # select current_scn from v $ database;

CURRENT_SCN
-----------
4935575

23:13:46 SYS @ orcl # alter system checkpoint;
Select CHECKPOINT_CHANGE #, CONTROLFILE_SEQUENCE #, CONTROLFILE_CHANGE # from v $ database;
The system has been changed.

23:13:46 SYS @ orcl #

CHECKPOINT_CHANGE # CONTROLFILE_SEQUENCE # CONTROLFILE_CHANGE #
----------------------------------------------------------
4935576 14313 4935578

23:13:49 SYS @ orcl #
23:14:09 SYS @ orcl # select CHECKPOINT_CHANGE #, CONTROLFILE_SEQUENCE #, CONTROLFILE_CHANGE # from v $ database;

CHECKPOINT_CHANGE # CONTROLFILE_SEQUENCE # CONTROLFILE_CHANGE #
----------------------------------------------------------
4935576 14314 4935649

23:16:42 SYS @ orcl # alter system switch logfile;

The system has been changed.

23:16:52 SYS @ orcl # select CHECKPOINT_CHANGE #, CONTROLFILE_SEQUENCE #, CONTROLFILE_CHANGE # from v $ database;

CHECKPOINT_CHANGE # CONTROLFILE_SEQUENCE # CONTROLFILE_CHANGE #
----------------------------------------------------------
4935576 14318 4935675

23:16:55 SYS @ orcl #

V $ database. CONTROLFILE_SEQUENCE # (indicates the sequence to be added based on database changes. During the query, the sequence number of the current control file is displayed, incremental varies with incremental checkpoints and full checkpoints or database structures.

V $ datafile. CHECKPOINT_CHANGE # (this value indicates that the scn Number of the last checkpoint in the data file is the same as the CHECKPOINT_CHANGE # In v $ database. That is to say, when a full checkpoint is performed, the scn in the data file must be consistent with the scn in the control file. When we start to the database mount state, and then dump the data file to see the checkpoint scn is exactly this value, during the database startup to verify this value)
23:26:05 SYS @ orcl # select CHECKPOINT_CHANGE #, CONTROLFILE_SEQUENCE #, CONTROLFILE_CHANGE # from v $ database;

CHECKPOINT_CHANGE # CONTROLFILE_SEQUENCE # CONTROLFILE_CHANGE #
----------------------------------------------------------
4935675 14332 4937039

23:26:30 SYS @ orcl # select checkpoint_change # from v $ data file;

CHECKPOINT_CHANGE #
------------------
4935675
4935675
4935675
4935675
4935675
4935675
4935675
4935675
4935675
4935675
4935675

CHECKPOINT_CHANGE #
------------------
4935675

12 rows have been selected.

23:26:31 SYS @ orcl # alter system checkpoint;

The system has been changed.

23:27:27 SYS @ orcl # select CHECKPOINT_CHANGE #, CONTROLFILE_SEQUENCE #, CONTROLFILE_CHANGE # from v $ database;

CHECKPOINT_CHANGE # CONTROLFILE_SEQUENCE # CONTROLFILE_CHANGE #
----------------------------------------------------------
4937086 14336 4937088

23:27:31 SYS @ orcl # select checkpoint_change # from v $ data file;

CHECKPOINT_CHANGE #
------------------
4937086
4937086
4937086
4937086
4937086
4937086
4937086
4937086
4937086
4937086
4937086

CHECKPOINT_CHANGE #
------------------
4937086

12 rows have been selected.

23:27:32 SYS @ orcl #
V $ datafile_header. CHECKPOINT_CHANGE # (indicates the checkpoint scn of the data file header. This value is consistent with the checkpoint of the data file, the checkpoint scn in the header in the dump file of the data file that runs in the database to the open state is this value .)