oracle 日志挖掘（Oracle日志挖掘）.docVIP

oracle 日志挖掘（Oracle日志挖掘） Log mining Today, I reviewed the knowledge of log mining and sorted out as follows: Log mining can locate the DDL, DML, SCN, accurate time, you can find out who is the implementation of the DML, you can mistake the DML impact of the repair back, you can according to SCN DDL back to the library before the operation of the state. Log mining can be divided into DDL log mining and DML log mining, and DDL can be dug directly, but mining DML needs to set the database into additional log data schema. Here are two steps to dig: DDL log mining: 1. check the archive file, the redolog directory, and the current group Select, sequence#, name, first_time, next_time, from, v$archived_log, where, not, name, is,... Null order by next_time DESC; - see the path to the archive file Select * from v$logfile - - check the path of the log file Select * from v$log -- see the current log group and status 2. add redolog or archivelog, add the corresponding time redolog or archivelog according to the DDL execution time Exec dbms_logmnr.add_logfile (/u01/oracle/oradata/orcl/redo/redo04.log) Exec dbms_logmnr.add_logfile (/u01/oracle/oradata/orcl/redo/redo01.log) Add too many to delete Exec Dbms_logmnr.remove_logfile (/u01/oracle/oradata/orcl/redo/redo01.log) 3. start log mining Exec dbms_logmnr.start_logmnr 4. view mining results Select, SCN, timestamp, sql_redo, sql_undo, username, os_username, machine_name, from V$logmnr_contents where seg_owner=SCOTT; The results can only be found in SCN, timestamp, sql_redo, and other, such as sql_undo, the result is empty, if you want to know who executed this DDL, unfortunately Username, os_username, and machine_name are both UNKNOWN, and it seems that if you want to know who executed the DDL, you can only use the audit. 5. end mining: Exec dbms_logmnr.end_logmnr ------------------------------------------------------------------------------------------------------ DML log mining: 1. checks whether the library is currently an additi