Logminer在线日志解析
1、查看是否开启归档模式以及归档日志保存路径
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 933
Next log sequence to archive 935
Current log sequence 935
2、构造测试数据以及删除操作
记录删除操作时的时间点，为了能够将操作记录至归档需要手动切换几次日志。
SQL> create table test as select * from TQZHZF.FRAME_ATTACHSTORAGE;
Table created.
SQL> delete from test where cliengtag='PRINT_FILE';
91 rows deleted.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
3、根据删除操作的时间点查询归档信息
SQL> select name from v$archived_log a
2 where a.first_time >=to_date('2019/08/21 15:24:00','yyyy-mm-dd hh24:mi:ss')
3 and a.first_time<=to_date('2019/08/21 15:25:00','yyyy-mm-dd hh24:mi:ss');
NAME
--------------------------------------------------------------------------------
/u01/arch/1_975_1011530876.dbf
/u01/arch/1_976_1011530876.dbf
4、开启最小附加日志
SQL> alter database add supplemental log data(primary key,unique index) columns;
Database altered.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP
--- ---
YES YES
5、安装logmnr
需要使用sysdba用户执行
@?/rdbms/admin/dbmslm.sql
@?/rdbms/admin/dbmslmd.sql
@?/rdbms/admin/dbmslms.sql
6、添加日志列表
--添加首个日志
SQL> exec dbms_logmnr.add_logfile('/u01/arch/1_975_1011530876.dbf',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
--添加第N个日志
SQL> exec dbms_logmnr.add_logfile('/u01/arch/1_976_1011530876.dbf', dbms_logmnr.ADDFILE);
PL/SQL procedure successfully completed.
7、日志解析
使用当前redo作为数据字典
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
8、将日志解析内容做存档
SQL> create table log_miner as select * from v$logmnr_contents;
Table created.
9、分析日志
按用户、表名以及操作分析日志，其中查询结果包括操作时间、重做SQL以及回滚SQL。
SQL> select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo,sql_undo from sys.log_miner
where seg_owner='ADMIN'
and table_name='TEST'
and operation='DELETE';
10、结束日志分析
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
整理：江苏国泰新点软件基础设施支持部
