oracle timestamp转date_用logmnr追踪oracle操作记录

1 确保打开补充日志

alter database add supplemental log data;

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

--------

YES

2 确保已配置utl_file_dir

SQL> show parameter utl_file_dir;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

utl_file_dir string

如果value值为空，说明没设置该值，需要设置下，示例：

alter system set UTL_FILE_DIR='d:logmnr' scope=spfile;

重启数据库，使修改生效：

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup;

ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes

Fixed Size 2175408 bytes

Variable Size 939527760 bytes

Database Buffers 335544320 bytes

Redo Buffers 8818688 bytes

数据库装载完毕。

数据库已经打开。

SQL> show parameter utl_file_dir;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

utl_file_dir string d:logmnr

3 制造测试数据(可选)

用jiao这个用户执行一条sql：

update scott.emp set comm=8888 where empno=7900;

4 追踪操作记录

4.1 建立数据字典分析文件

SQL> exec dbms_logmnr_d.build('dict.ora','d:logmnr',dbms_logmnr_d.store_in_flat_file);

PL/SQL 过程已成功完成。

4.2 添加日志分析(要追踪的sql所在日志)

exec dbms_logmnr.add_logfile(logfilename=>'D:APP65128FLASH_RECOVERY_AREAORCLARCHIVELOG2020_04_22O1_MF_1_95_H9ZDZ5RT_.ARC',options=>dbms_logmnr.new);

4.3 执行分析

exec dbms_logmnr.start_logmnr(dictfilename=>'d:logmnrdict.ora',options=>dbms_logmnr.ddl_dict_tracking);

--这里也可以限制时间范围，如：

execute dbms_logmnr.start_logmnr(startTime => to_date('2020-04-22 11:00:18','yyyy-mm-dd hh24:mi:ss'),endTime => to_date('2020-04-22 11:10:06','yyyy-mm-dd hh24:mi:ss'),DictFileName => 'd:logmnrdict.ora',options=>dbms_logmnr.ddl_dict_tracking);

4.4 查看分析结果

查看关于EMP表的update操作记录：

select username,seg_owner,scn,to_char(timestamp, 'YYYY-MM-DD HH:MI:SS'),sql_redo from v$logmnr_contents where lower(sql_redo) like '%update%' and table_name='EMP';

可以看到是JIAO这个用户执行了一个update操作，sql_redo里可以看到具体的操作。注意，where条件和执行时的where条件有些出入。

4.5 结束分析

execute dbms_logmnr.end_logmnr;