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 1 4 52428800 1 NO INACTIVE 1103335 2013-1-14 13:30:38

2 2 1 5 52428800 1 NO CURRENT 1131856 2013-1-18 1:33:36

3 3 1 3 52428800 1 YES INACTIVE 1014044 2013-1-7 20:08:55

oracle scn系統改變號

相關文章