Oracle-找回误删的表数据(LogMiner 挖掘日志)

      数据库的表被误删了部分数据 找回前提：有这个时间段的归档文件 或者 redo文件没被覆盖

      解决办法：Oracle LogMiner 挖掘日志 或 数据库闪回。

     

1.查看归档状态

SYS@orcl> select log_mode from v$database;

show parameter log_archive_dest_1 ；

NAME                                 TYPE         VALUE
------------------------------------ ------------- ------------------------------
log_archive_dest_1            string         location=/home/u01/app/oracle/oradata/ORCL/arc

2.准备归档日志 

将待挖掘的日志移动到指定目录下：/home/oracle/temp/

cd /home/u01/app/oracle/oradata/ORCL/arc/

mv 1_19567_1150134424.dbf /home/oracle/temp/
mv 1_19568_1150134424.dbf /home/oracle/temp/
mv 1_19569_1150134424.dbf /home/oracle/temp/
mv 1_19570_1150134424.dbf /home/oracle/temp/
mv 1_19571_1150134424.dbf /home/oracle/temp/
mv 1_19572_1150134424.dbf /home/oracle/temp/
mv 1_19573_1150134424.dbf /home/oracle/temp/
mv 1_19574_1150134424.dbf /home/oracle/temp/

3.日志挖掘命令

1)dbms_logmnr.add_logfile

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/home/oracle/temp/1_19514_1150134424.dbf',Options=>dbms_logmnr.new);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/home/oracle/temp/1_19515_1150134424.dbf');
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/home/oracle/temp/1_19516_1150134424.dbf');

执行结果：

2)dbms_logmnr.start_logmnr

SYS@orcl> EXECUTE dbms_logmnr.start_logmnr(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

3)查看结果

SYS@orcl> select count(*) from V$LOGMNR_CONTENTS ;

SYS@orcl>create table aaa as select * from  V$LOGMNR_CONTENTS ;

 报错解决：

select * from dba_temp_files;
alter tablespace TEMP add tempfile '/home/u01/app/oracle/oradata/ORCL/temp03.dbf' size 100M autoextend on maxsize 30G;

SYS@orcl> EXECUTE DBMS_LOGMNR.END_LOGMNR; 

4.分析挖掘结果

create  table data_014002025103200003 as 
select scn SCN1,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') times,sql_redo CZ,sql_undo SQLS,ttt.*
from ttt where table_name in('FM_AS_SUBJECTS',
'FM_AS_DEPRECIATION',
'FM_AS_PROVISION',
'FM_AC_BOOK',
'FM_AC_ASSIST',
'FM_AC_SUBJECT',
'FM_RPT_KMYEB_TEMP',
'FM_CRED_MANAGE',
'FM_RPT_KMYEB'
) and seg_owner='NH_MCRO_FINANCE'  and sql_redo like '%014002025103200003%'  --'%014009030102200001%'
--and sql_redo like 'delete%' 
and sql_redo<>'Unsupported'
order by 2,1;

select * from data_FM_AC_ASSIST t
WHERE T.CZ LIKE '%014006021201209001%'

014002025103200003
014002025103211001
014006021201209001