本文从一个用户操作错误引出数据恢复，通过LogMiner来辅助找回丢失的数据。
SQL> select count(*) from test;
COUNT(*)
----------
14
SQL> delete from test where rownum <=4;
4 rows deleted.
SQL> select count(*) from test;
COUNT(*)
----------
10
如果没有提交，使用回滚 rollback;
SQL> rollback;
Rollback complete.
SQL> select count(*) from test;
COUNT(*)
----------
14
SQL> delete from test where rownum <=4;
4 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from test;
COUNT(*)
----------
10
事务提交了，如果有足够的undo，则通过闪回来回滚数据。
SQL> select count(*) from test as of timestamp(sysdate -1/1440);
COUNT(*)
----------
14
SQL> insert into test select * from test as of timestamp(sysdate -1/1440) minus select * from test;
4 rows created.
或者
SQL> insert into test
select * from test as of timestamp (sysdate -1/1440) #过去1分钟
where empno not in (select empno from test);
由于用户的误操作需要做不完全的恢复时，由于无法确定这个操作是哪个时间做的，所以这对你的恢复 就带来的很大的难度，丢失的数据也不能完全恢复回来。而LogMiner就可以帮你确定这个误操作的准确时间。
SQL> commit;
Commit complete.
SQL> select count(*) from test;
COUNT(*)
----------
14
数据已找回。
日志挖掘
日志挖掘的流程：
0. LogMiner配置
1. 生成数据字典文件
2. 添加用于分析的日志文件
3. 开分析（挖掘）
4. 查询结果
5. 结束挖掘
#开启补全日志
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
SQL> alter database add supplemental log data;
Database altered.
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
#设置logminer分析的数据文件存放位置，如放在/tmp目录
SQL> alter system set utl_file_dir = '/tmp' scope=spfile;
System altered.
设置后重启数据库
SQL> startup force;
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string tmp
#数据库为归档模式
检查：
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
修改为归档：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
修改归档文件路径
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/arch1';
使用DBMS_LOGMNR_D.BUILD来生成数据字典，如：
SQL> begin
2 dbms_logmnr_d.build(
3 dictionary_filename => 'dictionary.ora',
4 dictionary_location => '/tmp');
5 end;
6 /
PL/SQL procedure successfully completed.
#dictionary_location指的是Logminer数据字典文件存放的位置,它必须匹配utl_file_dir的设定。其中的dictionary_filename指的是放于存放位置的字典文件的名字,名字可以任意取。
检查：
SQL> ! ls -lh tmp
total 36M
-rw-r--r-- 1 oracle asmadmin 36M Aug 10 12:16 dictionary.ora
#设置使用的表空间
SQL> execute dbms_logmnr_d.set_tablespace('TEST');
PL/SQL procedure successfully completed.
#查看当前日志
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------------- ------------- ------------ ------------ ------------
1 1 10 52428800 512 2 NO
CURRENT 1097852 10-AUG-17 2.8147E+14
2 1 8 52428800 512 2 YES
INACTIVE 1097144 10-AUG-17 1097775 10-AUG-17
3 1 9 52428800 512 2 YES
INACTIVE 1097775 10-AUG-17 1097852 10-AUG-17
#当前日志序列为10,切换日志组，让在线日志写入归档：
SQL> alter system switch logfile;
System altered.
#查看归档日志文件，可知归档日志写入编号为10的文件：
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/arch1/ORCL/archivelog/2017_08_10/o1_mf_1_8_drqtf20o_.arc
/u01/app/oracle/arch1/ORCL/archivelog/2017_08_10/o1_mf_1_9_drqtmght_.arc
/u01/app/oracle/arch1/ORCL/archivelog/2017_08_10/o1_mf_1_10_drqtydtg_.arc
#添加归档日志文件
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/arch1/ORCL/archivelog/2 017_08_10/o1_mf_1_10_drqtydtg_.arc',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
Tips：可继续添加日志文件用dbms_logmnr.addfile,或者删除日志文 件用dbms_logmnr.removefile，分别为options的三个选项。
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp,sql_redo,sql_undo from v$logmnr_contents where seg_name='TEST';
SCN TIMESTAMP SQL_REDO SQL_UNDO
---------- -------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1098292 2017-08-10 13:13:26 insert into "SCOTT"."TEST"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('76 delete from "SCOTT"."TEST" where "EMPNO" = '7654' and "ENAME" = 'MARTIN' and "JOB" = 'SALESMAN' and
54','MARTIN','SALESMAN','7698',TO_DATE('28-SEP-81', 'DD-MON-RR'),'1250','1400','30'); "MGR" = '7698' and "HIREDATE" = TO_DATE('28-SEP-81', 'DD-MON-RR') and "SAL" = '1250' and "COMM" = '1
400' and "DEPTNO" = '30' and ROWID = 'AAASNOAAEAAAAIPAAA';
1098292 2017-08-10 13:13:26 insert into "SCOTT"."TEST"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('76 delete from "SCOTT"."TEST" where "EMPNO" = '7698' and "ENAME" = 'BLAKE' and "JOB" = 'MANAGER' and "M
98','BLAKE','MANAGER','7839',TO_DATE('01-MAY-81', 'DD-MON-RR'),'2850',NULL,'30'); GR" = '7839' and "HIREDATE" = TO_DATE('01-MAY-81', 'DD-MON-RR') and "SAL" = '2850' and "COMM" IS NUL
L and "DEPTNO" = '30' and ROWID = 'AAASNOAAEAAAAIPAAB';
1098292 2017-08-10 13:13:26 insert into "SCOTT"."TEST"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('77 delete from "SCOTT"."TEST" where "EMPNO" = '7782' and "ENAME" = 'CLARK' and "JOB" = 'MANAGER' and "M
82','CLARK','MANAGER','7839',TO_DATE('09-JUN-81', 'DD-MON-RR'),'2450',NULL,'10'); GR" = '7839' and "HIREDATE" = TO_DATE('09-JUN-81', 'DD-MON-RR') and "SAL" = '2450' and "COMM" IS NUL
L and "DEPTNO" = '10' and ROWID = 'AAASNOAAEAAAAIPAAC';
SCN TIMESTAMP SQL_REDO SQL_UNDO
---------- -------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1098292 2017-08-10 13:13:26 insert into "SCOTT"."TEST"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('77 delete from "SCOTT"."TEST" where "EMPNO" = '7788' and "ENAME" = 'SCOTT' and "JOB" = 'ANALYST' and "M
88','SCOTT','ANALYST','7566',TO_DATE('19-APR-87', 'DD-MON-RR'),'3000',NULL,'20'); GR" = '7566' and "HIREDATE" = TO_DATE('19-APR-87', 'DD-MON-RR') and "SAL" = '3000' and "COMM" IS NUL
L and "DEPTNO" = '20' and ROWID = 'AAASNOAAEAAAAIPAAD';
1098300 2017-08-10 13:13:44 delete from "SCOTT"."TEST" where "EMPNO" = '7839' and "ENAME" = 'KING' and "JOB" = 'PRESIDENT' and " insert into "SCOTT"."TEST"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('78
MGR" IS NULL and "HIREDATE" = TO_DATE('17-NOV-81', 'DD-MON-RR') and "SAL" = '5000' and "COMM" IS NUL 39','KING','PRESIDENT',NULL,TO_DATE('17-NOV-81', 'DD-MON-RR'),'5000',NULL,'10');
L and "DEPTNO" = '10' and ROWID = 'AAASNOAAEAAAAILAAI';
1098300 2017-08-10 13:13:44 delete from "SCOTT"."TEST" where "EMPNO" = '7844' and "ENAME" = 'TURNER' and "JOB" = 'SALESMAN' and insert into "SCOTT"."TEST"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('78
"MGR" = '7698' and "HIREDATE" = TO_DATE('08-SEP-81', 'DD-MON-RR') and "SAL" = '1500' and "COMM" = '0 44','TURNER','SALESMAN','7698',TO_DATE('08-SEP-81', 'DD-MON-RR'),'1500','0','30');
SCN TIMESTAMP SQL_REDO SQL_UNDO
---------- -------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
' and "DEPTNO" = '30' and ROWID = 'AAASNOAAEAAAAILAAJ';
1098300 2017-08-10 13:13:44 delete from "SCOTT"."TEST" where "EMPNO" = '7876' and "ENAME" = 'ADAMS' and "JOB" = 'CLERK' and "MGR insert into "SCOTT"."TEST"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('78
" = '7788' and "HIREDATE" = TO_DATE('23-MAY-87', 'DD-MON-RR') and "SAL" = '1100' and "COMM" IS NULL 76','ADAMS','CLERK','7788',TO_DATE('23-MAY-87', 'DD-MON-RR'),'1100',NULL,'20');
and "DEPTNO" = '20' and ROWID = 'AAASNOAAEAAAAILAAK';
1098300 2017-08-10 13:13:44 delete from "SCOTT"."TEST" where "EMPNO" = '7900' and "ENAME" = 'JAMES' and "JOB" = 'CLERK' and "MGR insert into "SCOTT"."TEST"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('79
" = '7698' and "HIREDATE" = TO_DATE('03-DEC-81', 'DD-MON-RR') and "SAL" = '950' and "COMM" IS NULL a 00','JAMES','CLERK','7698',TO_DATE('03-DEC-81', 'DD-MON-RR'),'950',NULL,'30');
nd "DEPTNO" = '30' and ROWID = 'AAASNOAAEAAAAILAAL';
SQL> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
Tips: v$logmnr_contents内容保存了日志的内容，只在当前会话有效，如果想长期保存分析，可以在当前会话用create table tablename as select * from v$logmnr_contents语句来持久保存。
参考文档：
Utilities --> Using LogMiner to Analyze Redo Log Files
