Oraclelogmnr log mining example

Oraclelogmnr log mining example, using logmnr to mine information in logs

An example of Oracle logmnr log mining, using logmnr to mine information in logs

1. Perform the delete operation.
SQL> drop table t;


Table dropped.


SQL> select * from tab;


TNAME TABTYPE CLUSTERID
-----------------------------------------------
BIN $ wWGJJd0UCrDgRAAMKRcWyg = $0 TABLE
TTS SYNONYM
TTSS SYNONYM


SQL> purge table t;


Table purged.


Use logmnr to mine log information
2. view the operation directory


SQL> show parameter utl_file_dir


NAME TYPE VALUE
-----------------------------------------------------------------------------
Utl_file_dir string


If this parameter is not specified, a folder is specified 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 the database


-Bash-3.00 $ srvctl stop databases-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.


4. Analyze logs
Add relevant logs to analysis
Begin
Dbms_logmnr.add_logfile (logfilename => '/gwrs/PROD/archivelog/2012_06_01/o1_mf_1_35_7wjflz93 _. 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_000036_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 );
Dbms_logmnr.add_logfile (logfilename => '/gwrs/PROD/archivelog/2012_06_01/o1_mf_000038_7wjmwbcs _. 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. Analyze Operation Records
Note: 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 to save it.
Create table logmnr_tmp tablespace users as select * from v $ logmnr_contents;


Then you can analyze the log.
SQL> col seg_name for a10
SQL> col SQL _redo for a50
SQL> 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
---------------------------------------------------------------------------------------------------
1823865 01-JUN-12 dex t alter table "DEX". "T" rename to "BIN $ wWGJJd0UCrDgR
AAMKRcWyg = $0 ";


1823868 01-JUN-12 dex t drop table t AS "BIN $ wWGJJd0UCrDgRAAMKRcWyg = $0"
;


1823953 01-JUN-12 dex t purge table "BIN $ wWGJJd0UCrDgRAAMKRcWyg = $0 ";
If the status of username, OS _username, and machine_name is unknow or null, This is a bug. This situation may occur when you repeatedly execute the same logmnr parameter (not for the first time) BUG: 10129774
Link address https://support.Oracle.com/CSP/main/article? Cmd = show & type = BUG & id = 10129774
Install patch 10129774