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

This article mainly introduces how Oracle checkpoints related to the SCN, the article is very detailed, has a certain reference value, interested friends must read it!

1. SCN involved in checkpoint

(1)。 The header in the data file initiates the scn of the checkpoint

Stored in the header of each data file

Select name,checkpoint_change#

From v$datafile_header

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.

When the database is restarted, Oracle compares the startup scn (in each data file) in the header with the database file checkpoint scn (in the control file). 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 the redo records of all data blocks have been committed, and no changes to the database have been lost during the shutdown process, so no recovery operation is required to start the database this time, and the database can be opened at this time.

(2)。 Data file checkpoint SCN

Stored in the control file:

SELECT T.NAME,T.CHECKPOINT_CHANGE#,b.tablespace_name

FROM V$DATAFILE T,dba_data_files b

Where t.FILE#=b.file_id

(3)。 Termination scn of data files

Stored in the control file

Select name,last_change#

From v$datafile

Terminating SCN is only involved when the database is closed or the tablespace is offline. During normal database operations, the terminating scn of all data files in online read-write mode is null.

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.

(4)。 System checkpoint SCN

Stored in the control file

Select t.CHECKPOINT_CHANGE#

From v$database t

(5)。 Scn of the log file

SCN,t.FIRST_TIME,t.NEXT_CHANGE# at the beginning of select t.FIRST_CHANGE# and SCN at the end of SCN

From V$log_History t

Order by t.FIRST_TIME

two。 When a full checkpoint occurs:

Data file header that identifies the enabled scn that was last processed.

On the other hand, the CKPT process updates the Start SCN recorded on the header of all data files (regardless of whether the data in redo log affects the data file or not) to Next SCN; (can be queried through the field checkpoint_change# of the view v$datafile_header)

At the same time, update the System Checkpoint SCN in the control file to Next SCN; (can be queried through the field checkpoint_change# of the view v$database)

The Datafile Checkpoint corresponding to each data file is also updated to Next SCN; (which can be queried through the field checkpoint_change# of the view v$datafile)

All data recorded by redo from Low SCN to Next SCN is written to the data file by the DBWn process.

3. How does the system generate 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:

Select dbms_flashback.get_system_change_number, SCN_TO_TIMESTAMP (dbms_flashback.get_system_change_number)

From dual

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

Select timestamp_to_scn (SYSTIMESTAMP) as scn

From dual

4. In addition to reflecting transaction data changes and keeping synchronization, SCN also acts as a "heartbeat" of the system-the system refreshes the system SCN every 3 seconds or so.

The above is all the content of the article "how to checkpoint the SCN involved 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.