Oracle tool: simple use of logminer

Oracle tool: simple use of logminer my environment: [root @ localhost ~] # Uname-aLinux localhost. localdomain 2.6.18-308. el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012
I686 i686 i386 GNU/Linuxsys @ ORCL> select * from v $ version where rownum = 1; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod 10g actually does not need to generate a dictionary file. You only need to load the log file and analyze it. 1 generate database operation www.2cto.com [SQL] 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; www.2cto.com SEQUENCE # STATUS ---------- ---------------- 14 CURRENT 13 INACTIVE 12 INACTIVE hr @ 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 sequence ------------------------------------------------------------------------------------
----------------............................. 14/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_09/o1_mf_1_14_84q1_5co _. arc www.2cto.com 2 is used to analyze the specified log file [SQL] sys @ ORCL> select db_name, thread_sqn, filename 2 from v $ logmnr_logs; no rows selected sys @ ORCL> exec DBMS_LOGMNR.ADD_LOGFILE ('/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09 _
09/examples _. 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 already ORCL 14/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_09/o1_mf_1_14_84q1_5co _. start logminer [SQL] sys @ ORCL> exec DBMS_LOGMNR.START_LOGMNR (OPTIONS => SYS. DBMS_LOGMNR.DICT_FROM_ONLINE _
CATALOG); PL/SQL procedure successfully completed. For analysis of large data volumes, you can specify the SCN or time range. 4. Analyze the log Content 1) Check the details of data changes the data in the database may change due to unexpected causes or errors. In the redo log file, you can
To find the details of these changes, such as: Who made these changes? When did you change it? How to change it?
[SQL] 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 restart failed ---------- DDL 2012/09/09 08:20:47 721905 select SQL _redo, SQL _undo from v $ logmnr_contents where seg_name = 'logmnr _ test' and seg_owner = 'hr 'and seg_type_name = 'table '; SQL _REDO www.2cto.com -- -------------------------------------------------------------------------------------- SQL _UNDO tables 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, such as the frequency and frequency of DML generation in the analysis table [SQL] selec T 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 08:20:47 1 3) Search for details about DDL commands. For example, you can use logminer to find the table deletion time and scn to facilitate media recovery. [SQL] select seg_name, operation, scn, timestamp, count (*) from v $ logmnr_contents www.2cto.com where operation = 'delete' group by seg_name, operation, scn, timestamp order by scn; 5. To disable logminer for further analysis, save the v $ logmnr_contents content and create table logmnr_contents as select * from v $ logmnr_contents. Then, execute the following command to disable exec dbms_logmnr.end_logmnr: