In-depth Oracle SCN Research

I. SCN description

I have also compiled several Oracle SCN articles as follows:

An in-depth study on the relationship between the time modification problem and SCN of the Oracle DB Server System

Description of Oracle Blockscn/commit scn/cleanout scn

RedoLogCheckpoint and SCN relationship

Here is a summary.

You can use the following SQL statement to view the SCN of Oracle:

SQL> select CURRENT_SCN from v $ database;

CURRENT_SCN

-----------

3713849

The preceding result returns a string of numbers.

However, Oracle does not use numbers to store SCN internally.

In Oracle, SCN is stored in two parts: scn wrap and scn base. In fact, the SCN length is 48 bits, that is, it is actually a 48-bit integer. It may be because in the early years, only 32-bit or even 16-bit data can be processed, so it is artificially divided into low 32-bit (scnbase) and high 16-bit (scn wrap ).

Why is it not designed to be 64-bit? This may be because the 48-bit length is enough to save two bytes of space :). Then, the 48-digit long integer SCN is a large number at the maximum of 2 ^ 48 (48 power of 2, 281 trillion, 281474976710656.

Here is an important formula:

SCN = (SCN_WRP * 4294967296) + SCN_BAS

The data value of SCN can be calculated based on the formula above.

In many transaction-related records, the records are scn wrap and scn base.

SQL> select START_SCNB, START_SCNW from v $ transaction;
SQL> desc smon_scn_time
Name Null? Type
-----------------------------------------------------------------------------
THREAD NUMBER
TIME_MP NUMBER
TIME_DP DATE
SCN_WRP NUMBER
SCN_BAS NUMBER
NUM_MAPPINGS NUMBER
TIM_SCN_MAP RAW (1200)
SCN NUMBER
ORIG_THREAD NUMBER

It is included in our Data block and uses scn wrp and scn base.

Ii. Test Cases

Here we use DataBlock 92967 on Data File 1 as an example.

BBED> show
FILE #1
BLOCK #92967.
OFFSET 0
DBA 0x00416b27 (4287271, 92967)
FILENAME/u01/app/oracle/oradata/dave/system.256.816661027
BIFILE bifile. bbd
LISTFILE/u01/filelist.txt
Blocsize 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 1, 8192
LOGFILE log. bbd
SPOOL No

 

BBED> p kcbh
Struct kcbh, 20 bytes @ 0
Ub1 type_kcbh @ 0 0x06
Ub1 frmt_kcbh @ 1 0xa2
Ub1 sparejavaskcbh @ 2 0x00
Ub1 spare2_kcbh @ 3 0x00
Ub4 rdba_kcbh @ 4 0x00416b27
Ub4 bas_kcbh @ 8 0x003566cc
Ub2 wrp_kcbh @ 12 0x0000
Ub1 seq_kcbh @ 14 0x01
Ub1 flg_kcbh @ 15 0x04 (KCBHFCKV)
Ub2 chkval_kcbh @ 16 0xc36e
Ub2 spare3_kcbh @ 18 0x0000

Result of Block Dump:

Start dump data blocks tsn: 0 file #: 1 minblk 92967 maxblk 92967
Block dump from cache:
Dump of buffer cache at level 4 for tsn = 0 rdba = 4287271
Block dump from disk:
Buffer tsn: 0 rdba: 0x00416b27 (1/92967)
Scn: 0x0000. 003566cc seq: 0x01 flg: 0x04 tail: 0x66cc0601
Frmt: 0x02 chkval: 0xc36e type: 0x06 = transdata
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0x00007FBE18328A00 to0x00007FBE1832AA00
7FBE18328A00 2017a206 00416B27 003566CC04010000 [...... 'ka ......]
7FBE18328A10 2017c36e 00000001 00012965003566B8 [n ...... e) ...... f5]
......
7FBE1832A9F0 0144494C 014E014E 02C1024E66CC0601 [LID. N. N... f]

The SCN here is: 0x0000. 003566cc

At first glance, it is estimated that it is very dizzy. After contacting us with the above BBED results, we should understand it.

Ub4 bas_kcbh @ 8 0x003566cc

Ub2 wrp_kcbh @ 12 0x0000

The SCN format is SCNWRAP. scn base.

We use the formula described in section 1 to calculate the SCN:

SQL> select to_number ('3566cc', 'xxxxxxx') from dual;

TO_NUMBER ('3566cc', 'xxxxxxx ')

-----------------------------

3499724

-- The current SCN of the system is:

SQL> select CURRENT_SCN from v $ database;

CURRENT_SCN

-----------

3715593

The SCN for modifying our block is:

SCN = (0*4294967296) + 3499724 = 3499724

That is, when our block: 92967 is modified, the corresponding SCN is 3499724.