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

方法一、

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';