SCN and dump controlf

SCN(system change number),常见的事务表、控制文件、数据文件头、日志文件、数据块头等都记录有SCN值。

SYS@ORCL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

  719191

SYS@ORCL> select CHECKPOINT_CHANGE#,LAST_CHANGE#,NAME from v$datafile;

 

SYS@ORCL> select GROUP#,SEQUENCE# ,STATUS,FIRST_CHANGE# from v$log;

    GROUP#  SEQUENCE# STATUS       FIRST_CHANGE#

---------- ---------- ---------------- -------------

 1   10 CURRENT      719204

 2    8 INACTIVE      683565

 3    9 ACTIVE      718616

 

 

 

 

 

设置调试的进程

SYS@ORCL> oradebug setmypid
Statement processed.

 

dump级别

SYS@ORCL> oradebug dump controlf 3

有以下五种级别：
1 ：only the file header
2 ：just the file header, the database info record, and checkpoint progress records
3 ：all record types, but just the earliest and latest records for circular reuse record types
4 ：as above, but includes the 4 most recent records for circular reuse record types
5+ ：as above, but the number of circular reuse records included doubles with each level

显示跟踪文件的路径

SYS@ORCL> oradebug tracefile_name
/u01/oracle/admin/orcl/udump/orcl_ora_18022.trc

 

[oracle@carter udump]$ vi

 

Version:#1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine:i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 28
Unix process pid: 18022, image: oracle@carter (TNS V1-V3)

 

*** 2011-01-24 04:51:51.298
*** SERVICE NAME:(SYS$USERS) 2011-01-24 04:51:51.297
*** SESSION ID:(157.1180) 2011-01-24 04:51:51.297
DUMP OF CONTROL FILES, Seq # 2292 = 0x8f4
 V10 STYLE. FILE HEADER:
        Compatibility Vsn = 169869568=0xa200100
        Db ID=1267411716=0x4b8b2b04, Db Name='ORCL'
        Activation ID=0=0x0
        Control Seq=2292=0x8f4, File size=450=0x1c2
        File Number=0, Blksiz=16384, File Type=1 CONTROL

***************************************************************************
DATA FILE RECORDS
***************************************************************************
 (size = 428, compat size = 428, section max = 100, section in-use = 6,
  last-recid= 46, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
  (name #7) /u01/oracle/oradata/orcl/system01.dbf
creation size=0 block size=8192 status=0xe head=7 tail=7 dup=1
 tablespace 0, index=1 krfil=1 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
  Checkpoint cnt:133 scn: 0x0000.000af964 01/24/2011 04:21:14
  Stop scn: 0xffff.ffffffff 01/24/2011 02:25:48
 Creation Checkpointed at scn:  0x0000.00000009 06/30/2005 19:10:11
 thread:0 rba:(0x0.0.0)

 

SQL> select * from v$logfile;

***************************************************************************
LOG FILE RECORDS
***************************************************************************
 (size = 72, compat size = 72, section max = 16, section in-use = 16,
  last-recid= 18, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  (name #3) /u01/oracle/oradata/orcl/redo01.log
 Thread 1 redo log links: forward: 2 backward: 0
 siz: 0x19000 seq: 0x0000000a hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000af718
 Low scn: 0x0000.000af964 01/24/2011 04:21:14
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #2:
  (name #2) /u01/oracle/oradata/orcl/redo02.log
 Thread 1 redo log links: forward: 3 backward: 1
 siz: 0x19000 seq: 0x00000008 hws: 0xe bsz: 512 nab: 0x18eed flg: 0x1 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000a1aab
  Low scn: 0x0000.000a6e2d 01/22/2011 17:19:15
 Next scn: 0x0000.000af718 01/24/2011 04:02:34
LOG FILE #3:
  (name #1) /u01/oracle/oradata/orcl/redo03.log
 Thread 1 redo log links: forward: 0 backward: 2
 siz: 0x19000 seq: 0x00000009 hws: 0x2 bsz: 512 nab: 0x5ae flg: 0x1 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000a6e2d
 Low scn: 0x0000.000af718 01/24/2011 04:02:34
 Next scn: 0x0000.000af964 01/24/2011 04:21:14
LOG FILE #4:
 Thread 0 redo log links: forward: 0 backward: 0
 siz: 0x0 seq: 0x00000000 hws: 0x0 bsz: 0 nab: 0x0 flg: 0x3 dup: 0
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
 Low scn: 0x0000.00000000 01/01/1988 00:00:00
 Next scn: 0x0000.00000000 01/01/1988 00:00:00
LOG FILE #5:
 Thread 0 redo log links: forward: 0 backward: 0
 siz: 0x0 seq: 0x00000000 hws: 0x0 bsz: 0 nab: 0x0 flg: 0x3 dup: 0
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
 Low scn: 0x0000.00000000 01/01/1988 00:00:00
 Next scn: 0x0000.00000000 01/01/1988 00:00:00
.......

 

 

 

 

 

SCN实验：

第一个转储正常情况下的跟踪文件dump controlf:

SYS@ORCL> oradebug setmypid

SYS@ORCL> oradebug dump controlf 3

SYS@ORCL> oradebug tracefile_name
/u01/oracle/admin/orcl/udump/orcl_ora_20174.trc

SYS@ORCL> exit

 

第二个热备份状态，冻结检查点:

SYS@ORCL> alter tablespace system begin backup;

SYS@ORCL> oradebug setmypid

SYS@ORCL> oradebug tracefile_namedump controlf 3
Statement processed.

SYS@ORCL> oradebug tracefile_name
/u01/oracle/admin/orcl/udump/orcl_ora_20187.trc

 

第三个手动执行检查点:

SYS@ORCL> alter system checkpoint;

SYS@ORCL> oradebug setmypid

SYS@ORCL> oradebug tracefile_namedump controlf 3
Statement processed.

SYS@ORCL> oradebug tracefile_name
/u01/oracle/admin/orcl/udump/orcl_ora_20200.trc

 

第四个结束热备状态:

SYS@ORCL> oradebug setmypid
Statement processed.
SYS@ORCL> oradebug dump controlf 3
Statement processed.
SYS@ORCL> oradebug tracefile_name
/u01/oracle/admin/orcl/udump/orcl_ora_20207.trc

 

比较

orcl_ora_20174.trc

 Checkpoint cnt:137 scn: 0x0000.000b0ea2 01/24/2011 06:14:12

 

orcl_ora_20187.trc

Checkpoint cnt:138 scn: 0x0000.000b0f5c 01/24/2011 06:17:34

 

orcl_ora_20200.trc

Checkpoint cnt:139 scn: 0x0000.000b0f5c 01/24/2011 06:17:34

 

orcl_ora_20207.trc
Checkpoint cnt:140 scn: 0x0000.000b0f79 01/24/2011 06:18:03

 

#执行begin backup后checkpoint cnt 增加 1，对表空间执行begin backup会触发一次表空间检查点，检查点计数器(cnt)随之增加。

 

#执行手工检查点，表空间在热备模式下，checkpoine cnt 增加，数据文件检查点被冻结，SCN不改变。

 

#end backup 手，数据文件头解冻，SCN开始变化。

 

#如果检查点计数检查通过，则数据库进行第二次检查。第二次检查数据文件头的开始SCN和控制文件中记录的该文件的结束SCN是否一致，如果控制文件中记录的结束SCN等于数据文件头的开始SCN则不需要对那个文件进行恢复。

 

 

参考资料：

http://www.eygle.com/

http://my.unix-center.net/~Zianed/?p=50

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20928363/viewspace-684511/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20928363/viewspace-684511/