Use logminer to analyze oracle logs

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. Oracledb provides an analysis log package logmnrlogminer

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 logminer

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.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 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.