An example of oracle logmnr log mining

1. execute the delete operation SQL> drop table t; Table dropped. SQL> select * from tab; tname tabtype clusterid ------------------------------- ---------- BIN $ tables = $0 tabletts synonymttss synonym SQL> purge table t; Table purged. www.2cto.com uses logmnr to mine information in logs. 2. view the operation directory SQL> show parameter utl_file_dir NAME TYPE VALUE ------------------------------------------- ---------------------------- utl_file_dir string is not specified, specify a folder to store the dictionary file of logmnr-bash-3.00 $ mkdir/gwrs/logmnr SQL> alter system set utl_file_dir = '/gwrs/logmnr' scope = spfile; System altered. restart database-bash-3.00 $ srvctl stop database-d prod-bash-3.00 $ srvctl start database-d prod 3. create a dictionary file SQL> execute dbms_logmnr_d.build ('dict. ora ','/gwrs/logmnr ', dbms_logmnr_d.store_in_flat
_ File); PL/SQL procedure successfully completed. www.2cto.com 4. Add the relevant logs to the analysis logs (logfilename => '/gwrs/PROD/archivelog/2012_06_01/logs
_. Arc', options => dbms_logmnr.new); dbms_logmnr.add_logfile (logfilename => '/gwrs/PROD/archivelog/2012_06_01/o1_mf_2_31_7wjfngtq
_. Arc', options => dbms_logmnr.addfile); dbms_logmnr.add_logfile (logfilename => '/gwrs/PROD/archivelog/2012_06_01/o1_mf_2_32_7wjfnkbr
_. Arc', options => dbms_logmnr.addfile); dbms_logmnr.add_logfile (logfilename => '/gwrs/PROD/archivelog/2012_06_01/o1_mf_1_36_7wjgg4dh
_. Arc', options => dbms_logmnr.addfile); dbms_logmnr.add_logfile (logfilename => '/gwrs/PROD/archivelog/2012_06_01/o1_mf_1_37_7wjlnoxr
_. Arc', options => dbms_logmnr.addfile); www.2cto.com dbms_logmnr.add_logfile (logfilename => '/gwrs/PROD/archivelog/2012_06_01/logs
_. Arc', options => dbms_logmnr.addfile); dbms_logmnr.add_logfile (logfilename => '/gwrs/PROD/archivelog/2012_06_01/o1_mf_2_33_7wjmwjfj
_. Arc', options => dbms_logmnr.addfile); dbms_logmnr.start_logmnr (dictfilename => '/gwrs/logmnr/dict. ora '); end;/5. analysis operation records note that here the logmnr log takes effect only in this session. If you need to analyze these logs in other sessions, you need to create a table.
Create table logmnr_tmp tablespace users as select * from v $ logmnr_contents; then you can analyze the logs. SQL> col seg_name for a10 SQL> col SQL _redo for a50SQL> col seg_owner for a10 SQL> col table_name for a10 select scn, timestamp, seg_owner, seg_name, table_name, SQL _redo from logmnr_tmp where table_name = 'T'; scn timestamp SEG_OWNER SEG_NAME TABLE_NAME SQL _REDO ---------- --------- ---------- limit 1823865 01-JUN-12 DEX T ALTER TABLE "DEX ". "T" rename to "BIN $ wWGJJd0UCrDgR www.2cto.com AAMKRcWyg = $0"; 1823868 01-JUN-12 dex t drop table t AS "BIN $ items = $0 "; 1823953 01-JUN-12 dex t purge table "BIN $ wWGJJd0UCrDgRAAMKRc
Wyg = $0 "; if username, OS _username, and machine_name are found to be unknow or null, This is a bug when you
This problem may occur when you repeatedly execute the same logmnr using the same parameters (not for the first time). BUG: 10129774
Link address https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=10129774 Patch 10129774 or use the supplemental log in advance. Let's try SQL> alter database add supplemental log data; Database altered. SQL> set time on 15:00:41 SQL> 15:01:16 SQL> select log_mode, supplemental_log_data_min from v $ database; LOG_MODE SUPPLEME ------------ -------- archivelog yes 15:02:18 SQL> create table t (x int primary key ); table created. 15:02:32 SQL> drop table t purge; Table dropped. begindbms_logmnr.add_logfile (logfilename => '/gwrs/PROD/archivelog/2012_06_01/o1_mf_2_36_7wjtbo2y
_. Arc', options => dbms_logmnr.new); dbms_logmnr.add_logfile (logfilename => '/gwrs/PROD/archivelog/2012_06_01/o1_mf_1_46_7wjtbsns
_. Arc', options => dbms_logmnr.addfile); dbms_logmnr.start_logmnr (dictfilename => '/gwrs/logmnr/dict. ora '); end;/create table log_tmp tablespace users as select * from v $ logmnr_contents; 15:15:59 SQL> set lines 150 15:13:15 SQL> col username for a20 15:15:50 SQL> col OS _username for a10 15:15:50 SQL> col machine_name for a10 15:15:50 SQL> col SQL _redo for a50 15:15:51 SQL> select username, OS _usernam E, machine_name, SQL _redo from logmnr_tmp where table_name = 'T'; www.2cto.com USERNAME OS _USERNAM MACHINE_NA SQL _REDO tables ---------- using DEX oracle sol1 create table T (x int primary key ); DEX oracle sol1 drop table t purge; OK. Run the command again to check whether the bug still exists. 15:16:11 SQL> exec dbms_logmnr.end_logmnr (); begindbms_logmnr.add_logfile (logfilename => '/gwrs/PROD/archivelog/2012_06_01/login
_. Arc', options => dbms_logmnr.new); dbms_logmnr.add_logfile (logfilename => '/gwrs/PROD/archivelog/2012_06_01/o1_mf_1_46_7wjtbsns
_. Arc', options => dbms_logmnr.addfile); www.2cto.com dbms_logmnr.start_logmnr (dictfilename => '/gwrs/logmnr/dict. ora '); end;/15:17:53 SQL> select username, OS _username, machine_name, SQL _redo from v $ logmnr_cont
Ents where table_name = 'T'; USERNAME OS _USERNAM MACHINE_NA SQL _REDO ------------------ ---------- DEX oracle sol1 create table T (x int primary key); DEX oracle sol1 drop table t purge; OK. Author renfengjun