分类：oracle

控制文件

ckpt的一项任务是更新数据文件头和控制文件,记录检查点信息,这些信息对于数据库的恢复和完整性校验都至关重要.下面来看一下控制文件和数据文件头都记录了哪些信息.通过以下内部命令可以转储oracle的数据文件头信息:
alter session set events ‘immediate trace name file_hdrs level 10’;
首先以immediate方式关闭数据库,在mount状态下执行该命令,研究一下此时转储的文件头信息:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> alter session set events 'immediate trace name file_hdrs level 10';

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_4606.trc

查看跟踪文件信息,选取一个文件的信息(这里选择userso1.dbf文件),这类trace文件的信息包含两个部分,一部分来自控制文件,另一部分来自数据文件:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
 Stop scn: 0x0000.0011601b 01/18/2013 06:31:30
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 ......
 V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4511=0x119f, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 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:0x0 root dba:0x00000000 chkpt cnt: 192 ctl cnt:191
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.0011601b 01/18/2013 06:31:30
 thread:1 rba:(0x9.1a0.10)
 .....

其中”FILE HEADER”开始的信息就是来自数据文件头,之前的相关内容来自控制文件,在mount状态下将users01.dbf文件移除,重新转储数据文件头:

With the Partitioning, OLAP and Data Mining options
[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 18 06:37:49 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> ! mv /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.bak

SQL> alter session set events 'immediate trace name file_hdrs level 10';

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_4620.trc

检查现在生成的跟踪文件可以看到,由于users01.dbf文件丢失,”FILE HEADER”部分信息将无法获得:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
 Stop scn: 0x0000.0011601b 01/18/2013 06:31:30
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)
 ......
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf'
*** Error 1157 in open/read file # 4 ***

此时报出的错误信息是,文件无法找到,也就是说当执行trace file_hdrs时需要读取数据文件头,获得相关信息,回过头来看一下来自控制文件部分的信息,其中包含:
Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
在”FILE HEADER”部分信息中包含了如下部分:
status:0x0 root dba:0x00000000 chkpt cnt: 192 ctl cnt:191
begin-hot-backup file size: 640
Checkpointed at scn: 0x0000.0011601b 01/18/2013 06:31:30
其中控制文件中记录的scn指最后一次成功完成的检查点scn;数据文件头中记录的checkpointed at scn指数据文件头中记录的最后一次成功完成的检查点scn;这两者在正常情况下是相等的.此外在控制文件和数据文件头都记录一个检查点计数(chkpt cnt或checkpoint cnt)而且数据文件头还记录了一个控制文件检查点计数(ctl cnt),在以上输出中ctl cnt:191比控制文件中的checkpoint cnt192要小1,这是因为当检查点更新控制文件和数据文件头上的chkpt cnt/checkpoint cnt信息时,在更新控制文件之前,可以获得当前的控制文件的clt cnt,这个信息被记入到数据文件头中,也就是ctlcnt:191,为什么要写这个ctl cnt到数据文件头了.是因为不能保证当前更新控制文件上的checkpoint cnt一定会成功(数据库可能突然crash了),记录之前成功的ctl cnt可以确保上一次的checkpoint是成功完成的,从而节了校验步骤.

数据库的启动验证
在数据库启动过程中的检验包含以下两个步骤;
第一步检查数据文件头中的checkpoint cnt是否与对应的控制文件中的checkpoint cnt一致.如果相等,则进行第二步检查.

第二步检查数据文件头的开始scn和对应的控制文件中的结束scn是否一致,如果控制文件中的结束scn等于数据文件头中的开始scn,则不需要对那个文件进行恢复.

对于每个数据文件都要完成检查后才打开数据库,同时将每个数据文件的结束的scn设置为无穷大也就是0xFFFFFFF.FFFFF

当使用alter session set events ‘immediate trace name file_hdrs level 10’来转储数据文件头信息时,oracle会转储两部分信息,一部分来自控制文件,另一部分来自数据文件,在数据库启动过程中,这两部分信息要用来进行启动验证.通过以下过程来进一步深入探讨一下这部分内容.

首先来看一下来自mount状态控制文件部分转储;

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
 Stop scn: 0x0000.0011601b 01/18/2013 06:31:30
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)
这部分中包含的重要信息有检查点计数(ckeckpoint cnt:192),检查点scn
(scn: 0x0000.0011601b 01/18/2013 06:31:30)和数据文件Stop scn(
top scn: 0x0000.0011601b 01/18/2013 06:31:30).

接下来再来看来自数据文件头的信息:

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4511=0x119f, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 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:0x0 root dba:0x00000000 chkpt cnt: 192 ctl cnt:191
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.0011601b 01/18/2013 06:31:30
 thread:1 rba:(0x9.1a0.10)

这部分中包含的重要信息有检查点scn(Checkpointed at scn: 0x0000.0011601b 01/18/2013 06:31:30)
和检查点计数库(chkpt cnt: 192 ctl cnt:191),这两者都和控制文件中所记录的一致.如果这两者一致.数据库启动时就能通过验证,启动数据库.

那么如果不一致,oracle则会请求进行恢复;以下是从崩溃中进行恢复users01.dbf文件.首先第一部分从控制文件中获得的信息是相同的:
先复制users01.dbf

[oracle@jingyong udump]$ cp /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.bak

使用shutdown abort来模拟崩溃故障

SQL> shutdown abort;
ORACLE instance shut down.

再来删除users01.dbf文件

[oracle@jingyong udump]$ mv /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.del

再从备份的users01.dbf.bak文件中还原users01.dbf文件

[oracle@jingyong udump]$ mv /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.bak /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf

再启动到mount状态下转储数据文件头信息

SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.

再业转储数据文件头信息

Database mounted.
SQL> alter session set events 'immediate trace name file_hdrs level 10';

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_4923.trc


DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:201 scn: 0x0000.00120188 01/18/2013 07:15:10
 Stop scn: 0x0000.00120188 01/18/2013 07:15:10
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)

得到的控制文件中记录检查点计数(Checkpoint cnt:201),检查点scn

(scn: 0x0000.00120188 01/18/2013 07:15:10),和数据文件Stop scn
(Stop scn: 0x0000.00120188 01/18/2013 07:15:10)

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4545=0x11c1, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 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/18/2013 07:12:48
 status:0x4 root dba:0x00000000 chkpt cnt: 199 ctl cnt:198
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.00120027 01/18/2013 07:12:49
 thread:1 rba:(0xb.2.10)

而从文件头中获得的备份文件信息则是:检查点是Checkpointed at scn: 0x0000.00120027 01/18/2013 07:12:49
检查点计数为:chkpt cnt: 199 ctl cnt:198

数据文件头中的检查计数为chkpt cnt: 199小于控制文件中的记录的Checkpoint cnt:201oracle可以判断文件是从备份中恢复的,或者文件故障,需要进行介质恢复.如果此时试图打开数据库,则oracle提示文件需要介质恢复.而且控制文件中的检查点scn: 0x0000.00120188与数据文件头中的Checkpointed at scn: 0x0000.00120027也不相同

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4:
'/u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf'

执行介质恢复

SQL> recover datafile 4;
Media recovery complete.

再来对数据文件头信息进行转储,来看一下恢复完成之后,控制文件和数据文件头的变化.

SQL> alter session set events 'immediate trace name file_hdrs level 10';

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_4940.trc

首先看控制文件的变化:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:202 scn: 0x0000.00120188 01/18/2013 07:15:10
 Stop scn: 0x0000.00120187 01/18/2013 07:15:10
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)

检查点计数据Checkpoint cnt:202,执行了恢复之后,检查点计数较前增加了1,此时检查点scn是scn: 0x0000.00120188 01/18/2013 07:15:10,数据文件的Stop scn为
Stop scn: 0x0000.00120187 01/18/2013 07:15:10,说明数据文件stop scn和数据文件进行了同步.

以下是数据文件头信息:

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4554=0x11ca, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 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/18/2013 07:24:36
 status:0x0 root dba:0x00000000 chkpt cnt: 202 ctl cnt:201
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.00120187 01/18/2013 07:15:10
 thread:1 rba:(0xb.15a.10)

此时数据文件头信息显示检查点(Checkpointed at scn: 0x0000.00120187 01/18/2013 07:15:10)
和控制文件中记录的Stop scn(Stop scn: 0x0000.00120187 01/18/2013 07:15:10)一致,数据库启动可以顺利进行.检查点计数为(chkpt cnt: 202 ctl cnt:201)

打开数据库,看一看open阶段的变化:

SQL> alter database open;

Database altered.


SQL> alter session set events 'immediate trace name file_hdrs level 10';

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_4968.trc

此时数据库恢复正常运行,控制文件信息如下:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:203 scn: 0x0000.00120189 01/18/2013 07:28:46
 Stop scn: 0xffff.ffffffff 01/18/2013 07:15:10
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)

此时Stop scn被置为无穷大(Stop scn: 0xffff.ffffffff)
数据文件头信息如下,其中检查点信息和控制文件中记录的checkpoint信息一致:

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4557=0x11cd, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 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/18/2013 07:24:36
 status:0x4 root dba:0x00000000 chkpt cnt: 203 ctl cnt:202
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.00120189 01/18/2013 07:28:46
 thread:1 rba:(0xb.15a.10)

检查点

检查点只是一个数据库事件,它存在的根本意义在于减少崩溃恢复(crash recovery)时间.检查点事件由ckpt后台进程触发,当检查点发生时,ckpt进程会负责通知dbwr进程将脏数据(dirty buffer)写出到数据文件上,ckpt进程的另外一个职责是负责更新数据文件头及控制文件上的检查点信息.

检查点(checkpoint)的工作原理
在oracle数据库中,当进行数据修改时,需要首先将数据读入内存中(buffer cache),修改数据的同时,oracle会记录重做(redo)信息用于恢复.因为有了重做信息的存在,oracle不需要在事务提交时(commit)立即将变化的数据写回磁盘(立即写的效率会很低),重做的存在也正是为了在数据库崩溃之后,数据可以恢复.

常见的情况,数据库可能因为断电而crash,那么内存中修改过的,尚没有写入数据文件的数据将会丢失.在下一次数据库启动之后,oracle可以通过重做(redo)日志进行事务重演(也就是进行前滚),将数据库恢复到崩溃之前的状态,然后数据库可以打开提供使用之后oracle可以将没有提交的事务进行回滚.

检查点的存在就是为了缩短这个恢复时间.当检查点发生时(此时的scn被称为checkpoint scn). oracle会通知dbwr进程,把修改过的数据,也就是此checkpoint scn之前的脏数据(dirty data)从buffer cache写入磁盘,当写入完成之后,ckpt进程则会相应更新控制文件和数据文件头,记录检查点信息,标识变更.

checkpoint scn可以从数据库中查询得到:

SQL> select file#,checkpoint_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time from v$datafile;

     FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------ -------------------
         1            1131856 2013-01-18 01:33:36
         2            1131856 2013-01-18 01:33:36
         3            1131856 2013-01-18 01:33:36
         4            1131856 2013-01-18 01:33:36
         5            1131856 2013-01-18 01:33:36
         6            1131856 2013-01-18 01:33:36

6 rows selected.

SQL> select dbid,checkpoint_change# from v$database;

      DBID CHECKPOINT_CHANGE#
---------- ------------------
3172629284            1131856

在检查点完成之后,此检查点之前修改过的数据都已经写回磁盘,重做日志文件中的相应重做记录对于崩溃/实例恢复不再有用.

如果检查点的频率高,那么恢复时间需要应用的重做日志就相对得少,恢复时间就可以缩短,如果oracle可以在性能允许的情况下,使得检查点的scn接宾redo的最新变更,那么,使得oracle可以最大化地减少恢复时间.

常规检查点与增量检查点
脏缓冲列表(Dirty List).
当数据在buffer cache中被修改之后,Dirty Buffer会被转移到dirty list,以便将来
执行的检查点可以将这些修改过的buffer写出到数据文件上.

但是注意,由于dirty list上的buffer并没有顺序,有的buffer反复被修改,在链表上的位置就可能发生变化,当检查点发生时,oracle需要将脏缓冲列表上的数据全部写出到数据文件.为了区分,在oracle8之前,oracle实施的这类检查点通常被称为常规检查点(Conventional checkpoint),由于检查点时需要写出全部的脏数据,所以也被称为完全检查点(complete checkpoint),常规检查点按特定的条件触(log_checkpoint_interval,
log_checkpoint_timeout参数设置及log switch等条件触发),触发时会同时更新数据文件头以及控制文件记录检查点信息.

从oracle8开始,oracle引入了增量检查点(Incremental checkpoint)的概念.和以前的
版本相比,在新版本中,主要的变化是引入了检查点队列(checkpoint queue ckptq)机制.在数据库内部,每一个脏数据块都会被记录到检查点队列中,按照LRBA(Low RBA,第一次对此数据块修改所对应的redo byte address)的顺序来排列,如果一个数据块进行过多次修改,该数据块在检查点队列上的顺序并不会发生变化(相对LRBA,
后面修改的RBA被称为HRBA)

当执行增量检查点时,dbwr从检查点队列按照Low RBA的顺序写出,此时先修改的数据就可以被按顺序优先写出,实例检查点因此可以不断增进,同时,ckpt进程也阶段性地使用非常轻量级的控制文件更新协议,将当前的最低的RBA写控制文件.为了减少频繁增量检查点的性能影响,ckpt在进行轻量级更新时,并不会改写控制文件中数据文件
的检查点信息以及数据文件头信息.而只是记录控制文件检查点scn(controlfile checkpoint at scn)并且根据增量检查点的写出增进RBA信息.

通过增量检查点,数据库可以将以前的全量写出变更为增量渐进写出,从而可以极大工减少对于数据库性能的影响;而检查点队列则进一步地将RBA和检查点关联起来,从而可以通过检查点来确定恢复的起点.

检查点队列在数据库内部通过latch保护:

select name,gets,misses from v$latch where name='checkpoint queue latch';

SQL> select name,gets,misses from v$latch where name='checkpoint queue latch';

NAME                                                     GETS     MISSES
-------------------------------------------------- ---------- ----------
checkpoint queue latch                                  86382          0

checkpoint queue latch存在多个子latch,可以通过v$latch_children视图查询:

select name,gets,misses from v$latch_children where name='checkpoint queue latch';

SQL> select name,gets,misses from v$latch_children where name='checkpoint queue latch';

NAME                                                     GETS     MISSES
-------------------------------------------------- ---------- ----------
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                  11374          0
checkpoint queue latch                                  11724          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0

16 rows selected.

除了检查点队列(ckptq)之外,数据库中还存在另外一个队列和检查点相关,这就是文件检查点队列(file queue),通常缩写为fileq,文件检查点队列的引入提高了表空间检查点(tablespace checkpoint)的性能.每个dirty buffer同时链接到这两个队列,ckptq包含实例所有需要执行检查点的buffer,fileq包含属于特定文件需要执行检查点的buffer,每个文件都包含一个文件队列,在执行表空间检查点请求时需要使用fileq,通常当对表空间执行offline等操作时会触发表空间检查点.

在buffer cache中,每个buffer的header上都存在ckptq以及fileq队列信息,通过如下命令可以转储buffer cache信息(注意应发仅在测试环境中尝试);

alter session set events 'immediate trace name buffers level 10';

SQL> alter session set events 'immediate trace name buffers level 10';

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_4239.trc

以下bh信息来自oracle10g

    BH (0x24ff61dc) file#: 3 rdba: 0x00c00a6c (3/2668) class: 1 ba: 0x24eee000
      set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 46
      dbwrid: 0 obj: 8780 objn: 8780 tsn: 2 afn: 3
      hash: [29115d3c,29115d3c] lru: [24ff62e0,24ff60d0]
      lru-flags:
      ckptq: [24ff5184,29150a60] fileq: [24ff518c,29150a9c] objq: [27b37888,27b37888]
      st: XCURRENT md: NULL tch: 35
      flags: buffer_dirty gotten_in_current_mode block_written_once
              redo_since_read
      LRBA: [0x5.2e7.0] HSCN: [0x0.11546d] HSUB: [1]
      buffer tsn: 2 rdba: 0x00c00a6c (3/2668)
      scn: 0x0000.0011546d seq: 0x01 flg: 0x02 tail: 0x546d0601
      frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

注意信息中的ckptq和fileq,这就是检查点队列和文件队列,每个队列后面记录了两个地址信息,分别是前一块以及下一块的地址,通过这个信息ckptq和fileq构成了双向链表.注意仅仅只有dirty buffer才会包含ckptq信息,否则为null,信息类似如下

    BH (0x24be637c) file#: 1 rdba: 0x004096b0 (1/38576) class: 1 ba: 0x2480a000
      set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 46
      dbwrid: 0 obj: 18 objn: 18 tsn: 0 afn: 1
      hash: [290c0878,290c0878] lru: [24be6320,24be6480]
      lru-flags: moved_to_tail
      ckptq: [NULL] fileq: [NULL] objq: [24be6374,24be64d4]
      st: XCURRENT md: NULL tch: 3
      flags: only_sequential_access
      LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
      buffer tsn: 0 rdba: 0x004096b0 (1/38576)
      scn: 0x0000.00030101 seq: 0x01 flg: 0x04 tail: 0x01010601
      frmt: 0x02 chkval: 0x8c8d type: 0x06=trans data

[oracle@jingyong udump]$ grep ckptq jingyong_ora_4239.trc |grep -v NULL
      ckptq: [26c13b04,233f6364] fileq: [237e9e1c,237e9d6c] objq: [237e9de4,237ea0a4]
      ckptq: [233f6414,237e6be4] fileq: [233f641c,237e6bec] objq: [237e6c64,233f6494]
      ckptq: [237e9aa4,237e9cb4] fileq: [237e9aac,237e9cbc] objq: [237e9d34,27b8afa8]
      ckptq: [237e6c94,233f6154] fileq: [237e6c9c,233f615c] objq: [233f61d4,237e6d14]
      ckptq: [237e9944,237ea0d4] fileq: [237e994c,237ea0dc] objq: [237ea154,237e99c4]
      ckptq: [29150a60,237e9944] fileq: [29150a74,237e994c] objq: [27b1cc58,27b1cc58]
      ckptq: [26c13b04,26c18194] fileq: [26c13b0c,26c1819c] objq: [26c18214,26c13b84]
      ckptq: [26c12924,291529c8] fileq: [26c1292c,29152a40] objq: [27b1d0b8,26c129a4]
      ckptq: [26c18034,26c12874] fileq: [26c1803c,26c1287c] objq: [26c128f4,26c180b4]
      ckptq: [291529c8,26c12be4] fileq: [29152a40,26c12bec] objq: [26c12c64,27b1d0b8]
      ckptq: [233f6204,26c18034] fileq: [233f620c,26c1803c] objq: [26c180b4,233f6284]
      ckptq: [26c12b34,233f6204] fileq: [26c12b3c,233f620c] objq: [233f6284,26c12bb4]
      ckptq: [26c12be4,26c127c4] fileq: [26c12bec,26c127cc] objq: [26c12844,26c12c64]
      ckptq: [26c12874,233f6364] fileq: [26c1287c,233f636c] objq: [233f63e4,26c128f4]
      ckptq: [26c127c4,233f6414] fileq: [26c127cc,233f641c] objq: [233f6494,26c12844]
      ckptq: [29150a60,24ff50d4] fileq: [29150a9c,24ff50dc] objq: [27b37f18,27b37f18]
      ckptq: [24ff5184,29150a60] fileq: [24ff518c,29150a9c] objq: [27b37888,27b37888]
      ckptq: [233f6364,26c12924] fileq: [233f636c,26c1292c] objq: [26c129a4,233f63e4]

在sga中存在一块内存区域用于记录这个检查点队列

select name,bytes from v$sgastat where upper(name) like '%CHECKPOINT%';

SQL> select name,bytes from v$sgastat where upper(name) like '%CHECKPOINT%';

NAME                            BYTES
-------------------------- ----------
Checkpoint queue               128320

从oracle10g开始,数据库中额外增加了对象检查点队列(object queue,objq)用于记录对象检查点信息:

    BH (0x24ff61dc) file#: 3 rdba: 0x00c00a6c (3/2668) class: 1 ba: 0x24eee000
      set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 46
      dbwrid: 0 obj: 8780 objn: 8780 tsn: 2 afn: 3
      hash: [29115d3c,29115d3c] lru: [24ff62e0,24ff60d0]
      lru-flags:
      ckptq: [24ff5184,29150a60] fileq: [24ff518c,29150a9c] objq: [27b37888,27b37888]
      st: XCURRENT md: NULL tch: 35
      flags: buffer_dirty gotten_in_current_mode block_written_once
              redo_since_read
      LRBA: [0x5.2e7.0] HSCN: [0x0.11546d] HSUB: [1]
      buffer tsn: 2 rdba: 0x00c00a6c (3/2668)
      scn: 0x0000.0011546d seq: 0x01 flg: 0x02 tail: 0x546d0601
      frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

共享池中分配了相关内存用于OBJECT QUEUE:

select * from v$sgastat where name like 'object queue%';
SQL> select * from v$sgastat where name like 'object queue%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  object queue hash table d        3040
shared pool  object queue hash buckets       69632
shared pool  object queue                    16352

下面来看一下控制文件以及增量检查点的协同工作,以下输出来自oracle10g，两次level 8级控制文件的转储.
第一部分重要信息是控制文件的seq号,控制文件随着数据库的变化而增进版本.

[oracle@jingyong udump]$ diff jingyong_ora_4445.trc jingyong_ora_4463.trc
1c1
< /u01/app/oracle/admin/jingyong/udump/jingyong_ora_4445.trc
---
> /u01/app/oracle/admin/jingyong/udump/jingyong_ora_4463.trc
13c13
< Unix process pid: 4445, image: oracle@jingyong (TNS V1-V3)
---
> Unix process pid: 4463, image: oracle@jingyong (TNS V1-V3)
15,18c15,18
< *** 2013-01-18 05:40:06.832
< *** SERVICE NAME:(SYS$USERS) 2013-01-18 05:40:06.797
< *** SESSION ID:(159.15) 2013-01-18 05:40:06.797
< DUMP OF CONTROL FILES, Seq # 4498 = 0x1192
---
> *** 2013-01-18 05:44:49.284
> *** SERVICE NAME:(SYS$USERS) 2013-01-18 05:44:49.258
> *** SESSION ID:(159.21) 2013-01-18 05:44:49.258
> DUMP OF CONTROL FILES, Seq # 4499 = 0x1193
23c23
< Control Seq=4498=0x1192, File size=450=0x1c2
---
>       Control Seq=4499=0x1193, File size=450=0x1c2
44c44
< Database checkpoint: Thread=1 scn: 0x0000.00114550
---
>  Database checkpoint: Thread=1 scn: 0x0000.00115af8

接下来是控制文件检查点scn,增量检查点不断增进的内容之一:
66c66
< Controlfile Checkpointed at scn:  0x0000.001145c1 01/18/2013 05:38:56
---
>  Controlfile Checkpointed at scn:  0x0000.00115af8 01/18/2013 05:44:37
96,97c96,97

检查点记录之后是RBA信息,检查点和redo相关联在这里实现,通过以下信息可以注意到,通过增量检查点之后,而low cache rba从0x5.307.0增进到0x5.412.0,low cache rba是下一次恢复的起点,而on disk rba则是指已经写入磁盘(redo log file)的rba地址.
这就是前滚恢复能够到达的终点.增量检查点的作用由此体现:

< low cache rba:(0x5.307.0) on disk rba:(0x5.407.0)
< on disk scn: 0x0000.00115a04 01/18/2013 05:33:13
---
> low cache rba:(0x5.412.0) on disk rba:(0x5.442.0)
> on disk scn: 0x0000.00115af9 01/18/2013 05:44:38
99c99

最后一部分是heartbeat心跳信息,每3秒更新一次用于验证实例的存活性：

< heartbeat: 805047415 mount id: 3142683107
---
> heartbeat: 805047509 mount id: 3142683107

通过以上分析可以清晰地看到增量检查点的实施过程,因为增量检查点可以连续进行,所以检查点rba可以比常规点更接近数据库的最后状态,从而在数据库的实例恢复中可以极大地减少恢复时间.而且,通过增量检查点,dbwr可以持续进行写出,从而避免了常规检查点出发的峰值,写入对于I/O的过度征用.

显而易见的是,增量检查点明显优于常规的完全检查点,所以在引入检查点队列之后,数据库正常情况下执行的都是增量检查点,从oracle8i开始,完全检查点仅仅在以下两种情况下出现:
alter system checkpoint;
shutdown(除了abort方式外)
log switch事件同样是触发的增量检查点,但是在log switch触发的检查点会促使数据文件头与控制文件信息的同步

log_checkpoints_to_alert参数
在数据库中,可以设置初始化参数log_checkpoints_to_alert为true,则数据库会将检查点的执行情况记入警告日志文件中,这个参数的初始值为false:

SQL> show parameter checkpoints_to

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     FALSE
SQL> alter system set log_checkpoints_to_alert=true;

System altered.

当数据库执行各类检查点进,日志文件中会记录详细信息,以下是来自oracle 10g的警告日志文件中的信息摘录.注意以下信息中,共发生了两次检查点,触发条件都是log switch,在日志中,注意rba信息和检查点scn同时出现,这就是检查点队列的作用,log switch检查点的特别之处在于,需要同时在控制文件和数据文件头上标记检查点进度

[oracle@jingyong bdump]$ tail -20 alert_jingyong.log
Fri Jan 18 06:12:00 2013
Beginning log switch checkpoint up to RBA [0x8.2.10], SCN: 1138121
Thread 1 advanced to log sequence 8
  Current log# 2 seq# 8 mem# 0: /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log
Fri Jan 18 06:12:21 2013
Beginning log switch checkpoint up to RBA [0x9.2.10], SCN: 1138140
Thread 1 advanced to log sequence 9
  Current log# 3 seq# 9 mem# 0: /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log
Fri Jan 18 06:17:12 2013
Completed checkpoint up to RBA [0x8.2.10], SCN: 1138121
Fri Jan 18 06:17:30 2013
Completed checkpoint up to RBA [0x9.2.10], SCN: 1138140

从以上信息还可以观察到,检查点的触发和检查点完成具有一定的时间间隔,这进一步说明,检查点仅仅是一个数据库事件,发生检查点时ckpt进程负责通知dbwr执行写出,但是检查点不会等待写出完成,它会在下一次触发时写出上一次成功完成的检查点信息.

在警告日志文件中,可能还会看到Incremental checkpoint的信息,这些信息和检查点的另外一个触发条件有关.为了保证检查点不会滞后整个日志文件,oracle限制最长的检查点跨度不超过最小日志大小的90%.所以数据库在运行过程中会根据log tail进行计算,主动触发增量检查点.

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

istener.ora文件的内容如下:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = jingyong)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
      (SID_NAME = jingyong)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong)(PORT = 1521))
    )
  )

上面的配置只配置了一个listener

下面再增加一个listener_2

SID_LIST_LISTENER_2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = jingyong)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
      (SID_NAME = jingyong)
    )
  )

LISTENER_2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong)(PORT = 1522))
    )
  )

SID_LIST_ 这里的要和LISTENER名称一致。
SID_DESC则注册要提供服务的数据库实例名。这样LISTENER2 才能够提供服务。

再来启动两个listener

[oracle@jingyong udump]$ lsnrctl stop listener

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-JAN-2013 18:06:21

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jingyong)(PORT=1521)))
The command completed successfully
[oracle@jingyong udump]$ lsnrctl start listener_2

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-JAN-2013 18:06:37

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db/network/log/listener_2.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jingyong)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener_2
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                13-JAN-2013 18:06:37
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db/network/log/listener_2.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong)(PORT=1522)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "jingyong" has 1 instance(s).
  Instance "jingyong", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@jingyong udump]$ lsnrctl start listener

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-JAN-2013 18:06:41

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jingyong)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                13-JAN-2013 18:06:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "jingyong" has 1 instance(s).
  Instance "jingyong", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

一个oracle实例对于多个监听，一个监听对于多个端口。
现在我们可以创建一个oracle实例一个监听的两个端口。这一共有两种方法：
在listener文件添加一行，具体如下：

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong)(PORT = 1522))
    )
  )

这时我们只有重启监听，就可以用两个端口连接数据库实例了。。。。
当然我们也可以一个实例启动两个监听，但是具体只有一个监听起作用。
我们可以在一个数据库下，一个实例对于一个监听，但实例必须在不同目录。或者先建监听，再建实例。一样能达到效果。。。
具体配置如下：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = jingyong)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
      (SID_NAME = jingyong)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong)(PORT = 1521))
    )
  )

SID_LIST_LISTENER_2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = jingyong)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
      (SID_NAME = jingyong)
    )
  )

LISTENER_2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong)(PORT = 1522))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong)(PORT = 1523))
    )
  )

在oracle文档中,file#是被定义为绝对文件号(the absolute file number)

查询dba_objects视图,发现v$tempseg_usage视图是一个同义词

SQL> select object_type from dba_objects where object_name='V$TEMPSEG_USAGE';

OBJECT_TYPE
-------------------
SYNONYM

v$tempseg_usage是v_$sort_usage的同义词,也就是和v$sort_usage同源.

select * from dba_synonyms where synonym_name='V$TEMPSEG_USAGE';
SQL> select * from dba_synonyms where synonym_name='V$TEMPSEG_USAGE';

OWNER    SYNONYM_NAME     TABLE_OWNER  TABLE_NAME     DB_LINK
-------- ---------------- ------------ -------------- ----------
PUBLIC   V$TEMPSEG_USAGE  SYS          V_$SORT_USAGE

查看这个视图的构造语句;

SQL> select view_definition from v$fixed_view_definition where view_name='GV$SORT_USAGE';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select x$ktsso.inst_id,
       username,
       username,
       ktssoses,
       ktssosno,
       prev_sql_addr,
       prev_hash_value,
       prev_sql_id,
       ktssotsn,
       decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'),
       decode(ktssosegt,
              1,
              'SORT',
              2,
              'HASH',
              3,
              'DATA',
              4,
              'INDEX',
              5,
              'LOB_DATA',
              6,
              'LOB_INDEX',
              'UNDEFINED'),
       ktssofno,
       ktssobno,
       ktssoexts,
       ktssoblks,
       ktssorfno
  from x$ktsso, v$session
 where ktssoses = v$session.saddr
   and ktssosno = v$session.serial#

注意到在oracle文档中segfile#的定义为:
segfile# number file number of initial extent
在视图中,这个字段来自x$ktsso.ktssofno,也就是说这个字段实际上代表的是绝对文件号,
那么这个绝对文件号能否与v$tempfile中的file#字段关联了

来查看一下v$tempfile的来源,

select view_definition from v$fixed_view_definition where view_name='GV$TEMPFILE';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select tf.inst_id,
       tf.tfnum,
       to_number(tf.tfcrc_scn),
       to_date(tf.tfcrc_tim,
               'MM/DD/RR HH24:MI:SS',
               'NLS_CALENDAR=Gregorian'),
       tf.tftsn,
       tf.tfrfn,
       decode(bitand(tf.tfsta, 2), 0, 'OFFLINE', 2, 'ONLINE', 'UNKNOWN'),
       decode(bitand(tf.tfsta, 12),
              0,
              'DISABLED',
              4,
              'READ ONLY',
              12,
              'READ WRITE',
              'UNKNOWN'),
       fh.fhtmpfsz * tf.tfbsz,
       fh.fhtmpfsz,
       tf.tfcsz * tf.tfbsz,
       tf.tfbsz,
       fn.fnnam
  from x$kcctf tf, x$kccfn fn, x$kcvfhtmp fh
 where fn.fnfno = tf.tfnum
   and fn.fnfno = fh.htmpxfil
   and tf.tffnh = fn.fnnum
   and tf.tfdup != 0
   and bitand(tf.tfsta, 32) <> 32
   and fn.fntyp = 7
   and fn.fnnam is not null

再来查看x$kcctf底层表,注意到TFAFN(Temp File Absolute File Number)是存在的

SQL> desc x$kcctf
Name      Type         Nullable Default Comments
--------- ------------ -------- ------- --------
ADDR      RAW(8)       Y
INDX      NUMBER       Y
INST_ID   NUMBER       Y
TFNUM     NUMBER       Y
TFAFN     NUMBER       Y
TFCSZ     NUMBER       Y
TFBSZ     NUMBER       Y
TFSTA     NUMBER       Y
TFCRC_SCN VARCHAR2(16) Y
TFCRC_TIM VARCHAR2(20) Y
TFFNH     NUMBER       Y
TFFNT     NUMBER       Y
TFDUP     NUMBER       Y
TFTSN     NUMBER       Y
TFTSI     NUMBER       Y
TFRFN     NUMBER       Y
TFPFT     NUMBER       Y
TFMSZ     NUMBER       Y
TFNSZ     NUMBER       Y

而v$kcctf.tfafn这个字段在构造v$tempfile时并没有使用,所以不能通过v$sort_usage
和vg$tempfile直接关联绝对文件号.查询一下排序段使用

SQL> select username,segtype,segfile#,segblk#,extents,segrfno# from v$sort_usage;

USERNAME                       SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS   SEGRFNO#
------------------------------ --------- ---------- ---------- ---------- ----------
ZW2003                         DATA             201    2097801          1          1
ZW2001                         DATA             201    2104073          1          1
ZW2001                         DATA             201    2096265          1          1

看到这里的segfile#=201,而在v$tempfile是找不到这个信息的;

select file#,rfile#,ts#,status,blocks from v$tempfile;
SQL> select file#,rfile#,ts#,status,blocks from v$tempfile;

     FILE#     RFILE#        TS# STATUS      BLOCKS
---------- ---------- ---------- ------- ----------
         1          1          3 ONLINE     3840000

可以从x$kcctf中获得这些信息,并可以看到v$tempfile.file#实际上来自x$kcctf.tfnum,
这个字段是临时文件的文件号,而绝对文件号是v$kcctf.tfafn,只有这个字段才可以与
v$sort_usage.segfile#关联;

SQL> select indx,tfnum,tfafn,tfcsz from x$kcctf;

      INDX      TFNUM      TFAFN      TFCSZ
---------- ---------- ---------- ----------
         0          1        201    1048576

为了分离临时文件号和数据文件号,oracle对临时文件的编号是以db_files为起点,所以临时文件
的绝对文件号是等于db_files+file#

db_files参数的值如下:

SQL> show parameter db_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200

所以v$tempfile.file#定义为绝对文件号是不确切的.

数据文件的的文件号

SQL> select a.object_name,a.object_type from dba_objects a where a.object_name='V$DATAFILE';

OBJECT_NAME     OBJECT_TYPE
--------------- -------------------
V$DATAFILE      SYNONYM

从这个查询知道v$datafile是同义词来源于v_$datafile

SQL> select * from dba_synonyms a where a.synonym_name='V$DATAFILE';

OWNER     SYNONYM_NAME  TABLE_OWNER  TABLE_NAME   DB_LINK
--------- ------------- ------------ ------------ -------
PUBLIC    V$DATAFILE    SYS          V_$DATAFILE

v_$datafile的构造语句如下:

SQL> select view_definition from v$fixed_view_definition where view_name='GV$DATAFILE';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select /*+ rule */
 fe.inst_id,
 fe.fenum,
 to_number(fe.fecrc_scn),
 to_date(fe.fecrc_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
 fe.fetsn,
 fe.ferfn,
 decode(fe.fetsn,
        0,
        decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
        decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER')),
 decode(fe.fedor,
        2,
        'READ ONLY',
        decode(bitand(fe.festa, 12),
               0,
               'DISABLED',
               4,
               'READ ONLY',
               12,
               'READ WRITE',
               'UNKNOWN')),
 to_number(fe.fecps),
 to_date(fe.fecpt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
 to_number(fe.feurs),
 to_date(fe.feurt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
 to_number(fe.fests),
 decode(fe.fests,
        NULL,
        to_date(NULL),
        to_date(fe.festt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')),
 to_number(fe.feofs),
 to_number(fe.feonc_scn),
 to_date(fe.feonc_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
 fh.fhfsz * fe.febsz,
 fh.fhfsz,
 fe.fecsz * fe.febsz,
 fe.febsz,
 fn.fnnam,
 fe.fefdb,
 fn.fnbof,
 decode(fe.fepax, 0, 'UNKNOWN', 65535, 'NONE', fnaux.fnnam),
 to_number(fh.fhfirstunrecscn),
 to_date(fh.fhfirstunrectime,
         'MM/DD/RR HH24:MI:SS',
         'NLS_CALENDAR=Gregorian')
  from x$kccfe fe, x$kccfn fn, x$kccfn fnaux, x$kcvfh fh
 where ((fe.fepax != 65535 and fe.fepax != 0 and fe.fepax = fnaux.fnnum) or
       ((fe.fepax = 65535 or fe.fepax = 0) and fe.fenum = fnaux.fnfno and
       fnaux.fntyp = 4 and fnaux.fnnam is not null and
       bitand(fnaux.fnflg, 4) != 4 and fe.fefnh = fnaux.fnnum))
   and fn.fnfno = fe.fenum
   and fn.fnfno = fh.hxfil
   and fe.fefnh = fn.fnnum
   and fe.fedup != 0
   and fn.fntyp = 4
   and fn.fnnam is not null
   and bitand(fn.fnflg, 4) != 4

从上面的构造语句可知v$datafile.file#来源于x$kccfe.fenum字段

col operation format a50

col cost      format 999999
col kbytes    format 999999
col object    format a20
select hash_value,child_number,
       lpad('',2*depth)
       ||operation
       ||''
       ||options
       ||decode(id,0,substr(optimizer,1,6)||'Cost='||to_char(cost))operation,
       object_name,object_type,cost,round(bytes/1024) kbytes
from v$sql_plan where hash_value in(
                     select a.sql_hash_value
                     from v$session a,v$session_wait b
                     where a.sid=b.sid
                     and b.event='db file sequential read')
order by hash_value,child_number,ID;

SQL> col operation format a55
SQL> col cost      format 99999
SQL> col kbytes    format 999999
SQL> col object    format a25
SQL> select hash_value,child_number,
  2         lpad('',2*depth)
  3         ||operation
  4         ||''
  5         ||options
  6         ||decode(id,0,substr(optimizer,1,6)||'Cost='||to_char(cost))operation,
  7         object_name,object_type,cost,round(bytes/1024) kbytes
  8  from v$sql_plan where hash_value in(
  9                       select a.sql_hash_value
 10                       from v$session a,v$session_wait b
 11                       where a.sid=b.sid
 12                       and b.event='db file sequential read')
 13  order by hash_value,child_number,ID;

HASH_VALUE CHILD_NUMBER OPERATION                                               OBJECT_NAME                    OBJECT_TYPE                               COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563            0 SELECT STATEMENTFIRST_Cost=203255                                                                                               20325
                                                                                                                                                            5

1722201563            0 SORTORDER BY                                                                                                                    20325   5910
                                                                                                                                                            5

1722201563            0 HASHGROUP BY                                                                                                                    20325   5910
                                                                                                                                                            5

1722201563            0 HASH JOIN                                                                                                                       20193   5910
                                                                                                                                                            1

1722201563            0 TABLE ACCESSFULL                                        BS_CORP                        TABLE                                       85     41
1722201563            0 NESTED LOOPSSEMI                                                                                                                20184   4561
                                                                                                                                                            5

1722201563            0 HASH JOIN                                                                                                                       20184   4540
                                                                                                                                                            3

1722201563            0 HASH JOIN                                                                                                                       19970   2515
                                                                                                                                                            4

1722201563            0 HASH JOIN                                                                                                                       19529   1813
                                                                                                                                                            2

1722201563            0 HASH JOINRIGHT SEMI                                                                                                             19442    489
                                                                                                                                                            1

1722201563            0 VIEW                                                    VW_SQ_1                        VIEW                                     19402     36
                                                                                                                                                            8

1722201563            0 FILTER
1722201563            0 NESTED LOOPS                                                                                                                    19402    241
                                                                                                                                                            8

1722201563            0 NESTED LOOPS                                                                                                                    19402    157
                                                                                                                                                            6

1722201563            0 MERGE JOINCARTESIAN                                                                                                             28436 264415
1722201563            0 INLIST ITERATOR
1722201563            0 INDEXRANGE SCAN                                         PK_BS_CORP                     INDEX (UNIQUE)                               4      0
1722201563            0 BUFFERSORT                                                                                                                      28432   5509
1722201563            0 TABLE ACCESSFULL                                        LV_BUSI_RECORD                 TABLE                                      862   5509
1722201563            0 TABLE ACCESSBY INDEX ROWID                              BS_FAMILY                      TABLE                                    19402      0
                                                                                                                                                            6


HASH_VALUE CHILD_NUMBER OPERATION                                               OBJECT_NAME                    OBJECT_TYPE                               COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563            0 BITMAP CONVERSIONTO ROWIDS
1722201563            0 BITMAP AND
1722201563            0 BITMAP CONVERSIONFROM ROWIDS
1722201563            0 INDEXRANGE SCAN                                         PK_BS_FAMILY                   INDEX (UNIQUE)                               1
1722201563            0 BITMAP CONVERSIONFROM ROWIDS
1722201563            0 INDEXRANGE SCAN                                         IDX_BS_FAMILY_CORP_ID          INDEX                                        1
1722201563            0 INDEXRANGE SCAN                                         INDEX_FAMILY_ID                INDEX                                        1      0
1722201563            0 TABLE ACCESSFULL                                        LV_BUSI_BILL                   TABLE                                      393    847
1722201563            0 TABLE ACCESSFULL                                        LV_BUSI_RECORD                 TABLE                                      867  15524
1722201563            0 TABLE ACCESSFULL                                        BS_FAMILY                      TABLE                                     4407  23768
1722201563            0 TABLE ACCESSFULL                                        LV_BUSI_ASSIGN                 TABLE                                      913  12421
1722201563            0 INDEXRANGE SCAN                                         INDEX_FAMILY_ID                INDEX                                        1   3236
1722201563            1 SELECT STATEMENTFIRST_Cost=197255                                                                                               19725
                                                                                                                                                            5

1722201563            1 SORTORDER BY                                                                                                                    19725    907
                                                                                                                                                            5

1722201563            1 HASHGROUP BY                                                                                                                    19725    907
                                                                                                                                                            5

1722201563            1 HASH JOIN                                                                                                                       19704    907
                                                                                                                                                            8

1722201563            1 HASH JOIN                                                                                                                       19613    516
                                                                                                                                                            0

1722201563            1 TABLE ACCESSFULL                                        BS_CORP                        TABLE                                       85     41
1722201563            1 NESTED LOOPSSEMI                                                                                                                19604    389
                                                                                                                                                            4

1722201563            1 NESTED LOOPS                                                                                                                    19604    386
                                                                                                                                                            3

1722201563            1 NESTED LOOPS                                                                                                                    19488    278
                                                                                                                                                            0


HASH_VALUE CHILD_NUMBER OPERATION                                               OBJECT_NAME                    OBJECT_TYPE                               COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563            1 HASH JOINSEMI                                                                                                                   19442     75
                                                                                                                                                            1

1722201563            1 TABLE ACCESSFULL                                        LV_BUSI_BILL                   TABLE                                      392     70
1722201563            1 VIEW                                                    VW_SQ_1                        VIEW                                     19402     36
                                                                                                                                                            8

1722201563            1 FILTER
1722201563            1 NESTED LOOPS                                                                                                                    19402    241
                                                                                                                                                            8

1722201563            1 NESTED LOOPS                                                                                                                    19402    157
                                                                                                                                                            6

1722201563            1 MERGE JOINCARTESIAN                                                                                                             28436 264415
1722201563            1 INLIST ITERATOR
1722201563            1 INDEXRANGE SCAN                                         PK_BS_CORP                     INDEX (UNIQUE)                               4      0
1722201563            1 BUFFERSORT                                                                                                                      28432   5509
1722201563            1 TABLE ACCESSFULL                                        LV_BUSI_RECORD                 TABLE                                      862   5509
1722201563            1 TABLE ACCESSBY INDEX ROWID                              BS_FAMILY                      TABLE                                    19402      0
                                                                                                                                                            6

1722201563            1 BITMAP CONVERSIONTO ROWIDS
1722201563            1 BITMAP AND
1722201563            1 BITMAP CONVERSIONFROM ROWIDS
1722201563            1 INDEXRANGE SCAN                                         PK_BS_FAMILY                   INDEX (UNIQUE)                               1
1722201563            1 BITMAP CONVERSIONFROM ROWIDS
1722201563            1 INDEXRANGE SCAN                                         IDX_BS_FAMILY_CORP_ID          INDEX                                        1
1722201563            1 INDEXRANGE SCAN                                         INDEX_FAMILY_ID                INDEX                                        1      0
1722201563            1 TABLE ACCESSBY INDEX ROWID                              LV_BUSI_RECORD                 TABLE                                        1      0
1722201563            1 INDEXRANGE SCAN                                         IDX_BUSI_BILL_BILLSN           INDEX                                        1

HASH_VALUE CHILD_NUMBER OPERATION                                               OBJECT_NAME                    OBJECT_TYPE                               COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563            1 TABLE ACCESSBY INDEX ROWID                              BS_FAMILY                      TABLE                                        1      0
1722201563            1 INDEXUNIQUE SCAN                                        PK_BS_FAMILY                   INDEX (UNIQUE)                               1
1722201563            1 INDEXRANGE SCAN                                         INDEX_FAMILY_ID                INDEX                                        1   3236
1722201563            1 TABLE ACCESSFULL                                        LV_BUSI_ASSIGN                 TABLE                                      913  12421

66 rows selected

块清除(Block Cleanouts)

当用户发出提交(commit)之后,oracle怎样来处理的.oracle是需要写出redo来保证故障时数据可以被
恢复,我们知道oracle并不需要在提交时就写出变更的数据块.那么在提交时,oracle会对数据块进行什么操作?

在事务需要修改数据时,必须分配ITL事务槽,必须锁定行,必须分配回滚段事务槽和回滚表空间来记录要修改
的数据的前镜像.当事务提交时,oracle需要将回滚段上的事务表信息标记为非活动,以便空间可以被重用
那么还有ITL事务信息和锁定信息需要清除,以记录提交.

由于oracle在数据块上存储了ITL和锁定等事务信息,所以oracle必须在事务提交之后清除这些事务数据,
这就是块清除.块清除主要清除的数据有行级锁,ITL信息(包括提交标志,scn等).

如果提交时修改过的数据块仍然在buffer cache中,那么oracle可以清除ITL信息,这种清除叫做快速块清除
(fast block cleanout),快速块清除还有一个限制,当修改的块数量超过buffer cache的10%,则对超出的部
分不再进行快速块清除.

如果提交事务时,修改过的数据块已经被写回到数据文件上(或大量修改超出10%的部分),再次读出该数据块
进行修改,显然成本过于高昂,对于这种情况,oracle选择延迟块清除(delayed block cleanout),等到下一次
访问该block时再来清除ITL锁定信息,这就是延迟块清除.oracle通过延迟块清除来提高数据库的性能,加快
提交操作.快速提交是最普遍的情况.来看一下延迟块清除的处理.
进行测试:

SQL> update emp set sal=4000 where empno=7788;
1 row updated.

SQL> update emp set sal=4000 where empno=7782;

1 row updated.

SQL> update emp set sal=4000 where empno=7698;

1 row updated.

更新完成之后,强制刷新buffer cache,将buffer cache中的数据都写出到数据文件:

SQL> alter session set events='immediate trace name flush_cache';

Session altered

此时再提交事务;

SQL> commit;

Commit complete.

由于此时更新过的数据已经写出到数据文件,oracle将执行延迟块清除,将此时的数据块和回滚段转储出来:

[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 10:18:56 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system dump datafile 4 block 32;

System altered.

SQL> alter system dump undo header '_SYSSMU9$';

System altered.

SQL> alter system dump datafile 2 block 1350;

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 sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.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 sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_5023.trc

查看跟踪文件信息,看数据块上的信息,ITL事务信息仍然存在:
其中scn表示提交commit scn,fsc表示快速提交scn

*** 2013-01-07 10:19:33.032
*** SERVICE NAME:(SYS$USERS) 2013-01-07 10:19:33.031
*** SESSION ID:(140.421) 2013-01-07 10:19:33.031
Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e6ebb seq: 0x01 flg: 0x04 tail: 0x6ebb0601
frmt: 0x02 chkval: 0xf364 type: 0x06=trans data

Block header dump:  0x01000020
 Object id on Block? Y
 seg/obj: 0xc7cc  csc: 0x00.e6ebb  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000019 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000181  0x00800546.0129.18  ----    3  fsc 0x0002.00000000
0x02   0x0002.010.00000158  0x00800598.013f.26  C---    0  scn 0x0000.000d4495
xid=0x0009.01d.00000181的事务lck=3

数据块的锁定信息仍然存在:

tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4d 63
col  1: [ 5]  42 4c 41 4b 45
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 05 01 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 1f
tab 0, row 6, @0x1d39
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4e 53
col  1: [ 5]  43 4c 41 52 4b
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 06 09 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4e 59
col  1: [ 5]  53 43 4f 54 54
col  2: [ 7]  41 4e 41 4c 59 53 54
col  3: [ 3]  c2 4c 43
col  4: [ 7]  77 bb 04 13 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 15

再来看回滚段的信息:

*** 2013-01-07 10:20:01.417

********************************************************************************
Undo Segment:  _SYSSMU9$ (9)
********************************************************************************

Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00800546.0129.18 ext: 0xa  spc: 0x12ea
    uba: 0x00000000.0129.05 ext: 0xa  spc: 0x1e08
    uba: 0x00000000.0129.42 ext: 0xa  spc: 0x73e
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
  TRN TBL::

index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
0x1d    9    0x00  0x0181  0xffff  0x0000.000e743c  0x00800546  0x0000.000.00000000  0x00000001   0x00000000  1357525082

事务提交,事务表已经释放。如果此时查询scott.emp表,数据库将产生延迟块清除:

SQL> set autotrace on
SQL> select * from scott.emp;

EMPNO      ENAME      JOB              MGR HIREDATE            SAL       COMM      DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800         20

      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300        30

      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500        30
      
      7566 JONES      MANAGER         7839 02-APR-81          2975         20

      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400        30

      7698 BLAKE      MANAGER         7839 01-MAY-81          4000         30

      7782 CLARK      MANAGER         7839 09-JUN-81          4000         10

      7788 SCOTT      ANALYST         7566 19-APR-87          4000         20

      7839 KING       PRESIDENT            17-NOV-81          5000         10
      
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0        30

      7876 ADAMS      CLERK           7788 23-MAY-87          1100         20

      7900 JAMES      CLERK           7698 03-DEC-81           950         30
      
      7902 FORD       ANALYST         7566 03-DEC-81          3000         20

      7934 MILLER     CLERK           7782 23-JAN-82          1300         10


14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
      345 redo size
       1413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

注意,在此查询是产生了物理读取和redo,这个redo就是因为延迟块清除导致的,再次查询则不会
产生redo了:

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM  DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ----------  ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800         20

      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300        30

      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500        30
      
      7566 JONES      MANAGER         7839 02-APR-81          2975         20

      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400        30

      7698 BLAKE      MANAGER         7839 01-MAY-81          4000         30

      7782 CLARK      MANAGER         7839 09-JUN-81          4000         10

      7788 SCOTT      ANALYST         7566 19-APR-87          4000         20

      7839 KING       PRESIDENT            17-NOV-81          5000         10


      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0        30

      7876 ADAMS      CLERK           7788 23-MAY-87          1100         20

      7900 JAMES      CLERK           7698 03-DEC-81           950         30

      7902 FORD       ANALYST         7566 03-DEC-81          3000         20

      7934 MILLER     CLERK           7782 23-JAN-82          1300         10


14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

再次转储一下该数据块和回滚段:

SQL> alter system dump datafile 4 block 32;

System altered.

SQL> alter system dump undo header '_SYSSMU9$';

System altered.

SQL> alter system dump datafile 2 block 1350;

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 sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.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 sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_5043.trc

查看跟踪文件,看到此时ITL事务信息已经清除,但是注意,这里的xid和uba信息仍然存在:

Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e7560 seq: 0x01 flg: 0x00 tail: 0x75600601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump:  0x01000020
 Object id on Block? Y
 seg/obj: 0xc7cc  csc: 0x00.e7560  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000019 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000181  0x00800546.0129.18  C---    0  scn 0x0000.000e743c
0x02   0x0002.010.00000158  0x00800598.013f.26  C---    0  scn 0x0000.000d4495

数据块块的锁定位也已经清除了:
tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4d 63
col  1: [ 5]  42 4c 41 4b 45
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 05 01 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 1f
tab 0, row 6, @0x1d39
tl: 40 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4e 53
col  1: [ 5]  43 4c 41 52 4b
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 06 09 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4e 59
col  1: [ 5]  53 43 4f 54 54
col  2: [ 7]  41 4e 41 4c 59 53 54
col  3: [ 3]  c2 4c 43
col  4: [ 7]  77 bb 04 13 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 15

提交之后的undo信息
当提交事务之后,回滚段事务表标记该事务为非活动,继续再来看一下回滚段数据块的信息,
看到这里的irb指向了0x28，此前的事务已经不可回滚

********************************************************************************
UNDO BLK:
xid: 0x0009.02f.00000181  seq: 0x129 cnt: 0x28  irb: 0x28  icl: 0x0   flg: 0x0000

看一下偏移量列表也已经新增了一条信息0x28 0x0a4c

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f3c     0x02 0x1ed0     0x03 0x1e54     0x04 0x1de8     0x05 0x1d3c
0x06 0x1c90     0x07 0x1c24     0x08 0x1b78     0x09 0x1acc     0x0a 0x1a20
0x0b 0x1974     0x0c 0x1908     0x0d 0x189c     0x0e 0x17f0     0x0f 0x1784
0x10 0x1718     0x11 0x166c     0x12 0x1600     0x13 0x1594     0x14 0x14e8
0x15 0x147c     0x16 0x13e4     0x17 0x1388     0x18 0x132c     0x19 0x1280
0x1a 0x11d4     0x1b 0x1128     0x1c 0x10ac     0x1d 0x1000     0x1e 0x0f54
0x1f 0x0ee8     0x20 0x0e7c     0x21 0x0e10     0x22 0x0da4     0x23 0x0cf8
0x24 0x0c4c     0x25 0x0ba0     0x26 0x0b24     0x27 0x0ab8     0x28 0x0a4c

再看前镜像0x18 0x132c的信息,仍然存在:

*-----------------------------
* Rec #0x18  slt: 0x1d  objn: 51148(0x0000c7cc)  objd: 51148  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x17
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800546.0129.17
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000020  hdba: 0x0100001b
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 1
col  5: [ 3]  c2 1d 33

虽然这个事务已经提交了,不可以回滚了,但是在覆盖之前,这个前镜像信息仍然存在,通过某些手段,还是可以获得这个信息的.比如回闪查询

redo的内容

oracle通过redo来实同快速提交,一方面是因为redo log file可以连续,顺序地快速写出,另外一个方面
也和redo记录的精简内容有关.

为了了解redo的内容,先需要了解两个概念:改变向量和重做记录
改变向量(change vector)
改变向量表示对数据库内某一个数据块所做的一次变更.改变向量(change vector)中包含了变更的数据
块的版本号,事务操作代码,变更从属数据块的地址(DBA)以及更新后的数据.例如,一个update事务包含
一系列的改变向量,对于数据块的修改是一个向量,对于回滚段的修改又是一处向量.

重做记录(redo record)
重做记录通常由一组改变向量组成,是一个改变向量的集合,代表一个数据库的变更(insert,update,delete
等操作),构成数据库变更的最小恢复单位.例如,一个update的重做记录包括相应的回滚段的改变向量和相应
的数据块的改变向量等.

假定发出了一个更新语句;
update scott.emp set sal=4000 where empno=7788;
看一下这个语句是怎么执行的
1.检查empno=7788记录在buffer cache中是否存在,如果不存在则读取到buffer cache中.
2.在回滚表空间的相应回滚段事务表上分配事务槽,这个操作需要记录redo信息.
3.从回滚段读入或者在buffer cache中创建sal=3000的前镜像,这需要产生redo信息并记入redo log buffer
4.修改sal=4000,这是update的数据变更,需要记入redo log buffer
5.当用户提交时,会在redo log buffer记录提交信息,并在回滚段标记该事务为非激活(Inactive)

下面通过珍上具体的试验来再现这个过程.
1.先通过switch logfile切换日志,使用sys用户进行日志切换,使得接下来的更新可以使用新的日志.

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
         1          1         14   52428800          1 NO  ACTIVE                  801067 03-JAN-14

         2          1         15   52428800          1 NO  CURRENT                813289 04-JAN-13

         3          1         13   52428800          1 NO  INACTIVE               764715 30-DEC-13

2.更新并提交事务

SQL> select * from scott.emp a where a.empno=7788;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87          3000         20


SQL> update scott.emp  set sal=4000 where empno=7788;

1 row updated.

SQL> commit;

Commit complete.

3.使用sys用户在另外的session转储日志文件:

SQL> alter system dump logfile '/u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.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 sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.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 sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_3969.trc

4.获取trace文件
从日志文件的转储信息中,可以找到这个事务信息(sid=149,serial#=930)

*** SERVICE NAME:(SYS$USERS) 2013-01-04 17:06:48.813
*** SESSION ID:(149.930) 2013-01-04 17:06:48.813

改变向量1
这是对于回滚段头的修改,分配事务表，从绝对文件号为2(AFN:2)可以知道这是UNDO
表空间,通过UBA的DBA换算能力找到相应的block

SQL> SELECT DBMS_UTILITY.data_block_address_file (
  2            TO_NUMBER(LTRIM('0x00800099', '0x'), 'xxxxxxxx'))
  3            AS file_no,
  4         DBMS_UTILITY.data_block_address_block (
  5            TO_NUMBER(LTRIM ('0x00800099', '0x'), 'xxxxxxxx'))
  6            AS block_no
  7    FROM DUAL;

   FILE_NO   BLOCK_NO
---------- ----------
         2        153


REDO RECORD - Thread:1 RBA: 0x00000f.00000023.0010 LEN: 0x0200 VLD: 0x0d
SCN: 0x0000.000c68fd SUBSCN:  1 01/04/2013 17:04:58

CHANGE #2 TYP:0 CLS:35 AFN:2 DBA:0x00800099 OBJ:4294967295 SCN:0x0000.000c6859 SEQ:  1 OP:5.2
ktudh redo: slt: 0x0025 sqn: 0x00000137 flg: 0x0012 siz: 128 fbi: 0
            uba: 0x0080106d.0102.29    pxid:  0x0000.000.00000000


CHANGE #4 TYP:0 CLS:36 AFN:2 DBA:0x0080106d OBJ:4294967295 SCN:0x0000.000c6858 SEQ:  1 OP:5.1
ktudb redo: siz: 128 spc: 2418 flg: 0x0012 seq: 0x0102 rec: 0x29
            xid:  0x000a.025.00000137
ktubl redo: slt: 37 rci: 0 opc: 11.1 objn: 51148 objd: 51148 tsn: 4
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x0080106d.0102.28
prev ctl max cmt scn:  0x0000.000c61f3  prev tx cmt scn:  0x0000.000c61ff
txn start scn:  0x0000.00000000  logon user: 0  prev brb: 8392808  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01000020  hdba: 0x0100001b
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 0
Vector content:
col  5: [ 2]  c2 1f

改变向量2
这里记录的是前镜像信息,注意到”col 5: [ 2] c2 1f记录的就是对于col5的修改,修改前的数据值3000(c21f)

SQL>  select utl_raw.cast_to_number('c21f') from dual;

UTL_RAW.CAST_TO_NUMBER('C21F')
------------------------------
                          3000

改变向量3
这里记录的是对于数据块的修改,”col 5: [ 2] c2 29″记录的是对于col5的修改,
修改后的值为4000(c229)

SQL>   select utl_raw.cast_to_number('c229') from dual;

UTL_RAW.CAST_TO_NUMBER('C229')
------------------------------
                          4000

CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x01000020 OBJ:51148 SCN:0x0000.0006bfdb SEQ: 16 OP:11.5
KTB Redo
op: 0x11  ver: 0x01
op: F  xid:  0x000a.025.00000137    uba: 0x0080106d.0102.29
Block cleanout record, scn:  0x0000.000c68f4 ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0000.0006bfdb
KDO Op code: URP row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01000020  hdba: 0x0100001b
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 2 ckix: 191
ncol: 8 nnew: 1 size: 0
Vector content:
col  5: [ 2]  c2 29

改变向量4
当事务提交之后,记录的scn信息,注意这里标记为”MEDIA RECOVERY MARKER SCN”,也就是说,这是一个
可以恢复的时间点,事务的恢复必须以redo record为最小单位

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

session信息
最前面的部分记录的是产生这些redo的session信息

*** 2013-01-04 17:06:48.813
*** SERVICE NAME:(SYS$USERS) 2013-01-04 17:06:48.813
*** SESSION ID:(149.930) 2013-01-04 17:06:48.813

对于redo日志来说,重做信息却相当精简,oracle只需要记录那些重构事务必须的信息(如事务号,文件号,块号,
行号,字段等)即可,这个数据量大大减少

oracle回闪查询的新特性

从oracle9i开始,oracle开始提供回闪查询特性(flashback query),允许将回滚段中的数据进行回闪,通过下面的例子来看一下这个从oracle9i开始提供的新特性.

SQL> update emp set sal=4000 where empno=7788;

1 row updated.
SQL> update emp set sal=4000 where empno=7782;

1 row updated.

SQL> update emp set sal=4000 where empno=7698;

1 row updated.

先不提交这个事务,在另外窗口新开session,使用sys用户查询相关信息,进行进一步的分析

获得事务信息
从事务表中可以获得关于这个事务的信息,该事务位于9号回滚段(XIDUSN),在9号回滚段
上,该事务位于第29号事务槽(XIDSLOT):

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         9         29        385       1350          2         22

从v$rollstat视图中也可获得事务信息,xacts字段代表的是活动事务的数量,同样看到该事务
位于9号回滚段

SQL> select usn,writes,rssize,xacts,hwmsize,shrinks,wraps from v$rollstat;

       USN     WRITES     RSSIZE      XACTS    HWMSIZE    SHRINKS      WRAPS
---------- ---------- ---------- ---------- ---------- ---------- ----------
         0       7620     385024          0     385024          0          0
         1      21390   29351936          0   29351936          0          0
         2      22108    3268608          0    3268608          0          0
         3      29954     450560          0     450560          0          0
         4      23700     843776          0     843776          0          0
         5      23334     450560          0     450560          0          0
         6      21082     450560          0     450560          0          0
         7      23146    2285568          0    2285568          0          0
         8      28742     843776          0     843776          0          1
         9      22648    2088960          1    2088960          0          0
        10      24326    2220032          0    2220032          0          0

11 rows selected.

这是执行alter system dump datafile 2 block 1350
转储的回滚表空间中的数据块的信息的一部分

*-----------------------------
* Rec #0x1d  slt: 0x24  objn: 517(0x00000205)  objd: 517  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x00
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
uba: 0x00800546.0129.1b ctl max scn: 0x0000.000e4e9c prv tx scn: 0x0000.000e4ea6
txn start scn: scn: 0x0000.000e7526 logon user: 0
 prev brb: 8389956 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0x0006.016.0000015d uba: 0x00800419.00fe.11
                      flg: C---    lkc:  0     scn: 0x0000.000e7524
KDO Op code: URP row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0040100f  hdba: 0x00401001
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 116(0x74) flag: 0x2c lock: 0 ckix: 191
ncol: 9 nnew: 7 size: 0
Vector content:
col  2: [ 2]  c1 0a
col  3: [ 2]  c1 0a
col  4: [ 1]  80
col  5: [ 1]  80
col  6: [ 1]  80
col  7: [ 1]  80
col  8: [ 7]  78 71 01 07 0b 07 34

先注意到这里存在一个信息ctl max scn: 0x0000.000e4e9c,这个转换为scn值就是:

SQL> select (to_number('000','xxxx')*power(2,32)+to_number('e4e9c','xxxxxxxx')) scn
from dual;

       SCN
----------
    937628

查询一下当前数据的scn:

SQL> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
    949630

SQL>

通过特定的语法,可以将scn 937628的历史状态数据查询出来:

SQL> select * from scott.emp as of scn 937628 where empno in(7788,7782,7698);

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19       10.00               20

在查询结果中,注意到3名员工的薪水恢复到了之前的状态.而在当前的查询中,这个数据是变化
后的4000:

SQL> select * from scott.emp  where empno in(7788,7782,7698);

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7698 BLAKE      MANAGER    7839 1981-5-1      4000.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      4000.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     4000.00               20

由于这个查询需要从undo中获限前镜像信息,如果undo中的信息被覆盖,则以上查询将会失败.
测试一下,当新建undo表空间,切换undo表空间,再将原表空间offline之后:

SQL> create undo tablespace undotbs2 datafile
  2  '/u01/app/oracle/product/10.2.0/oradata/jingyong/undotbs02.dbf' size 10M;

Tablespace created


SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> alter tablespace undotbs1 offline;

Tablespace altered.

SQL> alter session set events='immediate trace name flush_cache';

Session altered.

再来查询,此时出现错误,记录该文件已经不可读取:

SQL> select * from scott.emp as of scn 937628 where empno in(7788,7782,7698);

select * from scott.emp as of scn 937628 where empno in(7788,7782,7698)

ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/product/10.2.0/oradata/jingyong/undotbs01.dbf'

将undotbs1重新启用:

SQL> alter tablespace undotbs1 online;

Tablespace altered.

SQL> alter system set undo_tablespace=undotbs1;

System altered.

此时前镜像信息再次可以查询,

SQL> select * from scott.emp as of scn 937628 where empno in(7788,7782,7698);

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19       10.00               20

如果在其他session中执行大量事务,使用前镜像信息被覆盖:
在执行其它大量事务之前观察回滚段的使用情况:

SQL> select usn,xacts,rssize,hwmsize from v$rollstat where usn=9;

       USN      XACTS     RSSIZE    HWMSIZE
---------- ---------- ---------- ----------
         9          0    2088960    2088960


SQL> begin
  2  for i in 1..2000 loop
  3   update scott.emp set sal=4000;
  4   rollback;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select usn,xacts,rssize,hwmsize from v$rollstat where usn=9;

       USN      XACTS     RSSIZE    HWMSIZE
---------- ---------- ---------- ----------
         9          1    4186112    4186112

那么再次查询就可能会收到如下错误:

SQL> select * from scott.emp as of scn 937628 where empno in(7788,7782,7698);
select * from scott.emp as of scn 937628 where empno in(7788,7782,7698)
ERROR at line 1:
ORA-01555: snapshot too old:rollback segment number 9 with name "_SYSSMU9$" too small

ORA-01555错误出现说明要查询的前镜偈信息已经丢失了.

使用errorstack进行错误跟踪
errorstack是oracle提供的接口用于诊断oracle的错误信息.

诊断事件可以在session级设置,也可以在系统级设置,通常如果要诊断全局错误,最好在
系统级设置.设置了errorstack事件之后,oracle会将出错时的信息记入跟踪文件中.
用户就可以通过跟踪文件进行错误诊断和排查.

可以通过errorstack事件来跟踪ora-01555错误:

OS Pid: 2928 executed alter session set events '1555 trace name errorstack level 4'
ORA-01555 caused by SQL statement below (SQL ID: bnaqc462nnjtb, Query Duration=0 sec, SCN: 0x0000.00068478):
Mon Jan  7 20:23:35 2013
select * from scott.emp as of scn 427128 where empno in(7788,7782,7698)
Mon Jan  7 20:23:35 2013
Errors in file /u01/app/oracle/admin/jingyong/udump/jingyong_ora_2928.trc:
ORA-01555: snapshot too old: rollback segment number 3 with name "???" too small

这里注意到,触发ora-01555错误的语句被记录,出现错误的scn也被记录,这个scn:0x0000.00068478
找到jingyong_ora_2928.trc跟踪文件,就可以获得关于这次错误的相关信息用于诊断.
错误信息如下

ksedmp: internal or fatal error
ORA-01555: snapshot too old: rollback segment number 3 with name "???" too small
Current SQL statement for this session:
select * from scott.emp as of scn 427128 where empno in(7788,7782,7698)

数据块信息,这里的块头就包含了ITL信息,根据这个ITL信息中的UBA,oracle可以定位回滚段.
查询前镜像信息,如果不存在,就可能出现ora-01555错误

Block header dump:  0x01000024
 Object id on Block? Y
 seg/obj: 0xc7cd  csc: 0x00.6bfcb  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1000021 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0003.011.000000f2  0x00805794.00c8.4a  --U-   14  fsc 0x0000.0006bfdb

oracle 10g回闪查询特性的增强
oracle9i提供的闪回特性增强,为恢复带来了极大的方便,但是oracle9i的回闪查询只能提供某个
时间点的数据视图,并不能告诉用户这样的数据经过几个事务,怎样被修改的(update,insert,delete)
,而这些信息在回滚段中是存在的,在oracle10g中,oracle进一步加强了回闪查询的特性,提供以下
两种回闪查询:
回闪版本查询(flashback versions query)
回闪事务查询(flashback transaction query)

回闪版本查询允许使用一个新的versions子句查询两个时间点或者scn之间的数据版本.
这些版本可以按照事务进行区分,回闪版本查询只返回提交数据.没提交的数据不被显示

先创建一个测试表,执行一些DML操作

SQL> create table test as select username from dba_users;

Table created.

SQL> select * from test;

USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
JYTEST1
JYTEST
OUTLN
MDSYS
ORDSYS

USERNAME
------------------------------
EXFSYS
DMSYS
WMSYS
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS
TSMSYS
BI

USERNAME
------------------------------
PM
MDDATA
IX
SH
DIP
OE
HR

29 rows selected.

SQL> delete from test where username='BI';

1 row deleted.

SQL> delete from test where username='JYTEST1';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from test;

USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
JYTEST
OUTLN
MDSYS
ORDSYS
EXFSYS

USERNAME
------------------------------
DMSYS
WMSYS
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS
TSMSYS
PM
MDDATA

USERNAME
------------------------------
IX
SH
DIP
OE
HR

27 rows selected.

再多执行一些DML操作

SQL> update test set username='JYTEST1' where username='JYTEST';

1 row updated.

SQL> delete from test where username='IX';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from test;

USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
JYTEST1
OUTLN
MDSYS
ORDSYS
EXFSYS

USERNAME
------------------------------
DMSYS
WMSYS
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS
TSMSYS
PM
MDDATA

USERNAME
------------------------------
SH
DIP
OE
HR
JY

27 rows selected.

再来看看oracle10g的回闪版本查询,通过使用versions子句,和对数据表引入了一系列的伪列(
version_startime等),可以获得对数据表的所有事务操作,注意以下输出中的versions_operation
代表了不同类型的操作(D-Delete,I-Insert,U-Update),versions_xid是一个重要数据,代表了不
同版本的事务ID:

SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,
  2  username from test versions between timestamp minvalue and maxvalue;

VERSIONS_STARTTIME           VERSIONS_ENDTIME            VERSIONS_XID     VERSIONS_OPERATION USERNAME
--------------------------- ---------------------------- ---------------- ------------------ ---------------------- --------
07-1月 -13 11.07.58 下午                                 0300150096010000 I                  JY
07-1月 -13 11.06.46 下午                                 06002B0093010000 D                  IX
07-1月 -13 11.06.46 下午                                 06002B0093010000 U                  JYTEST1
07-1月 -13 11.05.22 下午                                 05001E00A7010000 D                  JYTEST1
07-1月 -13 11.05.22 下午                                 05001E00A7010000 D                  BI
                                                                                             MGMT_VIEW
                                                                                             SYS
                                                                                             SYSTEM
                                                                                             DBSNMP
                                                                                             SYSMAN
                                                                                             SCOTT
                            07-1月 -13 11.05.22 下午                                         JYTEST1
                            07-1月 -13 11.06.46 下午                                         JYTEST
                                                                                             OUTLN
                                                                                             MDSYS
                                                                                             ORDSYS
                                                                                             EXFSYS
                                                                                             DMSYS
                                                                                             WMSYS
                                                                                             CTXSYS

VERSIONS_STARTTIME          VERSIONS_ENDTIME             VERSIONS_XID     VERSIONS_OPERATION USERNAME
--------------------------- ---------------------------- ---------------- ------------------ ---------------------- --------
                                                                                             ANONYMOUS
                                                                                             XDB
                                                                                             ORDPLUGINS
                                                                                             SI_INFORMTN_SCHEMA
                                                                                             OLAPSYS
                                                                                             TSMSYS
                            07-1月 -13 11.05.22 下午                                         BI
                                                                                             PM
                                                                                             MDDATA
                            07-1月 -13 11.06.46 下午                                         IX
                                                                                             SH
                                                                                             DIP
                                                                                             OE
                                                                                             HR

34 rows selected

通过以上输出,根据versions_xid可以清晰地区分不同事务在不同时间对数据所作的更改

具备了flashback version query查询的基础,就可以进行基于flashback version query的事务级恢复了.
这就是flashback transaction query, flashback transaction query可以从flashback transaction query
视图中获得指定事务的历史信息以及undo_sql,通过undo_sql,就可以撤消特定的提交事务.flashback
transaction query需要用到flashback_transaction_query视图,先看一下视图:

SQL> desc flashback_transaction_query
Name             Type           Nullable Default Comments
---------------- -------------- -------- ------- -----------------------------------------
XID              RAW(8)         Y                Transaction identifier
START_SCN        NUMBER         Y                Transaction start SCN
START_TIMESTAMP  DATE           Y                Transaction start timestamp
COMMIT_SCN       NUMBER         Y                Transaction commit SCN
COMMIT_TIMESTAMP DATE           Y                Transaction commit timestamp
LOGON_USER       VARCHAR2(30)   Y                Logon user for transaction
UNDO_CHANGE#     NUMBER         Y                1-based undo change number
OPERATION        VARCHAR2(32)   Y                forward operation for this undo
TABLE_NAME       VARCHAR2(256)  Y                table name to which this undo applies
TABLE_OWNER      VARCHAR2(32)   Y                owner of table to which this undo applies
ROW_ID           VARCHAR2(19)   Y                rowid to which this undo applies
UNDO_SQL         VARCHAR2(4000) Y                SQL corresponding to this undo

该视图的定义为:

select xid, start_scn, start_timestamp,
          decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn)
          commit_scn, commit_timestamp,
          logon_user, undo_change#, operation, table_name, table_owner,
          row_id, undo_sql
from sys.x$ktuqqry;

对于x$ktuqqry的查询非常耗时
测试一下对于x$ktuqqry表的查询

SQL> select count(addr) from x$ktuqqry;

COUNT(ADDR)
-----------
      74786

如果需要撤消xid=0300150096010000的事务,可以通过如下步骤进行:

SQL> set autotrace on
SQL> select undo_sql from flashback_transaction_query where xid='0300150096010000';

UNDO_SQL
--------------------------------------------------------------------------------
delete from "SYS"."TEST" where ROWID = 'AAAM3VAABAAAO9KAAd';



Execution Plan
----------------------------------------------------------
Plan hash value: 1115820779

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |  2008 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$KTUQQRY |     1 |  2008 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(RAWTOHEX("XID")='0300150096010000')


Statistics
----------------------------------------------------------
      90926  recursive calls
          0  db block gets
     238964  consistent gets
      21783  physical reads
          0  redo size
        506  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
       4115  sorts (memory)
          0  sorts (disk)
          2  rows processed

通过执行相应的undo语句可以撤消该事务,通过这些新特性,oracle提供了一种”回滚”提交事务的
方法,极大地方便了用户应对不同情况的数据库恢复.

1.从DML更新事务开始

来看这个更新语句:

SQL> conn scott/scott
Connected.
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800         20

      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      
      7566 JONES      MANAGER         7839 02-APR-81          2975         20

      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81          2850         30

      7782 CLARK      MANAGER         7839 09-JUN-81          2450         10

      7788 SCOTT      ANALYST         7566 19-APR-87            10         20

      7839 KING       PRESIDENT            17-NOV-81          5000         10

      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0        30

      7876 ADAMS      CLERK           7788 23-MAY-87          1100         20

      7900 JAMES      CLERK           7698 03-DEC-81           950         30

      7902 FORD       ANALYST         7566 03-DEC-81          3000         20

      7934 MILLER     CLERK           7782 23-JAN-82          1300         10


14 rows selected.

SQL>

SQL> select sal from emp where empno=7788;

       SAL
----------
        10

SQL> update emp set sal=4000 where empno=7788;

1 row updated.

SQL> select sal from emp where empno=7788;

       SAL
----------
      4000

先不提交这个事务,在另外窗口新开session,使用sys用户查询相关信息,进行进一步的分析

2.获得事务信息
从事务表中可以获得关于这个事务的信息,该事务位于9号回滚段(XIDUSN),在9号回滚段
上,该事务位于第29号事务槽(XIDSLOT):

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         9         29        385       1350          2         22

从v$rollstat视图中也可获得事务信息,xacts字段代表的是活动事务的数量,同样看到该事务
位于9号回滚段

SQL> select usn,writes,rssize,xacts,hwmsize,shrinks,wraps from v$rollstat;

       USN     WRITES     RSSIZE      XACTS    HWMSIZE    SHRINKS      WRAPS
---------- ---------- ---------- ---------- ---------- ---------- ----------
         0       7620     385024          0     385024          0          0
         1      21390   29351936          0   29351936          0          0
         2      22108    3268608          0    3268608          0          0
         3      29954     450560          0     450560          0          0
         4      23700     843776          0     843776          0          0
         5      23334     450560          0     450560          0          0
         6      21082     450560          0     450560          0          0
         7      23146    2285568          0    2285568          0          0
         8      28742     843776          0     843776          0          1
         9      22648    2088960          1    2088960          0          0
        10      24326    2220032          0    2220032          0          0

11 rows selected.

获得回滚段名称并转储段头信息
查询v$rollname视图获得回滚段名称,并转储回滚段头信息:

SQL> select * from v$rollname a where a.usn=9;

       USN NAME
---------- ------------------------------
         9 _SYSSMU9$

SQL> alter system dump undo header '_SYSSMU9$';

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 sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.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 sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_2611.trc

4.获得跟踪文件信息
注意这就是前边多闪提到过的回滚段头信息,其中包括事务表信息,从以下的跟踪文件中,
可以清晰地看到这些内容:

*** 2013-01-07 08:19:09.209

********************************************************************************
Undo Segment:  _SYSSMU9$ (9)
********************************************************************************
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 17     #blocks: 255
                  last map  0x00000000  #maps: 0      offset: 4080
      Highwater::  0x00800546  ext#: 10     blk#: 61     ext size: 128
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 10
                   Unlocked
     Map Header:: next  0x00000000  #extents: 17   obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0080008a  length: 7
   0x008000f1  length: 8
   0x008001d1  length: 8
   0x008001d9  length: 8
   0x008001e1  length: 8
   0x008001e9  length: 8
   0x008001f1  length: 8
   0x008001f9  length: 8
   0x00800201  length: 8
   0x00800289  length: 8
   0x00800509  length: 128
   0x00800011  length: 8
   0x00800041  length: 8
   0x00800061  length: 8
   0x008000d9  length: 8
   0x008000e9  length: 8
   0x008000a9  length: 8

 Retention Table
  -----------------------------------------------------------
 Extent Number:0  Commit Time: 1388711270
 Extent Number:1  Commit Time: 1388711270
 Extent Number:2  Commit Time: 1388711270
 Extent Number:3  Commit Time: 1388711270
 Extent Number:4  Commit Time: 1388711270
 Extent Number:5  Commit Time: 1388711270
 Extent Number:6  Commit Time: 1388711270
 Extent Number:7  Commit Time: 1388711270
 Extent Number:8  Commit Time: 1388711270
 Extent Number:9  Commit Time: 1388712870
 Extent Number:10  Commit Time: 0
 Extent Number:11  Commit Time: 1388710789
 Extent Number:12  Commit Time: 1388710854
 Extent Number:13  Commit Time: 1388710854
 Extent Number:14  Commit Time: 1388710854
 Extent Number:15  Commit Time: 1388711270
 Extent Number:16  Commit Time: 1388711270

  TRN CTL:: seq: 0x0129 chd: 0x001e ctl: 0x0019 inc: 0x00000000 nfb: 0x0000
            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00800546.0129.16 scn: 0x0000.000e4b58
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00000000.0129.15 ext: 0xa  spc: 0x1440
    uba: 0x00000000.0129.05 ext: 0xa  spc: 0x1e08
    uba: 0x00000000.0129.42 ext: 0xa  spc: 0x73e
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
  TRN TBL::

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x0181  0x0004  0x0000.000e5e2d  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357510314
   0x01    9    0x00  0x0181  0x0003  0x0000.000e570f  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357505284
   0x02    9    0x00  0x0181  0x0009  0x0000.000e5c13  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357508885
   0x03    9    0x00  0x0181  0x0007  0x0000.000e58ac  0x00800545  0x0000.000.00000000  0x00000002   0x00000000   1357506485
   0x04    9    0x00  0x0181  0x0006  0x0000.000e5f7d  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357511286
   0x05    9    0x00  0x0181  0x0002  0x0000.000e5c07  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357508885
   0x06    9    0x00  0x0181  0x000a  0x0000.000e5f89  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357511286
   0x07    9    0x00  0x0181  0x0008  0x0000.000e58b6  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357506485
   0x08    9    0x00  0x0181  0x002e  0x0000.000e5a56  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357507685
   0x09    9    0x00  0x0181  0x000b  0x0000.000e5c1e  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357508886
   0x0a    9    0x00  0x0181  0x0011  0x0000.000e5f93  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357511286
   0x0b    9    0x00  0x0181  0x002a  0x0000.000e5dba  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357510086
   0x0c    9    0x00  0x0181  0x0000  0x0000.000e5dd4  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357510086
   0x0d    9    0x00  0x0181  0x0010  0x0000.000e5fa8  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357511286
   0x0e    9    0x00  0x0181  0x0016  0x0000.000e62f8  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357513687
   0x0f    9    0x00  0x0181  0x0014  0x0000.000e6662  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357516088
   0x10    9    0x00  0x0181  0x0012  0x0000.000e5fb2  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357511286
   0x11    9    0x00  0x0181  0x000d  0x0000.000e5f9d  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357511286
   0x12    9    0x00  0x0181  0x0017  0x0000.000e6150  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357512487
   0x13    9    0x00  0x0181  0x002d  0x0000.000e64a1  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357514887
   0x14    9    0x00  0x0180  0x001b  0x0000.000e67fe  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357517287
   0x15    9    0x00  0x0181  0x000f  0x0000.000e6658  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357516088
   0x16    9    0x00  0x0181  0x0013  0x0000.000e6301  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357513687
   0x17    9    0x00  0x0181  0x000e  0x0000.000e615b  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357512487
   0x18    9    0x00  0x0180  0x0029  0x0000.000e53b5  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357502883
   0x19    9    0x00  0x0181  0xffff  0x0000.000e6813  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357517288
   0x1a    9    0x00  0x0181  0x0015  0x0000.000e64ba  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357514888
   0x1b    9    0x00  0x0181  0x0019  0x0000.000e6809  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357517288
   0x1c    9    0x00  0x0180  0x001f  0x0000.000e4cff  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357498082
   0x1d   10    0x80  0x0181  0x000a  0x0000.000e6858  0x00800546  0x0000.000.00000000  0x00000001   0x00000000  0
   0x1e    9    0x00  0x0180  0x001c  0x0000.000e4cf4  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357498082
   0x1f    9    0x00  0x0180  0x0024  0x0000.000e4e9c  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357499280
   0x20    9    0x00  0x0180  0x002f  0x0000.000e5059  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357500481
   0x21    9    0x00  0x0180  0x0005  0x0000.000e5a6a  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357507685
   0x22    9    0x00  0x0180  0x0023  0x0000.000e5044  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357500481
   0x23    9    0x00  0x0180  0x0020  0x0000.000e504f  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357500481
   0x24    9    0x00  0x0180  0x0022  0x0000.000e4ea6  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357499282
   0x25    9    0x00  0x0180  0x0026  0x0000.000e5568  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357504084
   0x26    9    0x00  0x0180  0x0001  0x0000.000e5705  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357505284
   0x27    9    0x00  0x0180  0x0028  0x0000.000e5201  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357501683
   0x28    9    0x00  0x0180  0x002c  0x0000.000e520c  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357501683
   0x29    9    0x00  0x0180  0x002b  0x0000.000e5553  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357504083
   0x2a    9    0x00  0x0180  0x000c  0x0000.000e5dc6  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357510086
   0x2b    9    0x00  0x0180  0x0025  0x0000.000e555e  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357504084
   0x2c    9    0x00  0x0180  0x0018  0x0000.000e53a9  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357502883
   0x2d    9    0x00  0x0180  0x001a  0x0000.000e64ac  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357514888
   0x2e    9    0x00  0x0180  0x0021  0x0000.000e5a60  0x00800545  0x0000.000.00000000  0x00000001   0x00000000   1357507685
   0x2f    9    0x00  0x0180  0x0027  0x0000.000e5064  0x00800544  0x0000.000.00000000  0x00000001   0x00000000   1357500483

回顾前面的事务信息,该事务正好占用的是第29号事务槽(0x1d),状态(state)为10代表是活动的事务

SQL> select to_number('1d','xx') from dual  ;

TO_NUMBER('1D','XX')
--------------------
                  29

5.转储前镜像信息
再来看DBA(Data Block Address),这个DBA指向的就是包含这个事务的前镜像的数据块地址
0x00800546

DBA代表数据块的存储地址,通过转换DBA计算出文件号和数据块号

SQL> SELECT DBMS_UTILITY.data_block_address_file (
  2            TO_NUMBER (LTRIM ('0x00800546', '0x'), 'xxxxxxxx'))
  3            AS file_no,
  4         DBMS_UTILITY.data_block_address_block (
  5            TO_NUMBER (LTRIM ('0x00800546', '0x'), 'xxxxxxxx'))
  6            AS block_no
  7    FROM DUAL;

   FILE_NO   BLOCK_NO
---------- ----------
         2       1350

经过转换后,该前镜像信息位于file 2, block 1350,这和从事务表中查询得的数据完全一致:

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         9         29        385       1350          2         22

为了说明一些其他内容,继续先前scott用户的事务,再更新2条记录:

SQL> update emp set sal=4000 where empno=7782;

1 row updated.

SQL> update emp set sal=4000 where empno=7698;

1 row updated.

将回滚段中的这个block转储出来:

SQL> alter system dump datafile 2 block 1350;

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 sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.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 sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4778.trc

这是跟踪文件开始部分的信息:

*** 2013-01-07 08:46:13.536
*** SERVICE NAME:(SYS$USERS) 2013-01-07 08:46:13.536
*** SESSION ID:(142.51) 2013-01-07 08:46:13.536
Start dump data blocks tsn: 1 file#: 2 minblk 1350 maxblk 1350
buffer tsn: 1 rdba: 0x00800546 (2/1350)
scn: 0x0000.000e6ba3 seq: 0x01 flg: 0x00 tail: 0x6ba30201
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1

********************************************************************************
UNDO BLK:
xid: 0x0009.01d.00000181  seq: 0x129 cnt: 0x18  irb: 0x18  icl: 0x0   flg: 0x0000

注意,这部分信息中有一个参数irb:0x18,irb指的是回滚段中记录的最近的没有提交变更的开始
之处,如果开始回滚,这是起始的搜索点.

接下来是回滚信息的偏移量,最后一个偏移地址正是0x18的信息

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f3c     0x02 0x1ed0     0x03 0x1e54     0x04 0x1de8     0x05 0x1d3c
0x06 0x1c90     0x07 0x1c24     0x08 0x1b78     0x09 0x1acc     0x0a 0x1a20
0x0b 0x1974     0x0c 0x1908     0x0d 0x189c     0x0e 0x17f0     0x0f 0x1784
0x10 0x1718     0x11 0x166c     0x12 0x1600     0x13 0x1594     0x14 0x14e8
0x15 0x147c     0x16 0x13e4     0x17 0x1388     0x18 0x132c

那么我们可以找到Rec 0x18的信息:

*-----------------------------
* Rec #0x18  slt: 0x1d  objn: 51148(0x0000c7cc)  objd: 51148  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x17
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800546.0129.17
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000020  hdba: 0x0100001b
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 1
col  5: [ 3]  c2 1d 33

从上面的信息中可以看到objn: 51148(0x0000c7cc) objd: 51148 tblspc: 4(0x00000004)
惹objn: 51148(0x0000c7cc) objd: 51148 是指object_id
tblspc: 4(0x00000004)是指表空间号4

SQL> select a.owner,a.object_name,a.object_type from dba_objects a where a.object_id=51148;

OWNER                          OBJECT_NAME       OBJECT_TYPE
------------------------------ ----------------- -------------------
SCOTT                          EMP               TABLE
SQL> select  * from v$tablespace;

       TS# NAME                           INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP
---------- ------------------------------ --------------------------- ------- ------------ -----------------
         0 SYSTEM                         YES                         NO      YES
         1 UNDOTBS1                       YES                         NO      YES
         2 SYSAUX                         YES                         NO      YES
         4 USERS                          YES                         NO      YES
         6 EXAMPLE                        YES                         NO      YES
         3 TEMP                           NO                          NO      YES

6 rows selected

SQL> select username,default_tablespace from dba_users where username='SCOTT';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT                          USERS

通过上面的查询可以知道0x18的前镜像记录是记录的对scott用户的emp表修改的记录的前镜像信息
col 5: [ 3] c2 1d 33
c2 id 33转换为十进制是2850

SQL> select utl_raw.cast_to_number('c21d33') from dual;

UTL_RAW.CAST_TO_NUMBER('C21D33
------------------------------
                          2850

这就是下面的更新语句的前镜像信息,oracle就是这样通过回滚段保留前镜像信息的

SQL> update emp set sal=4000 where empno=7698;
SQL> select empno,sal from scott.emp where empno in(7788,7782,7698);

     EMPNO        SAL
---------- ----------
      7698       2850
      7782       2450
      7788         10

在这条undo记录上,还记录一个数据rci,该参数代表的就是undo chain(同一个事务中的多次修改,
根据undo chain链接关联)的下一个偏移量,此处为0x17,找到0x17这条undo记录:

*-----------------------------
* Rec #0x17  slt: 0x1d  objn: 51148(0x0000c7cc)  objd: 51148  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x16
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800546.0129.16
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000020  hdba: 0x0100001b
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 6(0x6) flag: 0x2c lock: 0 ckix: 41
ncol: 8 nnew: 1 size: 1
col  5: [ 3]  c2 19 33

这里记录的c2 19 33转换为十进制就是2450,是第二条更新记录的前镜像信息

SQL> update emp set sal=4000 where empno=7782;

SQL> select utl_raw.cast_to_number('c21933') from dual;

UTL_RAW.CAST_TO_NUMBER('C21933')
------------------------------
                          2450

这条undo记录中的rci指向下一条记录是0x16,找到0x16:

*-----------------------------
* Rec #0x16  slt: 0x1d  objn: 51148(0x0000c7cc)  objd: 51148  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x00
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
uba: 0x00800546.0129.14 ctl max scn: 0x0000.000e4b4d prv tx scn: 0x0000.000e4b58
txn start scn: scn: 0x0000.000e684e logon user: 54
 prev brb: 8389956 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0x0009.00e.0000017e uba: 0x0080053d.0129.2f
                      flg: C---    lkc:  0     scn: 0x0000.000d444e
KDO Op code: URP row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01000020  hdba: 0x0100001b
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 0
Vector content:
col  5: [ 2]  c1 0b

这里的c10b转换十进制是10,正是第一条更新的记录:

SQL> update emp set sal=4000 where empno=7788;

SQL> select utl_raw.cast_to_number('c10b') from dual;

UTL_RAW.CAST_TO_NUMBER('C10B')
------------------------------
                            10

这是这个事务中最后一条更新数据,所以其实undo chain的指针rci为0x00,表示这是最后
一条记录.也可以从x$bh中找到这些数据块:

SQL> select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state
  2  from x$bh a,dba_extents b
  3  where b.relative_fno=a.dbarfil
  4  and b.block_id< =a.dbablk and b.block_id+b.blocks>a.dbablk
  5  and b.owner='SCOTT' and b.segment_name='EMP' order by a.dbablk asc;

SEGMENT_NAME                 FILE#    DBARFIL     DBABLK      CLASS      STATE
----------------------- ---------- ---------- ---------- ---------- ----------
EMP                              4          4         27          4          1
EMP                              4          4         28          1          1
EMP                              4          4         29          1          1
EMP                              4          4         30          1          1
EMP                              4          4         31          1          1
EMP                              4          4         32          1          3
EMP                              4          4         32          1          1

7 rows selected

注意class为4的是段头,class为1,块号为28到32的为数据块,如果此时在其他进程中查询
scott.emp表,oracle需要构造一致性读,通过前镜像把变化前的数据展现给用户:

SQL> select * from scott.emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19       10.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

14 rows selected

再来查询哪些数据是脏数据

SQL> select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state,
  2  decode(bitand(flag,1),0,'N','Y') dirty
  3  from x$bh a,dba_extents b
  4  where b.relative_fno=a.dbarfil
  5  and b.block_id< =a.dbablk and b.block_id+b.blocks>a.dbablk
  6  and b.owner='SCOTT' and b.segment_name='EMP' order by a.dbablk asc;

SEGMENT_NAME                   FILE#    DBARFIL     DBABLK      CLASS      STATE DIRTY
------------------------- ---------- ---------- ---------- ---------- ---------- -----
EMP                                4          4         27          4          1 N
EMP                                4          4         28          1          1 N
EMP                                4          4         29          1          1 N
EMP                                4          4         30          1          1 N
EMP                                4          4         31          1          1 N
EMP                                4          4         32          1          3 N
EMP                                4          4         32          1          3 N
EMP                                4          4         32          1          1 Y
EMP                                4          4         32          1          3 N

9 rows selected

注意此时,buffer cache中多出了两个数据块,也就是32存在4份,其中state为3的就是一致性读
构造的前镜像.dirty为Y的记录就是进行更改后没有提交的记录

6.转储数据块信息
在前镜像信息中,oracle还记录了前镜像对应的数据块的地址.可以从bdba记录中获得这部分信息
以上面数据为例bdba: 0x01000020记录了更改的数据块块地址.

SQL> SELECT DBMS_UTILITY.data_block_address_file (
  2            TO_NUMBER (LTRIM ('0x01000020', '0x'), 'xxxxxxxx'))
  3            AS file_no,
  4         DBMS_UTILITY.data_block_address_block (
  5            TO_NUMBER (LTRIM ('0x01000020', '0x'), 'xxxxxxxx'))
  6            AS block_no
  7    FROM DUAL;

   FILE_NO   BLOCK_NO
---------- ----------
         4         32
0x01000020记录的正是file 4,block 32

现在将数据表中的block转储出来,看看其中的记录是什么样的信息:

SQL> alter system dump datafile 4 block 32;

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 sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.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 sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4859.trc

检查跟踪文件信息:

** 2013-01-07 09:27:18.032
*** SERVICE NAME:(SYS$USERS) 2013-01-07 09:27:18.030
*** SESSION ID:(142.53) 2013-01-07 09:27:18.029
Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e6ebb seq: 0x01 flg: 0x04 tail: 0x6ebb0601
frmt: 0x02 chkval: 0xf364 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

Block header dump:  0x01000020
 Object id on Block? Y
 seg/obj: 0xc7cc  csc: 0x00.e6ebb  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000019 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000181  0x00800546.0129.18  ----    3  fsc 0x0002.00000000
0x02   0x0002.010.00000158  0x00800598.013f.26  C---    0  scn 0x0000.000d4495

其中scn表示提交commit scn,fsc表示快速提交scn
这里存在ITL事务槽信息,ITL事务槽指Interested Transaction List(ITL),事务必须
获得一个ITL事务槽才能进行数据修改,ITL内容主要包括:
Xid———Transaction ID;
Uba———Undo Block Address
Lck———Lock Status
注意:Xid=Undo.segment.number+transaction.table.slot.number+wrap

在上面的输出,看到itl1(0x01)上存在活动事务，将xid=0x0009.01d.00000181分解一下
该事务指向的回滚段号是9

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         9         29        385       1350          2         22

事务表的事务槽slot号为0x1d(转换为十进制是29)

SQL> select to_number('1d','xx') from dual;

TO_NUMBER('1D','XX')
--------------------
                  29
wrap#为0181正是dump回滚段看到的那个事务
TRN TBL::

index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
......
0x1d   10    0x80  0x0181  0x000a  0x0000.000e6858  0x00800546  0x0000.000.00000000  0x00000001   0x00000000  0
.....

可以看到,在数据块上同样存在指向回滚段的事务信息.
uba代表的是undo block address,指向具体的回滚段.可以看到ITL上uba=0x00800546.0129.18
将这个UBA进行分解:
0x00800546正是前镜像的数据块地址
seq:0129是顺序号
18是undo记录的开始地址(irb信息)
uba的内容和undo中的信息完全相符:

********************************************************************************
UNDO BLK:
xid: 0x0009.01d.00000181  seq: 0x129 cnt: 0x18  irb: 0x18  icl: 0x0   flg: 0x0000

继续向下可以找到这3条被修改的记录,锁定位信息LB指向0x01号ITL事务槽:

tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4d 63
col  1: [ 5]  42 4c 41 4b 45
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 05 01 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 1f
tab 0, row 6, @0x1d39
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4e 53
col  1: [ 5]  43 4c 41 52 4b
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 06 09 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4e 59
col  1: [ 5]  53 43 4f 54 54
col  2: [ 7]  41 4e 41 4c 59 53 54
col  3: [ 3]  c2 4c 43
col  4: [ 7]  77 bb 04 13 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 15

SQL> select utl_raw.cast_to_number('c229') from dual;

UTL_RAW.CAST_TO_NUMBER('C229')
------------------------------
                          4000

这个事务的过程如下:

TRN TBL::

index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
......
0x1d   10    0x80  0x0181  0x000a  0x0000.000e6858  0x00800546  0x0000.000.00000000  0x00000001   0x00000000  0
.....                                                                       |
                                                                            |
                                                                         TX table slot
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000181  0x00800546.0129.18  ----    3  fsc 0x0002.00000000
0x02   0x0002.010.00000158  0x00800598.013f.26  C---    0  scn 0x0000.000d4495
ITL
   data row1
    data row2  回滚表空间的数据块-------------------------------Undo block
     data row3 (UNDO BLK:                                          |
                xid: 0x0009.01d.00000181                           |
                seq: 0x129 cnt: 0x18  irb: 0x18                    |
                icl: 0x0   flg: 0x0000)                         

锁定位0x01 Rec #0x18 slt: 0x1d objn: 51148(0x0000c7cc) objd: 51148 …

数据段 回滚段

(1)当一个事务开始时,需要在回滚段事务表上分配一个事务槽.
(2)在数据块头部获得一个ITL事务槽,该事务槽指向回滚段头的事务槽
(3)在修改数据之前,需要记录前镜像信息,这个信息以undo record的形式存储在回滚段中,
回滚段头事务槽指向该记录.
(4)锁定修改的行,修改行锁定位(lb-lock byte)指向ITL事务槽
(5)数据修改可以进行
这是一个事务的基本过程

7.块清除(Block Cleanouts)
当用户发出提交(commit)之后,oracle怎样来处理的.oracle是需要写出redo来保证故障时数据可以被
恢复,我们知道oracle并不需要在提交时就写出变更的数据块.那么在提交时,oracle会对数据块进行什么操作?

在事务需要修改数据时,必须分配ITL事务槽,必须锁定行,必须分配回滚段事务槽和回滚表空间来记录要修改
的数据的前镜像.当事务提交时,oracle需要将回滚段上的事务表信息标记为非活动,以便空间可以被重用
那么还有ITL事务信息和锁定信息需要清除,以记录提交.

由于oracle在数据块上存储了ITL和锁定等事务信息,所以oracle必须在事务提交之后清除这些事务数据,
这就是块清除.块清除主要清除的数据有行级锁,ITL信息(包括提交标志,scn等).

如果提交时修改过的数据块仍然在buffer cache中,那么oracle可以清除ITL信息,这种清除叫做快速块清除
(fast block cleanout),快速块清除还有一个限制,当修改的块数量超过buffer cache的10%,则对超出的部
分不再进行快速块清除.

如果提交事务时,修改过的数据块已经被写回到数据文件上(或大量修改超出10%的部分),再次读出该数据块
进行修改,显然成本过于高昂,对于这种情况,oracle选择延迟块清除(delayed block cleanout),等到下一次
访问该block时再来清除ITL锁定信息,这就是延迟块清除.oracle通过延迟块清除来提高数据库的性能,加快
提交操作.快速提交是最普遍的情况.来看一下延迟块清除的处理.
继续进行测试:

SQL> update emp set sal=4000 where empno=7788;
1 row updated.

SQL> update emp set sal=4000 where empno=7782;

1 row updated.

SQL> update emp set sal=4000 where empno=7698;

1 row updated.

更新完成之后,强制刷新buffer cache,将buffer cache中的数据都写出到数据文件:

SQL> alter session set events='immediate trace name flush_cache';

Session altered

此时再提交事务;

SQL> commit;

Commit complete.

由于此时更新过的数据已经写出到数据文件,oracle将执行延迟块清除,将此时的数据块和回滚段转储出来:

[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 10:18:56 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system dump datafile 4 block 32;

System altered.

SQL> alter system dump undo header '_SYSSMU9$';

System altered.

SQL> alter system dump datafile 2 block 1350;

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 sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.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 sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_5023.trc

查看跟踪文件信息,看数据块上的信息,ITL事务信息仍然存在:

*** 2013-01-07 10:19:33.032
*** SERVICE NAME:(SYS$USERS) 2013-01-07 10:19:33.031
*** SESSION ID:(140.421) 2013-01-07 10:19:33.031
Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e6ebb seq: 0x01 flg: 0x04 tail: 0x6ebb0601
frmt: 0x02 chkval: 0xf364 type: 0x06=trans data

Block header dump:  0x01000020
 Object id on Block? Y
 seg/obj: 0xc7cc  csc: 0x00.e6ebb  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000019 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000181  0x00800546.0129.18  ----    3  fsc 0x0002.00000000
0x02   0x0002.010.00000158  0x00800598.013f.26  C---    0  scn 0x0000.000d4495
xid=0x0009.01d.00000181的事务lck=3

数据块的锁定信息仍然存在:

tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4d 63
col  1: [ 5]  42 4c 41 4b 45
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 05 01 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 1f
tab 0, row 6, @0x1d39
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4e 53
col  1: [ 5]  43 4c 41 52 4b
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 06 09 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4e 59
col  1: [ 5]  53 43 4f 54 54
col  2: [ 7]  41 4e 41 4c 59 53 54
col  3: [ 3]  c2 4c 43
col  4: [ 7]  77 bb 04 13 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 15

再来看回滚段的信息:

*** 2013-01-07 10:20:01.417

********************************************************************************
Undo Segment:  _SYSSMU9$ (9)
********************************************************************************

Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00800546.0129.18 ext: 0xa  spc: 0x12ea
    uba: 0x00000000.0129.05 ext: 0xa  spc: 0x1e08
    uba: 0x00000000.0129.42 ext: 0xa  spc: 0x73e
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
  TRN TBL::

index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
0x1d    9    0x00  0x0181  0xffff  0x0000.000e743c  0x00800546  0x0000.000.00000000  0x00000001   0x00000000   1357525082

事务提交,事务表已经释放。如果此时查询scott.emp表,数据库将产生延迟块清除:

SQL> set autotrace on
SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM  DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ----------  ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800         20

      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300          30

      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500          30

      7566 JONES      MANAGER         7839 02-APR-81          2975         20

      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400          30

      7698 BLAKE      MANAGER         7839 01-MAY-81          4000         30

      7782 CLARK      MANAGER         7839 09-JUN-81          4000         10

      7788 SCOTT      ANALYST         7566 19-APR-87          4000         20

      7839 KING       PRESIDENT            17-NOV-81          5000         10

      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0          30

      7876 ADAMS      CLERK           7788 23-MAY-87          1100         20

      7900 JAMES      CLERK           7698 03-DEC-81           950         30

      7902 FORD       ANALYST         7566 03-DEC-81          3000         20

      7934 MILLER     CLERK           7782 23-JAN-82          1300         10


14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

注意,在此查询是产生了物理读取和redo,这个redo就是因为延迟块清除导致的,再次查询则不会
产生redo了:

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800         20

      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81          2975         20

      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81          4000         30

      7782 CLARK      MANAGER         7839 09-JUN-81          4000         10

      7788 SCOTT      ANALYST         7566 19-APR-87          4000         20

      7839 KING       PRESIDENT            17-NOV-81          5000         10

      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87          1100         20

      7900 JAMES      CLERK           7698 03-DEC-81           950         30

      7902 FORD       ANALYST         7566 03-DEC-81          3000         20

      7934 MILLER     CLERK           7782 23-JAN-82          1300         10


14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

再次转储一下该数据块和回滚段:

SQL> alter system dump datafile 4 block 32;

System altered.

SQL> alter system dump undo header '_SYSSMU9$';

System altered.

SQL> alter system dump datafile 2 block 1350;

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 sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.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 sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_5043.trc

查看跟踪文件,看到此时ITL事务信息已经清除,但是注意,这里的xid和uba信息仍然存在:

Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e7560 seq: 0x01 flg: 0x00 tail: 0x75600601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump:  0x01000020
 Object id on Block? Y
 seg/obj: 0xc7cc  csc: 0x00.e7560  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000019 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000181  0x00800546.0129.18  C---    0  scn 0x0000.000e743c
0x02   0x0002.010.00000158  0x00800598.013f.26  C---    0  scn 0x0000.000d4495
其中scn表示提交commit scn,fsc表示快速提交scn
数据块块的锁定位也已经清除了:
tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4d 63
col  1: [ 5]  42 4c 41 4b 45
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 05 01 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 1f
tab 0, row 6, @0x1d39
tl: 40 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4e 53
col  1: [ 5]  43 4c 41 52 4b
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 06 09 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4e 59
col  1: [ 5]  53 43 4f 54 54
col  2: [ 7]  41 4e 41 4c 59 53 54
col  3: [ 3]  c2 4c 43
col  4: [ 7]  77 bb 04 13 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 15

8.提交之后的undo信息
当提交事务之后,回滚段事务表标记该事务为非活动,继续再来看一下回滚段数据块的信息,
看到这里的irb指向了0x28，此前的事务已经不可回滚

********************************************************************************
UNDO BLK:
xid: 0x0009.02f.00000181  seq: 0x129 cnt: 0x28  irb: 0x28  icl: 0x0   flg: 0x0000

看一下偏移量列表也已经新增了一条信息0x28 0x0a4c

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f3c     0x02 0x1ed0     0x03 0x1e54     0x04 0x1de8     0x05 0x1d3c
0x06 0x1c90     0x07 0x1c24     0x08 0x1b78     0x09 0x1acc     0x0a 0x1a20
0x0b 0x1974     0x0c 0x1908     0x0d 0x189c     0x0e 0x17f0     0x0f 0x1784
0x10 0x1718     0x11 0x166c     0x12 0x1600     0x13 0x1594     0x14 0x14e8
0x15 0x147c     0x16 0x13e4     0x17 0x1388     0x18 0x132c     0x19 0x1280
0x1a 0x11d4     0x1b 0x1128     0x1c 0x10ac     0x1d 0x1000     0x1e 0x0f54
0x1f 0x0ee8     0x20 0x0e7c     0x21 0x0e10     0x22 0x0da4     0x23 0x0cf8
0x24 0x0c4c     0x25 0x0ba0     0x26 0x0b24     0x27 0x0ab8     0x28 0x0a4c

再看前镜像0x18 0x132c的信息,仍然存在:

*-----------------------------
* Rec #0x18  slt: 0x1d  objn: 51148(0x0000c7cc)  objd: 51148  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x17
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800546.0129.17
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000020  hdba: 0x0100001b
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 1
col  5: [ 3]  c2 1d 33