Use logminner in Oracle

1. Create a dbms_logmnr package
The following directory is the installation directory of the Oracle server.
SQL> @ D:/Oracle/product/10.2.0/db_1/rdbms/admin/dbmslm. SQL

2. Create a data dictionary
SQL> @ D:/Oracle/product/10.2.0/db_1/rdbms/admin/dbmslmd. SQL

3. Modify the initialization parameter utl_file_dir to specify the storage location of the analysis data.
SQL> alter system set utl_file_dir = 'C:/oralog/logs' scope = spfile;

4. Restart the database
SQL> shutdown immediate
SQL> startup
Startup Mount;
5. Create a data dictionary file

SQL> execute dbms_logmnr_d.build (dictionary_filename => 'sqltrace. ora ', dictionary_location => 'C:/oralog/logs ');

6. Create a log analysis table

SQL> execute dbms_logmnr.add_logfile (Options => dbms_logmnr.new, logfilename => 'd:/Oracle/product/10.2.0/oradata/Oracle/redo01.log ');

7. Add log files for analysis

SQL> execute dbms_logmnr.add_logfile (Options => dbms_logmnr.addfile, logfilename => 'd:/Oracle/product/10.2.0/oradata/Oracle/redo02.log ');

SQL> execute dbms_logmnr.add_logfile (Options => dbms_logmnr.addfile, logfilename => 'd:/Oracle/product/10.2.0/oradata/Oracle/redo03.log ');

Execute dbms_logmnr.add_logfile (Options => dbms_logmnr.addfile, logfilename => 'd:/Oracle/product/10.2.0/flash_recovery_area/Oracle/archivelog/2008_02_02/o1_mf_1_18_3t7rg1qx_.arc ');

Delete

SQL> execute dbms_logmnr.add_logfile (Options => dbms_logmnr.removefile, logfilenam

E => '/opt/Oracle/ora92/rdbms/arc00011.001 ');

8. Start logminer for analysis

SQL> execute dbms_logmnr.start_logmnr (dictfilename => 'C:/oralog/logs/sqltrace. ora ', starttime => to_date ('2014: 00: 00', 'yyyymmdd hh24: MI: ss'), endtime => to_date ('2014 22:00:00', 'yyyymmdd hh24: mi: ss '));
Or
SQL> execute dbms_logmnr.start_logmnr (dictfilename => 'C:/oralog/logs/sqltrace. ora ')

Execute dbms_logmnr.add_logfile (Options => dbms_logmnr.addfile, logfilename => 'd:/Oracle/product/10.2.0/flash_recovery_area/Oracle/archivelog/2008_02_02/o1_mf_1_18_3t7rg1qx_.arc ');
Dbms_logmnr.add_logfile (Options => dbms_logmnr.addfile, logfilename => 'd:/Oracle/product/10.2.0/flash_recovery_area/Oracle/archivelog/20080128/28. arc ');

9. See the analysis results.

Select Operation, SQL _redo, SQL _undo, timestamp from V $ logmnr_contents where username = 'test' or table_name = 'test1' and timestamp> to_date ('2017-01-28 09:00:00 ', 'yyyy-mm-dd hh24: MI: ss ');
Select username, count (username) from V $ logmnr_contents group by username
View several log files

Select member from V $ logfile;
Check which log file logminner is operating on
Select filename from V $ logmnr_logs;

10. End Analysis

SQL> execute dbms_logmnr.end_logmnr;

The analysis results in view v $ logmnr_contents will no longer exist.