Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of SCN and checkpoint in Oracle, which is very detailed and has certain reference value. Friends who are interested must finish it!

Definition of 1.SCN

SCN (System Change Number), commonly known as the system change number, is a very important data structure in the database.

SCN is used to identify the version of the database that was submitted at an exact time. When a transaction commits, it is given a unique SCN that identifies the transaction. SCN is also used as the internal clock mechanism of Oracle database and can be regarded as logical clock. Each database has a global SCN generator.

As the logical clock within the database, database transactions are sorted according to SCN, and Oracle also implements important database functions such as consistent read (Read Consistency) according to SCN. In addition, SCN is also extremely important for distributed transactions (Distributed Transactions), so I won't cover it any more here.

SCN is unique in the database and increases over time, but may not be coherent. The value of SCN will never be reset to 0 unless the database is rebuilt.

For a long time, there has been a lot of controversy about SCN. Many people think that SCN refers to System Commit Number, but usually SCN changes when it is submitted, so these two nouns often appear repeatedly in documents. Even in the official documentation of Oracle, SCN often appears in both forms of System Change/Commit Number. Which word is not the most important, it is important to know that SCN is the clock mechanism within Oracle, Oracle maintains database consistency through SCN, and implements Oracle's vital recovery mechanism through SCN. SCN is ubiquitous in the database, and SCN values are recorded in common transaction tables, control files, data file headers, log files, data data blocks, and so on.

With different prefixes, SCN also has different names, such as checkpoint SCN (Checkpoint SCN), Resetlogs SCN, etc.

How to obtain 2.SCN

You can get the current or approximate SCN of the database in the following ways.

SQL > SELECT dbms_flashback.get_system_change_number FROM DUAL; GET_SYSTEM_CHANGE_NUMBER---- 6051905241299SQL >

Further explanation of 3.SCN

The current SCN of the system does not change when any database operation occurs. SCN usually changes when a transaction is committed or rolled back. There are SCN in control file, data file header, data block, log file header and log file change vector, but their functions are different.

(1) the header of the data file contains the Checkpoint SCN of the data file, indicating the SCN of the last checkpoint operation performed by the data file.

Every data file contains an entry that records the value of the checkpoint SCN of the file and the time when the checkpoint occurs. Here Checkpoint SCN, Stop SCN and Checkpoint Cnt are all very important data structures.

4. Checkpoint

Many documents describe Checkpoint as very complex, which brings obstacles to our correct understanding of checkpoints, as a result checkpoints have now become a very complex issue. In fact, a checkpoint is just a database event, and the fundamental meaning of its existence is to reduce crash recovery (Crash Recovery) time.

When you modify the data, you need to first read the data into memory (Buffer Cache). While modifying the data, Oracle will record the redo information (Redo) for recovery. Because of the redo information, Oracle does not need to write the changed data back to disk immediately upon submission (it is inefficient to write immediately), and redo (Redo) exists so that the data can be recovered after the database crashes.

In the most common case, the database may be Crash due to a power outage, and data modified in memory that has not been written to the file will be lost. After the next database startup, Oracle can replay the transaction (that is, roll forward) through the redo log (Redo) to restore the database to the state it was before the crash, then the database can be opened for use, and Oracle can then roll back the uncommitted transaction.

In this process, people are usually most concerned about how long it will take for the database to be opened. That is, how many redo logs need to be read to complete the roll forward. Of course, users want this time as short as possible, and Oracle is constantly optimizing this process through various means to shorten the recovery time.

Checkpoints exist to shorten this recovery time.

When a checkpoint occurs (the SCN at this time is called Checkpoint SCN), Oracle will notify the DBWR process to write the modified data, that is, the dirty data (Dirty Data) before the Checkpoint SCN, from Buffer Cache to disk. When the write is completed, the CKPT process updates the control file and data file header, records the checkpoint information, and identifies the change.

Checkpoint SCN can be queried from the database:

SQL > select file#,NAME,CHECKPOINT_CHANGE#,to_char (CHECKPOINT_TIME,'yyyy-mm-dd hh34:mi:ss') CPT from v$datafile FILE# NAME CHECKPOINT_CHANGE# CPT -- 1 / u01/app/oracle/oradata/orcl/system01.dbf 6051905239995 2016-05-05 04:14:32 2 / u01/app/oracle/oradata/orcl/sysaux01.dbf 60519052399952016-05-05 04:14:32 3 / u01/app/oracle/oradata/orcl/undotbs01.dbf 6051905239995 2016-05-05 04:14:32 4 / u01/app/oracle/oradata/orcl/users01.dbf 6051905239995 2016-05-05 04:14:32 5 / u01/app/oracle/oradata/orcl/example01.dbf 6051905239995 2016-05 04:14:32 6 / u01/app / oracle/oradata/orcl/DEV_odi_user.dbf 6051905239995 2016-05-05 04:14:32 7 / u01/app/oracle/oradata/orcl/apex_01.dbf 6051905239995 2016-05-05 04 u01/app/oracle/oradata/orcl/APEX_6121090681146232.dbf 14 rows selected 328 rows selected

After the checkpoint is complete, the data modified by the checkpoint has been written back to disk, and the corresponding redo records in the redo log file are no longer useful for crash / instance recovery.

The frequency of checkpoints has a great impact on the recovery time of the database. If the frequency of checkpoints is high, there are relatively few redo logs to be applied during recovery, and the recovery time can be shortened. However, it should be noted that the internal operations of the database are highly relevant, and too frequent checkpoints will also lead to performance questions, especially those databases that are updated frequently. Therefore, the optimization of the database is a systematic project and should not be hasty.

Furthermore, we can know that if Oracle can make the SCN of the checkpoint gradually approach the latest changes of Redo when the performance permits, then we can finally get an optimal balance point, so that Oracle can minimize the recovery time.

To achieve this goal, Oracle has been improving the checkpoint algorithm in different versions.

The above is all the contents of the article "sample Analysis of SCN and checkpoints in Oracle". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.