Instance recovery and Oracle SCN, instance oraclescn

Instance recovery and Oracle SCN, instance oraclescn

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 namelike '% 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.

Oracle can terminate SCN

When oracle is running normally, the data file termination SCN is NULL or infinite.

If the "various" data files terminate SCN (control files exist) when the database is normally closed and the "correspond" Data File Header SCN (with data file headers) is consistent, no recovery is required.
 
Why does an oracle data file have SCN?

Start scn
Oracle stores the scn of this checkpoint in the file header of each data file. This value is called Startup scn because it is used when the database instance is started,
Check whether database recovery is required.
Select name, checkpoint_change # from v $ datafile_header
Terminate scn
The ending scn of each data file is stored in the control file.
Select name, last_change # from v $ datafile
This SCN number is used to check whether instance recovery is required during database startup. Only when the database file status changes
When the database runs normally, this SCN is NULL for the online data files that can be read and written.