日志挖掘针对DML语句

ゝ一世哀愁。 2021-12-24 03:39 286阅读 0赞

作用：

针对用户的误操作，比如更改数据错误，误删除表等，可以用日志挖掘的方式，跟踪哪个用户什么时候做的操作，并进行数据还原。

***一。前期准备：***

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.

转载于:https://www.cnblogs.com/gw666/p/6811347.html