Analyzing Oracle logs using log miner

When we accidentally operate the database data loss, change, we need to do a point-in-time recovery of database objects, find the data we need, this point of time can not be considered accurate, we can be analyzed by the Oracle log, and get no operational precise point in time.

Oracle DB provides an analysis log package LOGMNR

Use of Logminer Tools
-------Mining Redo log to find DDL or DML operations at a point in time (including: Point in time, DataBlock SCN, SQL statement)

Experimental test

Sql> select name from V$archived_log; NAME--------------------------------------------------/oracle/arch1/1_2_883536782.dbfsql>sql>sql> Delete from scott.t1;576 rows deleted. Sql> alter system archive log current; System altered. Sql> CREATE TABLE Scott.t6 as SELECT * from Scott.emp; Table created. Sql> alter system archive log current; System altered. Sql> select name from V$archived_log; NAME--------------------------------------------------/oracle/arch1/1_2_883536782.dbf/oracle/arch1/1_3_ 883536782.dbf/oracle/arch1/1_4_883536782.dbf

--Start log miner Add the logs to be analyzed

Sql> Execute dbms_logmnr.add_logfile (logfilename=> '/oracle/arch1/1_2_883536782.dbf ', Options=>dbms_ logmnr.new);P L/sql procedure successfully completed.

--Add a log to analyze

Sql> Execute dbms_logmnr.add_logfile (logfilename=> '/oracle/arch1/1_3_883536782.dbf ', Options=>dbms_ logmnr.addfile);P L/sql procedure successfully completed. Sql> Execute dbms_logmnr.add_logfile (logfilename=> '/oracle/arch1/1_4_883536782.dbf ', Options=>dbms_ logmnr.addfile) PL/SQL procedure successfully completed.

--Execute log Miner

Sql> Execute DBMS_LOGMNR.START_LOGMNR (options=>dbms_logmnr.dict_from_online_catalog);P l/sql procedure Successfully completed.

--Query analysis results

sql> alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss '; Session altered. Sql> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_ Name= ' T1 '; username                               SCN  TIMESTAMP------------------------------ ---------- -------------------Sql_ REDO--------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------delete from  " SCOTT "." T1 " where " EMPNO " =  ' 7782 '  and " ename " =  ' CLARK '  and  ' JOB '  =  ' MANAGER '  and  ' MGR '  =  ' 7839 '  and  ' hiredate '  = to_date (' 1981-06-09 00:00:00 ', &nbsP; ' Yyyy-mm-dd hh24:mi:ss ')  and  "SAL"  =  ' 2450 '  and  "COMM"  is null  and  "DEPTNO"  =  '  and ROWID =  ' Aaavbsaafaaaacxabi '; sys                                1494545 2015-06-28  04:24:44delete from  "SCOTT". " T1 " where " EMPNO " =  ' 7839 '  and " ename " =  ' KING '  and " JOB "  =  ' President '  and  ' MGR '  IS NULL and  ' hiredate '  = to_date (' 1981-11-17 00:00:00 ',  ' yyyy-mm-dd hh24:mi:ss ')  and  "SAL"  =  '  and '   "COMM"  IS NULL and  "DEPTNO"  =  '  and ROWID =  ' AAAVBSAAFAAAACXABJ '; Sys                               1494545  2015-06-28 04:24:44delete from  "SCOTT". " T1 " where " EMPNO " =  ' 7844 '  and " ename " =  ' TURNER '  and " JOB "  =  ' salesman '  and  ' MGR '  =  ' 7698 '  and  ' hiredate '  = to_date (' 1981-09-08 00:00:00 ',  ' yyyy-mm-dd hh24:mi:ss ')  and  "SAL"  =  ' 1 .....

--End log miner analysis

Sql> execute Dbms_logmnr.end_logmnr;pl/sql procedure successfully completed.

This article is from the "DBA Sky" blog, so be sure to keep this source http://kevinora.blog.51cto.com/9406404/1669087

Analyzing Oracle logs using log miner