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

Editor to share with you the example analysis of the SCN mechanism in oracle, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

As an important mechanism in oracle, SCN (System Change Number) plays an important role in data recovery, Data Guard, Streams replication, synchronization between RAC nodes and so on. Understanding how SCN works can help you gain a deeper understanding of the above functions.

Before we understand SCN, let's take a look at how data changes in oracle transactions are written to the data file:

1. Start the transaction

2. Find the required data block in buffer cache, and if not, load it into buffer cache from the data file

3. The transaction modifies the data block of buffer cache, which is identified as "dirty data" and written to log buffer.

4. The transaction commits, and the LGWR process writes the "dirty data" in log buffer to redo log file.

5. When the checkpoint,CKPT process updates the information in the header of all data files, the DBWn process is responsible for writing the dirty data in Buffer Cache to the data file.

After the above five steps, the data changes in the transaction are finally written to the data file. However, once the database goes down unexpectedly in the above intermediate link, how do you know which data has been written to the data file and which has not been written when restarting (similarly, there is a similar question in DG and streams: what is the data that has been replicated in the last synchronization and which is not)? The SCN mechanism can solve the above problems perfectly.

SCN is a number, to be exact, a number that will only increase, not decrease. It is this only added feature that ensures that Oracle knows what should be restored and which should be replicated.

There are a total of 4 SCN: system checkpoint (System Checkpoint) SCN, data file checkpoint (Datafile Checkpoint) SCN, end SCN (Stop SCN), start SCN (Start SCN). The SCN in face 3 exists in the control file, and the last one exists in the header of the data file.

In the control file, System Checkpoint SCN is global for the whole database, so there is one, while Datafile Checkpoint SCN and Stop SCN are for each data file, so a data file corresponds to a Datafile Checkpoint SCN and Stop SCN in the control file. During the normal operation of the database, Stop SCN (which can be queried through the field last_change# of the view v$datafile) is an infinite number or NULL.

After a transaction commits (step 4 above), there is a redo record in redo log, and the system provides it with an up-to-date SCN (you can know the current latest SCN through the function dbms_flashback.get_system_change_number), which is recorded in that record. If the record is emptied when the redo log is empty (when the log is full for switching or when checkpoint occurs, all change logs have been written to the data file), then its SCN is recorded as the low SCN of redo log. Later, in the redo record where the log is written again and unprecedented, SCN becomes Next SCN.

When the log is switched or checkpoint occurs (step 5 above), all redo records from Low SCN to Next SCN are written to the data file by the DBWn process, while the CKPT process updates the Start SCN recorded on the file header of all data files (regardless of whether the data in the redo log affects the data file or not) to Next SCN (which can be queried through the field checkpoint_change# of the view v$datafile_header) At the same time, the System Checkpoint SCN in the control file (which can be queried through the field checkpoint_change# of the view v$database) and the corresponding Datafile Checkpoint of each data file (which can be queried through the field checkpoint_change# of the view v$datafile) are also updated to Next SCN. However, if the tablespace of the data file is set to read-only, neither the Start SCN of the data file nor the Datafile Checkpoint SCN in the control file will be updated.

So how does the system produce the latest SCN? In fact, this number was converted by the timestamp at that time. Whenever a new SCN-to-redo record needs to be generated, the system takes the current timestamp and converts it to a number as a SCN. We can convert it back to timestamp through the function SCN_TO_TIMESTAMP (after 10g):

SQL > select dbms_flashback.get_system_change_number, SCN_TO_TIMESTAMP (dbms_flashback.get_system_change_number) from dual GET_SYSTEM_CHANGE_NUMBER----SCN_TO_TIMESTAMP (DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)- -287707675617-AUG-07 02.15.26.000000000 PM

You can also use the function timestamp_to_scn to convert a timestamp to SCN:

SQL > select timestamp_to_scn (SYSTIMESTAMP) as scn from dual; SCN-2877078439

Finally, in addition to reflecting changes in transaction data and keeping it synchronized, SCN also acts as a "heartbeat" of the system-the system SCN is refreshed every 3 seconds or so.

Next, let's take a brief look at how SCN works in database recovery.

When the database shuts down normally (shutdown immediate/normal), it will do a checkpoint first, write the data in the log file to the data file, and update the control file, the SCN in the data file (including the Stop SCN in the control file) to the latest SCN.

Database exception / unexpected shutdown will not update or only update part of the Stop SCN.

When the database starts, Oracle first checks whether each Datafile Checkpoint SCN in the control file is the same as the Start SCN in the data file, and then checks that each Datafile Checkpoint SCN and Stop SCN are the same. If a difference is found, the missing SCN is found in the Redo Log and rewritten to the data file for recovery. The specific data recovery process will not be discussed here.

As an important mechanism in Oracle, SCN plays a "controller" role in many important functions. Understand the generation and implementation of SCN, and help DBA understand and deal with the problems of recovery, DG, and Streams replication.

Finally, using SCN mechanism, some practical functions are added in Oracle10g and 11g-database flashback, database load reappearance and so on.

The above is all the content of the article "sample Analysis of SCN Mechanism in oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more 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.