oracle scn系统改变号

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

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.