日志挖掘针对DML语句
作用：
针对用户的误操作，比如更改数据错误，误删除表等，可以用日志挖掘的方式，跟踪哪个用户什么时候做的操作，并进行数据还原。
一。前期准备：
1.添加最小补充日志，能够记录到更详细的信息，为日志挖掘分析做准备：
SQL> alter database add supplemental log data;
Database altered.
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
2.查看归档模式：
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/FRA
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
3.开启归档模式，并设置归档日志的位置:
设置归档日志的位置：
[oracle@edbjr2p1 ~]$ mkdir arch
[oracle@edbjr2p1 ~]$ pwd
/home/oracle
[oracle@edbjr2p1 ~]$ cd arch
[oracle@edbjr2p1 arch]$ pwd
/home/oracle/arch
4.数据库更改参数：SQL> show parameter archive;
SQL> alter system set log_archive_dest_1='location=/home/oracle/arch';
System altered.
SQL> show parameter format;
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
System altered.
SQL> startup force
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.arc
SQL> alter system switch logfile;
System altered.
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/home/oracle/arch/1_10_860888149.dbf
/home/oracle/arch/1_11_860888149.arc
/home/oracle/arch/1_12_860888149.arc
二。模拟用户scott的dml业务操作
5.解锁并更改scott用户密码：
SQL> alter user scott account unlock identified by tiger;
User altered.
查看scott用户7369号员工的工资
SCOTT@PROD1>select empno,sal from emp where empno=7369;
EMPNO SAL
---------- ----------
7369 800
误修改数据将7369号员工的工资改成了8000：
SCOTT@PROD1>update emp set sal=8000 where empno=7369;
1 row updated.
SCOTT@PROD1>commit;
Commit complete.
SCOTT@PROD1>select empno,sal from emp where empno=7369;
EMPNO SAL
---------- ----------
7369 8000
查看当前归档日志为第1组：
SCOTT@PROD1>conn / as sysdba
Connected.
SYS@PROD1>select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ------------
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE
将当前的归档日志组归档：
SYS@PROD1> alter system archive log current;
System altered.
（alter system switch logfile;)
再查看1组日志归档，当前归档日志组切换为了2组：
SYS@PROD1>select group#,members,status,archived from v$log;
GROUP# MEMBERS STATUS ARC
---------- ---------------- ------------ --------
1 1 ACTIVE YES
2 1 CURRENT NO
3 1 INACTIVE YES
查看当前组日志对应的日志文件：
SYS@PROD1>col member for a50;
SYS@PROD1>select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/PROD1/redo03.log
2 /u01/app/oracle/oradata/PROD1/redo02.log
1 /u01/app/oracle/oradata/PROD1/redo01.log
查看当前归档日志信息：
SYS@PROD1>col name for a50
SYS@PROD1>select name from v$archived_log;
NAME
--------------------------------------------------
。。。。。。。
/home/oracle/arc/arc_7c1c5413_0001_0860888149_0000
000016.log
三。开始分析步骤
添加要分析的日志文件和归档日志：注：此时添加的日志文件是归档之前的日志文件，归档日志文件是最新产生的归档日志文件
SYS@PROD1>execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/PROD1/redo01.log',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
SYS@PROD1>execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/arc/arc_7c1c5413_0001_0860888149_0000000022.log',options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
开始挖掘：
开始挖掘：
SYS@PROD1>execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
查询挖掘的结果：
SYS@PROD1>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SYS@PROD1>col username for a15
SYS@PROD1>col sql_redo for a50
SYS@PROD1>select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='EMP';
USERNAME SCN TIMESTAMP SQL_REDO
--------------- ---------- ------------------- --------------------------------------------------
SCOTT 1007829 2017-04-19 18:11:07 update "SCOTT"."EMP" set "SAL" = '8000' where "SAL" = '800' and ROWID = 'AAASYzAAEAAAACXAAA';
四。结束日志挖掘：
SYS@PROD1> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
