Check the segment or query causing a high archive log with Log Miner in Oracle

1. Set up the Log Miner for the Oracle Database.

--login with Sysdba user and run the following script present in RDBMS folder.
@ORACLE_HOME/rdbms/admin/dbmslm.sql

2. Enable the Database SUPPLEMENTAL Log

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

3. Check the time when more archive is generated and choose the archive log of that time to check with the log miner package.

set pages 1000
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select trunc(COMPLETION_TIME,'HH') Hour,thread# , 
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives from v$archived_log 
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 4 desc ;

4. Get the files generated during these hours and add them in the Log Miner package as:

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\archivelog\ARCH_563.arc', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\archivelog\ARCH_564.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);

5. Start the Log Miner process:

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); 

6. Check the Log Miner table for analysis the root cause of high archive generation:

-- Find the segment name which cause more DML operations:
select operation,seg_owner,seg_name,count(*)  from v$logmnr_contents group by seg_owner,seg_name,operation;

-- Find the SQL Query executing during that period of time for particular user:
SELECT username,operation,SQL_REDO,SQL_UNDO 
     FROM V$LOGMNR_CONTENTS WHERE username IN ('<Username>');

7. Close the Log Mineer Session:

EXECUTE DBMS_LOGMNR.END_LOGMNR();