Shulou(Shulou.com)05/31 Report--

This article will explain in detail how Logmnr extracts log data from oracle. Xiaobian thinks it is quite practical, so share it with you for reference. I hope you can gain something after reading this article.

Logmnr, dba need to audit or query the cause of a misoperation afterwards, or even retrieve the data of the misoperation. At this time, flashback may not be able to help you, because time may have passed for a long time, and it may be a DDL operation, and you want to know which user sent it. Then, as long as there is an archive log for this time, you can use Logmnr to analyze the archive log.

1. Using logmnr, you can do the following

(1) Find out the change records of the database, or conduct change analysis. If some users suspect that their application has problems and has produced a large number of mistakes, they can use logmnr to analyze these transactions and see what happened.

(2) Detect and correct user misoperations, such as some users accidentally deleted a table, but do not admit it, this time you can use logmnr to analyze who performed DML or DDL operations.

(3), retrieve lost data, when flashback can not be used or the use of flashback is limited, we can consider using logmnr to retrieve data, at this time, as long as there is an archive log.

Note: The metadata analyzed by logminer, such as update table set a=a+10, actually affects 1000 pieces of data, so how can logmnr return 1000 undo statements?

2, logmnr contains content and data dictionary

logminer contains two PL/SQL packages and several views

dbms_logmnr_d package: used to extract dictionary information to external flat files or online logs.

dbms_logmnr package: contains three procedures.

(1) add_logfile: Used to add and delete log files for analysis

(2) start_logmnr: Used to enable log analysis, and can enable a lot of different analysis options, such as only analyzing commit information committed_data_only, etc.

(3) end_logmnr: Used to enable log analysis, and can enable a lot of different analysis options, such as only analyzing commit information committed_data_only, etc.

If ordinary users want to use the above packages, they must first authorize them in sys, such as:

grant execute on dbms_logmnr to piner;

When using it, it is best to use the prefix sys. For example sys.dbms_logmnr.

The dictionary associated with logminer is as follows:

v$logmnr_dirctionary: information about the data dictionary logminer may use. Since logmnr can have multiple dictionary files, this view is used to display this information.

v$logmnr_parameters: parameter information set by the current logminer.

v$logmnr_logs: List of logs currently used for analysis.

v$logmnr_contents: log analysis results.

3. Logmnr analysis process

(1) Switch logs and establish tables, and perform insert and delete data operations.

alter system archive log current;

create table test(a int);

insert into test values(1);

insert into test values(2);

insert into test values(3);

delete from test;

commit;

alter system archive log current;

(2) Then find the archive log containing these operations between the two previous switches.

The archive log of the switch is as follows: /home/oracle/product/flash_recovery_area/ORCL/archivelog/2012_09_22/o1_mf_1_54_8

5tondqb_.arc

Then analyze the process online.

1) Add logs and analyze logs

exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/product/flash_recovery_area/ORCL/archivelog/2012_09_22/o1_mf_1_54_85tondqb_.arc',options=>sys.dbms_logmnr.new);

exec sys.dbms_logmnr.start_logmnr(options=> sys.dbms_logmnr.dict_from_online_catalog);

Generally, if there are other logs, you can also add them as follows:

exec sys.dbms_logmnr.add_logfile(logfilename='/home/oracle/product/flash_recovery_area/ORCL/archivelog/2012_09_22/o1_mf_1_54_85tondqb_.arc');

(3) You can now view the analysis results.

select t.scn,t.timestamp,t.seg_owner,t.operation,t.sql_undo,t.sql_undo from v$logmnr_contents t where t.seg_name='TEST';

select t.scn,t.timestamp,t.seg_owner,t.operation,t.sql_undo from v$logmnr_contents t where t.seg_name='TEST';

SCN TIMESTAMP SEG_OWNER OPERATION SQL_UNDO

-------- ----------- ---------- ---------- --------------------------------------------------------------------------------

1367841 2012/9/22 1 CAIWENCAN DDL

1367848 2012/9/22 1 CAIWENCAN INSERT delete from "CAIWENCAN". "TEST" where "A" = '1' and ROWID = 'AAAM4wAAEAAAAG+AAA';

1367848 2012/9/22 1 CAIWENCAN INSERT delete from "CAIWENCAN". "TEST" where "A" = '2' and ROWID = 'AAAM4wAAEAAAAG+AAB';

1367848 2012/9/22 1 CAIWENCAN INSERT delete from "CAIWENCAN". "TEST" where "A" = '3' and ROWID = 'AAAM4wAAEAAAAG+AAC';

1367859 2012/9/22 1 CAIWENCAN DELETE insert into "CAIWENCAN". "TEST"("A") values ('1');

1367859 2012/9/22 1 CAIWENCAN DELETE insert into "CAIWENCAN". "TEST"("A") values ('2');

1367859 2012/9/22 1 CAIWENCAN DELETE insert into "CAIWENCAN". "TEST"("A") values ('3');

Where SEG_OWNER is the operator, operation is the operation mode, and sql_undo is the operation statement.

The v$logmnr_contents view information here is session level. If you need to keep the information, you need to create temporary storage.

create table logmnr_contents as select * from v$logmnr_contents;

(4) After the analysis is completed, the session memory information can be cleared.

exec sys.dbms_logmnr.end_logmnr;

(5) If you want to recover, you can write pl/sql for rollback recovery.

declare

mysql varchar2(4000);

num number :=0;

begin

from c_tmp in (select sql_undo from logmnr_contents where operation='DELETE') loop

mysql:=replace(c_tmp.sql,':','');

execute immediate mysql;

num:=num+1;

if mod(num,1000)=0 then

commit;

end if;

end loop;

commit;

exception

when others then

.... exception handling

end;

(6) If you want to change a specific table space for logmnr,

Here is the change space for users space.

exec sys.dbms_logmnr_d.set_tablespace('users');

v$logmnr_dirctionary: information about the data dictionary logminer may use. Since logmnr can have multiple dictionary files, this view is used to display this information.

v$logmnr_parameters: parameter information set by the current logminer.

v$logmnr_logs: List of logs currently used for analysis.

v$logmnr_contents: log analysis results.

About "Oracle Logmnr how to extract log data" This article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.

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.