在上一篇的试验中使用logminer可以很容易的找到误删除的表,下面来看一个关于update的例子。
1.创建一个数据字典
EXECUTE dbms_logmnr_d.build( -
dictionary_filename => 'dictionary.ora', -
dictionary_location => '/home/oracle');
2.产生update语句
conn scott/tiger
update emp set sal=10,comm=20 where empno=7844;
commit;
3,为分析制定日志文件
conn / as sysdba
select member from v$logfile where group#=(select group# from v$log where status='CURRENT');
EXECUTE dbms_logmnr.add_logfile( -
logfilename => '/oradata/PRACTICE/redo03.log', -
options => dbms_logmnr.NEW);
查看将要分析的日志
select DB_NAME,THREAD_SQN,FILENAME from v$logmnr_logs;
DB_NAME THREAD_SQN FILENAME
------------------------ ---------- -----------------------------------------
PRACTICE 24 /oradata/PRACTICE/redo03.log
4,启动log miner
conn / as sysdba
EXECUTE dbms_logmnr.start_logmnr( -
dictfilename => '/home/oracle/dictionary.ora');
查看统计信息, 没有挖掘出任何数据
SELECT sql_redo,sql_undo
from v$logmnr_contents
where seg_name='EMP'
and seg_owner='SCOTT'
and seg_type_name='TABLE';
no rows selected
接下来有请今天的主角supplemental log
1,增加supplemental log
conn / as sysdba
alter database add supplemental log data;
SELECT supplemental_log_data_min FROM v$database;
SUPPLEMENTAL_LOG_DATA_MI
------------------------
YES
2,产生update语句
conn scott/tiger
SCOTT@PRACTICE >update emp set sal=10,comm=20 where empno=7844;
commit;
3,为分析制定日志文件
select member from v$logfile where group#=(select group# from v$log where status='CURRENT');
MEMBER
--------------------------------------------------------------------------------
/oradata/PRACTICE/redo03.log
SYS@PRACTICE >EXECUTE dbms_logmnr.add_logfile( -
> logfilename => '/oradata/PRACTICE/redo03.log', -
> options => dbms_logmnr.NEW);
SYS@PRACTICE >select DB_NAME,THREAD_SQN,FILENAME from v$logmnr_logs;
DB_NAME THREAD_SQN
------------------------ ----------
FILENAME
--------------------------------------------------------------------------------
PRACTICE 24
/oradata/PRACTICE/redo03.log
4,启动log miner
SYS@PRACTICE >EXECUTE dbms_logmnr.start_logmnr( -
> dictfilename => '/home/oracle/dictionary.ora');
PL/SQL procedure successfully completed.
SYS@PRACTICE >SELECT sql_redo,sql_undo
2 from v$logmnr_contents
3 where seg_name='EMP'
4 and seg_owner='SCOTT'
5 and seg_type_name='TABLE';
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
update "SCOTT"."EMP" set "SAL" = '10', "COMM" = '20' where "SAL" = '100' and "CO
MM" = '200' and ROWID = 'AAASZHAAEAAAACXAAJ';
update "SCOTT"."EMP" set "SAL" = '100', "COMM" = '200' where "SAL" = '10' and "C
OMM" = '20' and ROWID = 'AAASZHAAEAAAACXAAJ';
实验成功,挖掘到了update语句。
关闭日志挖掘
execute dbms_logmnr.end_logmnr;
删除supplemental log
alter database drop supplemental log data;
1.创建一个数据字典
EXECUTE dbms_logmnr_d.build( -
dictionary_filename => 'dictionary.ora', -
dictionary_location => '/home/oracle');
2.产生update语句
conn scott/tiger
update emp set sal=10,comm=20 where empno=7844;
commit;
3,为分析制定日志文件
conn / as sysdba
select member from v$logfile where group#=(select group# from v$log where status='CURRENT');
EXECUTE dbms_logmnr.add_logfile( -
logfilename => '/oradata/PRACTICE/redo03.log', -
options => dbms_logmnr.NEW);
查看将要分析的日志
select DB_NAME,THREAD_SQN,FILENAME from v$logmnr_logs;
DB_NAME THREAD_SQN FILENAME
------------------------ ---------- -----------------------------------------
PRACTICE 24 /oradata/PRACTICE/redo03.log
4,启动log miner
conn / as sysdba
EXECUTE dbms_logmnr.start_logmnr( -
dictfilename => '/home/oracle/dictionary.ora');
查看统计信息, 没有挖掘出任何数据
SELECT sql_redo,sql_undo
from v$logmnr_contents
where seg_name='EMP'
and seg_owner='SCOTT'
and seg_type_name='TABLE';
no rows selected
接下来有请今天的主角supplemental log
1,增加supplemental log
conn / as sysdba
alter database add supplemental log data;
SELECT supplemental_log_data_min FROM v$database;
SUPPLEMENTAL_LOG_DATA_MI
------------------------
YES
2,产生update语句
conn scott/tiger
SCOTT@PRACTICE >update emp set sal=10,comm=20 where empno=7844;
commit;
3,为分析制定日志文件
select member from v$logfile where group#=(select group# from v$log where status='CURRENT');
MEMBER
--------------------------------------------------------------------------------
/oradata/PRACTICE/redo03.log
SYS@PRACTICE >EXECUTE dbms_logmnr.add_logfile( -
> logfilename => '/oradata/PRACTICE/redo03.log', -
> options => dbms_logmnr.NEW);
SYS@PRACTICE >select DB_NAME,THREAD_SQN,FILENAME from v$logmnr_logs;
DB_NAME THREAD_SQN
------------------------ ----------
FILENAME
--------------------------------------------------------------------------------
PRACTICE 24
/oradata/PRACTICE/redo03.log
4,启动log miner
SYS@PRACTICE >EXECUTE dbms_logmnr.start_logmnr( -
> dictfilename => '/home/oracle/dictionary.ora');
PL/SQL procedure successfully completed.
SYS@PRACTICE >SELECT sql_redo,sql_undo
2 from v$logmnr_contents
3 where seg_name='EMP'
4 and seg_owner='SCOTT'
5 and seg_type_name='TABLE';
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
update "SCOTT"."EMP" set "SAL" = '10', "COMM" = '20' where "SAL" = '100' and "CO
MM" = '200' and ROWID = 'AAASZHAAEAAAACXAAJ';
update "SCOTT"."EMP" set "SAL" = '100', "COMM" = '200' where "SAL" = '10' and "C
OMM" = '20' and ROWID = 'AAASZHAAEAAAACXAAJ';
实验成功,挖掘到了update语句。
关闭日志挖掘
execute dbms_logmnr.end_logmnr;
删除supplemental log
alter database drop supplemental log data;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29047826/viewspace-1252474/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29047826/viewspace-1252474/