一、
首先模拟,用户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
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
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
SQL>
二、设定logminer
2.1设定数据字典文件存放位置
SQL>
SQL> show parameter utl
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 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> alter system set utl_file_dir='E:\oracle\ora92\logminer' scope=spfile;
系统已更改。
SQL>
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
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
数据库装载完毕。
数据库已经打开。
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
---------- ---------- ---------- ---------- ---------- --- ----------------
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
2142302 29-8月 -06
3 1 4 104857600 1 YES INACTIVE
2141551 29-8月 -06
2141551 29-8月 -06
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
2 ONLINE
E:\ORACLE\ORADATA\MYDB\REDO02.LOG
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
2 ONLINE
E:\ORACLE\ORADATA\MYDB\REDO02.LOG
1 ONLINE
E:\ORACLE\ORADATA\MYDB\REDO01.LOG
E:\ORACLE\ORADATA\MYDB\REDO01.LOG
3 ONLINE
E:\ORACLE\ORADATA\MYDB\REDO03.LOG
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;
---------- ---------- --------
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
---------- ---------- ---------- ---------- --------------------------------
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
----------
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
----------
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';
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博客 ” ,链接:http://blog.itpub.net/7220098/viewspace-157774/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7220098/viewspace-157774/