Shulou(Shulou.com)06/01 Report--

Log mining: logminer

Function:

Through the analysis of the log, the user's dml operation statement can be obtained, which can be used to repair the loss or error of user data.

Through log mining, the reverse operation (undo_sql) or forward operation (redo_sql) of user action can be obtained.

Implementation conditions:

Secondary log function (SUPPLEMENTAL_LOG_DATA_MIN) is required to perform log mining.

SYS@orcl11g > select supplemental_log_data_min from vault database;-- check the enabled status of the secondary log

SUPPLEME

-

YES

SYS@orcl11g > alter database add supplemental log data;-- enable additional logs

The main purpose of starting the minimum supplementary log is to make logminer have the ability to recognize line migration and row movement caused by the update command.

* set * scene * *

Sample demonstration:

1. Create a table

SCOTT@orcl11g > drop table e_logminer purge

SCOTT@orcl11g > create table e_logminer as select * from emp

two。 The table was updated with the wrong update condition

SCOTT@orcl11g > update e_logminer set sal=12345

SCOTT@orcl11g > commit

3. Switch logs and simulate this operation for a long time.

SCOTT@orcl11g > conn / as sysdba

SYS@orcl11g > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / u01/app/oracle/arch3

Oldest online log sequence 8

Next log sequence to archive 10

Current log sequence 10

SYS@orcl11g > alter system switch logfile

…… Switch n times

* * scene setup completed * *

Start log mining:

1. Find all the required logs (archived logs and online redo logs) based on the approximate time of user error

The time to judge whether the approximate data is correct is 2015-04-24 13:00:00

SYS@orcl11g > select name from v$archived_log where first_time > =

(select max (first_time) from v$archived_log

Where first_time =

(select max (first_time) from v$archived_log where first_time set trim on

SYS@orcl11g > set trims on

SYS@orcl11g > set term off

SYS@orcl11g > set heading off

SYS@orcl11g > set feedback off

SYS@orcl11g > set echo off

SYS@orcl11g > set linesize 200

SYS@orcl11g > set pagesize 0

SYS@orcl11g > spool / home/oracle/logmnr.sql

SYS@orcl11g > @ log.sql

SYS@orcl11g > spool off

SYS@orcl11g > quit

-- modify logmnr.sql

[oracle@db253 ~] $cat logmnr.sql

Exec dbms_logmnr.add_logfile ('/ u01Accord oracleArch3 Arch3 *

Exec dbms_logmnr.add_logfile ('/ u01Accord oracleArch3 Arch3, 11 characters 8192 18658. Dbfhammer Magazine dbmswatches logmnr.addfile)

Exec dbms_logmnr.add_logfile ('/ u01 Accord oracleArch3 Arch3 Accord 1'12 '8192 1865 8. Dbfaud dbmswatches logmnr.addfile)

Exec dbms_logmnr.add_logfile ('/ u01Accord oracleArch3 Arch3 Action113081918658.dbfaure.dbmsencoding logmnr.addfile)

Exec dbms_logmnr.add_logfile ('/ u01Accord oracleArch3 Arch3 Action114081918658.dbfaure.dbmsencoding logmnr.addfile)

Exec dbms_logmnr.add_logfile ('/ u01Accord oracleArch3 Arch3 Accord 119,819,18658.dbfaure.dbmsencoding logmnr.addfile)

Exec dbms_logmnr.add_logfile ('/ u01Accord oracleArch3 Arch3 Accord 116,00081918658.dbfaure.dbmsencoding logmnr.addfile)

Exec dbms_logmnr.add_logfile ('/ u01 Accord oracleArch3 Arch3 Accord 1711981918658.dbfaure.dbmsencoding logmnr.addfile)

Exec dbms_logmnr.add_logfile ('/ u01Actur oradataAccord oradataPlacement orcl11g Uniqpare redo03.logarithm.addfile)

Add log files to a new or existing log list for processing by the log miner

3. Execute mining queue script

SYS@orcl11g > @ logmnr.sql-- do not exit this session

Manually register minable redo logs for logminer mining sessions

4. Start digging.

SYS@orcl11g > exec dbms_logmnr.start_logmnr (options= > dbms_logmnr.dict_from_online_catalog)

Start mining by loading data dictionaries, which are conversion dictionaries used by the miner to translate oracle internal objects in redo records into readable information

5. Get mining results

SYS@orcl11g > set trim on

SYS@orcl11g > set trims on

SYS@orcl11g > set term off

SYS@orcl11g > set heading off

SYS@orcl11g > set feedback off

SYS@orcl11g > set echo off

SYS@orcl11g > set linesize 500

SYS@orcl11g > set pagesize 0

SYS@orcl11g > spool / home/oracle/undo_logmnr.sql

SYS@orcl11g > select sql_undo from v$logmnr_contents

Where table_name='E_LOGMINER'

And lower (sql_redo) like 'update345%'

SYS@orcl11g > spool off

SYS@orcl11g > quit

6. Modify undo script

[oracle@db253 ~] $cat undo_logmnr.sql

Update "SCOTT". "E_LOGMINER" set "SAL" = '800' where "EMPNO" =' 7369' and "ENAME" = 'SMITH' and "JOB" =' CLERK' and "MGR" = '7902' and "HIREDATE" = TO_DATE ('1980-12-17 00 CLERK' and,' yyyy-mm-dd hh34:mi:ss') and "SAL" = '12345' and "COMM" IS NULL and "DEPTNO" =' 20' and ROWID = 'AAATQQAAEAAAAIjAAA'

Update "SCOTT". "E_LOGMINER" set "SAL" = '1600' where "EMPNO" =' 7499' and "ENAME" = 'ALLEN' and "JOB" =' SALESMAN' and "MGR" = '7698' and "HIREDATE" = TO_DATE (' 1981-02-2000 SALESMAN' and 00V, 'yyyy-mm-dd hh34:mi:ss') and "SAL" =' 12345' and "COMM" = '300' and "DEPTNO" =' 30' and ROWID = 'AAATQQAAEAAAAIjAAB'

Update "SCOTT". "E_LOGMINER" set "SAL" = '1250' where "EMPNO" =' 7521' and "ENAME" = 'WARD' and "JOB" =' SALESMAN' and "MGR" = '7698' and "HIREDATE" = TO_DATE (' 1981-02-22 00 SALESMAN' and 00V, 'yyyy-mm-dd hh34:mi:ss') and "SAL" =' 12345' and "COMM" = '500' and "DEPTNO" =' 30' and ROWID = 'AAATQQAAEAAAAIjAAC'

Update "SCOTT". "E_LOGMINER" set "SAL" = '2975' where "EMPNO" =' 7566' and "ENAME" = 'JONES' and "JOB" =' MANAGER' and "MGR" = '7839' and "HIREDATE" = TO_DATE (' 1981-04-02 00VV 00V, 'yyyy-mm-dd hh34:mi:ss') and "SAL" =' 12345' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID =' AAATQQAAEAAAAIjAAD'

Update "SCOTT". "E_LOGMINER" set "SAL" = '1250' where "EMPNO" =' 7654' and "ENAME" = 'MARTIN' and "JOB" =' SALESMAN' and "MGR" = '7698' and "HIREDATE" = TO_DATE (' 1981-09-28 00lace 00V, 'yyyy-mm-dd hh34:mi:ss') and "SAL" =' 12345' and "COMM" = '1400' and "DEPTNO" =' 30' and ROWID = 'AAATQQAAEAAAAIjAAE'

Update "SCOTT". "E_LOGMINER" set "SAL" = '2850' where "EMPNO" =' 7698' and "ENAME" = 'BLAKE' and "JOB" =' MANAGER' and "MGR" = '7839' and "HIREDATE" = TO_DATE (' 1981-05-01 00 MANAGER' and, 'yyyy-mm-dd hh34:mi:ss') and "SAL" =' 12345' and "COMM" IS NULL and "DEPTNO" = '30' and ROWID =' AAATQQAAEAAAAIjAAF'

Update "SCOTT". "E_LOGMINER" set "SAL" = '2450' where "EMPNO" =' 7782' and "ENAME" = 'CLARK' and "JOB" =' MANAGER' and "MGR" = '7839' and "HIREDATE" = TO_DATE (' 1981-06-09 00VF 00V, 'yyyy-mm-dd hh34:mi:ss') and "SAL" =' 12345' and "COMM" IS NULL and "DEPTNO" = '10' and ROWID =' AAATQQAAEAAAAIjAAG'

Update "SCOTT". "E_LOGMINER" set "SAL" = '3000' where "EMPNO" =' 7788' and "ENAME" = 'SCOTT' and "JOB" =' ANALYST' and "MGR" = '7566' and "HIREDATE" = TO_DATE (' 1987-04-1900 JOB, 'yyyy-mm-dd hh34:mi:ss') and "SAL" =' 12345' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID =' AAATQQAAEAAAAIjAAH'

Update "SCOTT". "E_LOGMINER" set "SAL" = '5000' where "EMPNO" =' 7839' and "ENAME" = 'KING' and "JOB" =' PRESIDENT' and "MGR" IS NULL and "HIREDATE" = TO_DATE ('1981-11-17 0000 JOB,' yyyy-mm-dd hh34:mi:ss') and "SAL" = '12345' and "COMM" IS NULL and "DEPTNO" =' 10' and ROWID = 'AAATQQAAEAAAAIjAAI'

Update "SCOTT". "E_LOGMINER" set "SAL" = '1500' where "EMPNO" =' 7844' and "ENAME" = 'TURNER' and "JOB" =' SALESMAN' and "MGR" = '7698' and "HIREDATE" = TO_DATE (' 1981-09-08 00VV 00V, 'yyyy-mm-dd hh34:mi:ss') and "SAL" =' 12345' and "COMM" ='0' and "DEPTNO" = '30' and ROWID =' AAATQQAAEAAAAIjAAJ'

Update "SCOTT". "E_LOGMINER" set "SAL" = '1100' where "EMPNO" =' 7876' and "ENAME" = 'ADAMS' and "JOB" =' CLERK' and "MGR" = '7788' and "HIREDATE" = TO_DATE (' 1987-05-23 00VV 00V, 'yyyy-mm-dd hh34:mi:ss') and "SAL" =' 12345' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID =' AAATQQAAEAAAAIjAAK'

Update "SCOTT". "E_LOGMINER" set "SAL" = '950' where "EMPNO" =' 7900' and "ENAME" = 'JAMES' and "JOB" =' CLERK' and "MGR" = '7698' and "HIREDATE" = TO_DATE (' 12345' and "COMM" IS NULL and "DEPTNO" = '30' and ROWID =' AAATQQAAEAAAAIjAAL'')

Update "SCOTT". "E_LOGMINER" set "SAL" = '3000' where "EMPNO" =' 7902' and "ENAME" = 'FORD' and "JOB" =' ANALYST' and "MGR" = '7566' and "HIREDATE" = TO_DATE (' 1981-12-03 00Vista, 'yyyy-mm-dd hh34:mi:ss') and "SAL" =' 12345' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID =' AAATQQAAEAAAAIjAAM'

Update "SCOTT". "E_LOGMINER" set "SAL" = '1300' where "EMPNO" =' 7934' and "ENAME" = 'MILLER' and "JOB" =' CLERK' and "MGR" = '7782' and "HIREDATE" = TO_DATE (' 1982-01-23 00 JOB, 'yyyy-mm-dd hh34:mi:ss') and "SAL" =' 12345' and "COMM" IS NULL and "DEPTNO" = '10' and ROWID =' AAATQQAAEAAAAIjAAN'

7. Execute undo script

[oracle@db253 ~] $sqlplus scott/tiger @ undo_logmnr.sql

8. End digging

SYS@orcl11g > exec dbms_logmnr.end_logmnr

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.