打开oracle归档日志，并启用logminer

一、登录数据库查看logminer打开状态

# 使用sysdba 的角色登录数据库
sqlplus sys/yourpassword as sysdba;



 select dbid,name,log_mode from v$database;

      DBID NAME      LOG_MODE
---------- --------- ------------
1596267283 ORCL      NOARCHIVELOG

# 根据结果，显示没有启动日志

二、启动归档日志

2.1 设置归档日志文件路径

-- 使用sys 用户登录 as sysdba
alter system set log_archive_dest="/ora/oracle/archive" scope spfile;

创建所在的目录，并授权

mkdir -p /ora/oracle/archive
grant +755 /ora/oracle/archive

# 如果在创建log_archive_dest路径错误，重启报错后
sqlplus sys/password as sysdba

create pfile from spfile ;
exit

# 修改数据库启动
vi initeprep.ora 找到log_archive_dest=''进行注释或者删除
# 重新使用sysdba登录并执行
create spfile from pfile ;

shutdown immediate;

startup mount
启动数据库

2.2 修改归档日志的格式

 alter system set log_archive_format=''arch_%d_%t_%r_%s.log''

2.3 将数据库设置为归档模式，并将数据文件打开

alter database archivelog;
alter database open;

三、启动logminer

3.1 使用sys账号创建logminer用户属于sysdba的权限

-- 创建用户
create user LOGMINER
  identified by "LOGMINER"
  default tablespace LCAM_PUB_TBS --表空间依据当前数据库的情况而定
  temporary tablespace TEMP
  profile DEFAULT;

grant connect to LOGMINER;
grant resource to LOGMINER;
grant sysdba to LOGMINER;
grant dba to LOGMINER

3.2 使用logminer用户执行oracle脚本，生成logminer的package 确保数据库打开辅助日志

SQL> @/ora/oracle/product/11.2.0/dbhome_1/rdbms/admin/dbmslm.sql;

SQL> @/ora/oracle/product/11.2.0/dbhome_1/rdbms/admindbmslmd.sql;

select supplemental_log_data_min from v$database;

suppleme
-------------
NO

alter database ad supplemental log data ;

3.3 用户logminer 用户设置utl_file_dir 确定这个目录存在并授权

alter system set utl_file_dir='/oracle/oradata/oradb11/LOGMNR' scope=spfile;
-- 建议创建后，重启oracle

3.4 查看归档redo组

-- Status为current为当前启用的在线日志，为了试验简洁，我只分析当前的日志。

SQL> select l.STATUS,s.MEMBER from v$log l,v$logfile s where l.GROUP# = s.GROUP#;

STATUS           MEMBER

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

INACTIVE         /oracle/oradata/oradb11/redo04.log

CURRENT          /oracle/oradata/oradb11/redo05.log

INACTIVE         /oracle/oradata/oradb11/redo06.log

3.5 在L_PUB上执行测试场景脚本

-- 记录操作开始时间和结束时间。
select sysdate from dual;
create table test(id number ,name varchar2(100));
insert into test values(1,'张三');
insert into test values(2,'李四');
commit;
insert into test values(3,'王五');
rollback;
insert into test values(4,'赵六');
insert into test values(5,'冯七');
insert into test values(6,'刘八');
insert into test values(7,'廖九');
commit;
update test set name='刘八八' where id=6;
commit;
delete from test where id=7;
rollback;
delete from test where id=1;
commit;
select sysdate from dual;

3.6用logminer用户生成数据字典,需要保证dictionary_location所指定的目录存在

execute dbms_logmnr_d.build(dictionary_filename =>'dictionary.ora',dictionary_location =>'/oracle/oradata/oradb11/LOGMNR');

3.7 用logminer用户添加分析的redo日志

exec dbms_logmnr.add_logfile(logfilename=>'/oracle/oradata/oradb11/redo05.log',options=>dbms_logmnr.new);

dbms_logmnr.add_logfile(logfilename=>'/oracle/oradata/oradb11/redo06.log',options=>dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile(logfilename=>'/oracle/oradata/oradb11/redo06.log',options=>dbms_logmnr.addfile);

3.8 用logminer用户启动logminer

--全分析，
execute dbms_logmnr.start_logmnr(DictFileName=>'/oracle/oradata/oradb11/LOGMNR/dictionary.ora');

--按时间段来分析，
execute dbms_logmnr.start_logmnr(startTime => to_date('2017-03-06 11:00:18','yyyy-mm-dd hh24:mi:ss'),endTime => to_date('2017-03-06 11:02:06','yyyy-mm-dd hh24:mi:ss'),DictFileName => '/oracle/oradata/oradb11/LOGMNR/dictionary.ora');

四、使用logminer进行分析

4.1 用logminer用户logminer分析处理的结果只有本session能看到，可以先用表把数据记录然后分析

Drop table logminer_t purge;

Create table logminer_t  as select * from V$LOGMNR_CONTENTS;

4.2 用logminer用户分析后释放内存

execute dbms_logmnr.end_logmnr;

4.3 用logminer用户可以慢慢分析

Select S.SCN,

       s.start_scn,

       S.COMMIT_SCN,

       S.TIMESTAMP,

       s.START_TIMESTAMP,

       S.COMMIT_TIMESTAMP,

       S.OPERATION,

       S.ROLLBACK,

       S.SEG_OWNER,

       S.SEG_NAME,

       S.TABLE_NAME,

       S.TABLE_SPACE,

       S.SQL_REDO,

       S.SQL_UNDO

  From logminer_t s

 where s.SEG_OWNER = 'L_PUB'

   and s.table_name = 'TEST'

 order by scn;
 
 我们可以捕获到执行的SQL：



-- 以下是提交事务的SQL,同步的时候可以用到:

with co_scn as(

select start_scn,commit_scn

  from logminer_t s

 where s.start_scn is not null

    and s.commit_scn is not null),

operate_scn as(

Select scn,s.sql_redo  From logminer_t s

 where s.SEG_OWNER = 'L_PUB'

   and s.table_name = 'TEST'

)

Select scn,sql_redo

  From operate_scn s, co_scn co

 where s.scn >= co.start_scn

   and s.scn <= co.commit_scn;



-- 以下是回滚的SQL，同步时可以忽略：

select S.SCN,

       S.TIMESTAMP,

       S.OPERATION,

       S.ROLLBACK,

       S.SEG_OWNER,

       S.SEG_NAME,

       S.TABLE_NAME,

       S.SQL_REDO from logminer_t s where pxid in

(select pxid from logminer_t where rollback=1 and SEG_OWNER = 'LCAM_PUB')

order by scn;