sql中如何使用dbms_logmnr

发布时间：2021-12-16 10:52 所属栏目：53 来源：互联网

导读：这篇文章主要介绍sql中如何使用dbms_logmnr，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！讲到不完全恢复（找回drop table）时，经常需要确定drop table的确切时间，所以经常需要用到dbms_logmner SQL desc dbms_logmnr_d PROCEDU

这篇文章主要介绍sql中如何使用dbms_logmnr，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

讲到不完全恢复（找回drop table）时，经常需要确定drop table的确切时间，所以经常需要用到dbms_logmner

SQL> desc dbms_logmnr_d
PROCEDURE BUILD
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
DICTIONARY_FILENAME            VARCHAR2                IN     DEFAULT
DICTIONARY_LOCATION            VARCHAR2                IN     DEFAULT
OPTIONS                        NUMBER                  IN     DEFAULT
PROCEDURE SET_TABLESPACE
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
NEW_TABLESPACE                 VARCHAR2                IN
--过程set_tablespace可以把logmnr相关的对象从sysaux表空间转移到set_tablespace设置的表空间
SQL> exec dbms_logmnr_d.set_tablespace('TEST');

PL/SQL 过程已成功完成。

SQL> select count(*) from dba_segments where tablespace_name='TEST';

COUNT(*)
----------
       99

SQL> exec dbms_logmnr_d.set_tablespace('SYSAUX');

PL/SQL 过程已成功完成。

SQL> select count(*) from dba_segments where tablespace_name='TEST';

COUNT(*)
----------
        4

SQL> desc dbms_logmnr
PROCEDURE ADD_LOGFILE
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
LOGFILENAME                    VARCHAR2                IN
OPTIONS                        BINARY_INTEGER          IN     DEFAULT
FUNCTION COLUMN_PRESENT RETURNS BINARY_INTEGER
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQL_REDO_UNDO                  NUMBER                  IN     DEFAULT
COLUMN_NAME                    VARCHAR2                IN     DEFAULT
PROCEDURE END_LOGMNR
FUNCTION MINE_VALUE RETURNS VARCHAR2
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQL_REDO_UNDO                  NUMBER                  IN     DEFAULT
COLUMN_NAME                    VARCHAR2                IN     DEFAULT
PROCEDURE REMOVE_LOGFILE
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
LOGFILENAME                    VARCHAR2                IN
PROCEDURE START_LOGMNR
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
STARTSCN                       NUMBER                  IN     DEFAULT
ENDSCN                         NUMBER                  IN     DEFAULT
STARTTIME                      DATE                    IN     DEFAULT
ENDTIME                        DATE                    IN     DEFAULT
DICTFILENAME                   VARCHAR2                IN     DEFAULT
OPTIONS                        BINARY_INTEGER          IN     DEFAULT
--设置参数utl_file_dir
SQL> show parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string
SQL> exec dbms_logmnr_d.build('dict.ora','E:oracleproduct10.2.0admintestlo
gmnr');
BEGIN dbms_logmnr_d.build('dict.ora','E:oracleproduct10.2.0admintestlogmnr
'); END;

*
第 1 行出现错误:
ORA-01308: 未设置初始化参数 utl_file_dir
ORA-06512: 在 "SYS.DBMS_LOGMNR_INTERNAL", line 3474
ORA-06512: 在 "SYS.DBMS_LOGMNR_INTERNAL", line 3552
ORA-06512: 在 "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: 在 line 1

SQL> alter system set utl_file_dir='E:oracleproduct10.2.0admintestlogmnr';

alter system set utl_file_dir='E:oracleproduct10.2.0admintestlogmnr'
                *
第 1 行出现错误:
ORA-02095: 无法修改指定的初始化参数

SQL> alter system set utl_file_dir='E:oracleproduct10.2.0admintestlogmnr' scope=spfile;

系统已更改。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 163577856 bytes
Fixed Size                  1247876 bytes
Variable Size              92276092 bytes
Database Buffers           67108864 bytes
Redo Buffers                2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> exec dbms_logmnr_d.build('dict.ora','E:oracleproduct10.2.0admintestlo
gmnr');

PL/SQL 过程已成功完成。

SQL>
SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:>e:

E:>cd E:oracleproduct10.2.0admintestlogmnr

E:oracleproduct10.2.0admintestlogmnr>dir
驱动器 E 中的卷没有标签。
卷的序列号是 F6E5-4B31

E:oracleproduct10.2.0admintestlogmnr 的目录

2008-09-26 13:11    <DIR>          .
2008-09-26 13:11    <DIR>          ..
2008-09-26 13:11        10,871,850 dict.ora
              1 个文件     10,871,850 字节
              2 个目录 3,143,331,840 可用字节

E:oracleproduct10.2.0admintestlogmnr>exit

SQL> show user
USER 为 "SYS"
SQL> connect test/test
已连接。
SQL> set time on
13:13:04 SQL> desc tt
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------

ID                                                 NUMBER(38)
NAME                                               VARCHAR2(10)

13:13:08 SQL> select group#,status,sequence#,first_change#,first_time from v$log
;

GROUP# STATUS            SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ---------------- ---------- ------------- --------------
        1 CURRENT                  26       1576118 26-9月 -08
        2 INACTIVE                 25       1570052 26-9月 -08
        3 INACTIVE                 24       1563812 26-9月 -08

13:13:42 SQL> alter session set nls_date_format='yyyy/mm/dd hh34:mi:ss';

会话已更改。

13:13:58 SQL> select group#,status,sequence#,first_change#,first_time from v$log
;

GROUP# STATUS            SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ---------------- ---------- ------------- -------------------
        1 CURRENT                  26       1576118 2008/09/26 12:56:37
        2 INACTIVE                 25       1570052 2008/09/26 12:53:48
        3 INACTIVE                 24       1563812 2008/09/26 10:16:24

13:14:00 SQL> select *from tt;

未选定行

13:14:38 SQL> insert into tt values(1,'a');

已创建 1 行。

13:14:43 SQL> insert into tt values(2,'b');

已创建 1 行。

13:14:52 SQL> commit;

提交完成。

13:14:56 SQL> alter system switch logfile;

系统已更改。

13:15:03 SQL> alter system checkpoint;

系统已更改。

13:15:10 SQL> select group#,status,sequence#,first_change#,first_time from v$log
;

GROUP# STATUS            SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ---------------- ---------- ------------- -------------------
        1 INACTIVE                 26       1576118 2008/09/26 12:56:37
        2 INACTIVE                 25       1570052 2008/09/26 12:53:48
        3 CURRENT                  27       1580527 2008/09/26 13:15:02

13:15:13 SQL> update tt set id=100 ,name='logmnr';

已更新2行。

13:15:58 SQL> commit;

提交完成。

13:16:00 SQL> alter system switch logfile;

系统已更改。

13:16:04 SQL> alter system checkpoint;

系统已更改。

13:16:05 SQL> select group#,status,sequence#,first_change#,first_time from v$log
;

GROUP# STATUS            SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ---------------- ---------- ------------- -------------------
        1 INACTIVE                 26       1576118 2008/09/26 12:56:37
        2 CURRENT                  28       1580563 2008/09/26 13:16:04
        3 INACTIVE                 27       1580527 2008/09/26 13:15:02

13:16:06 SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------

E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG
E:ORACLEPRODUCT10.2.0ORADATATESTREDO02.LOG
E:ORACLEPRODUCT10.2.0ORADATATESTREDO03.LOG
13:32:51 SQL> select sequence#, first_change#,first_time,next_change#,next_time
from v$archived_log where sequence# in (26,27,28)
13:34:08   2 and resetlogs_id=666280390;

SEQUENCE# FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ------------- ------------------- ------------ -------------------
       26       1576118 2008/09/26 12:56:37      1580527 2008/09/26 13:15:02
       27       1580527 2008/09/26 13:15:02      1580563 2008/09/26 13:16:04

13:34:10 SQL> col name format a80
13:35:08 SQL> select name from v$archived_log where sequence# in (26,27,28)
13:35:16   2 and resetlogs_id=666280390;

NAME
--------------------------------------------------------------------------------

E:ORACLEPRODUCT10.2.0DB_2DATABASEDB_RECOVERY_FILE_DESTARC26_666280390_1
E:ORACLEPRODUCT10.2.0DB_2DATABASEDB_RECOVERY_FILE_DESTARC27_666280390_1

13:35:18 SQL>
13:38:46 SQL> show user
USER 为 "TEST"
13:38:48 SQL> exec dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0DB_2DATABA
SEDB_RECOVERY_FILE_DESTARC26_666280390_1',1);
BEGIN dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0DB_2DATABASEDB_RECOVER
Y_FILE_DESTARC26_666280390_1',1); END;

*
第 1 行出现错误:
ORA-06550: 第 1 行, 第 7 列:
PLS-00201: 必须声明标识符 'DBMS_LOGMNR.ADD_LOGFILE'
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored

13:40:31 SQL> connect / as sysdba
已连接。
13:40:57 SQL> exec dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0DB_2DATABA
SEDB_RECOVERY_FILE_DESTARC26_666280390_1',dbms_logmnr.new);

PL/SQL 过程已成功完成。
--dbms_logmnr.new的作用是清除掉之前(add_logfile)加进来的logfile
13:41:06SQL> exec dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0DB_2DATABA
SEDB_RECOVERY_FILE_DESTARC27_666280390_1',dbms_logmnr.new);

PL/SQL 过程已成功完成。

13:42:33 SQL> exec dbms_logmnr.remove_logfile('E:ORACLEPRODUCT10.2.0DB_2DAT
ABASEDB_RECOVERY_FILE_DESTARC27_666280390_1');

PL/SQL 过程已成功完成。

13:42:37 SQL> exec dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0DB_2DATABA
SEDB_RECOVERY_FILE_DESTARC27_666280390_1',dbms_logmnr.new);

PL/SQL 过程已成功完成。

13:42:45 SQL>
13:46:52 SQL> select filename from v$logmnr_logs;

FILENAME
--------------------------------------------------------------------------------

E:ORACLEPRODUCT10.2.0DB_2DATABASEDB_RECOVERY_FILE_DESTARC27_666280390_1

13:46:58 SQL> exec dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0DB_2DATABA
SEDB_RECOVERY_FILE_DESTARC26_666280390_1',dbms_logmnr.new);

PL/SQL 过程已成功完成。

13:47:12 SQL> select filename from v$logmnr_logs;

FILENAME
--------------------------------------------------------------------------------

E:ORACLEPRODUCT10.2.0DB_2DATABASEDB_RECOVERY_FILE_DESTARC26_666280390_1

13:47:27 SQL> exec dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0DB_2DATABA
SEDB_RECOVERY_FILE_DESTARC27_666280390_1');

PL/SQL 过程已成功完成。
13:47:41 SQL> desc v$logmnr_logs;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------

LOG_ID                                             NUMBER
FILENAME                                           VARCHAR2(512)
LOW_TIME                                           DATE
HIGH_TIME                                          DATE
DB_ID                                              NUMBER
DB_NAME                                            VARCHAR2(8)
RESET_SCN                                          NUMBER
RESET_SCN_TIME                                     DATE
THREAD_ID                                          NUMBER
THREAD_SQN                                         NUMBER
LOW_SCN                                            NUMBER
NEXT_SCN                                           NUMBER
DICTIONARY_BEGIN                                   VARCHAR2(3)
DICTIONARY_END                                     VARCHAR2(3)
TYPE                                               VARCHAR2(7)
BLOCKSIZE                                          NUMBER
FILESIZE                                           NUMBER
INFO                                               VARCHAR2(32)
STATUS                                             NUMBER

13:47:39 SQL> select filename from v$logmnr_logs;

FILENAME
--------------------------------------------------------------------------------

E:ORACLEPRODUCT10.2.0DB_2DATABASEDB_RECOVERY_FILE_DESTARC26_666280390_1
E:ORACLEPRODUCT10.2.0DB_2DATABASEDB_RECOVERY_FILE_DESTARC27_666280390_1

13:47:41 SQL>
13:51:01 SQL> select log_id,low_scn,low_time,next_scn,high_time from v$logmnr_lo
gs;

LOG_ID    LOW_SCN LOW_TIME              NEXT_SCN HIGH_TIME
---------- ---------- ------------------- ---------- -------------------
       26    1576118 2008/09/26 12:56:37    1580527 2008/09/26 13:15:02
       27    1580527 2008/09/26 13:15:02    1580563 2008/09/26 13:16:04

13:51:09 SQL>
13:51:09 SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'E:oracleproduct10.
2.0admintestlogmnrdict.ora',startscn=>1576118,endscn=>1580563);

PL/SQL 过程已成功完成。

13:55:42 SQL> select count(*) from v$logmnr_contents;

COUNT(*)
----------
     8648

13:56:15 SQL> desc v$logmnr_contents
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------

SCN                                                NUMBER
CSCN                                               NUMBER
TIMESTAMP                                          DATE
COMMIT_TIMESTAMP                                   DATE
THREAD#                                            NUMBER
LOG_ID                                             NUMBER
XIDUSN                                             NUMBER
XIDSLT                                             NUMBER
XIDSQN                                             NUMBER
PXIDUSN                                            NUMBER
PXIDSLT                                            NUMBER
PXIDSQN                                            NUMBER
RBASQN                                             NUMBER
RBABLK                                             NUMBER
RBABYTE                                            NUMBER
UBAFIL                                             NUMBER
UBABLK                                             NUMBER
UBAREC                                             NUMBER
UBASQN                                             NUMBER
ABS_FILE#                                          NUMBER
REL_FILE#                                          NUMBER
DATA_BLK#                                          NUMBER
DATA_OBJ#                                          NUMBER
DATA_OBJD#                                         NUMBER
SEG_OWNER                                          VARCHAR2(32)
SEG_NAME                                           VARCHAR2(256)
TABLE_NAME                                         VARCHAR2(32)
SEG_TYPE                                           NUMBER
SEG_TYPE_NAME                                      VARCHAR2(32)
TABLE_SPACE                                        VARCHAR2(32)
ROW_ID                                             VARCHAR2(18)
SESSION#                                           NUMBER
SERIAL#                                            NUMBER
USERNAME                                           VARCHAR2(30)
SESSION_INFO                                       VARCHAR2(4000)
TX_NAME                                            VARCHAR2(256)
ROLLBACK                                           NUMBER
OPERATION                                          VARCHAR2(32)
OPERATION_CODE                                     NUMBER
SQL_REDO                                           VARCHAR2(4000)
SQL_UNDO                                           VARCHAR2(4000)
RS_ID                                              VARCHAR2(32)
SEQUENCE#                                          NUMBER
SSN                                                NUMBER
CSF                                                NUMBER
INFO                                               VARCHAR2(32)
STATUS                                             NUMBER
REDO_VALUE                                         NUMBER
UNDO_VALUE                                         NUMBER
SQL_COLUMN_TYPE                                    VARCHAR2(30)
SQL_COLUMN_NAME                                    VARCHAR2(30)
REDO_LENGTH                                        NUMBER
REDO_OFFSET                                        NUMBER
UNDO_LENGTH                                        NUMBER
UNDO_OFFSET                                        NUMBER
DATA_OBJV#                                         NUMBER
SAFE_RESUME_SCN                                    NUMBER
XID                                                RAW(8)
PXID                                               RAW(8)
AUDIT_SESSIONID                                    NUMBER

14:08:10 SQL> select rbasqn,rbablk,rbabyte from v$logmnr_contents where seg_owne
r='TEST' and seg_name='TT';

RBASQN     RBABLK    RBABYTE
---------- ---------- ----------
       26       7544        400
       26       7546         16
       27         30         16
       27         30        468

14:08:33 SQL>
14:14:58 SQL> select scn,timestamp , sql_redo from v$logmnr_contents where seg_o
wner='TEST' and seg_name='TT';

SCN TIMESTAMP
---------- -------------------
SQL_REDO
--------------------------------------------------------------------------------
--------------------
  1580518 2008/09/26 13:14:47
insert into "TEST"."TT"("ID","NAME") values ('1','a');

1580520 2008/09/26 13:14:53
insert into "TEST"."TT"("ID","NAME") values ('2','b');

1580558 2008/09/26 13:15:59
update "TEST"."TT" set "ID" = '100', "NAME" = 'logmnr' where "ID" = '1' and "NAM
E" = 'a' and ROWID =
'AAAC+uAACAAAAMPAAA';

SCN TIMESTAMP
---------- -------------------
SQL_REDO
--------------------------------------------------------------------------------
--------------------

1580558 2008/09/26 13:15:59
update "TEST"."TT" set "ID" = '100', "NAME" = 'logmnr' where "ID" = '2' and "NAM
E" = 'b' and ROWID =
'AAAC+uAACAAAAMPAAB';
SQL> select group#,status,sequence# from v$log;

GROUP# STATUS            SEQUENCE#
---------- ---------------- ----------
        1 CURRENT                  29
        2 INACTIVE                 28
        3 INACTIVE                 27

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------

E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG
E:ORACLEPRODUCT10.2.0ORADATATESTREDO02.LOG
E:ORACLEPRODUCT10.2.0ORADATATESTREDO03.LOG

SQL> select * from test.tt;

ID NAME
---------- ----------
        1 a

SQL> CONNECT TEST/TEST
已连接。
SQL> delete from tt;

已删除 1 行。

SQL> commit;

提交完成。

SQL> alter system checkpoint;

系统已更改。

SQL> select group#,status,sequence# from v$log;

GROUP# STATUS            SEQUENCE#
---------- ---------------- ----------
        1 CURRENT                  29
        2 INACTIVE                 28
        3 INACTIVE                 27

SQL> connect / as sysdba
已连接。
SQL> exec dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.
LOG');

PL/SQL 过程已成功完成。

SQL> select filename from v$logmnr_logs;

FILENAME
--------------------------------------------------------------------------------

E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG

SQL> exec dbms_logmnr.start_logmnr;

PL/SQL 过程已成功完成。

SQL> select count(*) from v$logmnr_contents;

COUNT(*)
----------
      326

SQL> select count(*) from v$logmnr_contents where seg_name like '%tt%';

COUNT(*)
----------
        0

SQL> select count(*) from v$logmnr_contents where seg_name like '%TT%';

COUNT(*)
----------
        0

SQL> select count(*) from v$logmnr_contents where sql_redo like '%delete%'
2 ;

COUNT(*)
----------
        2
--从redo_sql中看出并没有出现对象tt的名字，而是使用了“"UNKNOWN"."OBJ# 12206"”
SQL> select sql_redo,seg_owner,seg_name from v$logmnr_contents where sql_redo li
ke '%delete%'
2 ;

SQL_REDO
--------------------------------------------------------------------------------

SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------

delete from "UNKNOWN"."OBJ# 12206" where "COL 1" = HEXTORAW('c102') and "COL 2"
= HEXTORAW('61') and ROWID = 'AAAC+uAACAAAAMQAAA';
UNKNOWN
OBJ# 12206

delete from "UNKNOWN"."OBJ# 8781" where "COL 1" = HEXTORAW('c20216') and "COL 2"

= HEXTORAW('c105') and "COL 3" = HEXTORAW('80') and "COL 4" = HEXTORAW('c102')

SQL_REDO
--------------------------------------------------------------------------------

SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------

and "COL 5" IS NULL and "COL 6" = HEXTORAW('436f6e63757272656e6379') and "COL 7"

IS NULL and "COL 8" = HEXTORAW('c22205') and "COL 9" = HEXTORAW('44617461626173

652054696d65205370656e742057616974696e6720282529') and "COL 10" = HEXTORAW('3537

2e3835363036') and "COL 11" = HEXTORAW('436f6e63757272656e6379') and "COL 12" IS

NULL and "COL 13" IS NULL and "COL 14" = HEXTORAW('786c091a0730380d4ab5c01c3c')

and "COL 15" = HEXTORAW('786c091a0730380d4ab5c01c3c') and "COL 16" IS NULL and
"COL 17" IS NULL and "COL 18" IS NULL and "COL 19" IS NULL and "COL 20" IS NULL

SQL_REDO
--------------------------------------------------------------------------------

SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------

and "COL 21" = HEXTORAW('c106') and "COL 22" IS NULL and "COL 23" = HEXTORAW('22

74657374222e227473696422') and "COL 24" = HEXTORAW('787973') and "COL 25" = HEXT

ORAW('3139322e3136382e302e323532') and "COL 26" = HEXTORAW('74736964') and "COL
27" = HEXTORAW('c102') and "COL 28" IS NULL and "COL 29" IS NULL and "COL 30" =
HEXTORAW('4438313436363034414543302d344141372d413732452d394230393735413130453746

2d30') and "COL 31" IS NULL and "COL 32" = HEXTORAW('c13a563d3a02274c150b') and
ROWID = 'AAACJNAADAAAAplAAC';

SQL_REDO
--------------------------------------------------------------------------------

SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------

UNKNOWN
OBJ# 8781

SQL> select object_id, data_object_id from dba_objects where object_name='TT' AN
D OWNER='TEST';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
    12206          12206
--验证下rba：
SQL> select rbasqn,rbablk,rbabyte from v$logmnr_contents where sql_redo like '%d
elete%';

RBASQN     RBABLK    RBABYTE
---------- ---------- ----------
       29        295         16
       29        558        400
SQL> select status,group# from v$log;

STATUS               GROUP#
---------------- ----------
CURRENT                   1
INACTIVE                  2
INACTIVE                  3

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------

E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG
E:ORACLEPRODUCT10.2.0ORADATATESTREDO02.LOG
E:ORACLEPRODUCT10.2.0ORADATATESTREDO03.LOG

SQL> alter system dump logfile 'E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG
';

系统已更改。
--dump logfile trace的rba是:RBA: 0x00001d.00000127.0010
SQL> select to_number('00000127','xxxxxxxx') from dual;

TO_NUMBER('00000127','XXXXXXXX')
--------------------------------
                            295

SQL> select redo_length from v$logmnr_contents where sql_redo like '%delete%';

REDO_LENGTH
-----------
         0
         0

SQL>
dump logfile trace:
--=============================================
REDO RECORD - Thread:1 RBA: 0x00001d.00000127.0010 LEN: 0x0188 VLD: 0x01
SCN: 0x0000.00182edd SUBSCN: 1 09/26/2008 14:45:55
CHANGE #1 TYP:0 CLS:23 AFN:4 DBA:0x01000039 OBJ:4294967295 SCN:0x0000.00182ed2 SEQ: 1 OP:5.2
ktudh redo: slt: 0x0025 sqn: 0x000000c9 flg: 0x0012 siz: 148 fbi: 0
           uba: 0x010000d1.005e.0c    pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:24 AFN:4 DBA:0x010000d1 OBJ:4294967295 SCN:0x0000.00182ed1 SEQ: 7 OP:5.1
ktudb redo: siz: 148 spc: 6928 flg: 0x0012 seq: 0x005e rec: 0x0c
           xid: 0x0004.025.000000c9
ktubl redo: slt: 37 rci: 0 opc: 11.1 objn: 12206 objd: 12206 tsn: 5
Undo type: Regular undo        Begin trans    Last buffer split: No
Temp Object: No
Tablespace Undo: No
            0x00000000 prev ctl uba: 0x010000d1.005e.05
prev ctl max cmt scn: 0x0000.00182b93 prev tx cmt scn: 0x0000.00182b98
txn start scn: 0xffff.ffffffff logon user: 31 prev brb: 16777278 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00800310 hdba: 0x0080030b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 8
fb: --H-FL-- lb: 0x0 cc: 2
null: --
col 0: [ 2] c1 02
col 1: [ 1] 61
CHANGE #3 TYP:2 CLS: 1 AFN:2 DBA:0x00800310 OBJ:12206 SCN:0x0000.00182caf SEQ: 1 OP:11.3
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0004.025.000000c9    uba: 0x010000d1.005e.0c
Block cleanout record, scn: 0x0000.00182edd ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.00182caf
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00800310 hdba: 0x0080030b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
--===================================================

以上是“sql中如何使用dbms_logmnr”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注亿速云行业资讯频道！

（编辑：ASP站长网）