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:28
resetlogs 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