An example of Oracle logmnr log mining

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

1 2 Next Page