logmnr简易操作说明

......

1. run two script, under the user “sys”:
SQL>$oracle_home/rdbms/admin/dbmslm.sql;
SQL>$oracle_home/rdbms/admin/dbmslmd.sql;

2. shut down the DB.

3. modify initSID.ora file.
We should add parameter UTL_FILE_DIR to inidSID.ora. Sample:
Utl_file_dir=d:oracle

4. Startup DB.

5. Build dictionary file.
execute dbms_logmnr_d.build(dictionary_filename=>'filename.ora',dictionary_location=>'d:oracle');

6. Build log file list
--Analyze online redo log file:

execute dbms_logmnr.add_logfile(logfilename=>'e:oracleoradatajlandzparedo01.log',options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>'e:oracleoradatajlandzparedo02.log',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'e:oracleoradatajlandzparedo03.log',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'e:oracleoradatajlandzparedo04.log',options=>dbms_logmnr.addfile);

--Analyze the offline redo log file:
1. create list
SQL> execute dbms_logmnr.add_logfile(
logfilename=>'d:oracleoradatasailorarchiveSAILORT001S00215.ARC',
options=>dbms_logmnr.new);
2.add the other log file
SQL> execute dbms_logmnr.add_logfile(
logfilename=>'d:oracleoradatasailorarchiveSAILORT001S00216.ARC',
options=>dbms_logmnr.addfile);

--Remove the log file
SQL> execute dbms_logmnr.add_logfile(logfilename=>'d:oracleoradatasailorredo03.log',options=>dbms_logmnr.removefile);

★Analyze with log miner
--no restrict

alter session set NLS_LANGUAGE=american;
execute dbms_logmnr.start_logmnr(dictfilename=>'d:oraclefilename.ora');

--restrict by time
execute dbms_logmnr.start_logmnr(dictfilename=>'d:oraclefilename.ora',starttime=>to_date('2005-08-31:21:30:00','yyyy-mm-dd:hh24:mi:ss'),endtime=>to_date('2005-08-31:21:45:00','yyyy-mm-dd:hh24:mi:ss'));

--restrict by scn
execute dbms_logmnr.start_logmnr(dictfilename=>'d:oraclefilename.ora',startscn=>20,
endscn=50);

★Read the analyzed result

select sql_redo,sql_undo from v$logmnr_contents;

★End the analyze
SQL>execute dbms_logmnr.end_logmnr

For example, suppose you wanted to find out about any delete operations that
a user named Ron had performed on the scott.orders table.
You could issue a query similar to the following:

select username,timestamp,session_info,row_id,sql_undo,sql_redo
from logmnr_contents
where SEG_OWNER = 'TEST' AND SEG_NAME = 'JIANGLEI' AND OPERATION = 'UPDATE';

create table logmnr_contents as select * from v$logmnr_contents;

------------------------------------------------------------------------------
select username,timestamp,session_info,row_id,sql_undo
from logmnr_contents
where SEG_OWNER = 'TEST' AND SEG_NAME = 'JIANGLEI';
------------------------------------------------------------------------------

select sql_undo
from v$logmnr_contents
where timestamp between to_date('2005-08-31:23:15:00','yyyy-mm-dd:hh24:mi:ss')
and to_date('2005-08-31:23:16:00','yyyy-mm-dd:hh24:mi:ss');

select username,to_char(DBMS_LOGMNR.MINE_VALUE(REDO_VALUE,'TEST.JIANGLEI.BBB'),'DD-MON-YYYY HH24:MI:SS') from v$logmnr_contents
where timestamp between to_date('2005-08-31:23:15:00','yyyy-mm-dd:hh24:mi:ss')
and to_date('2005-08-31:23:16:00','yyyy-mm-dd:hh24:mi:ss');

DBMS_LOGMNR.MINE_VALUE(REDO_VALUE,'TEST.JIANGLEI.BBB')

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60568/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/936/viewspace-60568/