ORACLE 日志 logminer 使用

1. logminer安装

SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql

Package created.


Grant succeeded.


Synonym created.

SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql

Package created.


Synonym created.

SQL> @$ORACLE_HOME/rdbms/admin/dbmslms.sql

Package created.

No errors.

Grant succeeded.

2. 配置

SQL> alter system set utl_file_dir='/home/oracle/log' scope=spfile;

System altered.

3. 重启oracle

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size 2160112 bytes
Variable Size 385878544 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7413760 bytes
Database mounted.
Database opened.
SQL>

execute dbms_logmnr_d.build(dictionary_filename=>'oracle.ora',dictionary_location=>'

/home/oracle/log/');


SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/log/oracle.ora');

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(options=>dbms_logmnr.addfile,logfilename=>'/home/oracle/log/redo03.log');

PL/SQL procedure successfully completed.

　5. 开始查日志

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/log/oracle.ora');

PL/SQL procedure successfully completed.

　　6. 找对于表的操作记录

SQL> Select sql_redo,timestamp,operation from v$logmnr_contents where table_name='T_UC_BMPCONSOLEMODE';