Category Archives: Oracle

In this we are handling Oracle Database Administration and development task. If provide solution of ORA Errors and configuration Steps for setup in Oracle.

Check the High Archive log with Log Miner in Oracle

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();