oracle logminer挖归档详细流程

查当天每小时的归档日志生成量

方法一、
alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;
select logtime,
count(*),
round(sum(blocks * block_size) / 1024 / 1024/1024) mbsize
from (select trunc(first_time, ‘hh’) as logtime, a.BLOCKS, a.BLOCK_SIZE
from v$archived_log a
where a.DEST_ID = 1
and a.FIRST_TIME > trunc(sysdate-1))
group by logtime
order by logtime desc;

方法二、
SELECT SUM(BLOCKS *BLOCK_SIZE )/1024/1024/1024 AS “Size(G)”,TRUNC(completion_time) FROM v$archived_log GROUP BY TRUNC(completion_time) order by TRUNC(completion_time);

方法三、
select thread#,name,FIRST_TIME,NEXT_TIME from gv$archived_log
where
first_time>=to_date(‘2019-01-27 09:00:00’,‘yyyy-mm-dd hh24:mi:ss’)
and next_time<=to_date(‘2019-01-27 12:00:00’,‘yyyy-mm-dd hh24:mi:ss’)
and to_char(first_time,‘hh24’) in(‘09’,‘10’,‘11’,‘12’)
and thread#=1
and standby_dest=‘NO’
order by 3;

方法四、
select t.THREAD#, t.SEQUENCE#, t.NAME
from v$archived_log t
where t.FIRST_TIME >=to_date (‘2019-11-05 10:24:30’, ‘yyyy-mm-dd hh24:mi:ss’)
and t.NEXT_TIME <=to_date (‘2019-11-05 14:00:30’, ‘yyyy-mm-dd hh24:mi:ss’);

安装logminer安装包

需要sys用户执行，不然需要赋权
GRANT EXECUTE ON DBMS_LOGMNR TO 用户;

SQL>@$ORACLE_HOME/rdbms/admin/dbmslm.sql
Package created.
Grant succeeded.
Synonym created.

SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql
Package created.
Synonym created.

添加日志

添加第一个
execute dbms_logmnr.add_logfile(logfilename=>‘/app_target/easdb_dg/arch/1_2_1023532682.dbf’,options=>dbms_logmnr.new);

继续添加
execute dbms_logmnr.add_logfile(logfilename=>‘/app_target/easdb_dg/arch/1_1_1023532682.dbf’,options=>dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile(logfilename=>‘/app_target/easdb_dg/arch/1_3_1023532682.dbf’,options=>dbms_logmnr.addfile);

开启 Logminer
execute dbms_logmnr.start_logmnr(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

查看Logminer
之前的操作是创建表，添加3行数据
select sql_redo from v$logmnr_contents where SEG_OWNER=‘HZMC’;

select sql_redo from v$logmnr_contents where OPERATION=‘DELETE’ and TABLE_NAME=‘D’ and SEG_OWNER=‘DEF’;

挖掘truncate
select sql_redo from v$logmnr_contents where OPERATION=‘DDL’ and TABLE_NAME=‘D’ and SEG_OWNER=‘DEF’;

sql_redo
select sql_redo from v$logmnr_contents where SEG_OWNER=‘HZMC’;

sql_undo
select sql_undo from v$logmnr_contents where SEG_OWNER=‘HZMC’;