mysql中的scn_Oracle数据库中几种常见的SCN

控制文件中的SCN

数据文件头的SCN

数据块中的SCN

日志文件头中的SCN

事务SCN

内存中的SCN

一 控制文件中的SCN

1.1 数据库SCN

数据库SCN表示最近一次全量checkpoint操作时的SCN

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

1744125

dump控制文件语法

alter session set events 'immediate trace name controlf level n';

1 文件头信息

2 level 1+数据库信息+检查点信息

3 level 2+可重用节信息

10 level 3

dump控制文件获取到的数据库SCN为0x00000000001a9cfd，转换为十进制为1744125

*** 2017-02-15T10:59:12.367312+08:00

DUMP OF CONTROL FILES, Seq # 1522 = 0x5f2

V10 STYLE FILE HEADER:

Compatibility Vsn = 203423744=0xc200000

Db ID=1463703229=0x573e56bd, Db Name='ORCL'

Activation ID=0=0x0

Control Seq=1522=0x5f2, File size=646=0x286

File Number=0, Blksiz=16384, File Type=1 CONTROL

***************************************************************************

DATABASE ENTRY

***************************************************************************

(size = 316, compat size = 316, section max = 1, section in-use = 1,

last-recid= 0, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 1, numrecs = 1)

02/14/2017 10:28:13

DB Name "ORCL"

Database flags = 0x00404000 0x00001000 0x00000080

Controlfile Creation Timestamp  02/14/2017 10:28:13

Incmplt recovery scn: 0x0000000000000000

Resetlogs scn: 0x0000000000157e2e Resetlogs Timestamp  02/14/2017 10:28:15

Prior resetlogs scn: 0x0000000000000001 Prior resetlogs Timestamp  01/26/2017 13:52:29

Redo Version: compatible=0xc200000

#Data files = 4, #Online files = 4

Database checkpoint: Thread=1 scn: 0x00000000001a9cfd

Threads: #Enabled=1, #Open=1, Head=1, Tail=1

1.2 数据文件SCN

通过 V$DATAFILE查询保存在控制文件中的数据文件SCN 数据文件头SCN

v$datafile.checkpoint_change#

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

1744125

1744125

1744125

1744125 STOP SCN

数据库打开或异常关闭，该值为空或无穷大0xffffffffffffffff

SQL> select last_change# from v$datafile;

LAST_CHANGE#

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

SQL> CREATION SCN

数据文件创建时的SCN

SQL> select creation_change# from v$datafile;

CREATION_CHANGE#

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

7

4665

1406609

29999

从dump的控制文件中获取到数据文件的SCN。一下为1号数据文件在控制文件中的SCN

***************************************************************************

DATA FILE RECORDS

***************************************************************************

(size = 520, compat size = 520, section max = 100, section in-use = 7,

last-recid= 18, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 11, numrecs = 100)

DATA FILE #1:

name #6: /oradata/ORCL/datafile/o1_mf_system_db4tp1o9_.dbf

creation size=0 block size=8192 status=0xe flg=0x1 head=6 tail=6 dup=1

pdb_id 0, tablespace 0, index=2 krfil=1 prev_file_in_ts=0 prev_file_in_pdb=0

unrecoverable scn: 0x0000000000000000 01/01/1988 00:00:00

Checkpoint cnt:61 scn: 0x00000000001a9cfd 02/15/2017 10:20:16

Stop scn: 0xffffffffffffffff 02/15/2017 10:17:27

Creation Checkpointed at scn:  0x0000000000000007 01/26/2017 13:52:40 thread:0 rba:(0x0.0.0)

1.3 CHECKPOINT PROGRESS RECORDS中的SCN

CHECKPOINT PROGRESS RECORDS中的on disk scn表示当前系统最新rba对应的scn，有ckpt进程每3秒更新一次。

如果数据库异常宕机，那么crash recovery时，服务器进程至少要应用到该scn为止

dump控制文件后能获得CHECKPOINT PROGRESS RECORDS中的SCN，如下所示

***************************************************************************

CHECKPOINT PROGRESS RECORDS

***************************************************************************

(size = 8180, compat size = 8180, section max = 11, section in-use = 0,

last-recid= 0, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 2, numrecs = 11)

THREAD #1 - status:0x2 flags:0x0 dirty:6

low cache rba:(0x4.853.0) on disk rba:(0x4.86b.0)

on disk scn: 0x00000000001aa3f5 02/15/2017 10:58:28resetlogs scn: 0x0000000000157e2e 02/14/2017 10:28:15

heartbeat: 936031722 mount id: 1463768667

二 数据文件头中的SCN

v$datafile_header creation_change#

数据文件创建时的SCN

SQL> select file#,creation_change# from v$datafile_header;

FILE# CREATION_CHANGE#

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

1          7

3           4665

4        1406609

7          29999 checkpoint_change#

表示数据文件头当前的SCN

SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#

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

1          1744125

3          1744125

4          1744125

7          1744125 resetlogs_change#

表示数据库以resetlogs方式打开时的SCN 在dump的控制文件中也能看到Resetlogs scn: 0x0000000000157e2e

SQL> select file#,resetlogs_change# from v$datafile_header;

FILE# RESETLOGS_CHANGE#

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

1         1408558

3         1408558

4         1408558

7         1408558 change#

表示数据文件冻结时的SCN。在做数据文件在线热备时，常用begin backup命令将数据文件头冻结，表明chang#这个点开始对数据文件进行备份

SQL> select file#,change# from v$backup;

FILE#    CHANGE#

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

1        0

3        0

4        0

7        0

SQL> alter database begin backup;

Database altered.

SQL> select file#,change# from v$backup;

FILE#    CHANGE#

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

1    1957900

3    1957900

4    1957900

7    1957900

以上SCN可以通过dump数据文件头来进行观察 0x00000000001de00c转换为十进制就是1957900

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_24591.trc

SQL> alter session set events 'immediate trace name file_hdrs level 3';

Session altered.

V10 STYLE FILE HEADER:

Compatibility Vsn = 203423744=0xc200000

Db ID=1463703229=0x573e56bd, Db Name='ORCL'

Activation ID=0=0x0

Control Seq=1740=0x6cc, File size=102400=0x19000

File Number=1, Blksiz=8192, File Type=3 DATA

Tablespace #0 - SYSTEM  rel_fn:1

Creation   at   scn: 0x0000000000000007 01/26/2017 13:52:40

Backup taken at scn: 0x00000000001de00c 02/15/2017 13:56:00 thread:1 reset logs count:0x37c90b3f scn: 0x0000000000157e2e

prev reset logs count:0x37b02e9d scn: 0x0000000000000001

recovered at 02/15/2017 13:53:24

status:0x2001 root dba:0x00400208 chkpt cnt: 70 ctl cnt:69

begin-hot-backup file size: 102400

Checkpointed at scn:  0x00000000001de00c 02/15/2017 13:56:00

三 数据块中的SCN 数据块变化时的SCN

很多操作会引起数据块改变，如业务数据的变化、块清理等。

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_29430.trc

SQL> alter system dump datafile 1 block 16;

System altered.

SQL> !more /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_29430.trc

Start dump data blocks tsn: 0 file#:1 minblk 16 maxblk 16

Block dump from cache:

Dump of buffer cache at level 4 for pdb=0 tsn=0 rdba=4194320

Block dump from disk:

buffer tsn: 0 rdba: 0x00400010 (1/16)

scn: 0xa seq: 0x01 flg: 0x04 tail: 0x000a1e01

frmt: 0x02 chkval: 0x80ab type: 0x1e=KTFB Bitmapped File Space Bitmap

Hex dump of block: st=0, typ_found=1

…… 数据块事务槽中的SCN

数据块中数据行的SCN

可以通过伪列ORA_ROWSCN查看数据行改变时的SCN

SQL> select id,ora_rowscn from aa;

ID ORA_ROWSCN

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

1    1960725

2    1960725

3    1960725

四 日志文件头中的SCN FISRT_CHANGE#

表示该在线日子文件被重用时的SCN NEXT_CHANGE#

表示该日志文件重用结束时的SCN RESETLOGS_CHANGE#

表示数据库已RESETLOGS方式打开时的SCN。通常和数据文件头的RESETLOGS_CHANGE#相同。

SQL> select first_change#,next_change#,resetlogs_change# from v$log_history  where sequence#=5;

FIRST_CHANGE# NEXT_CHANGE# RESETLOGS_CHANGE#

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

1856486       1956862         1408558

dump日志文件头获取上述SCN,

0x00000000001c53e6十进制1856486，0x00000000001ddbfe十进制1956862，0x0000000000157e2e十进制1408558

SQL> alter session set events 'immediate trace name redohdr level 3';

Session altered.

LOG FILE #2:

name #2: /oradata/ORCL/onlinelog/o1_mf_2_db4tt00w_.log

Thread 1 redo log links: forward: 3 backward: 1

siz: 0x64000 seq: 0x00000005 hws: 0x6 bsz: 512 nab: 0x478 flg: 0x1 dup: 1

Archive links: fwrd: 0 back: 0 Prev scn: 0x00000000001a9cfc

Low scn: 0x00000000001c53e6 02/15/2017 13:52:08

Next scn: 0x00000000001ddbfe 02/15/2017 13:53:25

FILE HEADER:

Compatibility Vsn = 203423744=0xc200000

Db ID=1463703229=0x573e56bd, Db Name='ORCL'

Activation ID=1463733693=0x573ecdbd

Control Seq=1735=0x6c7, File size=409600=0x64000

File Number=2, Blksiz=512, File Type=2 LOG

Format ID is 18

redo log key is 30c0dccb5d4341b7f1be4b600b3604e7

redo log key flag is 5

descrip:"T 0001, S 0000000005, SCN 0x00000000001c53e6-0x00000000001ddbfe"

thread: 1 nab: 0x478 seq: 0x00000005 hws: 0x6 eot: 0 dis: 0

reset logs count: 0x37c90b3f

Reset Logs scn: 0x0000000000157e2e

Low scn: 0x00000000001c53e6 02/15/2017 13:52:08

Next scn: 0x00000000001ddbfe 02/15/2017 13:53:25

Enabled scn: 0x0000000000157e2e 02/14/2017 10:28:15

Thread closed scn: 0x00000000001ddbfc 02/15/2017 13:52:30

Disk cksum: 0x6c0a Calc cksum: 0x0

Terminal Recovery Stop scn: 0x0000000000000000

Terminal Recovery Stamp  01/01/1988 00:00:00

Most recent redo scn: 0x0000000000000000

Largest LWN: 0 blocks

Real Next scn: 0x00000000001c555c

Previous Resetlogs scn: 0x0000000000000001

Miscellaneous flags: 0x800000

Miscellanous second flags: 0x0

Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000000000000000

Zero blocks: 8

Enabled redo threads: 1

五 事务开始时的SCN

SQL> update aa set id=10 where rownum=1;

1 row updated.

SELECT xidusn, start_scnb, start_scnw

FROM v$transaction

WHERE ses_addr = (SELECT saddr

FROM v$session

WHERE sid = (SELECT sid

FROM v$mystat

7                                    WHERE ROWNUM = 1));

XIDUSN START_SCNB START_SCNW

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

1    1960725           0

SQL> select name from v$rollname where usn=10;

NAME

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

_SYSSMU10_2925533193$

SQL> alter system dump undo header '_SYSSMU10_2925533193$';

System altered.

六 数据库的CURRENT SCN

SQL> ORADEBUG DUMPvar SGA kcsgscn

kcslf kcsgscn_ [0600113B8, 0600113E8) = 001DF7C8 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60049740 00000000

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

1963968

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

1964014