Checkpoint verification in the open stage of the database

When the recovery is required for the database open stage: 1. oracle compares system checkpointscn, datafilecheckpointscn, and startscn to determine whether media recovery is required for data files. 2. when the redo thread is opened, that is, when the database is open, stopscn is set to infinity.

When the recovery is required for the database open stage: 1. oracle compares system checkpoint scn, datafile checkpoint scn, and start scn to determine whether media recovery is required for data files. 2. when the redo thread is opened, that is, when the database is open, the stop scn is set to infinity.

When the database open stage requires a recovery:
1. oracle compares system checkpoint scn, datafile checkpoint scn, and start scn to determine whether media recovery is required for data files.
2. When the redo thread is enabled, that is, when the database is open, the stop scn is set to infinity. When the database is closed normally, the stop scn is equal to the datafile scn.
It should be noted that the stop scn is stored in controlfile, and some information on the internet says it exists in the datafile header, which is incorrect.
3. Before opening, oracle determines whether to restore the media, and then determines whether to perform instance recovery.
4. The relationships between the four scn types are as follows:
System checkpoint scn-stored in controlfile
Datafile checkpoint scn-stored in controlfile
Start scn-stored in datafile header
Stop scn-stored in controlfile

System scn, datafile checkpoint scn, and start scn are used to determine whether the data file needs to be restored by media. The three equal values do not need to be restored.
If all four of them are equal, you do not need to restore the instance. Stop scn is used to determine whether the instance is restored.

5. If the stop scn is larger than several other scn, You need to perform instance recover and scan redo. the start point of instance recovery is low cache rba, and the end point is
Is the end Of the redo log.

Your understanding:
Oracle recovery is classified into media recovery and instance recovery, and media recovery needs to be restored using backup sets and archives, instance recovery is automatically performed using online logs.
However, if you think about it, shutdown the abort database and delete all online logs, and restart the database to the mount status, you can clearly see the following process:
SQL> select checkpoint_change #, name from v $ datafile;

CHECKPOINT_CHANGE# NAME
------------------ --------------------------------------------------
1303384 G:\ORACLE\PRODUCT\10.2.0\ORADATA\UTF8\SYSTEM01.DBF
1303384 G:\ORACLE\PRODUCT\10.2.0\ORADATA\UTF8\UNDOTBS01.DB
F

1303384 G:\ORACLE\PRODUCT\10.2.0\ORADATA\UTF8\SYSAUX01.DBF
1303384 G:\ORACLE\PRODUCT\10.2.0\ORADATA\UTF8\USERS01.DBF

SQL> select name,checkpoint_change#,checkpoint_count from v$datafile_header;

NAME CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- ------------------ ----------------
G:\ORACLE\PRODUCT\10.2.0\ORADATA\UTF8\SY 1303384 112
STEM01.DBF

G:\ORACLE\PRODUCT\10.2.0\ORADATA\UTF8\UN 1303384 75
DOTBS01.DBF

G:\ORACLE\PRODUCT\10.2.0\ORADATA\UTF8\SY 1303384 112
SAUX01.DBF

G:\ORACLE\PRODUCT\10.2.0\ORADATA\UTF8\US 1303384 111
ERS01.DBF

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1303384

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\UTF8\SYSTEM01.DBF'

It can be seen that the checkpoint scn of the system and the checkpoint scn of the data file recorded in the control file are consistent with the start scn of the data file header record,

However, because all the redo logs are deleted here, all the redo numbers are reset during the open resetlogs process, and then redo is generated again. However, the instance fails to be restored due to the lack of the previous online redo logs, the system also prompts that file 1 needs to be further restored.

For example:
Here, ainemo wants to propose that it will not report the so-called error if it is not called instance recovery. What the database can achieve automatically in the open stage is instance recovery, and media recovery needs to be manually executed by dba,

However, if the instance fails to be restored, such as the lack of online redo, the database cannot be opened. This does not mean that the instance recovery does not affect the normal opening of the database.

Here, I have read several versions of the database when it needs to be restored and sorted out the following ideas: (eygle once said so)
1. Check whether the checkpoint cnt of the control file record is consistent with the checkpoint cnt of the data file header record. It does not mean that the startup scn of the data file header is consistent with the scn recorded in the control file.

The checkpoint cnt of the control file record and the checkpoint cnt of the data file header can be used to identify whether the data file and the control file come from the same version, rather than from the backup.

Checkpoint scn checkpoint count is mainly used to identify whether the data file and the control file come from the same version.

Some netizens have done tests. Even if the startup scn of the data file is inconsistent with the checkpint scn of the data file recorded in the control file, the database can be opened. (Note that the checkpoint scn of the data file is also recorded in the control file)

2. Check whether the startup scn of the data file header is consistent with the end scn of the control file. If they are inconsistent, instance recovery is required. instance recovery is automatic, but it does not mean that it is not important, if the database fails to be opened due to the lack of log recovery.

Test Description of a netizen on puber:
SQL> startup mount
SQL> select file #, checkpoint_change #, last_change # from v $ datafile;

FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 2bc309 2bc309
2 2bc309 2bc309
3 2bc309 2bc309
4 2bc309 2bc309
5 2bc309 2bc309

SQL>
SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
2bc309
2bc309
2bc309
2bc309
2bc309

SQL>
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
2bc309

SQL> host

RMAN> restore datafile 3
2> ;

........

RMAN> recover datafile 3;

........

RMAN> exit

The recovery manager is complete.

Cocuments and SettingsRequieM>exit

SQL> select file#,checkpoint_change#,last_change# from v$datafile;

FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 2bc309 2bc309
2 2bc309 2bc309
3 2bc309 2bc308
4 2bc309 2bc309
5 2bc309 2bc309

SQL>
SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
2bc309
2bc309
2bc308
2bc309
2bc309

SQL> alter database open;

Select several time points for the above steps to dump the control file and data file header. The process is too long. Just paste the result.

1. After shutdown;
2. After restore;
3. After recover;
4. After open db.

The result is as follows:
Time Point Control File Data File Header
Chk cnt chk scn
1 302 2bc309 302 2bc309
2 302 2bc309 264 23360c -- we can see that the start cnt of the data file header and the control file record cnt are inconsistent during restore.
3 303 2bc309 303 2bc308 -- after recover, the archive and online logs have reached the cnt consistency, but the checkpoint scn is inconsistent.
4 304 2bc30a 304 2bc30a

Conclusion: at the end of restoration, the chk scn in the file header is 1 smaller than the chk scn in the control file, but the chk cnt is identical.

Therefore, when the database is opened, it compares the checkpoint count and the start scn of the data file header and the end scn of the data file of the control file, instead of comparing the start scn of the data file header and the checkpoint scn of the control file.

I did a test myself:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;

rman>restore datafile 4;

alter session set events 'immediate trace name controlf level 12';

controlfile trace：
DUMP OF CONTROL FILES, Seq # 1497 = 0x5d9
V10 STYLE FILE HEADER:
Compatibility Vsn = 169870080=0xa200300
Db ID=4166494968=0xf857aaf8, Db Name='ORA10G'
Activation ID=0=0x0
Control Seq=1497=0x5d9, File size=432=0x1b0
File Number=0, Blksiz=16384, File Type=1 CONTROL
Logical block number 1 (header block)

Data file #4:
(Name #4) G: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORA10G \ USERS01.DBF
Creation size = 0 block size = 8192 status = 0xe head = 4 tail = 4 dup = 1
Tablespace 4, index = 4 krfil = 4 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 316 scn: 0x0000. 0018e7 05/28/2014 17:21:16 -- this checkpoint cnt 316 is the checkpoint count of the control file record. scn: 0x0000. 001818e7 is the Checkpoint scn of the data file that controls the file record
Stop scn: 0x0000. 001818e7 05/28/2014 17:21:16 -- the Stop scn: 0x0000. 001818e7 is the so-called stop scn of the data file. Note that this stop scn is not recorded in the data file, but in the control file.
Creation Checkpointed at scn: 0x0000.00002997 08/26/2008 12:33:50
Thread: 0 rba :( 0x0. 0.0)

alter session set events 'immediate trace name file_hdrs level 10';

File_hdrs trace:
Data file #4:
(Name #4) G: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORA10G \ USERS01.DBF
Creation size = 0 block size = 8192 status = 0xe head = 4 tail = 4 dup = 1
Tablespace 4, index = 4 krfil = 4 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 316 scn: 0x0000. 001818e7 05/28/2014 17:21:16 this section is not the information of the data file header, but only the information about the data file in the control file. It must be noted that this has plagued ainemo for a long time.
Stop scn: 0x0000. 001818e7 05/28/2014 17:21:16
Creation Checkpointed at scn: 0x0000.00002997 08/26/2008 12:33:50
Thread: 0 rba :( 0x0. 0.0)

This file header is the information of the data file header.
V10 style file header:
Compatibility Vsn = 169870080 = 0xa200300
Db ID = 4166494968 = 0xf857aaf8, Db Name = 'ora10g'
Activation ID = 0 = 0x0
Control Seq = 1474 = 0x5c2, File size = 12000 = 0x2ee0
File Number = 4, blksiz= 8192, File Type = 3 DATA
Tablespace #4-USERS rel_fn: 4
Creation at scn: 0x0000.00002997 08/26/2008 12:33:50
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread: 0
Reset logs count: 0x3285b2bc scn: 0x0000.00093009 reset logs terminal rcv data: 0x0 scn: 0x0000.00000000
Prev reset logs count: 0x2790538b scn: 0x0000.00000001 prev reset logs terminal rcv data: 0x0 scn: 0x0000.00000000
Recovered at 05/28/2014 17:25:05
Status: 0x0 root dba: 0x00000000 chkpt cnt: 314 ctl cnt: 313 -- the first chkpt cnt: 314 is the checkpoint count checkpoint cnt of the data file header.
Begin-hot-backup file size: 0
Checkpointed at scn: 0x0000. 001817ee 05/28/2014 17:20:29 -- and this checkpointed at scn: 0x0000. 001817ee is the scn of the data file header, which is the startup scn of the so-called data file header.

SQL> select checkpoint_change#,last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
1579239 1579239
1579239 1579239
1579239 1579239
1579239 1579239

In V $ datafile, Last_change # controls the stop scn of the data file recorded in the file, and checkpoint_change of v $ datafile # controls the checkpoint scn of the data file recorded in the file.

SQL> select checkpoint_change#,checkpoint_count from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_COUNT
------------------ ----------------
1579239 318
1579239 108
1579239 318
1578990 314

Checkpoint_change # In V $ datafile_header is the checkpoint scn (also called start scn) of the data file header, and checkpoint_count in v $ datafile_header is the checkpoint count of the data file header.

In the v $ datafile and v $ datafile_header views, we found that the query results are indeed consistent with the preceding dump controlfile and datafile header results.

For example, the checkpoint_change # In v $ datafile_header corresponds to the Checkpointed at scn: 0x0000 in the dump datafile header trace file. 001817ee, while the checkpoint_count 314 of v $ datafile_headers exactly corresponds to chkpt cnt: 314

SQL> recover datafile 4

SQL> select checkpoint_change#,last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
1579239 1579239
1579239 1579239
1579239 1579239
1579239 1579238

SQL> select checkpoint_change#,checkpoint_count from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_COUNT
------------------ ----------------
1579239 318
1579239 108
1579239 318
1579238 317

Controlfile trace:
Data file #4:
(Name #4) G: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORA10G \ USERS01.DBF
Creation size = 0 block size = 8192 status = 0xe head = 4 tail = 4 dup = 1
Tablespace 4, index = 4 krfil = 4 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 317 scn: 0x0000. 0018e7 05/28/2014 17:21:16 -- here, the checkpoint cnt is recorded in the control file and changed to 317. The checkpoint scn is not changed.
Stop scn: 0x0000. 0018e6 05/28/2014 17:21:16 -- Stop scn: 0x0000. 001818e7 changed to Stop scn: 0x0000. 0018e6. Here the stop scn is reduced by 1
Creation Checkpointed at scn: 0x0000.00002997 08/26/2008 12:33:50
Thread: 0 rba :( 0x0. 0.0)

datafile header trace:
DATA FILE #4:
(name #4) G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\USERS01.DBF
creation size=0 block size=8192 status=0xe head=4 tail=4 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:317 scn: 0x0000.001818e7 05/28/2014 17:21:16
Stop scn: 0x0000.001818e6 05/28/2014 17:21:16
Creation Checkpointed at scn: 0x0000.00002997 08/26/2008 12:33:50
thread:0 rba:(0x0.0.0)

Aux_file is NOT DEFINED
V10 style file header:
Compatibility Vsn = 169870080 = 0xa200300
Db ID = 4166494968 = 0xf857aaf8, Db Name = 'ora10g'
Activation ID = 0 = 0x0
Control Seq = 1499 = 0x5db, File size = 12000 = 0x2ee0
File Number = 4, blksiz= 8192, File Type = 3 DATA
Tablespace #4-USERS rel_fn: 4
Creation at scn: 0x0000.00002997 08/26/2008 12:33:50
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread: 0
Reset logs count: 0x3285b2bc scn: 0x0000.00093009 reset logs terminal rcv data: 0x0 scn: 0x0000.00000000
Prev reset logs count: 0x2790538b scn: 0x0000.00000001 prev reset logs terminal rcv data: 0x0 scn: 0x0000.00000000
Recovered at 05/28/2014 17:47:28
Status: 0x0 root dba: 0x00000000 chkpt cnt: 317 ctl cnt: 316 -- checkpoint cnt changed from 314 to 317, the checkpoint cnt recorded in the datafile header is consistent with the checkpoint cnt recorded in the controlfile.
Begin-hot-backup file size: 0
Checkpointed at scn: 0x0000. 0018e6 05/28/2014 17:21:16 -- the checkpoint scn (start scn) of the datafile header is changed to 0x0000. 0018e6 and the stop scn recorded in the control file are also consistent.

At this point, the database has completed the check: the checkpoint cnt recorded in controlfile is consistent with the checkpoint cnt recorded in the datafile header; the stop scn recorded in controlfile is the same as the checkpoint scn (also called start scn) recorded in the datafile header.

However, there are still many things to be aware of in this process, for example, what is the scn recorded in this controlfile and what is the relationship with the startup database, for recovery, you still have to wait for the bbed to have a deep understanding of block and recovery to be able to unlock the veil.

In fact, exploring these things may not bring us any benefit in the short term, but as ainemo personally believes, if we want to break through the so-called bottleneck (usually people will encounter it, at least ainemo is now met ), for example, in some aspects such as optimization, SQL tuning, troubleshooting, recovery and so on, the perseverance and behavior of deep learning exploration may gradually break through the bottleneck if you stick to it!

Original article address: I would like to thank the original author for sharing the checkpoint verification in the open stage of the database.