Oracle checkpoint and the SCN)

(From: http://space.itpub.net/12361284/viewspace-346)

At the beginning of the year, I read the book deep dive written by eygle, and I felt a lot of GAINS.
We can summarize the two topics SCN and checkpoint.

Checkpoint
Many people complicate the checkpoint concept. In fact, the checkpointDatabaseThe real significance of the concept introduction is to reduce the time spent in database recovery (instance recovery). Who will do the checkpoint? We all know that there is a ckpt process in the database. This is an optional process, but the task that actually executes the checkpoint is not completed by ckpt, instead, ckpt notifies the dbwn process after updating the control file and the data file header to generate a checkpoint, the dbwn process writes the dirty data in the buffer cache (the dirty data corresponding to the current online redo log) to our data file. In this case, if the database crashes at this time (for example, we do a shutdown abort), the current online redo log content will not be needed during instance recovery and will be completed soon. Therefore, the ckpt process is only a secondary process. Its tasks are used to update the information in the control file and data file header when the system performs checkpoints. In fact, in Oracle 8i, ckpt tasks are generally completed by the lgwr process. After 8i, with the introduction of ckpt processes, the workload of lgwr is much reduced (the speed of commit is faster)
How can we generate checkpoints?
There are three methods:
1. Alter system checkpoint
2. Alter system switch logfile
3. dbwn process writes dirty Blocks

SCN
In Oracle, an internal synchronization clock is abbreviated as system change number.Oracle DatabaseWe can use the dbms_flashback package to query the change number of the current system: Select dbms_flashback.get_system_change_number from dual; Generally, SCN is mainly used to identify all changes made by the database, and the change of this SCN is only forward, it cannot be rolled back. Unless we plan to re-build the database, the SCN in the database will never be 0. Generally, the forward triggering of the SCN is carried out by commit, in addition to these data, I observe that every 3 seconds, the system will also refresh the SCN.

Note that:
1. ckpt must update the current SCN of the database to the database file header and control file when the checkpoint occurs. At the same time, the dbwn process updates the dirty data block (dirty block) in the buffer cache) write to the data file (this dirty data must be the part protected by the current online redo log ).

2. At the same time, the ckpt process will record (redo block address) RBA in the control file. This address is used to mark the location in the log to start during recovery.

There are a total of 4 SCN related to checkpoint in the Oracle database
1. System checkpoint SCN (exists in the control file)
After the system executes the checkpoint, Oracle updates the system checkpoint SCN in the current control file.
We can use
Select checkpoint_change # from V $ database:
To view

2. datafile checkpoint SCN (exists in the control file)
The control file records the location and information of each database file in Oracle, including the datafile checkpoint SCN. Therefore, when performing checkpoint, oracle also updates the datafile checkpoint SCN of each data file recorded in the control file.
We can use
Select checkpoint_change # from V $ datafile;
To view

3. Start SCN (exists in various data file headers)
When the checkpoint is executed, Oracle updates the start SCN in the actual data file header (note that it is definitely not in the control file ), this SCN is used to check whether media recovery is required during database startup)
We can use
Select checkpoint_change # from V $ datafile_header;

4. End SCN (exists in the control file)
The last type of SCN, end SCN, is also recorded in the control file. Each recorded data file header has a corresponding end SCN, which must exist in the control file. The absolute significance of this SCN is to verify whether instance recovery is required during database startup. We can use
Select name, last_change # from V $ datafile
In fact, when the database runs normally, the SCN Number of the online data file for read/write is # ffffff (null ).

Next, let's talk about how to start the SCN on the database.

1. During database startup, when system checkpoint SCN = datafile checkpoint SCN = start SCN, the Oracle database can be started normally without any media recovery. If there is a difference between the three, media recovery is required.

2. When do I need to perform instance recovery? In fact, in a normal open database, Oracle sets the end SCN of each data file header recorded in the control file to # ffffff (null ), if the database is shut down normally, for example (shutdown or shutdown immediate), the system will execute a checkpoint, this checkpoint updates the end SCN of each data file header recorded in the control file to the start SCN of each data file header of the current online data file, that is, end SCN = start SCN, if the two are the same when you start the database again, open the database and set the end SCN to # ffffff (null) again. If the database is shut down unexpectedly, so the checkpoint will not be executed, so when you open the database again, the end SCN <> Start SCN needs to be restored by the instance.

Who has done so many update SCN operations? As we have already said, our ckpt process will not only update SCN, but also notify dbwn to do his work.

Besides, system checkpoint SCN and datafile checkpoint SCN are both recorded in the control file. But what are the functions of these two SCN?
Logzgh has some discussions. I thought about it myself and learned his conclusion:

1. For read-only tablespaces, The datafile checkpoint SCN, start SCN, and end SCN numbers of the data files are the same. The three SCN will be frozen when the tablespace is read-only.

2. if the control file is not the current control file (that is to say, the control file is outdated for the current redo log SCN ), then the system checkpoint SCN is smaller than the start SCN (the start SCN is from the actual data file header and has a comparison basis ). Record these SCN numbers to identify whether the control file is the current control file. When a start SCN (obtained from various online data files) number exceeds the system checkpoit SCN number, it indicates that the control file is not the current control file, therefore, using backup controlfile must be used for recovery. This is one of the reasons for recording systemcheckpoint SCN.

When recreating a control file, there are two reconstruction methods (resetlogs and noresetlogs)

1. when the resetlogs option is used, system checkpoint SCN is classified as 0, the datafile checkpoint SCN of each data file recorded here is from start SCN (that is, it may be obtained from the data file header of the cold backup data file ). According to the preceding description, using backup controlfile must be used for recovery. Therefore, system checkpoint SCN = 0 <start SCN = datafile checkpoint SCN

2. When using the noresetlogs option, there must be an online redo log. Otherwise, use the resetlogs option. When the control file is rebuilt, the system checkpoint SCN = datafile checkpoint SCN = lastest checkpoint SCN in online redo log shows that the datafile checkpoint SCN is not read from the start SCN. Instead, it reads the SCN in the latest log file as its own data. At this time, the role of the reconstructed control file in recovery is similar to that of the latest control file, system checkpoint SCN (the checkpoint SCN information of the latest redo log has been read) it may be> Start SCN (because the data file may be recovered from the cold backup), you do not need to add the using backup controlfile clause when restoring.

Supplement to backup controlfile: Backup controlfile only has archive log information at the backup time, and does not have archive log information at the DB crash time. Therefore, online redo log is not automatically applied, instead, the system prompts that the archive log with the serial number lastest archive log sequence + 1 cannot be found. Although you can manually specify the online redo log for full recovery, once the using backup controlfile clause is used, oracle is considered to be incomplete recovery and must open resetlogs! In fact, if you have an old control file and do not want to resetlogs, it is very easy to use the old control file mount and back up to trace, then manually create the control file, use reuse database... noresetlogs. in this way, you can recover the database automatically and open the database without resetlogs.