scn的定义
scn(system change number)就是通常所说的系统改变号,是数据库中非常重要的一个数据结构,
用以标识数据库在某个确切时刻提交的版本.在事务提交时,它被赋予一个唯一的标示事务的scn.
scn同时被作为oracle数据库的内部时钟机制,可以被看作逻辑时钟,每一个数据库都有一个全局的
scn生产器.
作为数据库内部的逻辑时钟,数据库事务依照scn而排序,oracle也依据scn来实现一致性读(
read consistency)等重要数据库功能,另外对于分布式事务(distributed transactions),
scn也极为重要,scn在数据库中是唯一的,并随时间而增加,但是scn可能并不连续,除非重建数据库,
要不然scn的值永远都不会被重置为0.
scn来维护数据库的一致性,并通过scn实施oracle至关重要的恢复机制.
scn在数据库中是无处不在的,常见的事务表,控制文件,数据文件头,日志文件,数据块头等都记录
有scn值.冠以不同前缀,scn也有了不同的名称,比如检查点scn(checkpoint scn),resetlogs scn等.
scn由两部分组成,高位scn wrap由2 bytes记录,低位scn base由4 bytes记录.
scn的获取方式
可以通过如下几种方式获得数据库的当前或近似scn.
(1)从oracle9i开始
可以通过使用dbms_flashback.get_system_change_number来获得:
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1131096
(2)oracle9i之前
可以通过查询x$ktuxe([K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry (table))获得系统
最接近当前值的scn
select max(ktuxescnw*power(2,32)+ktuxescnb) scn_now from x$ktuxe; SQL> select max(ktuxescnw*power(2,32)+ktuxescnb) scn_now from x$ktuxe; SCN_NOW ---------- 1131134
(3)从oracle10g开始
在v$database视图中增加了current_scn字段,通过查询该字段可以获得数据库的当前scn值;
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1131155
(4)从内存中取得scn信息
通过oradebug工具可以直接读取内存中用于记录scn的内存变量;
SQL> oradebug setmypid
Statement processed.
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009104, 20009124) = 00000000 001142B5 000002AE 00000000 00000000
00000000 00000000 20008F10
SQL> select to_number('1142B5','xxxxxxxxx') scn from dual;
SCN
----------
1131189
scn的进一步说明
系统当前scn并不是在任何的数据库操作发生时都会改变,scn通常在事务提交或回滚时改变,
在控制文件,数据文件头,数据块,日志文件头,日志文件change vector中都有scn,但其作用
各不相同.
(1)数据文件头中包含了该数据文件的checkpoint scn,表示该数据文件最近一次执行检查点
操作时的scn.
从控制文件的dump文件中,可以得到以下内容:
*************************************************************************** DATA FILE RECORDS *************************************************************************** (size = 428, compat size = 428, section max = 100, section in-use = 6, last-recid= 45, old-recno = 0, last-recno = 0) (extent = 1, blkno = 11, numrecs = 100) DATA FILE #1: (name #8) /u01/app/oracle/product/10.2.0/oradata/jingyong/system01.dbf creation size=0 block size=8192 status=0xe head=8 tail=8 dup=1 tablespace 0, index=1 krfil=1 prev_file=0 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00 Checkpoint cnt:177 scn: 0x0000.00113c5a 01/18/2013 00:35:03 Stop scn: 0xffff.ffffffff 01/18/2013 00:34:20 Creation Checkpointed at scn: 0x0000.00000009 06/30/2005 19:10:11
对于每一个数据文件都包含了一个这样的条目,记录该文件的检查点scn的值以及检查点发生的
时间,这里的checkpoint scn,stop scn以及checkpoint cnt都是非常重要的数据结构.
同样可以通过命令转储数据文件头,观察其具体信息及检查点记录等;
DATA FILE #1: (name #8) /u01/app/oracle/product/10.2.0/oradata/jingyong/system01.dbf creation size=0 block size=8192 status=0xe head=8 tail=8 dup=1 tablespace 0, index=1 krfil=1 prev_file=0 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00 Checkpoint cnt:177 scn: 0x0000.00113c5a 01/18/2013 00:35:03 Stop scn: 0xffff.ffffffff 01/18/2013 00:34:20 Creation Checkpointed at scn: 0x0000.00000009 06/30/2005 19:10:11 thread:0 rba:(0x0.0.0) ... Offline scn: 0x0000.000e487b prev_range: 0 Online Checkpointed at scn: 0x0000.000e487c 01/07/2013 02:11:19 thread:1 rba:(0x1.2.0) ..... Hot Backup end marker scn: 0x0000.00000000 aux_file is NOT DEFINED V10 STYLE. FILE HEADER: Compatibility Vsn = 169869568=0xa200100 Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG' Activation ID=0=0x0 Control Seq=4491=0x118b, File size=76800=0x12c00 File Number=1, Blksiz=8192, File Type=3 DATA Tablespace #0 - SYSTEM rel_fn:1 Creation at scn: 0x0000.00000009 06/30/2005 19:10:11 Backup taken at scn: 0x0000.001128c1 01/17/2013 04:06:35 thread:1 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000 recovered at 01/14/2013 13:30:37 status:0x2004 root dba:0x00400179 chkpt cnt: 177 ctl cnt:176 begin-hot-backup file size: 76800 Checkpointed at scn: 0x0000.00113c5a 01/18/2013 00:35:03 thread:1 rba:(0x4.2feb.10) ... Backup Checkpointed at scn: 0x0000.00112909 01/17/2013 04:09:44 thread:1 rba:(0x4.2a47.10) .... External cache id: 0x0 0x0 0x0 0x0 Absolute fuzzy scn: 0x0000.00000000 Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00 Terminal Recovery Stamp 01/01/1988 00:00:00 Platform. Information: Creation Platform. ID: 10 Current Platform. ID: 10 Last Platform. ID: 10
在以上输出中,file header部份这前信息来自控制文件,之后信息来自数据文件头,在数据库的启动过程中,
需要依赖两部分信息进行比对判断,从而确保数据库的一致性和判断是否需要进行恢复.
(2)日志文件头中包含了Low scn和next scn
这两个scn标示该日志文件包含有介于low scn到next scn的重做信息,对于current的日志文件
(当前正在被使用的redo logfile),其最终scn不可知,所以next scn被设置为无穷大,也就是ffffffff
看一下日志文件的情况;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 4 52428800 1 NO CURRENT 1103335 2013-1-14 1
2 1 2 52428800 1 YES INACTIVE 981195 2013-1-7 20
3 1 3 52428800 1 YES INACTIVE 1014044 2013-1-7 20
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1131849
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 4 52428800 1 NO ACTIVE 1103335 2013-1-14 1
2 1 5 52428800 1 NO CURRENT 1131856 2013-1-18 1
3 1 3 52428800 1 YES INACTIVE 1014044 2013-1-7 20
可以看到,scn 1131849显然位于log group#为1的日志文件中,该日志文件包含了scn自1103335到1131856的redo信息.
oracle在进行恢复时就需要根据低scn和高scn来确定需要的恢复信息位于哪一个日志或归档日志文件中.
通过控制文件转储,可以在控制文件中找到关于日志文件的信息:
SQL> alter session set events 'immediate trace name controlf level 8'; Session altered. SQL> select 2 d.value||'/'||lower(rtrim(i.instance, 3 chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name 4 from ( select p.spid 5 from v$mystat m, 6 v$session s,v$process p 7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, 8 ( select t.instance from v$thread t,v$parameter v 9 where v.name = 'thread' and 10 (v.value = 0 or t.thread# = to_number(v.value))) i, 11 ( select value from v$parameter 12 where name = 'user_dump_dest') d 13 / TRACE_FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/admin/jingyong/udump/jingyong_ora_3310.trc *************************************************************************** LOG FILE RECORDS *************************************************************************** (size = 72, compat size = 72, section max = 16, section in-use = 3, last-recid= 9, old-recno = 0, last-recno = 0) (extent = 1, blkno = 10, numrecs = 16) LOG FILE #1: (name #3) /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log Thread 1 redo log links: forward: 2 backward: 0 siz: 0x19000 seq: 0x00000004 hws: 0x24 bsz: 512 nab: 0x3313 flg: 0x0 dup: 1 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000f791c Low scn: 0x0000.0010d5e7 01/14/2013 13:30:38 Next scn: 0x0000.00114550 01/18/2013 01:33:36 LOG FILE #2: (name #2) /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log Thread 1 redo log links: forward: 3 backward: 1 siz: 0x19000 seq: 0x00000005 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.0010d5e7 Low scn: 0x0000.00114550 01/18/2013 01:33:36 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00 LOG FILE #3: (name #1) /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log Thread 1 redo log links: forward: 0 backward: 2 siz: 0x19000 seq: 0x00000003 hws: 0x1b bsz: 512 nab: 0x819c flg: 0x1 dup: 1 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000ef8cb Low scn: 0x0000.000f791c 01/07/2013 20:08:55 Next scn: 0x0000.0010d5e7 01/14/2013 13:30:38
从以上信息可以注意到,log file#2是当前的日志文件,该文件拥有的next scn为无穷大.
同样可以直接dump日志文件的方式来进行转储:
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------------------------
3 STALE ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log
2 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log
1 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log
SQL> alter system dump logfile '/u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log';
System altered.
SQL> select
2 d.value||'/'||lower(rtrim(i.instance,
3 chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
4 from ( select p.spid
5 from v$mystat m,
6 v$session s,v$process p
7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
8 ( select t.instance from v$thread t,v$parameter v
9 where v.name = 'thread' and
10 (v.value = 0 or t.thread# = to_number(v.value))) i,
11 ( select value from v$parameter
12 where name = 'user_dump_dest') d
13 /
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_3318.trc
DUMP OF REDO FROM FILE '/u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=3141682910=0xbb4246de
Control Seq=4496=0x1190, File size=102400=0x19000
File Number=1, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000004, SCN 0x00000010d5e7-0x000000114550"
thread: 1 nab: 0x3313 seq: 0x00000004 hws: 0x24 eot: 0 dis: 0
resetlogs count: 0x2fecc5c7 scn: 0x0000.000e487c (936060)
resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
prev resetlogs count: 0x2fecb1fc scn: 0x0000.000df5e6 (914918)
prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
Low scn: 0x0000.0010d5e7 (1103335) 01/14/2013 13:30:38
Next scn: 0x0000.00114550 (1131856) 01/18/2013 01:33:36
Enabled scn: 0x0000.000e487c (936060) 01/07/2013 02:11:19
Thread closed scn: 0x0000.00113c59 (1129561) 01/18/2013 00:34:20
Disk cksum: 0x3c58 Calc cksum: 0x3c58
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 1914 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x0
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
在上面的信息中可以看到
Low scn: 0x0000.0010d5e7 (1103335) 01/14/2013 13:30:38
Next scn: 0x0000.00114550 (1131856) 01/18/2013 01:33:36
与v$log视图中log group#1日志文件记录的scn的信息是一致的
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ------------------
1 1 14 52428800 1 NO INACTIVE 1103335 2013-1-14 13:30:38
2 2 15 52428800 1 NO CURRENT 1131856 2013-1-18 1:33:36
3 3 13 52428800 1 YES INACTIVE 1014044 2013-1-7 20:08:55