oracle 11g 归档日志分析

1. 开启数据库归档模式；（必须）

2. 开启数据库补全日志；（必须）

3. 生成离线数据字典；（不推荐使用在线数据字典，日志量大的情况下会影响效率）

4. 创建数据库触发器；（用来记录客户端IP地址，可选）

5. 开始日志分析；

6. 查询分析结果；

7. 遗留问题。

1. 开启数据库归档日志

--在磁盘上创建归档日志文件存放目录
SQL> HOST MKDIR d:\arch_dest_01;
SQL> HOST MKDIR d:\arch_dest_02;

--指定归档日志存放路径及文件命名格式：
SQL> ALTER SYSTEM SET log_archive_dest_1='location=d:\arch_dest_01' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_2='location=d:\arch_dest_02' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_format='redo_%D_%T_%R_%S.log' SCOPE=SPFILE;
--关闭并重新加载数据库
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

--启用归档日志模式并打开数据库
SQL> ALTER DATABASE archivelog;
SQL> ALTER DATABASE open;

--确认数据库为归档日志模式
SQL> SELECT log_mode FROM v$database;   --返回ARCHIVELOG为归档模式
SQL> SELECT archiver FROM v$instance;   --返回STARTED为归档模式

--强制日志切换并确认归档成功
SQL> ALTER SYSTEM SWITCH logfile;
SQL> SELECT name FROM v$archived_log;

2. 开启数据库补全日志

SQL> ALTER DATABASE ADD SUPPELEMENTAL LOG DATA(PRIMARY KEY, UNIQUE INDEX) COLUMNS;

--查看补全日志是否开启
     SELECT supplemental_log_data_min,
            supplemental_log_data_pk,
            supplemental_log_data_ui,
            supplemental_log_data_fk,
            supplemental_log_data_all
       FROM v$database;

3. 生成离线数据字典

--初始化数据字典路径
SQL> ALTER SYSTEM SET UTL_FILE_DIR='d:\ora_dic' SCOPE=SPFILE;

--重启数据库
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

--生成离线数据字典
SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'test.dic', dictionary_location => 'd:\ora_dic');

4. 创建数据库触发器

SQL> CREATE OR REPLACE TRIGGER on_logon_trigger
SQL>   AFTER logon ON DATABASE
SQL> BEGIN
SQL>   dbms_application_info.set_client_info(sys_context('userenv','ip_address'));
SQL> END;

5. 开始日志分析

--添加待分析归档日志文件
SQL> EXECUTE dbms_logmnr.add_logfile(logfilename => 'd:\arch_dest_01\redo_xxx1.log', options => dbms_logmnr.new);
SQL> EXECUTE dbms_logmnr.add_logfile(logfilename => 'd:\arch_dest_01\redo_xxx2.log');
SQL> EXECUTE dbms_logmnr.add_logfile(logfilename => 'd:\arch_dest_01\redo_xxx3.log');

--方法1：使用在线数据字典（只能在被分析数据库上使用）
SQL> EXECUTE dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);

--方法2：使用离线数据字典
SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName => 'd:\oar_dic\test.dic'); 

--分析结果在v$logmnr_content，结束分析会话
SQL> EXECUTE dbms_logmnr.end_logmnr;

6. 查询分析结果

--根据相关视图创建分析结果表
SQL> CREATE TABLE logmnr_result AS (SELECT * FROM v$logmnr_contents);

--创建索引以提高查询速度
CREATE INDEX LOGMNR_TIMESTAMP ON logmnr_result (TIMESTAMP)
  tablespace MYCRM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

--查询分析结果
     SELECT l.timestamp,
            l.operation,
            l.table_name,
            l.machine_name,
            l.session_info,
            l.sql_redo
       FROM logmnr_20131101 l
      WHERE l.table_space = 'MYCRM'
        AND l.table_name IN('ORDERHIST', 'ORDERDET', 'STOCK')
      ORDER BY l.timestamp;

如下图：

7. 遗留问题

虽然创建了相关触发器，且在视图v$session的client_info列成功记录了IP地址，但分析结果SESSION_INFO列中的Client_info信息仍然为空，不知道问题在哪儿，这个留待以后解决。

oracle 11g 归档日志分析

浏览过的版块