Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Scn explanation in Oracle

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

SCN

System Change Numbers (SCNs) & System Commit Numbers (system change number & system submission number)

A system change number (SCN) is a logical, internal time stamp used by Oracle Database

*

SCN features:

SCN itself is a kind of time.

SCN is a monotonously increasing sequence from the time you build a library; when you run out of SCN numbers, the database won't run; but it won't run out for hundreds of years!

SCN is an important mechanism, which plays an important role in various functions such as data recovery, Data Guard, Streams replication, synchronization between RAC nodes, etc.

*

Types of Oracle database SCN:

1. The current SCN of the database

SYS@orcl11g > select current_scn from v$database

CURRENT_SCN

-

1159919

SYS@orcl11g > select dbms_flashback.get_system_change_number () from dual

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER ()

1159919

SYS@orcl11g > select dbms_flashback.get_system_change_number () from dual

2 union all

3 * select current_scn from v$database

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER ()

1159919

1159919

two。 Database checkpoint scn (system scn)-the source control file that identifies the data update information of the current database (used to indicate the update behavior of the database)

SYS@orcl11g > select checkpoint_change#,current_scn from v$database

CHECKPOINT_CHANGE# CURRENT_SCN

1666278 1676455

3. Checkpoint scn of data files-source control files that identify when each file is synchronized

SYS@orcl11g > select file#,checkpoint_change# from v$datafile order by 1

FILE# CHECKPOINT_CHANGE#

1 1666278

2 1666278

3 1666278

4 1666278

5 1666278

6 1666278

7 1384282

8 1666278

9 1666278

SYS@orcl11g > select tablespace_name,file_id from dba_data_files where file_id=7

TABLESPACE_NAME FILE_ID

TBS02 7

SYS@orcl11g > select tablespace_name,status from dba_tablespaces where tablespace_name='TBS02'

TABLESPACE_NAME STATUS

TBS02 OFFLINE

4. Checkpoint SCN of the data file header-- derived from the data file header

SYS@orcl11g > select file#,checkpoint_change# from v$datafile_header

FILE# CHECKPOINT_CHANGE#

1 1676699

2 1676699

3 1676699

4 1676699

5 1676699

6 1676699

7 1676699

8 0

9 1676699

9 rows selected.

If it is 0, it means that the data file is offline and will no longer be read.

5. Last_change# of data file-- close SCN of data file (end SCN)

SYS@orcl11g > select file#,checkpoint_change#,last_CHANGE# from v$datafile

FILE# CHECKPOINT_CHANGE# LAST_CHANGE#

-

1 1737727 (null)

2 1737727 (null)

3 1737727 (null)

4 1737727 (null)

5 1737727 (null)

6 1737727 (null)

7 1737727 (null)

8 1737727 (null)

9 1737727 (null)

So how does the system produce the latest SCN?

It 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.

The conversion function between TIME and SCN:

SYS@orcl11g > select scn_to_timestamp (1906338) from dual

SCN_TO_TIMESTAMP (1906338)

26-JUN-13 10.52.09.000000000 AM

SYS@orcl11g > select timestamp_to_scn (to_timestamp ('2013-06-26 10 to_timestamp 53 to_timestamp 53 hh34:mi:ss')) from dual

TIMESTAMP_TO_SCN (TO_TIMESTAMP ('2013-06-2610, 53-51, 27-31, 5, 5, 5, 5, 5, 5, 5, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,

-

1906370

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report