备份与恢复系列 六 续 日志挖掘(Log Miner)找回update语句
来源:互联网 发布:win10安装后优化设置 编辑:程序博客网 时间:2024/05/16 09:32
在上一篇的试验中使用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;
0 0
- 备份与恢复系列 六 续 日志挖掘(Log Miner)找回update语句
- 备份与恢复系列 六 日志挖掘(Log Miner)找到误删除表的SCN
- 日志挖掘 log miner
- mysql bin-log日志与mysqldump备份与恢复
- mysql bin-log日志与mysqldump备份与恢复
- RMAN备份与恢复系列之redo日志文件恢复
- 事务日志备份与恢复
- MySQL备份恢复与日志
- MSSQL备份与恢复语句
- 备份与恢复系列 七 续 catalog中的备份脚本
- 备份与恢复系列八 续 RMAN备份的确认
- oracle Logminer日志挖掘redo log中的sql语句
- 如何使用 RMAN 增量备份恢复 data guard log gap(日志断档)
- 使用Log Miner恢复数据的案例一则
- openwrt之六:备份与恢复
- sqlserver 完整备份、差异备份、日志备份与恢复脚本
- Linux日志管理以及备份与恢复
- Redis系列~数据备份与恢复(十三)
- 用Python和Pygame写游戏-从入门到精通(10)
- 变绿变宽变高
- SIP概述——SIP学习笔记(一)
- 先转再学,,,堆与堆排序
- 算法学习(java实现之字符串篇)·····判断字符串是否没有重复字符
- 备份与恢复系列 六 续 日志挖掘(Log Miner)找回update语句
- Android View 分析(上)
- 备份与恢复系列 六 日志挖掘(Log Miner)找到误删除表的SCN
- HDU--2030
- 用Python和Pygame写游戏-从入门到精通(11)
- Search a 2D Matrix
- 二叉排序树详解
- JAVA垃案例及圾回收器工作原理及及使用实例介绍
- “Lc.exe已退出 代码为-1”的问题