对ORACLE比较熟悉的人都知道v$datafile.CREATION_TIME和v$datafile_header.CREATION_TIME这两个列都是表示数据文件的创建时间,而根据我们的经验可以知道几点:
1.当v$datafile.CREATION_TIME与v$datafile_header.CREATION_TIME不一致时数据库不能正常启动
2.v$datafile.CREATION_TIME的值来源于v$datafile_header.CREATION_TIME
3.而v$datafile_header.CREATION_TIME的值来源于数据文件头的块中的信息
现在就出现一个问题,数据块中的kcvfhcrt是一个16进制的数,如何实现在v$datafile和v$datafile_header中转为为了数据文件创建的日期
数据文件中存储创建数据文件日期内容

ub4 kcvfhcrt                             @108      0x2c67319c

v$datafile.CREATION_TIME值

SQL> select to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') xifenfei
   2 from v$datafile where file#=1;
XIFENFEI
-------------------
2011-03-05 05:26:52

如何通过kcvfhcrt值推算出来CREATION_TIME或者通过CREATION_TIME推断出来kcvfhcrt的值规则:
熟悉数据库SCN计数原理的人都知道,我们现在使用的数据库是从1988/01/01 00:00:00开始记录SCN,也就是说我们的数据库的使用最早时间只能是从1988年元旦凌晨开始，那么也就是说数据库记录的创建时间可以采用这个时间点为起点,然后每增加一秒,数据库的kcvfhcrt就增加1,但是ORACLE为了计算简便,每个月按照31天计算
通过时间推算出来kcvfhcrt值

--数据库记录时间起点
1988/01/01 00:00:00
--当前数据文件创建日志
2011/03/05 05:26:52
--两者相差时间
23年02月04日05时26分52秒
--计算相差秒
23*12*31*24*60*60+2*31*24*60*60+4*24*60*60+5*60*60+26*60+52=744960412
--kcvfhcrt值转换
2c67319c(16进制)=744960412(10进制)

通过kcvfhcrt计算CREATION_TIME值

SQL> select to_number('2c67319c','xxxxxxxxxxx') from dual;
TO_NUMBER('2C67319C','XXXXXXXXXXX')
-----------------------------------
                          744960412
SQL> select 744960412/(12*31*24*60*60) from dual;
744960412/(12*31*24*60*60)
--------------------------
                23.1780295
SQL> select mod(744960412,(12*31*24*60*60)) from dual;
MOD(744960412,(12*31*24*60*60))
-------------------------------
                        5722012
SQL> select 5722012/(31*24*60*60) from dual;
5722012/(31*24*60*60)
---------------------
           2.13635454
SQL> select mod(5722012,(31*24*60*60)) from dual;
MOD(5722012,(31*24*60*60))
--------------------------
                    365212
SQL> select 365212/(24*60*60) from dual;
365212/(24*60*60)
-----------------
       4.22699074
SQL> select mod(365212,(24*60*60)) from dual;
MOD(365212,(24*60*60))
----------------------
                 19612
SQL> select 19612/(60*60) from dual;
19612/(60*60)
-------------
   5.44777778
SQL> select mod(19612,(60*60)) from dual;
MOD(19612,(60*60))
------------------
              1612
SQL> select 1612/60 from dual;
   1612/60
----------
26.8666667
SQL> select mod(1612,60) from dual;
MOD(1612,60)
------------
          52
从这里可以得出23年2月4天5时26分52秒，与1988年01月01日00时00分00秒相加得到
2011年03月05日 5:26:52
SQL> select to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') from v$datafile where file#=1;
TO_CHAR(CREATION_TI
-------------------
2011-03-05 05:26:52

生成Statspack报告时候发现Snap Id为”#####”

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
  631690435        1 VODAPP       vodapp       T5440-1
Enter value for dbid: 631690435
Using 631690435 for database Id
Enter value for inst_num: 1
Using 1 for instance number
Completed Snapshots
                           Snap                    Snap
Instance     DB Name         Id   Snap Started    Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
vodapp       VODAPP       ##### 16 Oct 2012 17:00     5
                          ##### 16 Oct 2012 18:00     5
                          ##### 16 Oct 2012 19:00     5
                          ##### 16 Oct 2012 20:00     5
                          ##### 16 Oct 2012 21:00     5
                          ##### 16 Oct 2012 22:00     5
                          ##### 16 Oct 2012 23:00     5
                          …………
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:

因为没有办法定位到Snap Id,所以暂时无法准确的输入对应值,当然可以通过如下sql查询相应的Snap Id

select SNAP_ID,to_char(SNAP_TIME,'yyyy-mm-dd hh24:mi:ss') SNAP_TIME
from STATS$SNAPSHOT order by SNAP_ID;

虽然可以通过这个方面来曲线解决这个问题,但是还有比较完善一点的解决方法,我们阅读spcreate.sql相关脚本,修改相关程序来实现

--通过spcreate.sql发现
spcreate.sql调用@@sprepins
--编辑sprepins.sql
column instart_fmt noprint;
column inst_name   format a12  heading 'Instance';
column db_name     format a12  heading 'DB Name';
column snap_id     format 9990 heading 'Snap|Id';
-->(修改为)column snap_id     format 999990 heading 'Snap|Id';
column snapdat     format a17  heading 'Snap Started' just c;
column lvl         format 99   heading 'Snap|Level';
column commnt      format a22  heading 'Comment';

再次生成sp报告

SQL> @?/rdbms/admin/sprepins.sql
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
  631690435        1 VODAPP       vodapp       T5440-1
Enter value for dbid: 631690435
Using 631690435 for database Id
Enter value for inst_num: 1
Using 1 for instance number
Completed Snapshots
                             Snap                    Snap
Instance     DB Name           Id   Snap Started    Level Comment
------------ ------------ ------- ----------------- ----- ----------------------
vodapp       VODAPP         58916 16 Oct 2012 17:00     5
                            58917 16 Oct 2012 18:00     5
                            58918 16 Oct 2012 19:00     5
                            58919 16 Oct 2012 20:00     5
                            58920 16 Oct 2012 21:00     5
                            58921 16 Oct 2012 22:00     5
                            58922 16 Oct 2012 23:00     5
                            58923 17 Oct 2012 00:00     5

ckpt进程的trace文件中出现类似：9430ms (rw) file: kct.c line: 7800 count: 13 total: 26874ms time: 1296124
ckpt进程的trace文件中报如下错误

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /oradb/11.2.0/db
System name:	AIX
Node name:	offondb2
Release:	1
Version:	6
Machine:	00CC83664C00
Instance name: offon2
Redo thread mounted by this instance: 2
Oracle process number: 20
Unix process pid: 19660878, image: oracle@offondb2 (CKPT)
*** 2012-10-26 13:25:00.971
  1: 9430ms (rw) file: kct.c line: 7800 count: 13 total: 26874ms time: 1296124
  2: 1777ms (rw) file: krse.c line: 1800 count: 164 total: 122140ms time: 364176
  3: 1450ms (rw) file: kct.c line: 1011 count: 1 total: 1450ms time: 1594117
  4: 1230ms (rw) file: kcrf.c line: 10012 count: 135 total: 90995ms time: 2630737
  5: 1173ms (ro) file: kcrr.c line: 3525 count: 13 total: 8842ms time: 3645916
  6: 890ms (rw) file: kcrf.c line: 9959 count: 8 total: 4812ms time: 3578222
  7: 830ms (ro) file: kcf.c line: 5306 count: 1 total: 830ms time: 1594116
  8: 677ms (rw) file: kcv.c line: 11783 count: 8 total: 4499ms time: 416869
Control file enqueue hold time tracking dump at time: 2092019
*** 2012-10-26 15:25:14.789
  1: 9430ms (rw) file: kct.c line: 7800 count: 13 total: 26874ms time: 1296124
  2: 1777ms (rw) file: krse.c line: 1800 count: 165 total: 122832ms time: 364176
  3: 1450ms (rw) file: kct.c line: 1011 count: 1 total: 1450ms time: 1594117
  4: 1230ms (rw) file: kcrf.c line: 10012 count: 135 total: 90995ms time: 2630737
  5: 1173ms (ro) file: kcrr.c line: 3525 count: 13 total: 8842ms time: 3645916
  6: 890ms (rw) file: kcrf.c line: 9959 count: 8 total: 4812ms time: 3578222
  7: 830ms (ro) file: kcf.c line: 5306 count: 1 total: 830ms time: 1594116
  8: 677ms (rw) file: kcv.c line: 11783 count: 8 total: 4499ms time: 416869

原因分析并解决

New controlfile enqueue hold time tracking statistics have been added in 11.2 to
aid diagnosis of controlfile transaction related performance related issues.
To disable the trace set _controlfile_enqueue_holding_time_tracking_size to 0:
- spfile
alter system set "_controlfile_enqueue_holding_time_tracking_size"=0 scope=spfile;
- pfile
_controlfile_enqueue_holding_time_tracking_size=0
A database restart is required.

查询_controlfile_enqueue_holding_time_tracking_size

SQL> col name for a32
SQL> col value for a24
SQL> col description for a70
SQL> set linesize 150
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  /
Enter value for param: _controlfile_enqueue_holding_time_tracking_size
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_controlfile_enqueue_holding_time_tracking_size%')
</strong>
NAME                             VALUE      DESCRIPTION
-------------------------------- ---------- ------------------------------------------------
_controlfile_enqueue_holding_tim 10         control file enqueue holding time tracking size
e_tracking_size

补充MOS说明[791417.1]

New controlfile enqueue hold time tracking statistics have been added in 11.2 to aid
diagnosis of controlfile transaction related performance related issues:
Control File Enqueue AWR Statistics:
max cf enq hold time - The maximum amount of time in milliseconds a client has held the control file enqueue.
total cf enq hold time - The total amount of time in milliseconds all clients have held the control file enqueue.
total number of cf enq holders - The total number of times clients have held the control file enqueue.
Periodically, the CKPT process dumps statistics for the top N control file enqueue holders.
N defaults to 10, but can be modified with the static hidden
parameter: _controlfile_enqueue_holding_time_tracking_size.The dump looks like the following:
Preface: "Control file enqueue hold time tracking dump at time: [relative time]".
a. Time the client has held the control file enqueue.
b. Type of client's control file enqueue transaction - rw or ro.
c. File name where the client obtained control file enqueue.
d. Line number where the client obtained control file enqueue.
e. Number of times the client has held the control file enqueue since it became a member of the top N.
f. Total time the client has held the control file in all those times from [e].
g. Relative time the client obtained the control file enqueue from [a].

数据库版本

SQL> select * from v$version;
BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

执行DBMS_STATS报错

SQL> exec dbms_stats.GATHER_TABLE_STATS('CHF','T_XIFENFEI');
begin sys.dbms_stats.GATHER_TABLE_STATS('CHF','T_XIFENFEI');; end;
ORA-04063: package body "SYS.DBMS_STATS" 有错误
ORA-06508: PL/SQL: 无法找到正在调用 : "SYS.DBMS_STATS" 的程序单元
ORA-06512: 在 line 2

重建DBMS_STATS包

SQL> drop package DBMS_STATS;
Package dropped.
SQL> @?/rdbms/admin/dbmsstat.sql
Package created.
No errors.
Synonym created.
Grant succeeded.
create role gather_system_statistics
            *
ERROR at line 1:
ORA-01921: role name 'GATHER_SYSTEM_STATISTICS' conflicts with another user or
role name
Grant succeeded.
Grant succeeded.
Library created.
SQL> @?/rdbms/admin/prvtstas.plb
Package created.
No errors.
SQL> @?/rdbms/admin/prvtstai.plb
Package body created.
No errors.
SQL> @?/rdbms/admin/prvtstat.plb
Package body created.
No errors.

重新执行DBMS_STATS

SQL> exec dbms_stats.GATHER_TABLE_STATS('CHF','T_XIFENFEI');
PL/SQL procedure successfully completed.

补充说明
1.建议数据库在restricted模式下执行重建DBMS_STATS相关脚本
2.对于11g以前版本,具体参考1310365.1

SQL> @?/rdbms/admin/dbmsstat.sql
SQL> @?/rdbms/admin/prvtstas.plb
SQL> @?/rdbms/admin/prvtstat.plb

以前对Transportable Tablespaces(TTS)一直理解不深,今天无意中看到TTS可以实现数据库升级,今天测试了实现使用TTS 迁移9.2.0.4的一个表空间到11.2.0.3,平台均为Linux 32位
源端版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

创建测试环境

SQL> create tablespace tts_xff
  2  datafile '/u01/oracle/oradata/xifenfei/tts_xifenfei01.dbf' size 10m autoextend on next 10m,
  3  '/u01/oracle/oradata/xifenfei/tts_xifenfei02.dbf' size 10m autoextend on next 10m
  4  ;
Tablespace created.
SQL> create user tts_xff identified by xifenfei;
User created.
SQL> grant dba to tts_xff;
Grant succeeded.
SQL> conn tts_xff/xifenfei
Connected.
SQL> create table t1 tablespace tts_xff
  2  as
  3  select * from dba_objects;
Table created.
SQL> create table t2 tablespace tts_xff
  2  as
  3  select * from dba_objects;
Table created.
SQL> create table t_xifenfei  tablespace tts_xff
  2  as
  3  select * from dba_objects;
Table created.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
T2                             TABLE
T_XIFENFEI                     TABLE
SQL> select count(*) from t1;
  COUNT(*)
----------
     30805
SQL> conn / as sysdba
Connected.
SQL> alter tablespace tts_xff read only;
Tablespace altered.

导出并传输测试表空间

[oracle@xifenfei ~]$ exp userid=\'/ as sysdba\' tablespaces=tts_xff file=/tmp/tts_xff.dmp transport_tablespace=y
Export: Release 9.2.0.4.0 - Production on Sun Oct 7 04:53:25 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TTS_XFF ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                             T1
. . exporting table                             T2
. . exporting table                     T_XIFENFEI
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
[oracle@xifenfei ~]$ scp /tmp/tts_xff.dmp 192.168.1.10:/tmp/
oracle@192.168.1.10's password:
tts_xff.dmp                                                       100%   16KB  16.0KB/s   00:00
[oracle@xifenfei ~]$ scp /u01/oracle/oradata/xifenfei/tts_xifenfei* 192.168.1.10:/u01/oracle/oradata/ora11g/
oracle@192.168.1.10's password:
tts_xifenfei01.dbf                                                100%   10MB   3.3MB/s   00:03
tts_xifenfei02.dbf                                                100%   10MB   5.0MB/s   00:02

目标库版本

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

创建用户

SQL> create user tts_11g identified by xifenfei;
User created.
SQL> grant dba to tts_11g;
Grant succeeded.

导入表空间

[oracle@xifenfei ~]$ imp userid=\'/ as sysdba\' tablespaces=tts_xff file=/tmp/tts_xff.dmp
> transport_tablespace=y datafiles=/u01/oracle/oradata/ora11g/tts_xifenfei01.dbf,
> /u01/oracle/oradata/ora11g/tts_xifenfei02.dbf fromuser=tts_xff touser=tts_11g
Import: Release 11.2.0.3.0 - Production on Sat Sep 29 04:18:04 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TTS_XFF's objects into TTS_11G
. . importing table                           "T1"
. . importing table                           "T2"
. . importing table                   "T_XIFENFEI"
Import terminated successfully without warnings.

测试数据

SQL> alter tablespace tts_xff read write;
Tablespace altered.
SQL> conn tts_11g/xifenfei
Connected.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
T2                             TABLE
T_XIFENFEI                     TABLE
SQL> select count(*) from t1;
  COUNT(*)
----------
     30805
SQL> delete from t1;
30805 rows deleted.
SQL> commit;
Commit complete.

至此测试完成，证明使用tts可以实现跨版本迁移数据

补充说明
1.10g及其以上版本可以实现不同平台的tts迁移，可能需要使用rman convert转换
2.迁移前需要使用 EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK检测依赖性

很多时候，我们在数据库中设置了event,如何确认设置的event生效或者如何确认你的库中设置了什么event.下面的文章测试了在11g中比较方便的方法
数据库版本

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

设置测试event

SQL> alter session set events '10510 trace name context forever,level 1';
Session altered.
SQL> alter session set events
  2  '10046 trace name context forever,level 4';
Session altered.
SQL> alter system set events '60025 trace name context forever';
System altered.
SQL> alter system set events '10513 trace name context forever,level 2';
System altered.

测试spfile参数中是否有event

SQL> create pfile='/tmp/pfile' from spfile;
File created.
solaris*orcl-/home/oracle$ grep -i event /tmp/pfile
--无记录
SQL> show parameter event;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
event                                string
xml_db_events                        string      enable

证明设置event不会在spfile中记录

查询会话event

--dbms_system实现
SQL> set serveroutput on size 1000000
SQL> declare
  2  event_level number;
  3  begin
  4  for i in 1..100000 loop
  5  sys.dbms_system.read_ev(i,event_level);
  6  if (event_level > 0) then
  7  dbms_output.put_line('Event '||to_char(i)||' set at level '||
  8  to_char(event_level));
  9  end if;
 10  end loop;
 11  end;
 12  /
Event 10510 set at level 1
Event 10513 set at level 2
Event 60025 set at level 1
PL/SQL procedure successfully completed.
--oradebug实现
SQL> oradebug SETMYPID
Statement processed.
SQL> oradebug eventdump session
10510 trace name context forever,level 1
10513 trace name context forever,level 2
60025 trace name context forever
sql_trace level=4

测试证明使用dbms_system可以捕获到event,oradebug可以捕获到本身会话,还可以通过setospid/setorapid来跟踪其他会话的event设置情况.event 10046对应的本质是sql_trace所以使用dbms_system不能捕获到10046

临时表空间被使用现状
接到客户反馈,他们的数据库使用了长连接,临时表空间使用率一直不下降,一个会话占用了几百M甚至几个G的临时表空间不释放,随着时间的积累,会话占用的临时表空间还在继续增加,最终的现象是100G的数据文件,160G的临时表空间还在继续报临时表空间不足.查询v$sort_usage发现其SEGTYPE全部为LOB_DATA而CONTENTS为TEMPORARY,而且BLOCKS都很大,通过上面的信息大概分析,怀疑是因为数据库查询或者操作LOB类型时候使用了TEMPORARY,但是没有释放导致

相关版本信息

OS:AIX 6.1(64)
DB:10.2.0.5

测试案例证明

--执行查询脚本
$ more check.sql
connect / as sysdba
select * from v$tempseg_usage where username not in ('HDDS_CLPS_DTA','FOGLIGHT');
--测试脚本1
$ more test1.sh
sqlplus /nolog <<EOF
connect / as sysdba
drop user xifenfei cascade;
create user xifenfei identified by tc
default tablespace users temporary tablespace temp quota unlimited on users;
grant connect,resource,alter session to xifenfei;
revoke unlimited tablespace from xifenfei;
connect xifenfei/tc
select to_nclob('a') from dual;
!sqlplus /nolog @check
commit;
!sqlplus /nolog @check
EOF
--测试脚本2
$ more test2.sh
sqlplus /nolog << EOF2
connect xifenfei/tc
alter session set events '60025 trace name context forever';
select to_nclob('a') from dual;
!sqlplus /nolog @check
commit;
!sqlplus /nolog @check
EOF2

测试结果

$ ./test1.sh
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
SQL> Connected.
SQL> drop user xifenfei cascade
          *
ERROR at line 1:
ORA-01918: user 'XIFENFEI' does not exist
Grant succeeded.
SQL>
Revoke succeeded.
SQL> SQL> SQL> Connected.
SQL>
TO_NCLOB('A')
--------------------------------------------------------------------------------
a
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected.
USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
xifenfei                           xifenfei                           07000002F96ECB30
      10152 07000002AE1C36E0 1362191183 9z69tsx8m2sug
TEMP                            TEMPORARY LOB_DATA         201       3465
         1        128          1
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL>
Commit complete.
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected.
USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
xifenfei                           xifenfei                           07000002F96ECB30
      10152 07000002AE1C36E0 1362191183 9z69tsx8m2sug
TEMP                            TEMPORARY LOB_DATA         201       3465
         1        128          1
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--测试脚本2
$ ./test2.sh
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
SQL> Connected.
SQL>
Session altered.
SQL>
TO_NCLOB('A')
--------------------------------------------------------------------------------
a
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected.
no rows selected
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL>
Commit complete.
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected.
no rows selected
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

1.Without event 60025 set (before and after commit):都出现v$tempseg_usage中存在对应记录,而且提交后不能释放Temp LOB space
2.With event 60025 set (before and after commit):都未现v$tempseg_usage中存在对应记录,证明提交后释放Temp LOB space

解决方案
通过上面的试验证明我们可以通过设置event 60025来解决该版本的会话提交后Temp LOB space不能被回收的问题.
我们可以通过在session级别使用”alter session set events ‘60025 trace name context forever’;”来实现。如果想实现全库级别的，但是因为event 60025不能通过system设置生效,所以我们可以通过logon触发器来实现该功能

create or replace trigger sys.login_db after logon on database
begin
execute immediate 'alter session set events ''60025 trace name context forever''';
end;
/

注意这个是ORCLE bug(Bug 5723140 – Temp LOB space not released after commit [ID 5723140.8]),从10.2.0.4开始虽然已经修复了该bug,但是默认情况下:为了更加高效的利用temp,在session未断开前,不自动释放temp 空间,可以通过设置event 60025来强制会话在commit之后就立即释放temp space

在哪些情况下,数据库不启动附加日志不能正常的被Logminer捕获到的,这里做了一个简单的测试,说明在不启用附加日志的情况下,很多操作不能被捕获,不仅仅是行迁移的数据记录.当然本实验仅供参考,因为在不同的数据库版本,不同的平台,甚至不同的操作都可能出现不同的结果.如果想要数据库日志通过Logminer获得较为完整的sql语句,强烈建议打开附加日志(当然会产生多一点日志,可能增加磁盘io的负担,凡事都有两面性,则其善而从之)
数据库版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

插入数据

--为了减少测试redo影响,切换归档日志
SQL> alter system switch logfile;
System altered.
SQL> show user;
USER is "CHF"
--当前scn
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949934814
--当前redo logfile
SQL> select member from v$logfile where group# in(
  2  select group# from v$log where status='CURRENT');
MEMBER
------------------------------------------------------------------
/u01/oracle/oradata/XFF/redo03.log
--创建测试表并插入数据
SQL> create table xifenfei(id number,name varchar2(4000));
Table created.
SQL> insert into xifenfei values(1,'xifenfei');
1 row created.
SQL> insert into xifenfei values(2,'XIFENFEI');
1 row created.
SQL> insert into xifenfei values(3,'XiFenFei');
1 row created.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949934864
--数据存储的datafile 和blocknum
SQL> select id,rowid,dbms_rowid.ROWID_RELATIVE_FNO(rowid) file_num,
  2  dbms_rowid.rowid_block_number(rowid) block_num from xifenfei;
        ID ROWID                FILE_NUM  BLOCK_NUM
---------- ------------------ ---------- ----------
         1 AAAMuvAAJAAAAmkAAA          9       2468
         2 AAAMuvAAJAAAAmkAAB          9       2468
         3 AAAMuvAAJAAAAmkAAC          9       2468
--dump数据块
SQL> alter system dump datafile 9 block 2468;
System altered.
--dump datablock内容
Block header dump:  0x024009a4
 Object id on Block? Y
 seg/obj: 0xcbaf  csc: 0x0a.3ff09  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24009a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.019.0000010d  0x00800b85.0111.2f  --U-    3  fsc 0x0000.0003ff0e
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
data_block_dump,data header at 0xcf6c464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0cf6c464
bdba: 0x024009a4
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f6b
avsp=0x1f53
tosp=0x1f53
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f89
0x14:pri[1]     offs=0x1f7a
0x16:pri[2]     offs=0x1f6b
block_row_dump:
tab 0, row 0, @0x1f89
tl: 15 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 8]  78 69 66 65 6e 66 65 69
tab 0, row 1, @0x1f7a
tl: 15 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 8]  58 49 46 45 4e 46 45 49
tab 0, row 2, @0x1f6b
tl: 15 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [ 8]  58 69 46 65 6e 46 65 69
--可以清楚的看到这三条记录都存在一个数据块中,并未发生行迁移等情况
--dump redo log
SQL> alter system dump logfile '/u01/oracle/oradata/XFF/redo03.log'
  2    scn min 42949934814 scn max 42949934864;
System altered.
--dump redo logfile内容
CHANGE #9 TYP:0 CLS: 4 AFN:9 DBA:0x024009a3 OBJ:52143 SCN:0x000a.0003ff0e SEQ:  1 OP:13.28
Low HWM
      Highwater::  0x024009a9  ext#: 0      blk#: 8      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 5
  mapblk  0x00000000  offset: 0
lfdba:  0x024009a1 CHANGE #10 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0003ff0e SEQ:  1 OP:11.2
KTB Redo
op: 0x01  ver: 0x01
op: F  xid:  0x0008.019.0000010d    uba: 0x00800b85.0111.2d
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 15
fb: --H-FL-- lb: 0x1  cc: 2
null: --
col  0: [ 2]  c1 02
col  1: [ 8]  78 69 66 65 6e 66 65 69
CHANGE #11 TYP:0 CLS:31 AFN:2 DBA:0x00800079 OBJ:4294967295 SCN:0x000a.0003fe8f SEQ:  1 OP:5.2
ktudh redo: slt: 0x0019 sqn: 0x0000010d flg: 0x0012 siz: 108 fbi: 0
            uba: 0x00800b85.0111.2d    pxid:  0x0000.000.00000000
CHANGE #12 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0003ff0e SEQ:  2 OP:11.2
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800b85.0111.2e
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) size/delt: 15
fb: --H-FL-- lb: 0x1  cc: 2
null: --
col  0: [ 2]  c1 03
col  1: [ 8]  58 49 46 45 4e 46 45 49
CHANGE #13 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0003ff0e SEQ:  3 OP:11.2
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800b85.0111.2f
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 15
fb: --H-FL-- lb: 0x1  cc: 2
null: --
col  0: [ 2]  c1 04
col  1: [ 8]  58 69 46 65 6e 46 65 69
--这里可以看到，只有redo的信息，没有太多undo信息(因为是插入数据)
--使用Logminer
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo03.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,sql_redo from v$Logminer_contents where SEG_NAME='XIFENFEI';
TABLE_NAME                       SQL_REDO
-------------------------------- -------------------------------------------------------
XIFENFEI                         create table xifenfei(id number,name varchar2(4000));
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
--这里可以明确的看到,Logminer没有找到任何关于这个表的dml操作,也就是说三条insert都没有被找到

说明:在redo中已经包含了insert操作的相关记录,但是因为没有启用附加日志,是的Logminer不能正常获得相关操作语句

简单更新操作

SQL> alter system switch logfile;
System altered.
SQL> conn chf/xifenfei
Connected.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949941538
SQL> select member from v$logfile where group# in(
  2  select group# from v$log
  3  where status='CURRENT');
MEMBER
---------------------------------------------------------------
/u01/oracle/oradata/XFF/redo01.log
SQL> update xifenfei set name='www.xifenfei.com' where id=1;
1 row updated.
SQL> update xifenfei set name='WWW.XIFENFEI.COM' WHERE ID=2;
1 row updated.
SQL> update xifenfei set name='www.orasos.com' where id=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949941552
SQL> alter system dump datafile 9 block 2468;
System altered.
--dump datablock
Block header dump:  0x024009a4
 Object id on Block? Y
 seg/obj: 0xcbaf  csc: 0x0a.4192a  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24009a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.019.0000010d  0x00800b85.0111.2f  C---    0  scn 0x000a.0003ff0e
0x02   0x0003.004.0000014e  0x0080002b.01a6.3b  --U-    3  fsc 0x0000.0004192d
data_block_dump,data header at 0xdf83464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0df83464
bdba: 0x024009a4
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f28
avsp=0x1f3d
tosp=0x1f3d
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f54
0x14:pri[1]     offs=0x1f3d
0x16:pri[2]     offs=0x1f28
block_row_dump:
tab 0, row 0, @0x1f54
tl: 23 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 02
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x1f3d
tl: 23 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 03
col  1: [16]  57 57 57 2e 58 49 46 45 4e 46 45 49 2e 43 4f 4d
tab 0, row 2, @0x1f28
tl: 21 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 04
col  1: [14]  77 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d
SQL> alter system dump logfile '/u01/oracle/oradata/XFF/redo01.log'
  2    scn min 42949941538 scn max 42949941552;
System altered.
--dump redo log
REDO RECORD - Thread:1 RBA: 0x000013.00000002.0010 LEN: 0x0408 VLD: 0x0d
SCN: 0x000a.0004192d SUBSCN:  1 09/26/2012 23:31:27
CHANGE #1 TYP:2 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0003ff0e SEQ:  5 OP:11.5
KTB Redo
op: 0x11  ver: 0x01
op: F  xid:  0x0003.004.0000014e    uba: 0x0080002b.01a6.39
Block cleanout record, scn:  0x000a.0004192a ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x000a.0003ff0e
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 251
ncol: 2 nnew: 1 size: 8
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
CHANGE #2 TYP:0 CLS:21 AFN:2 DBA:0x00800029 OBJ:4294967295 SCN:0x000a.00041781 SEQ:  1 OP:5.2
ktudh redo: slt: 0x0004 sqn: 0x0000014e flg: 0x0012 siz: 136 fbi: 0
            uba: 0x0080002b.01a6.39    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0004192d SEQ:  1 OP:11.5
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x0080002b.01a6.3a
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 2 ckix: 12
ncol: 2 nnew: 1 size: 8
col  1: [16]  57 57 57 2e 58 49 46 45 4e 46 45 49 2e 43 4f 4d
CHANGE #4 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0004192d SEQ:  2 OP:11.5
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x0080002b.01a6.3b
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 12
ncol: 2 nnew: 1 size: 6
col  1: [14]  77 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d
--OP:11.5 Update Row Piece
--包含了修改后的值(后镜像)
CHANGE #5 TYP:0 CLS:21 AFN:2 DBA:0x00800029 OBJ:4294967295 SCN:0x000a.0004192d SEQ:  1 OP:5.4
ktucm redo: slt: 0x0004 sqn: 0x0000014e srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x0080002b.01a6.3b ext: 0 spc: 416 fbi: 0
--OP:5.4  Commit transaction (transaction table update)
CHANGE #6 TYP:0 CLS:22 AFN:2 DBA:0x0080002b OBJ:4294967295 SCN:0x000a.00041780 SEQ:  2 OP:5.1
ktudb redo: siz: 136 spc: 750 flg: 0x0012 seq: 0x01a6 rec: 0x39
            xid:  0x0003.004.0000014e
ktubl redo: slt: 4 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x0080002b.01a6.37
prev ctl max cmt scn:  0x000a.00040ff1  prev tx cmt scn:  0x000a.00041076
txn start scn:  0x0000.00000000  logon user: 59  prev brb: 8391493  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 251
ncol: 2 nnew: 1 size: -8
col  1: [ 8]  78 69 66 65 6e 66 65 69
CHANGE #7 TYP:0 CLS:22 AFN:2 DBA:0x0080002b OBJ:4294967295 SCN:0x000a.0004192d SEQ:  1 OP:5.1
ktudb redo: siz: 96 spc: 612 flg: 0x0022 seq: 0x01a6 rec: 0x3a
            xid:  0x0003.004.0000014e
ktubu redo: slt: 4 rci: 57 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x0080002b.01a6.39
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: -8
col  1: [ 8]  58 49 46 45 4e 46 45 49
CHANGE #8 TYP:0 CLS:22 AFN:2 DBA:0x0080002b OBJ:4294967295 SCN:0x000a.0004192d SEQ:  2 OP:5.1
ktudb redo: siz: 96 spc: 514 flg: 0x0022 seq: 0x01a6 rec: 0x3b
            xid:  0x0003.004.0000014e
ktubu redo: slt: 4 rci: 58 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x0080002b.01a6.3a
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: -6
col  1: [ 8]  58 69 46 65 6e 46 65 69
--OP:5.1 Undo block or undo segment header
--包含了前镜像(修改前的值,其实就是undo中记录)
--Logminer操作
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo01.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,sql_redo from v$Logminer_contents where SEG_NAME='XIFENFEI';
no rows selected
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
--Logminer无任何记录，证明没有被捕获到

说明:在redo中已经包含了insert操作的相关记录,但是因为没有启用附加日志,是的Logminer不能正常获得相关操作语句

行迁移情况

SQL> alter system switch logfile;
System altered.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949943145
SQL> select member from v$logfile where group# in(
  2  select group# from v$log
  3  where status='CURRENT');
MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/XFF/redo03.log
--制造行迁移
SQL> update xifenfei set name=lpad('F',4000,'F') WHERE ID=1;
1 row updated.
SQL> update xifenfei set name=lpad('X',4000,'X') WHERE ID=2;
1 row updated.
SQL> update xifenfei set name=lpad('C',4000,'C') WHERE ID=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949943162
SQL> select id,rowid,dbms_rowid.ROWID_RELATIVE_FNO(rowid) file_num,
  2  dbms_rowid.rowid_block_number(rowid) block_num from xifenfei;
        ID ROWID                FILE_NUM  BLOCK_NUM
---------- ------------------ ---------- ----------
         1 AAAMuvAAJAAAAmkAAA          9       2468
         2 AAAMuvAAJAAAAmkAAB          9       2468
         3 AAAMuvAAJAAAAmkAAC          9       2468
SQL> alter system dump datafile 9 block 2472;
System altered.
--dump datablock
Block header dump:  0x024009a4
 Object id on Block? Y
 seg/obj: 0xcbaf  csc: 0x0a.41f6e  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24009a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.02f.00000113  0x00800085.011b.41  --U-    3  fsc 0x000c.00041f78
0x02   0x0003.004.0000014e  0x0080002b.01a6.3b  C---    0  scn 0x000a.0004192d
data_block_dump,data header at 0xec3f464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0ec3f464
bdba: 0x024009a4
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x31
avsp=0x19
tosp=0x25
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0xfef
0x14:pri[1]     offs=0x31
0x16:pri[2]     offs=0xfda
block_row_dump:
tab 0, row 0, @0xfef
tl: 4009 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [4000]
 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
…………
tab 0, row 1, @0x31
tl: 4009 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [4000]
 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
…………
tab 0, row 2, @0xfda
tl: 9 fb: --H----- lb: 0x1  cc: 0
nrid:  0x024009a8.0    <--发生行迁移，指向下一个迁移数据块
end_of_block_dump
End dump data blocks tsn: 9 file#: 9 minblk 2468 maxblk 2468
--找到下个数据块的block num
SQL> select to_number('9a8','xxxxx') from dual;
TO_NUMBER('9A8','XXXXX')
------------------------
                    2472
SQL> alter system dump datafile 9 block 2472;
System altered.
Block header dump:  0x024009a8
 Object id on Block? Y
 seg/obj: 0xcbaf  csc: 0x0a.3ff09  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24009a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.02f.00000113  0x00800085.011b.40  --U-    1  fsc 0x0000.00041f78
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
data_block_dump,data header at 0xec3f47c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x0ec3f47c
bdba: 0x024009a8
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0xfd1
avsp=0xfbd
tosp=0xfbd
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0xfd1
block_row_dump:
tab 0, row 0, @0xfd1
tl: 4015 fb: ----FL-- lb: 0x1  cc: 2
hrid: 0x024009a4.2   <--迁移对应的起点数据块
col  0: [ 2]  c1 04
col  1: [4000]
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
…………
end_of_block_dump
End dump data blocks tsn: 9 file#: 9 minblk 2472 maxblk 2472
SQL> alter system dump logfile '/u01/oracle/oradata/XFF/redo03.log'
  2    scn min 42949943145 scn max 42949943162;
System altered.
--dump redo log
REDO RECORD - Thread:1 RBA: 0x000015.00000002.0010 LEN: 0x1180 VLD: 0x0d
SCN: 0x000a.00041f6e SUBSCN:  1 09/27/2012 00:36:34
CHANGE #1 TYP:2 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0004192d SEQ:  4 OP:11.5
KTB Redo
op: 0x11  ver: 0x01
op: F  xid:  0x0008.02f.00000113    uba: 0x00800085.011b.3d
Block cleanout record, scn:  0x000a.00041f6e ver: 0x01 opt: 0x02, entries follow...
  itli: 2  flg: 2  scn: 0x000a.0004192d
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 174
ncol: 2 nnew: 1 size: 3986
col  1: [4000]
 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
…………
CHANGE #2 TYP:0 CLS:31 AFN:2 DBA:0x00800079 OBJ:4294967295 SCN:0x000a.00041ebd SEQ:  1 OP:5.2
ktudh redo: slt: 0x002f sqn: 0x00000113 flg: 0x0012 siz: 168 fbi: 0
            uba: 0x00800085.011b.3d    pxid:  0x0000.000.00000000
--OP:5.2 Update rollback segment header
CHANGE #3 TYP:0 CLS:32 AFN:2 DBA:0x00800085 OBJ:4294967295 SCN:0x000a.00041ebc SEQ:  1 OP:5.1
ktudb redo: siz: 168 spc: 862 flg: 0x0012 seq: 0x011b rec: 0x3d
            xid:  0x0008.02f.00000113
ktubl redo: slt: 47 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00800085.011b.3c
prev ctl max cmt scn:  0x000a.000416cd  prev tx cmt scn:  0x000a.000416d0
txn start scn:  0x0000.00000000  logon user: 59  prev brb: 8388734  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0x0008.019.0000010d uba: 0x00800b85.0111.2f
                      flg: C---    lkc:  0     scn: 0x000a.0003ff0e
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 174
ncol: 2 nnew: 1 size: -3986
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
REDO RECORD - Thread:1 RBA: 0x000015.0000000c.0010 LEN: 0x10d4 VLD: 0x05
SCN: 0x000a.00041f71 SUBSCN:  1 09/27/2012 00:36:40
CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x00800085 OBJ:4294967295 SCN:0x000a.00041f6e SEQ:  1 OP:5.1
ktudb redo: siz: 104 spc: 692 flg: 0x0022 seq: 0x011b rec: 0x3e
            xid:  0x0008.02f.00000113
ktubu redo: slt: 47 rci: 61 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800085.011b.3d
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: -3986
col  1: [16]  57 57 57 2e 58 49 46 45 4e 46 45 49 2e 43 4f 4d
CHANGE #2 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.00041f6e SEQ:  1 OP:11.5
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800085.011b.3e
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 1 ckix: 12
ncol: 2 nnew: 1 size: 3986
col  1: [4000]
 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
…………
REDO RECORD - Thread:1 RBA: 0x000015.00000015.010c LEN: 0x1098 VLD: 0x01
SCN: 0x000a.00041f74 SUBSCN:  2 09/27/2012 00:36:46
CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x00800085 OBJ:4294967295 SCN:0x000a.00041f74 SEQ:  1 OP:5.1
ktudb redo: siz: 60 spc: 516 flg: 0x0022 seq: 0x011b rec: 0x40
            xid:  0x0008.02f.00000113
ktubu redo: slt: 47 rci: 63 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a8  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)
CHANGE #2 TYP:0 CLS: 1 AFN:9 DBA:0x024009a8 OBJ:52143 SCN:0x000a.0003ff0e SEQ:  1 OP:11.2
KTB Redo
op: 0x01  ver: 0x01
op: F  xid:  0x0008.02f.00000113    uba: 0x00800085.011b.40
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a8  hdba: 0x024009a3
itli: 1  ispac: 24  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 4015
fb: ----FL-- lb: 0x1  cc: 2   <--这里没有H表明是发生了行迁移过来的记录(对应的flag可以转化为0x0c)
hrid: 0x024009a4.2
null: --
col  0: [ 2]  c1 04
col  1: [4000]
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
…………
REDO RECORD - Thread:1 RBA: 0x000015.0000001e.0078 LEN: 0x0124 VLD: 0x01
SCN: 0x000a.00041f74 SUBSCN:  3 09/27/2012 00:36:46
CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x00800085 OBJ:4294967295 SCN:0x000a.00041f74 SEQ:  2 OP:5.1
ktudb redo: siz: 124 spc: 454 flg: 0x0022 seq: 0x011b rec: 0x41
            xid:  0x0008.02f.00000113
ktubu redo: slt: 47 rci: 64 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800085.011b.3f
KDO Op code: ORP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 21
fb: --H-FL-- lb: 0x1  cc: 2
null: --
col  0: [ 2]  c1 04
col  1: [14]  77 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d
CHANGE #2 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.00041f74 SEQ:  1 OP:11.6
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800085.011b.41
KDO Op code: ORP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 9
fb: --H----- lb: 0x1  cc: 0  <--这里可以看到对应的块只有header信息无L,也就是发生了行迁移
nrid:  0x024009a8.0   <--通block dump说明
null:
--OP:11.6 Overwrite Row Piece
--Logminer操作
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo03.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
SQL> col sql_redo for a80
SQL> col TABLE_NAME for a15
SQL> set lines 134
SQL> select TABLE_NAME,sql_redo from v$Logminer_contents where scn>=42949943145 and scn<=42949943162;
TABLE_NAME      SQL_REDO
--------------- --------------------------------------------------------------------------------
XIFENFEI        update "CHF"."XIFENFEI" set "NAME" = 'XXXX……XXXXX' where "NAME" = 'WWW.XIFENFEI.COM'
                and ROWID = 'AAAMuvAAJAAAAmkAAB';
XIFENFEI        Unsupported
XIFENFEI        Unsupported
XIFENFEI        update "CHF"."XIFENFEI" set "ID" = NULL, "NAME" = NULL where "ID" = '3' and "NAM
                E" = 'www.orasos.com' and ROWID = 'AAAMuvAAJAAAAmkAAC';
                commit;
7 rows selected.
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
--获得了第二条记录(第一条没有任何记录,第三条因为行迁移,所以出现了update更新相关列为null,从而没有被Logminer正在的捕获)

说明：1)在发生行迁移之时,Logminer不能获得正常的sql语句,而是直接提示Unsupported;2)不发生行迁移也不一定能够获得update语句

删除操作

SQL> conn chf/xifenfei
Connected.
SQL> alter system switch logfile;
System altered.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949953508
SQL> select member from v$logfile where group# in(
  2  select group# from v$log
  3  where status='CURRENT');
MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/XFF/redo01.log
SQL> delete from xifenfei where id=1;
1 row deleted.
SQL> delete from xifenfei where id=2;
1 row deleted.
SQL> delete from xifenfei where id=3;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949953524
--原始数据所在block
SQL> alter system dump datafile 9 block 2468;
System altered.
--发生行迁移的block
SQL> alter system dump datafile 9 block 2472;
System altered.
--dump block 内容
Block header dump:  0x024009a4
 Object id on Block? Y
 seg/obj: 0xcbaf  csc: 0x0a.447e9  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24009a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.02f.00000113  0x00800085.011b.41  C---    0  scn 0x000a.00041f78
0x02   0x0004.014.000000da  0x00800398.00bf.02  --U-    3  fsc 0x1f55.000447f2
data_block_dump,data header at 0xdcb9464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0dcb9464
bdba: 0x024009a4
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x31
avsp=0x25
tosp=0x1f80
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0xfef
0x14:pri[1]     offs=0x31
0x16:pri[2]     offs=0xfda
block_row_dump:
tab 0, row 0, @0xfef
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x31
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 2, @0xfda
tl: 2 fb: --HD---- lb: 0x2
Block header dump:  0x024009a8
 Object id on Block? Y
 seg/obj: 0xcbaf  csc: 0x0a.447ef  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24009a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.02f.00000113  0x00800085.011b.40  C---    0  scn 0x000a.00041f78
0x02   0x0004.014.000000da  0x00800399.00bf.01  --U-    1  fsc 0x0fad.000447f2
0x03   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
data_block_dump,data header at 0xdcb947c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x0dcb947c
bdba: 0x024009a8
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0xfd1
avsp=0xfbd
tosp=0x1f6c
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0xfd1
block_row_dump:
tab 0, row 0, @0xfd1
tl: 2 fb: ---DFL-- lb: 0x2
end_of_block_dump
--通过数据块dump证明，记录确实已经被删除
--dump redo logfile
SQL> alter system dump logfile '/u01/oracle/oradata/XFF/redo01.log'
  2    scn min 42949953508 scn max 42949953524;
System altered.
--dump redolog
REDO RECORD - Thread:1 RBA: 0x000016.0000000b.0010 LEN: 0x1170 VLD: 0x0d
SCN: 0x000a.000447e9 SUBSCN:  1 09/27/2012 20:03:36
CHANGE #1 TYP:2 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.00041f78 SEQ:  1 OP:11.3
KTB Redo
op: 0x11  ver: 0x01
op: F  xid:  0x0004.014.000000da    uba: 0x00800397.00bf.06
Block cleanout record, scn:  0x000a.000447e9 ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x000a.00041f78
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)
--OP:11.3 Drop Row Piece
CHANGE #2 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000a.000447ae SEQ:  1 OP:5.2
ktudh redo: slt: 0x0014 sqn: 0x000000da flg: 0x0012 siz: 4172 fbi: 0
            uba: 0x00800397.00bf.06    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS:24 AFN:2 DBA:0x00800397 OBJ:4294967295 SCN:0x000a.000447ad SEQ:  1 OP:5.1
ktudb redo: siz: 4172 spc: 5804 flg: 0x0012 seq: 0x00bf rec: 0x06
            xid:  0x0004.014.000000da
ktubl redo: slt: 20 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00800397.00bf.05
prev ctl max cmt scn:  0x000a.00043852  prev tx cmt scn:  0x000a.00043862
txn start scn:  0x0000.00000000  logon user: 59  prev brb: 8389522  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0x0003.004.0000014e uba: 0x0080002b.01a6.3b
                      flg: C---    lkc:  0     scn: 0x000a.0004192d
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 4009
fb: --H-FL-- lb: 0x0  cc: 2
null: --
col  0: [ 2]  c1 02
col  1: [4000]
 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
…………
REDO RECORD - Thread:1 RBA: 0x000016.00000014.0010 LEN: 0x0044 VLD: 0x01
SCN: 0x000a.000447e9 SUBSCN:  1 09/27/2012 20:03:36
CHANGE #1 TYP:0 CLS: 8 AFN:9 DBA:0x024009a1 OBJ:52143 SCN:0x000a.00041f74 SEQ:  1 OP:13.22
Redo on Level1 Bitmap Block
Redo for state change
Len: 1 Offset: 3 newstate: 3
REDO RECORD - Thread:1 RBA: 0x000016.00000015.0010 LEN: 0x1100 VLD: 0x05
SCN: 0x000a.000447ed SUBSCN:  1 09/27/2012 20:03:42
CHANGE #1 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000a.000447e9 SEQ:  1 OP:5.2
ktudh redo: slt: 0x0014 sqn: 0x00000000 flg: 0x000a siz: 4108 fbi: 80
            uba: 0x00800398.00bf.01    pxid:  0x0000.000.00000000
CHANGE #2 TYP:1 CLS:24 AFN:2 DBA:0x00800398 OBJ:4294967295 SCN:0x000a.000447ec SEQ:  1 OP:5.1
ktudb redo: siz: 4108 spc: 1630 flg: 0x000a seq: 0x00bf rec: 0x01
            xid:  0x0004.014.000000da
ktubu redo: slt: 20 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00800397
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800397.00bf.06
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) size/delt: 4009
fb: --H-FL-- lb: 0x0  cc: 2
null: --
col  0: [ 2]  c1 03
col  1: [4000]
 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
…………
CHANGE #3 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.000447e9 SEQ:  1 OP:11.3
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800398.00bf.01
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1)
REDO RECORD - Thread:1 RBA: 0x000016.0000001d.0190 LEN: 0x0044 VLD: 0x01
SCN: 0x000a.000447ed SUBSCN:  1 09/27/2012 20:03:42
CHANGE #1 TYP:0 CLS: 8 AFN:9 DBA:0x024009a1 OBJ:52143 SCN:0x000a.000447e9 SEQ:  1 OP:13.22
Redo on Level1 Bitmap Block
Redo for state change
Len: 1 Offset: 3 newstate: 5
REDO RECORD - Thread:1 RBA: 0x000016.0000001e.0010 LEN: 0x0118 VLD: 0x05
SCN: 0x000a.000447ee SUBSCN:  1 09/27/2012 20:03:45
CHANGE #1 TYP:0 CLS:24 AFN:2 DBA:0x00800398 OBJ:4294967295 SCN:0x000a.000447ed SEQ:  1 OP:5.1
ktudb redo: siz: 96 spc: 4040 flg: 0x0022 seq: 0x00bf rec: 0x02
            xid:  0x0004.014.000000da
ktubu redo: slt: 20 rci: 1 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800398.00bf.01
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 9
fb: --H----- lb: 0x0  cc: 0
nrid:  0x024009a8.0
null:
CHANGE #2 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.000447ed SEQ:  1 OP:11.3
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800398.00bf.02
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2)
REDO RECORD - Thread:1 RBA: 0x000016.0000001e.0128 LEN: 0x10fc VLD: 0x01
SCN: 0x000a.000447f0 SUBSCN:  1 09/27/2012 20:03:45
CHANGE #1 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000a.000447ed SEQ:  1 OP:5.2
ktudh redo: slt: 0x0014 sqn: 0x00000000 flg: 0x000a siz: 4100 fbi: 84
            uba: 0x00800399.00bf.01    pxid:  0x0000.000.00000000
CHANGE #2 TYP:1 CLS:24 AFN:2 DBA:0x00800399 OBJ:4294967295 SCN:0x000a.000447ef SEQ:  1 OP:5.1
ktudb redo: siz: 4100 spc: 3942 flg: 0x000a seq: 0x00bf rec: 0x01
            xid:  0x0004.014.000000da
ktubu redo: slt: 20 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00800398
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a8  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 4015
fb: ----FL-- lb: 0x0  cc: 2
hrid: 0x024009a4.2
null: --
col  0: [ 2]  c1 04
col  1: [4000]
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
…………
CHANGE #3 TYP:2 CLS: 1 AFN:9 DBA:0x024009a8 OBJ:52143 SCN:0x000a.00041f78 SEQ:  1 OP:11.3
KTB Redo
op: 0x11  ver: 0x01
op: F  xid:  0x0004.014.000000da    uba: 0x00800399.00bf.01
Block cleanout record, scn:  0x000a.000447ef ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x000a.00041f78
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a8  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)
REDO RECORD - Thread:1 RBA: 0x000016.00000027.00b4 LEN: 0x0044 VLD: 0x01
SCN: 0x000a.000447f0 SUBSCN:  1 09/27/2012 20:03:45
CHANGE #1 TYP:0 CLS: 8 AFN:9 DBA:0x024009a1 OBJ:52143 SCN:0x000a.000447ed SEQ:  1 OP:13.22
Redo on Level1 Bitmap Block
Redo for state change
Len: 1 Offset: 7 newstate: 5
--可以看到redo部分没有太多记录,而undo部分的信息比较全(因为是delete操作)
--Logminer操作
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo01.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select sql_redo from v$Logminer_contents where SEG_NAME='XIFENFEI';
SQL_REDO
--------------------------------------------------------------------------------
delete from "CHF"."XIFENFEI" where "ID" = '2' and "NAME" = 'XXXXXXXXXXXXXXXXXXXX
…………
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' and ROWID = 'AAAMu
vAAJAAAAmkAAB';
Unsupported
Unsupported
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
--也只是捕获了第二条记录,第一条无任何信息,第三条因为行迁移所以提示Unsupported

说明:我们可以看到对于delete操作,有部分不能被Logminer正常捕获,行迁移的直接提示Unsupported

启用数据库附加日志

SQL> conn chf/xifenfei
Connected.
SQL> drop table xifenfei purge;
Table dropped.
SQL> alter database add supplemental log data;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949959788
SQL> select member from v$logfile where group# in(
  2  select group# from v$log where status='CURRENT');
MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/XFF/redo02.log
SQL> create table xifenfei(id number,name varchar2(4000));
Table created.
SQL> insert into xifenfei values(1,'xifenfei');
1 row created.
SQL> insert into xifenfei values(2,'XIFENFEI');
1 row created.
SQL> insert into xifenfei values(3,'XiFenFei');
1 row created.
SQL> commit;
Commit complete.
SQL> update xifenfei set name='www.xifenfei.com' where id=1;
1 row updated.
SQL> update xifenfei set name='WWW.XIFENFEI.COM' WHERE ID=2;
1 row updated.
SQL> update xifenfei set name='www.orasos.com' where id=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> update xifenfei set name=lpad('F',4000,'F') WHERE ID=1;
1 row updated.
SQL> update xifenfei set name=lpad('X',4000,'X') WHERE ID=2;
1 row updated.
SQL> update xifenfei set name=lpad('C',4000,'C') WHERE ID=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete from xifenfei where id=1;
1 row deleted.
SQL> delete from xifenfei where id=2;
1 row deleted.
SQL> delete from xifenfei where id=3;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949959845
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo02.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select sql_redo from v$Logminer_contents where SEG_NAME='XIFENFEI';
SQL_REDO
--------------------------------------------------------------------------------
create table xifenfei(id number,name varchar2(4000));
insert into "CHF"."XIFENFEI"("ID","NAME") values ('1','xifenfei');
insert into "CHF"."XIFENFEI"("ID","NAME") values ('2','XIFENFEI');
insert into "CHF"."XIFENFEI"("ID","NAME") values ('3','XiFenFei');
update "CHF"."XIFENFEI" set "NAME" = 'www.xifenfei.com' where "NAME" = 'xifenfei
' and ROWID = 'AAAMwEAAJAAAAmkAAA';
update "CHF"."XIFENFEI" set "NAME" = 'WWW.XIFENFEI.COM' where "NAME" = 'XIFENFEI
' and ROWID = 'AAAMwEAAJAAAAmkAAB';
update "CHF"."XIFENFEI" set "NAME" = 'www.orasos.com' where "NAME" = 'XiFenFei'
and ROWID = 'AAAMwEAAJAAAAmkAAC';
update "CHF"."XIFENFEI" set "NAME" = 'FFFFFFF…………FFFF' where
"NAME" = 'www.xifenfei.com' and ROWID = 'AAAMwEAAJAAAAmkAAA';
update "CHF"."XIFENFEI" set "NAME" = 'XXXXXXXXX…………XX' where
"NAME" = 'WWW.XIFENFEI.COM' and ROWID = 'AAAMwEAAJAAAAmkAAB';
update "CHF"."XIFENFEI" set "ID" = '3', "NAME" = 'CCCCC…………CCCCCCCCC'
where "ID" = '3' and "NAME"= 'www.orasos.com' and ROWID = 'AAAMwEAAJAAAAmkAAC';
delete from "CHF"."XIFENFEI" where "ID" = '1' and "NAME" = 'FFFFFF…………FF'
and ROWID = 'AAAMwEAAJAAAAmkAAA';
delete from "CHF"."XIFENFEI" where "ID" = '2' and "NAME" = 'XX…………XXX'
and ROWID = 'AAAMwEAAJAAAAmkAAB';
delete from "CHF"."XIFENFEI" where "ID" = '3' and "NAME" = 'CCCCCCCC…………CCC'
and ROWID = 'AAAMwEAAJAAAAmkAAC';
19 rows selected.

测试证明,启动附加日志后,数据库的相关操作都能够捕获,包括行迁移