oracle 19c logmnr操作-用途：恢复误更新/删除的记录数（undo已被覆盖，无法闪回）

一、挖掘DML日志和DDL日志

---dbms_logmnr.CONTINUOUS_MINE 在19c中已舍弃
--19c中plsql可以直接分析日志，普通用户也可以执行

set linesize  1000
col name for a100
select name,FIRST_TIME,NEXT_TIME from v$archived_log;


--如果需要的日志还在redo里记得add_logfile 里面写入redo
execute  dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/product/19c/db/dbs/arch1_15_1088930725.dbf',options=>dbms_logmnr.new); //第一个要加载的日志文件
execute  dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/oradata/ORA19C/redo02.log',options=>dbms_logmnr.addfile); //可以反复添加补充多个日志文件

--PLSQL  端执行:

begin
	dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/product/19c/db/dbs/arch1_15_1088930725.dbf',options=>dbms_logmnr.new); 
	dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/oradata/ORA19C/redo02.log',options=>dbms_logmnr.addfile);
	dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/oradata/ORA19C/redo02.log',options=>dbms_logmnr.addfile);
end;
/
	

3）执行 logmnr 分析
SQL>execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

4）查询分析结果，可以设置时间格式，也可以在显示方式里再确定格式.

SQL>select  username,scn,timestamp,sql_redo  from  v$logmnr_contents  where table_name='表名';


select t.OS_USERNAME,t.USERNAME,t.MACHINE_NAME,t.SEG_OWNER,t.TABLE_NAME,t.SQL_REDO,t.SQL_UNDO,t.TIMESTAMP from  v$logmnr_contents t  where t.table_name='T1' order by t.TIMESTAMP desc ;


select distinct sql_undo
                from v$logmnr_contents
               where seg_owner = 'LIJIE'
                 and table_name = 'T4'
                 and sql_redo like 'update%';


select distinct sql_redo
                from v$logmnr_contents
               where seg_owner = 'LIJIE'
                 and table_name = 'T4'
                 and sql_redo like 'drop%';				 

--自动根据sql_undo 回退：
begin
  for rec in (select sql_undo
                from v$logmnr_contents
               where seg_owner = 'LIJIE'
                 and table_name = 'T3'
                 and sql_redo like 'update%') loop
    if rec.sql_undo is not null then
      execute immediate substr(rec.sql_undo, 1, length(rec.sql_undo) - 1);
    end if;
  end loop;
  commit;
end;
/
 				 
				 
5）关闭日志分析
SQL>execute dbms_logmnr.end_logmnr;