A-A+
浅析scn
1.什么是scn
系统更改号（system change number），有的叫做 system commit number，这个叫法其实并不准确。scn是oracle的内部单调递增的序列时钟；scn确保事务的acid【原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）】性质；scn在实例恢复中可以剔除掉无用的日志，可以灵活通过scn进行不完全恢复；每个事务都含有scn。
2.scn分几类
2.1 以下scn记录在控制文件中
2.1.1 数据库scn
在控制文件中记录的最近一次的全量checkpoint的scn
sys@ora11gr2> alter system checkpoint;
System altered.
sys@ora11gr2> select CHECKPOINT_CHANGE# from v$database;
CHECKPOINT_CHANGE#
------------------
1320426
1 row selected.
dump控制文件头
sys@ora11gr2> @dump_ctl_hdrs_scn_by_dd 8192 /u01/app/oracle/oradata/ora11gr2/control01.ctl
001425ea
sys@ora11gr2> select conversion_api.to_dec('001425ea') from dual;
CONVERSION_API.TO_DEC('001425EA')
---------------------------------
1320426
1 row selected.
sys@ora11gr2> @dump_ctl 2
old 1: ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME CONTROLF LEVEL &&1'
new 1: ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME CONTROLF LEVEL 2'
Session altered.
TRACE_FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_12275.trc
1 row selected.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rhl6 dba_scripts]$ more /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_12275.trc
... ...
***************************************************************************
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)
12/25/2012 10:27:51
DB Name "ORA11GR2"
Database flags = 0x00404001 0x00001200
Controlfile Creation Timestamp 12/25/2012 10:27:56
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.00000001 Resetlogs Timestamp 12/25/2012 10:27:51
Prior resetlogs scn: 0x0000.00000000 Prior resetlogs Timestamp 01/01/1988 00:00:00
Redo Version: compatible=0xb200000
#Data files = 5, #Online files = 5
Database checkpoint: Thread=1 scn: 0x0000.001425ea //1320426
Threads: #Enabled=1, #Open=1, Head=1, Tail=1
... ...
2.1.2 数据文件头scn
这个scn就是判断控制文件和数据文件是否一致的标准之一。
sys@ora11gr2> col name for a50
sys@ora11gr2> select FILE#,CHECKPOINT_CHANGE#,name from v$datafile order by 1;
FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ --------------------------------------------------
1 1320426 /u01/app/oracle/oradata/ora11gr2/system01.dbf
2 1320426 /u01/app/oracle/oradata/ora11gr2/sysaux01.dbf
3 1320426 /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf
4 1320426 /u01/app/oracle/oradata/ora11gr2/users01.dbf
5 1320426 /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf
5 rows selected.
sys@ora11gr2> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header order by 1;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1320426
2 1320426
3 1320426
4 1320426
5 1320426
5 rows selected.
sys@ora11gr2> @dump_ctl 3
old 1: ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME CONTROLF LEVEL &&1'
new 1: ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME CONTROLF LEVEL 3'
Session altered.
TRACE_FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_12302.trc
1 row selected.
***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max = 100, section in-use = 5,
last-recid= 28, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
name #4: /u01/app/oracle/oradata/ora11gr2/system01.dbf
creation size=89600 block size=8192 status=0xe head=4 tail=4 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:319 scn: 0x0000.001425ea 07/15/2014 09:51:01 //1320426
Stop scn: 0xffff.ffffffff 07/15/2014 09:04:41
Creation Checkpointed at scn: 0x0000.00000029 12/25/2012 10:30:40
thread:1 rba:(0x1.3.10)
... ...
DATA FILE #5:
name #9: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf
creation size=128 block size=8192 status=0xe head=9 tail=9 dup=1
tablespace 5, index=6 krfil=5 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:246 scn: 0x0000.001425ea 07/15/2014 09:51:01 //1320426
Stop scn: 0xffff.ffffffff 07/15/2014 09:04:41
Creation Checkpointed at scn: 0x0000.000bbca7 12/17/2013 14:44:15
thread:1 rba:(0x44.b23a.10)
下边2个是从数据文件头中提取scn
sys@ora11gr2> @dump_datafile_hdrs_scn_by_dd 8192 /u01/app/oracle/oradata/ora11gr2/system01.dbf
001425ea
sys@ora11gr2> @dump_datafile_hdrs_scn_by_dd 8192 /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf
001425ea
sys@ora11gr2> @dump_datafile_hdrs
System altered.
TRACE_FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_12447.trc
1 row selected.
[oracle@rhl6 dba_scripts]$ more /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_12447.trc
... ...
DUMP OF DATA FILES: 5 files in database
DATA FILE #1:
name #4: /u01/app/oracle/oradata/ora11gr2/system01.dbf
creation size=89600 block size=8192 status=0xe head=4 tail=4 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:319 scn: 0x0000.001425ea 07/15/2014 09:51:01 //1320426
Stop scn: 0xffff.ffffffff 07/15/2014 09:04:41
Creation Checkpointed at scn: 0x0000.00000029 12/25/2012 10:30:40
thread:1 rba:(0x1.3.10)
... ...
DATA FILE #5:
name #9: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf
creation size=128 block size=8192 status=0xe head=9 tail=9 dup=1
tablespace 5, index=6 krfil=5 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:246 scn: 0x0000.001425ea 07/15/2014 09:51:01 //1320426
Stop scn: 0xffff.ffffffff 07/15/2014 09:04:41
Creation Checkpointed at scn: 0x0000.000bbca7 12/17/2013 14:44:15
thread:1 rba:(0x44.b23a.10)
... ...
BBED> d /v dba 1,1 offset 484 count 4
File: /u01/app/oracle/oradata/ora11gr2/system01.dbf (1)
Block: 1 Offsets: 484 to 487 Dba:0x00400001
-------------------------------------------------------
ea251400 l ê%.. //001425ea=1320426
<16 bytes per line>
BBED> d /v dba 5,1 offset 484 count 4
File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5)
Block: 1 Offsets: 484 to 487 Dba:0x01400001
-------------------------------------------------------
ea251400 l ê%.. //001425ea=1320426
<16 bytes per line>
sys@ora11gr2> select FHFNO,FHSCN from x$kcvfh order by 1;
FHFNO FHSCN
---------- ----------------
1 1320426
2 1320426
3 1320426
4 1320426
5 1320426
5 rows selected.
2.1.3 checkpoint记录的最新的scn
sys@ora11gr2> @dump_ctl 3
old 1: ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME CONTROLF LEVEL &&1'
new 1: ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME CONTROLF LEVEL 3'
Session altered.
TRACE_FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_12403.trc
1 row selected.
[oracle@rhl6 dba_scripts]$ more /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_12403.trc
... ...
***************************************************************************
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:32
low cache rba:(0xde.7b9.0) on disk rba:(0xde.937.0)
on disk scn: 0x0000.00142929 07/15/2014 10:11:49
... ...
sys@ora11gr2> select conversion_api.to_dec('00142929') from dual;
CONVERSION_API.TO_DEC('00142929')
---------------------------------
1321257
sys@ora11gr2> select CHECKPOINT_CHANGE# from v$database;
CHECKPOINT_CHANGE#
------------------
1320426
1 row selected.
2.2 数据块变化时的scn
sys@ora11gr2> desc yallonking
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
NAME VARCHAR2(20)
sys@ora11gr2> select * from yallonking;
no rows selected
sys@ora11gr2> insert into yallonking values(1,'yallonking');
1 row created.
sys@ora11gr2> commit;
Commit complete.
sys@ora11gr2> select t.rowid,t.* from yallonking t;
ROWID ID NAME
------------------ ---------- --------------------
AAARgJAABAAATipAAB 1 yallonking
1 row selected.
sys@ora11gr2> select get_rowid('AAARgJAABAAATipAAB') from dual;
GET_ROWID('AAARGJAABAAATIPAAB')
----------------------------------------------------------------------------------------------------
Row_id type is :1
Object# is :71689
Relative_fno is :1
Block number is :80041
Row number is :1
1 row selected.
BBED> d /v dba 1,80041 offset 8 count 6
File: /u01/app/oracle/oradata/ora11gr2/system01.dbf (1)
Block: 80041 Offsets: 8 to 13 Dba:0x004138a9
-------------------------------------------------------
727c1100 0000 l r|.... //00117c72
<16 bytes per line>
sys@ora11gr2> @dump_file_by_block 1 80041
old 1: alter system dump datafile &&1 block &&2
new 1: alter system dump datafile 1 block 80041
System altered.
TRACE_FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_12526.trc
1 row selected.
[oracle@rhl6 dba_scripts]$ more /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_12526.trc
... ...
Start dump data blocks tsn: 0 file#:1 minblk 80041 maxblk 80041
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0 rdba=4274345
BH (0x88be1a78) file#: 1 rdba: 0x004138a9 (1/80041) class: 1 ba: 0x88906000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 405,28
dbwrid: 0 obj: 71689 objn: 71689 tsn: 0 afn: 1 hint: f
hash: [0x968d0e20,0x968d0e20] lru: [0x88be1c90,0x88be1a30]
obj-flags: object_ckpt_list
ckptq: [0x8d3f25a8,0x96b03520] fileq: [0x96b03540,0x96b03540] objq: [0x9315ccf0,0x9315ccf0] objaq: [0x88be1cc8,0x9315ccd0]
st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' tch: 4
flags: buffer_dirty redo_since_read
LRBA: [0xde.9a9.0] LSCN: [0x0.142b84] HSCN: [0x0.142b84] HSUB: [1]
Block dump from disk:
buffer tsn: 0 rdba: 0x004138a9 (1/80041)
scn: 0x0000.00117c72 seq: 0x02 flg: 0x06 tail: 0x7c720602 //00117c72
... ...
2.3 日志文件头中的scn
sys@ora11gr2> select FIRST_CHANGE#,NEXT_CHANGE# from v$log where GROUP#=1;
FIRST_CHANGE# NEXT_CHANGE#
------------- ------------
1270429 1290688
1 row selected.
sys@ora11gr2> @dump_logfile_hdrs
Session altered.
TRACE_FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_12681.trc
1 row selected.
[oracle@rhl6 dba_scripts]$ more /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_12681.trc
... ...
DUMP OF LOG FILES: 3 logs in database
LOG FILE #1:
name #1: /u01/app/oracle/oradata/ora11gr2/redo01.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x000000dc hws: 0x8 bsz: 512 nab: 0x55 flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.0013629a
Low scn: 0x0000.0013629d 07/14/2014 16:40:36 //FIRST_CHANGE#(1270429)
Next scn: 0x0000.0013b1c0 07/14/2014 17:55:18 //NEXT_CHANGE#(1290688)
FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=119524391=0x71fcc27, Db Name='ORA11GR2'
Activation ID=119541799=0x7201027
Control Seq=4139=0x102b, File size=102400=0x19000
File Number=1, Blksiz=512, File Type=2 LOG
... ...
2.4 事务scn
yallonking@ora11gr2> show user
USER is "YALLONKING"
yallonking@ora11gr2> select * from yallonking;
ID NAME
---------- --------------------
1 yallonking
1 row selected.
yallonking@ora11gr2> update yallonking set name ='oraking' where id=1;
1 row updated.
yallonking@ora11gr2> select * from yallonking;
ID NAME
---------- --------------------
1 oraking
sys@ora11gr2> select ADDR,XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,STATUS,START_SCN from v$transaction;
ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_SCN
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
0000000095A45B30 8 23 809 3 1064 270 29 ACTIVE 1322847 //scn:00142f5f
1 row selected.
sys@ora11gr2> select * from V$ROLLNAME where USN=8;
USN NAME
---------- ------------------------------
8 _SYSSMU8_1994096333$
1 row selected.
sys@ora11gr2> oradebug setmypid
Statement processed.
sys@ora11gr2> alter system dump undo header '_SYSSMU8_1994096333$';
System altered.
sys@ora11gr2> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_12714.trc
[oracle@rhl6 dba_scripts]$ more /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_12714.trc
... ...
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x17 10 0x80 0x0329 0x0002 0x0000.00142f5f 0x00c00428 0x0000.000.00000000 0x00000001 0x00000000 0
... ...
2.5 内存中的scn
sys@ora11gr2> select current_scn from v$database;
CURRENT_SCN
-----------
1323873
1 row selected.
3.scn例子
这个例子是通过修改scn跳过缺失的归档日志恢复数据库。
http://www.yallonking.com/?p=1032
