首页 > 代码库 > Oracle日志挖掘
Oracle日志挖掘
logminer基于包:
[oracle@localhost ~]$ ls /u01/oracle/10g/rdbms/admin/dbmslm.sql -->dbms_logmnr工具
/u01/oracle/10g/rdbms/admin/dbmslm.sql
[oracle@localhost ~]$ ls /u01/oracle/10g/rdbms/admin/dbmslmd.sql-->dbms_logmnr_d工具
/u01/oracle/10g/rdbms/admin/dbmslmd.sql
挖掘联机日志:
SQL> execute dbms_logmnr.add_logfile
(‘/u01/oracle/oradata/orcl/redo03.log‘,dbms_logmnr.new);
SQL> execute dbms_logmnr.add_logfile
(‘/u01/oracle/oradata/orcl/redo02.log‘,dbms_logmnr.addfile);
SQL> execute dbms_logmnr.start_logmnr
(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);
SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name=‘EMP‘;
SQL> create table tlog as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
挖掘归档日志:
SQL> delete from dept where deptno=70;
1 row deleted.
SQL> commit;
Commit complete.
SQL> delete from dept where deptno=60;
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select name from v$archived_log;
NAME
------------------------------------------------------------------------------------------------------------------
/u01/oracle/flash_recovery_area/ORCL/archivelog/2015_04_01/o1_mf_1_2_bkp6s8vy_.arc
/u01/oracle/flash_recovery_area/ORCL/archivelog/2015_04_01/o1_mf_1_3_bkp6sdbz_.arc
/u01/oracle/flash_recovery_area/ORCL/archivelog/2015_04_01/o1_mf_1_4_bkp6sjbz_.arc
SQL> show parameter utl_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string
SQL> alter system set utl_file_dir=‘/home/oracle/‘ scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 88082024 bytes
Database Buffers 188743680 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> exec dbms_logmnr_d.build(‘log.ora‘,‘/home/oracle/‘,dbms_logmnr_d.store_in_flat_file);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile(‘/u01/oracle/flash_recovery_area/ORCL/archivelog/2015_04_01/o1_mf_1_12_bkr48xy4_.arc‘,dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile(‘/u01/oracle/flash_recovery_area/ORCL/archivelog/2015_04_01/o1_mf_1_11_bkr48wsk_.arc‘,dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile(‘/u01/oracle/flash_recovery_area/ORCL/archivelog/2015_04_01/o1_mf_1_10_bkr48vcs_.arc‘,dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>‘/home/oracle/log.ora‘);
PL/SQL procedure successfully completed.
SQL> select sql_undo,sql_redo from v$logmnr_contents where table_name=‘EMP‘;
no rows selected
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
Oracle日志挖掘