Check the segment or query causing a high archive log with Log Miner in Oracle
- 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: