logmnr恢复数据

logmnr恢复dml语句：
注意：通过logmnr恢复dml语句，需要之前项目上提前将额外日志打开才能恢复，否则恢复数据不全
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK FROM V$DATABASE;

1.查看最近的40个归档日志文件：
select * from (select 'archived-log',COMPLETION_TIME,THREAD#,ARCHIVED,SEQUENCE#, APPLIED,DELETED from v$archived_log order by first_time desc) where rownum < 40;
2.添加归档日志文件：
exec dbms_logmnr.add_logfile(logfilename=>'/oracle/app/bak/ORCL/archivelog/2023_05_19/o1_mf_1_21_l6fzxzjz_.arc',options=>dbms_logmnr.new);
exec dbms_logmnr.add_logfile(logfilename=>'/oracle/app/bak/ORCL/archivelog/2023_05_19/o1_mf_1_22_l6g6ylho_.arc',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'/oracle/app/bak/ORCL/archivelog/2023_05_19/o1_mf_1_23_l6g80fdg_.arc',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'/oradata/arch/1_11953_1002165838.dbf',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'/oradata/arch/1_11954_1002165838.dbf',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'/oradata/arch/1_11955_1002165838.dbf',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'/oradata/arch/1_11956_1002165838.dbf',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'/oradata/arch/1_11957_1002165838.dbf',options=>dbms_logmnr.addfile);
3.开始解析：
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
CREATE TABLE huifu9 AS select * from v$logmnr_contents where table_name='TEST11';
4.结束解析：
exec dbms_logmnr.end_logmnr;
SELECT TIMESTAMP,SESSION_INFO,SQL_REDO FROM huifu9 where table_name='TEST11';
SELECT TIMESTAMP,SESSION_INFO,SQL_REDO FROM huifu9 where upper(sql_redo) like '%TEST%';
5.通过sql_undo恢复数据：
通过sql_undo可以恢复之前delete的操作：
select timestamp,session_info,sql_redo,sql_undo from huifu9 where timestamp > to_date('2023-05-19 14:40:00','yyyy-mm-dd hh24:mi:ss');
select timestamp,session_info,sql_redo from jpt where timestamp > to_date('2023-05-19 14:40:00','yyyy-mm-dd hh24:mi:ss');
6.将sql_undo的数据单独导入一张表
create table huifu10 as select sql_undo from huifu9 where timestamp > to_date('2023-05-19 14:40:00','yyyy-mm-dd hh24:mi:ss');
select * from huifu10;
将huifu10表中的insert数据复制粘贴插入到test11被误删除数据的表中即可。

select sql_undo from huifu where table_name='EMP';
create table huifu1 as select sql_undo from huifu where table_name='EMP';