Oracle tool: simple use of logminer

My environment:
[Root @ localhost ~] # Uname-
Linux localhost. localdomain 2.6.18-308. el5xen #1 SMP Fri Jan 27 17:59:00 est 2012 i686 i686 i386 GNU/Linux

Sys @ orcl> select * from V $ version where rownum = 1;

Banner
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

 

10 Gb does not need to generate a dictionary file. You only need to load the log file and analyze it.

1. Generate database operations

hr@ORCL> drop table t purge;Table dropped.hr@ORCL> create table logmnr_test (id number,name varchar2(20));Table created.hr@ORCL> insert into logmnr_test values(1,'think');1 row created.hr@ORCL> insert into logmnr_test values(2,'water');1 row created.hr@ORCL> commit;                    Commit complete.hr@ORCL> select sequence#,status from v$log; SEQUENCE# STATUS---------- ----------------        14 CURRENT        13 INACTIVE        12 INACTIVEhr@ORCL> update logmnr_test set name='think_pad' where id=2;1 row updated.hr@ORCL> commit;Commit complete.hr@ORCL> alter system switch logfile;System altered.hr@ORCL> select sequence#,name from v$archived_log; SEQUENCE#----------NAME----------------------------------------------------------------------------------------------------.............................        14/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_09/o1_mf_1_14_84qrj5co_.arc

2. Specify log files for analysis

Sys @ orcl> select db_name, thread_sqn, filename 2 from V $ logmnr_logs; no rows selectedsys @ orcl> exec dbms_logmnr.add_logfile ('/u01/APP/Oracle/flash_recovery_area/orcl/archivelog/2012_09_09/o1_mf_1_14_84q1_5co _. arc', dbms_logmnr.new); PL/SQL procedure successfully completed. to analyze more logs, change dbms_logmnr.new to dbms_logmnr.addfile. Sys @ orcl> select db_name, thread_sqn, filename from V $ logmnr_logs; db_name thread_sqn -------- ---------- FILENAME----------------------------------------------------------------------------------------------------ORCL 14/u01/APP/Oracle/flash_recovery_area/orcl/archivelog/examples/example _. Arc

3. Start logminer

Sys @ orcl> exec dbms_logmnr.start_logmnr (Options => SYS. databases); PL/SQL procedure successfully completed. If you analyze large data volumes, you can specify the SCN or time range.

4. Analyze the log Content
1) check data change details
The data in the database may be changed due to unexpected reasons or errors. The details of these changes can be found in the redo log file. For example, who made these changes? When did you change it? How to change it?

select operation,timestamp,scn from v$logmnr_contents where seg_name='LOGMNR_TEST' and       seg_owner='HR' and       seg_type_name='TABLE';OPERATION                        TIMESTAMP                  SCN-------------------------------- ------------------- ----------DDL                              2012/09/09 08:20:47     721905select sql_redo,sql_undo from v$logmnr_contents where seg_name='LOGMNR_TEST' and       seg_owner='HR' and       seg_type_name='TABLE';       SQL_REDO----------------------------------------------------------------------------------------------------SQL_UNDO----------------------------------------------------------------------------------------------------create table logmnr_test (id number,name varchar2(20));select username,session_info from v$logmnr_contents where seg_name='LOGMNR_TEST' and       seg_owner='HR' and       seg_type_name='TABLE'

2) perform Capacity Analysis
For example, the frequency and frequency of DML generated in the analysis table

select operation,timestamp,count(*) total from v$logmnr_contents where seg_name='LOGMNR_TEST' and       seg_owner='HR' and       seg_type_name='TABLE'group by operation,timestamp;OPERATION                        TIMESTAMP                TOTAL-------------------------------- ------------------- ----------DDL                              2012/09/09 08:20:47          1

3) Search for DDL command details
For example, you can use logminer to find the table deletion time and SCN to facilitate media recovery.

select seg_name,operation,scn,timestamp,count(*)  from v$logmnr_contents where operation='DELETE' group by seg_name,operation,scn,timestamp order by scn;

5. Disable logminer
For further analysis, save the content of V $ logmnr_contents.
Create Table logmnr_contents as select * from V $ logmnr_contents;
Then, execute close:
Exec dbms_logmnr.end_logmnr;

Figure:

To quickly use Log Mining, follow these steps:

sys@ORCL> select GROUP#,status from v$log;sys@ORCL> select group#,member from v$logfile;sys@ORCL> exec DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_823q78og_.log',dbms_logmnr.NEW);PL/SQL procedure successfully completed.sys@ORCL> exec DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);PL/SQL procedure successfully completed.sys@ORCL> select operation,count(*) from v$logmnr_contents group by operation;