Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to understand checkpoint cnt and checkpoint scn. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

[explanation] imitate an article by Master eygle and do it yourself. I feel that only in this way can I be impressed. Original reference: http://www.eygle.com/archives/2004/06/checkpoint_scn_ckpcnt.html

1. Dump control files in different situations

SQL > alter session set events' immediate trace name CONTROLF level 10'

Session altered.

SQL > alter tablespace system begin backup

Tablespace altered.

SQL > alter session set events' immediate trace name CONTROLF level 10'

Session altered.

SQL > alter system checkpoint

System altered.

SQL > alter session set events' immediate trace name CONTROLF level 10'

Session altered.

SQL > alter tablespace system end backup

Tablespace altered.

SQL > alter session set events' immediate trace name CONTROLF level 10'

Session altered.

SQL >

two。 Extract and analyze the information about system tablespaces in trace file from the udump catalog

a. Dump information under normal conditions of the system

*

DATA FILE RECORDS

*

(blkno = 0x6, size = 180, max = 100, in-use = 3, last-recid= 0)

DATA FILE # 1:

(name # 6) D:\ OPT\ ORACLE9I\ PRODUCT\ 9.2.0\ ORADATA\ ORA92US\ SYSTEM01.DBF

Creation size=0 block size=8192 status=0xe head=6 tail=6 dup=1

Tablespace 0, index=1 krfil=1 prev_file=0

Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:55 scn: 0x0000.00194116 04/14/2021 19:36:49

Stop scn: 0xffff.ffffffff 04/13/2008 20:48:26

Creation Checkpointed at scn: 0x0000.00000005 03/17/2008 15:57:33

Thread:0 rba: (0x0.0.0)

Enabled threads: 00000000 00000000 00000000

00000000 00000000

Offline scn: 0x0000.00192482 prev_range: 0

Online Checkpointed at scn: 0x0000.00192483 04/11/2008 19:27:07

Thread:1 rba: (0x1.2.0)

Enabled threads: 01000000 00000000 00000000 00000000

00000000 00000000

Hot Backup end marker scn: 0x0000.00000000

Aux_file is NOT DEFINED

After b.alter tablespace system begin backup

We noticed that Checkpoint cnt increased by 1, where the table space checkpoint. 1 was triggered.

*

DATA FILE RECORDS

*

(blkno = 0x6, size = 180, max = 100, in-use = 3, last-recid= 0)

DATA FILE # 1:

(name # 6) D:\ OPT\ ORACLE9I\ PRODUCT\ 9.2.0\ ORADATA\ ORA92US\ SYSTEM01.DBF

Creation size=0 block size=8192 status=0xe head=6 tail=6 dup=1

Tablespace 0, index=1 krfil=1 prev_file=0

Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:56 scn: 0x0000.001941a7 04/14/2021 19:39:26

Stop scn: 0xffff.ffffffff 04/13/2008 20:48:26

Creation Checkpointed at scn: 0x0000.00000005 03/17/2008 15:57:33

Thread:0 rba: (0x0.0.0)

Enabled threads: 00000000 00000000 00000000

00000000 00000000

Offline scn: 0x0000.00192482 prev_range: 0

Online Checkpointed at scn: 0x0000.00192483 04/11/2008 19:27:07

Thread:1 rba: (0x1.2.0)

Enabled threads: 01000000 00000000 00000000 00000000

00000000 00000000

Hot Backup end marker scn: 0x0000.00000000

Aux_file is NOT DEFINED

After c.alter system checkpoint

At this point, Checkpoint cnt increases, but scn no longer changes

*

DATA FILE RECORDS

*

(blkno = 0x6, size = 180, max = 100, in-use = 3, last-recid= 0)

DATA FILE # 1:

(name # 6) D:\ OPT\ ORACLE9I\ PRODUCT\ 9.2.0\ ORADATA\ ORA92US\ SYSTEM01.DBF

Creation size=0 block size=8192 status=0xe head=6 tail=6 dup=1

Tablespace 0, index=1 krfil=1 prev_file=0

Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:57 scn: 0x0000.001941a7 04/14/2021 19:39:26

Stop scn: 0xffff.ffffffff 04/13/2008 20:48:26

Creation Checkpointed at scn: 0x0000.00000005 03/17/2008 15:57:33

Thread:0 rba: (0x0.0.0)

Enabled threads: 00000000 00000000 00000000

00000000 00000000

Offline scn: 0x0000.00192482 prev_range: 0

Online Checkpointed at scn: 0x0000.00192483 04/11/2008 19:27:07

Thread:1 rba: (0x1.2.0)

Enabled threads: 01000000 00000000 00000000 00000000

00000000 00000000

Hot Backup end marker scn: 0x0000.00000000

Aux_file is NOT DEFINED

After d.alter tablespace system end backup

At this point, the freeze of the data file header is lifted and the scn begins to change.

*

DATA FILE RECORDS

*

(blkno = 0x6, size = 180, max = 100, in-use = 3, last-recid= 0)

DATA FILE # 1:

(name # 6) D:\ OPT\ ORACLE9I\ PRODUCT\ 9.2.0\ ORADATA\ ORA92US\ SYSTEM01.DBF

Creation size=0 block size=8192 status=0xe head=6 tail=6 dup=1

Tablespace 0, index=1 krfil=1 prev_file=0

Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:58 scn: 0x0000.001941b3 04/14/2021 19:39:54

Stop scn: 0xffff.ffffffff 04/13/2008 20:48:26

Creation Checkpointed at scn: 0x0000.00000005 03/17/2008 15:57:33

Thread:0 rba: (0x0.0.0)

Enabled threads: 00000000 00000000 00000000

00000000 00000000

Offline scn: 0x0000.00192482 prev_range: 0

Online Checkpointed at scn: 0x0000.00192483 04/11/2008 19:27:07

Thread:1 rba: (0x1.2.0)

Enabled threads: 01000000 00000000 00000000 00000000

00000000 00000000

Hot Backup end marker scn: 0x0000.00000000

Aux_file is NOT DEFINED

Checkpoint cnt is used to ensure that the data file used in normal operation is the current version. Prevent the wrong version of the data file from being restored during recovery. Checkpoint cnt is always incremented, even if the tablespace is in hot backup mode.

Because the creation time of tablespaces is different, the Checkpoint cnt of different tablespaces / data files is usually different (editor's note: you can see this in dump files, different datafile does have different checkpoint cnt).

We know:

In the process of database open, Oracle has to check twice (editor's note: according to eygle himself in many other places, the actual situation is much more complicated, this is just a brief explanation. Maybe only Oracle developers know what to do.

Check for the first time whether the Checkpoint cnt in the header of the data file is consistent with the Checkpoint cnt in the corresponding control file.

If equal (editor's note: in fact, if equal, then System Checkpoint SCN = Datafile Checkpoint SCN = Start SCN), make a second check.

Check whether the start SCN of the data file header and the end SCN in the corresponding control file are consistent for the second time

If the end SCN equals the start SCN, there is no need to restore that file (editor's note: if not equal, that is, if the Stop SCN is infinite, then Istance Recovery is required).

Editor's note: the open process here is different from the article I reprinted about SCN, but I personally understand that the first time is to check if you need Media Recovery; the second time is to check if you need to do Instance Recovery. The first step is to start Media Recover if the version of the file is inconsistent, and determine the required log; according to the corresponding SCN. If Media Recovery is not needed, check whether Instance Recovery is needed, if the value of Stop SCN is infinite, then do, and submit the redo logs until the latest one)

After checking each data file, open the database. At the same time, the end SCN of each data file is set to infinite.

On how to understand checkpoint cnt and checkpoint scn to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.