logminer工具可以分析出所有对于数据库操作的DML(insert、update、delete等)语句，另外还可分析得到一些必要的回滚SQL语句。该工具特别适用于调试、审计或者回退某个特定的事务。LogMiner 工具即可以用来分析在线，也可以用来分析离线日志文件，即可以分析本身自己数据库的重作日志文件，也可以用来分析其他数据库的重作日志文件。
测试环境部署：
–建表
create table test as select * from dba_objects;
–模拟误更新
update test set object_id=1 where 1=1;
恢复步骤：
1、加入日志或归档，如果不确定时间就多家几组日志
execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/ORCL/onlinelog/redo4.log',dbms_logmnr.new);
execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/ORCL/onlinelog/redo5.log',dbms_logmnr.new);
execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/ORCL/onlinelog/redo6.log',dbms_logmnr.new);
execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/ORCL/onlinelog/redo7.log',dbms_logmnr.new);
2、查看你有哪些归档日志加入了列表中
select LOW_TIME,HIGH_TIME,LOW_SCN,NEXT_SCN from v$logmnr_logs;
LOW_TIME HIGH_TIME LOW_SCN NEXT_SCN
-------------- -------------- ---------- ----------
04-4月 -23 01-1月 -88 8114870 1.8447E+19
3、分析日志
--无限制条件
execute dbms_logmnr.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog); --使用在线目录
--限制起始时间
execute dbms_logmnr.start_logmnr( dictfilename=>'/home/oracle/logmnr.log', --指定日志文件
starttime=>to_date('2023-04-0415:00', 'YYYY-MM-DDHH24:MI:SS'),
endtime=>to_date('2023-04-0415:00', 'YYYY-MM-DDHH24:MI:SS'));
--限制起始SCN
execute dbms_logmnr.start_logmnr(
startscn=>20,
endscn=>50);
4、查询分析结果，可以按照表名使用like排查，利用sql_undo字段进行回滚对误操作数据进行恢复
select username,sql_undo from v$logmnr_contents where operation='UPDATE' and sql_redo like '%TEST%';
USERNAME SQL_UNDO
UNKNOWN update "SYC"."TEST" set "OBJECT_ID" = '190' where "OBJECT_ID" = '1' and ROWID = 'AAASCoAABAAALorAA2';
UNKNOWN update "SYC"."TEST" set "OBJECT_ID" = '189' where "OBJECT_ID" = '1' and ROWID = 'AAASCoAABAAALorAA3';
UNKNOWN update "SYC"."TEST" set "OBJECT_ID" = '191' where "OBJECT_ID" = '1' and ROWID = 'AAASCoAABAAALorAA4';
UNKNOWN update "SYC"."TEST" set "OBJECT_ID" = '192' where "OBJECT_ID" = '1' and ROWID = 'AAASCoAABAAALorAA5';
........................................ ........................................
转储到实体表：
create table test_recover as select * from v$logmnr_contents where operation='UPDATE' and sql_redo like '%TEST%';
5、执行sql_undo段回滚语句
