一、

首先模拟，用户sys 将dodd.test表truncate掉：

E:\oracle\ora92\bin>sqlplus "sys/oracle@mydb as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期二 8月 29 11:13:42 2006

连接到:
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

SQL> truncate table dodd.test;

表已截掉。

SQL>
SQL>
二、设定logminer

2.1设定数据字典文件存放位置
SQL>
SQL> show parameter utl

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
utl_file_dir                         string
SQL> alter system set utl_file_dir='E:\oracle\ora92\logminer';
alter system set utl_file_dir='E:\oracle\ora92\logminer'
                 *
ERROR 位于第 1 行:
ORA-02095: 无法修改指定的初始化参数

(utl_file_dir是静态参数)
SQL> alter system set utl_file_dir='E:\oracle\ora92\logminer' scope=spfile;

系统已更改。

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

Total System Global Area 135339844 bytes
Fixed Size                   454468 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。

2.2生成数据字典文件

SQL> execute dbms_logmnr_d.build('dictionary.ora','E:\oracle\ora92\logminer');

PL/SQL 过程已成功完成。

2.3确定要分析的redo日志

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
         1          1          5 104857600          1 YES INACTIVE
      2141986 29-8月 -06

2 1 6 104857600 1 NO CURRENT
2142302 29-8月 -06

3 1 4 104857600 1 YES INACTIVE
2141551 29-8月 -06

SQL> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
2 ONLINE
E:\ORACLE\ORADATA\MYDB\REDO02.LOG

1 ONLINE
E:\ORACLE\ORADATA\MYDB\REDO01.LOG

3 ONLINE
E:\ORACLE\ORADATA\MYDB\REDO03.LOG

SQL> execute dbms_logmnr.add_logfile('E:\ORACLE\ORADATA\MYDB\REDO02.LOG',dbms_logmnr.new);

PL/SQL 过程已成功完成。

SQL> execute dbms_logmnr.start_logmnr(DictFileName=>'E:\oracle\ora92\logminer\dictionary.ora');

PL/SQL 过程已成功完成。

SQL> select * from v$logmnr_dictionary;

TIMESTAMP       DB_ID DB_NAME
---------- ---------- --------
FILENAME
--------------------------------------------------------------------------------
DICTIONARY_SCN RESET_SCN RESET_SCN_ ENABLED_THREAD_MAP
-------------- ---------- ---------- --------------------------------
INFO                                 STATUS
-------------------------------- ----------
29-8月 -06 2513908929 MYDB
E:\oracle\ora92\logminer\dictionary.ora
             0          0
                                          0
SQL> select * from v$logmnr_parameters;

START_DATE END_DATE    START_SCN    END_SCN INFO
---------- ---------- ---------- ---------- --------------------------------
    STATUS
----------
01-1月 -88 01-1月 -11          0          0
         0

SQL> select * from v$logmnr_logs;

    LOG_ID
----------
FILENAME
--------------------------------------------------------------------------------
LOW_TIME   HIGH_TIME       DB_ID DB_NAME   RESET_SCN RESET_SCN_ THREAD_ID
---------- ---------- ---------- -------- ---------- ---------- ----------
THREAD_SQN    LOW_SCN   NEXT_SCN DIC DIC INFO
---------- ---------- ---------- --- --- --------------------------------
    STATUS
----------
         6
E:\ORACLE\ORADATA\MYDB\REDO02.LOG
29-8月 -06 01-1月 -88 2513908929 MYDB        2035017 26-8月 -06          1

    LOG_ID
----------
FILENAME
--------------------------------------------------------------------------------
LOW_TIME   HIGH_TIME       DB_ID DB_NAME   RESET_SCN RESET_SCN_ THREAD_ID
---------- ---------- ---------- -------- ---------- ---------- ----------
THREAD_SQN    LOW_SCN   NEXT_SCN DIC DIC INFO
---------- ---------- ---------- --- --- --------------------------------
    STATUS
----------
         6    2142302 2.8147E+14 NO NO
         1

2.4查询视图，找到相应的DML语句

SQL> select SQL_REDO,USERNAME,SESSION_INFO from v$logmnr_contents
2 where username='SYS' and seg_name='TEST';

SQL>

SQL_REDO	USERNAME	SESSION_INFO
truncate table dodd.test;	SYS	login_username=SYS client_info= OS_username=DODD\fandf Machine_name=WORKGROUP\DODD

ITPub博客

Oracle9i LogMiner实验记录

最新文章