日志挖掘logminer

在插入数据的时候，数据会先缓存到logbuffer，用户手动提交或者断开sqlplus或者logbuffer 1/3满的时候都会写到redolog中（具体触发写redolog事件这里不详谈）。
我们都知道oracle redolog是比较重要的文件，有很多同步软件都是通过同步redolgo来达到数据迁移或同步目的，目前redolog文件内部结构，数据存储方式仍然是个秘密。
通过oracle提供的logmnr工具来分析redolog，可以抓取未被覆盖的数据。

挖掘redolog

初始化：

#分析日志文件
SQL> @?/rdbms/admin/dbmslm.sql;

Package created.


Grant succeeded.


Synonym created.
#创建相关数据字典
SQL> @?/rdbms/admin/dbmslmd.sql;

Package created.


Synonym created.
sql> @?/rdbms/admin/dbmslms.sql;#授权

SQL> select member,status,group# from v$logfile;

MEMBER
--------------------------------------------------------------------------------
STATUS      GROUP#
------- ----------
/u01/app/oracle/oradata/jhim/redolog1.log
                 1

/u01/app/oracle/oradata/jhim/redolog2.log
                 2

/u01/app/oracle/oradata/jhim/redolog3.log

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE

添加redolog

SQL> execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/jhim/redolog2.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/jhim/redolog1.log',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/jhim/redolog3.log',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

执行commit

SQL> create table t1 (id int,name varchar(10));

Table created.

SQL> insert into t1 values(1,'1');

1 row created.
SQL> insert into t1 values(2,'hello');

1 row created.

SQL> commit;

Commit complete.

挖掘日志

SQL>  execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

查询挖掘结果

SQL> select sql_redo,sql_undo from  v$logmnr_contents where table_name ='T1' and operation='INSERT';

SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
insert into "SYS"."T1"("ID","NAME") values ('2','hello');
delete from "SYS"."T1" where "ID" = '2' and "NAME" = 'hello' and ROWID = 'AAADdD
AABAAAIcRAAB';

既然知道操作数据记录，我们就可以进行修改或跟踪了。
结束日志挖掘

execute dbms_logmnr.end_logmnr();

挖掘archivelog

数据操作记录在redologbuffer保存一段时间，当达到触发条件的时候会把logbuffer写入到redolog，当数据库发生cehckpoint或者数据正常关闭的时候，会把redolog数据写到磁盘，这个时候如果想挖掘历史吉利就需要挖掘archivelog。

开启归档

sql > archive log list;
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     405
Current log sequence           407
sql> shutdow immediate(如果是非正常关闭，数据库是不能开启归档的)
sql>startup mount
SQL> alter database archivelog;
sql> shutdow immediate
sql> startup 

SQL> startup
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive/jhim/
Oldest online log sequence     405
Next log sequence to archive   407
Current log sequence           407
SQL>  show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      location=/u01/app/oracle/archi
                                                 ve/jhim/

默认情况下数据库归档路径是放在flash_back下面，如果没有配置flash_back则放在$ORACLE_HOME/dbs/下面。
为了方便管理，修改归档路径

SQL> alter system set log_archive_dest_2 ='location=/u01/app/oracle/archive/jhim/' scope=spfile;

log_archive_dest是静态参数，不能直接修改，关于参数是否是静态动态可以查询

SQL> select name from v$system_parameter where issys_modifiable='IMMEDIATE';

archivelog归档日志挖掘，需要使用到平面文件，首先创建数据字典，上面 已经创建这里不再创建。
设置平面文件存放路径

alter system set utl_file_dir='/u01/app/oracle'  scope=spfile;
execute dbms_logmnr_d.build('utl_logmnr.ora','/u01/app/oracle',dbms_logmnr_d.store_in_flat_file);

添加archivelog

execute dbms_logmnr.add_logfile(LogFileName=>'/u01/app/oracle/archive/jhim/1_4_919938554.dbf',Options=>dbms_logmnr.new);##第一次添加archivelog日志

继续添加

execute dbms_logmnr.add_logfile(LogFileName=>'/u01/app/oracle/archive/jhim/1_5_919938554.dbf',Options=>dbms_logmnr.addfile);

启动挖掘

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/u01/app/oracle/utl_logmnr.ora');

PL/SQL procedure successfully completed.

查询挖掘结果

SQL>  select sql_redo from v$logmnr_contents where table_name='T1';

SQL_REDO
--------------------------------------------------------------------------------
create table t1 (id int,name varchar(10));
insert into "SYS"."T1"("ID","NAME") values ('2','hello');

结束archivelog挖掘

SQL> exec dbms_logmnr.end_logmnr();

取消utl_file_dir参数设置

SQL> alter system reset utl_file_dir scope=spfile sid='*';