Oracle scn details

I have previously written a basic article about scn, but it does not reflect the changes and existence of scn. Here I want to say that many situations of scn may change, rather than submitting or

The scn exists in multiple places. Such as log files, data files, and control files.

System checkpoint scn (v $ database (checkpoint_change #))

Data File checkpoint (v $ datafile (checkpoint_change #))

Data File final stop scn (v $ datafile (last_change #))

Checkpoints in data files

Start scn (v $ datafile_header (checkpoint_change #)

1. System checkpoint scn

After a checkpoint is completed, Oracle stores the scn of the system checkpoint in the control file.

Select checkpoint_change # from v $ database

2. Data File checkpoint scn

After a checkpoint is completed, Oracle separately stores the scn of each data file 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. This value is called the startup scn,

It is used to check whether database recovery is required when the database instance is started.

Select name, checkpoint_change # from v $ datafile_header

4. stop scn

The ending scn of each data file is stored in the control file.

Select name, last_change # from v $ datafile

During Normal database operations, the ending scn of all data files in online read/write mode is null.

5. scn value during database operation

After the database is opened and running, the system checkpoint in the control file and the data file checkpoint scn in the control file are the same as the startup scn in each data file header. The ending scn of each data file in the control file is null.

When the database is safely shut down, the system executes a checkpoint action. At this time, the scn of all data files is terminated.

All are set to the startup scn value in the data file header. When the database is restarted,

Oracle compares the startup scn in the file header with the database file checkpoint scn,

If these two values match each other, oracle will compare the startup scn in the data file header with the termination scn in the data file in the control file. If these two values are the same, it means that many data blocks have been submitted, and modifications to all databases are not lost when the database is closed, therefore, this database startup process does not require any restoration operations, so the database can be opened. After all the databases are opened, the data file ending scn value stored in the control file is changed to null again, which indicates that the data file has been opened and can be used properly.

1 2 3 Next Page