A-A+

浅析scn

2014年08月01日 BasicKnowledge 暂无评论 阅读 1,632 次

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

标签:

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: