How to Use log miner to analyze oracle logs, mineroracle

How to Use log miner to analyze oracle logs, mineroracle

When we accidentally mistakenly cause the database data to be lost or changed, we need to restore the database objects based on time points to find the data we need. This time point cannot be considered accurate, we can analyze oracle logs to obtain a precise time point without operations.
Oracle db provides an analysis log package logmnr
Use of logminer
------- Perform redo log mining to find out the DDL or DML operations performed at a certain time point (including time points, datablock scn, and SQL statements)
Lab Test

SQL> select name from v$archived_log; NAME--------------------------------------------------/oracle/arch1/1_2_883536782.dbf SQL>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 to add the log to be analyzed

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oracle/arch1/1_2_883536782.dbf',options=>dbms_logmnr.new);PL/SQL procedure successfully completed.

-- Add logs to be analyzed

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oracle/arch1/1_3_883536782.dbf',options=>dbms_logmnr.addfile); PL/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); PL/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', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '2450' and "COMM" IS NULL and "DEPTNO" = '10' 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" = '5000' and "COMM" IS NULL and "DEPTNO" = '10' 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.

The above is all the content of this article, and I hope you will like it.