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

In-depth Analysis-detailed interpretation of Oracle SCN Mechanism

Https://mp.weixin.qq.com/s?__biz=MjM5MDAxOTk2MQ==&mid=2650276971&idx=1&sn=b5fb89b351d5b5bedd6353ff9c0b2157&chksm=be479c7d8930156bf73bd87f0bac869029f7341b3fdb4ed26a838b4e401c811116669acd5499&mpshare=1&scene=24&srcid=0927zxmXBLuBo3yxm7qsFYOy#rd

Http://blog.chinaunix.net/uid-20274021-id-1969571.html

SCN, the system change number (System Change Number), is a timestamp that defines the committed version of the database at some point in time. Oracle assigns a unique SCN to each committed transaction. The value of SCN is the logical point in time to make changes to the database. Oracle uses this number to record changes to the database. SCN can also be said to be ubiquitous in the database. Data file headers, control files, data block headers, log files, and so on, are all marked with SCN. Just like this, the consistency maintenance of database is closely related to SCN. Whether it is data backup, recovery is inseparable from SCN.

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

Step 1: start the transaction

Step 2: find the required data block in buffer cache, and if not, load it into buffer cache from the data file

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

Step 4: transaction commit, and the LGWR process writes the log entries of "dirty data" in log buffer to redo log file.

Step 5: when the checkpoint,CKPT process updates the information in the headers of all data files, the DBWn process is responsible for the dirty data in the Buffer Cache

Write 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 is accidentally down in the above intermediate link, when it is restarted

How do you know which data has been written to the data file and which has not been written? (similarly, it also exists in DG and streams

Similar question: which data in redolog is replicated last time 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 the kind of thing that will only increase.

The added features ensure that Oracle knows what should be restored and which should be copied.

First of all, let's introduce four SCN concepts.

1. System checkpoint scn (System Checkpoint SCN)

When a checkpoint checkpoint action is completed, Oracle stores the SCN of the system checkpoint in the control file.

Select checkpoint_change# from v$database

2, data file checkpoint scn (Datafile Checkpoint SCN)

When a checkpoint action is completed, Oracle stores the Datafile Checkpoint SCN of each data file in a separate control file.

Select name,checkpoint_change# from v$datafile

3. Start scn (Start SCN)

Oracle stores the scn of this checkpoint in the header of each data file, a value called startup scn, which is used to check whether the database recovery media recovery needs to be performed when the database instance is started.

Select name,checkpoint_change# from v$datafile_header

4. Terminate scn (Stop SCN)

The termination scn of each data file is stored in the control file. This SCN number is used to check whether the database startup process requires instance recovery.

Select name,last_change# from v$datafile

5.media recovery and instance recovery

1). Media recovery needs to be restored with previous backups, while INSTANCE RECOVERY is not needed.

2). Media recovery usually occurs when the data files in the database are damaged, and the recovery needs to be done by using previous backups and needs to be handled manually.

3). Instance recovery is a recovery that occurs when the instance is shut down abnormally. It comes from INSTANCE itself and does not require human intervention.

6. Scn value during database operation

1)。 After the database is open and running, the system checkpoint in the control file, the data file checkpoint scn in the control file, and the startup scn in each data file header are the same. The termination scn of each data file in the control file is null.

2)。 In the process of safely shutting down the database, the system performs a checkpoint action, and the terminating scn of all data files is set to the value that starts scn in the header of the data file.

3)。 When the database is restarted, Oracle compares the startup scn in the header with the data file checkpoint scn, and if the two values match each other, oracle then compares the startup scn in the data file header with the termination scn of the data file in the control file. If the two values are the same, it means that most of the data blocks have been committed, and all changes to the database have not been lost in the process of shutting down the database, so no recovery operation is required to start the database this time. At this point, the database can be opened. When all databases are open, the value of the data file termination scn stored in the control file is changed to null again, indicating that the data file is open and ready for use.

7.SCN and Database Startup

In the process of database startup, when System Checkpoint SCN, Datafile Checkpoint SCN and Start SCN are all the same, the database can start normally without media recovery. When one of the three is different, you need to do media recovery. If End SCN is NULL during startup, you need to do instance recovery. Oracle first checks to see if media recovery is needed during startup, and then checks if instance recovery is needed.

8.SCN and database shutdown

If the database shuts down normally, a checkpoint will be triggered and the END SCN of the data file will be set to the Start SCN of the corresponding data file. When the database starts, it is found that they are consistent, so there is no need to do instance recovery. After the database starts normally, ORACLE sets END SCN to NULL. If the database is shut down abnormally, the END SCN will be NULL.

9. When do you need using backup controlfile to restore the database

Data file checkpoint scn (Datafile Checkpoint SCN)

Select checkpoint_change# from v$datafile

Start scn (Start SCN)

Select checkpoint_change# from v$datafile_header

If you query the result data file checkpoint scn > = start scn, you do not need to use using backup controlfile

If the query result data file checkpoint scn < starts scn, you need to use using backup controlfile

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.