Oracle安全之Oracle日志挖掘

最新推荐文章于 2021-04-10 06:10:36 发布

转载最新推荐文章于 2021-04-10 06:10:36 发布 · 59 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/wcwen1990/p/6661683.html

文章标签：

#数据库

本文介绍了 Oracle LogMiner 的使用方法，包括如何基于在线日志和归档日志进行数据挖掘，展示了如何通过 SQL 语句添加日志文件、启动和结束 LogMiner 会话，以及如何查看挖掘结果。

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.

转载于:https://www.cnblogs.com/wcwen1990/p/6661683.html