Session generating huge Archive logs

Yesterday we had an issue in our database, I was not there in office and my junior was taking care of the databases.

He was informed that one of the mount point is growing very fast and is about 90% used. The infrastructure team was afraid  of running the server out of space. He some how found out it was huge generation of archive log caused the issue. The growth rate was 1GB per minute.

As an immediate response he took a compressed backup of the archive logs, to the backup mount point from where it will be moved to tape later. He made enough room in the mount point to accommodate archive logs. The archive log generation went normal after some time. But he did not try to catch the session generating huge archive logs.

Today,  I need to find the sessions generated this much archive logs. And also the queries which was responsible for the huge archive log generation yesterday.

Well, I could have used  the query below to list the sessions and the generated redo in MB if the session is still connected. This was the query I used to use whenever I notice a huge archive log generation.
select s.username, s.osuser, s.status,s.sql_id,  sr.*  from 
  (select sid, round(value/1024/1024) as "RedoSize(MB)" 
          from v$statname sn, v$sesstat ss
          where sn.name = 'redo size'
                and ss.statistic# = sn.statistic#
          order by value desc) sr,
   v$session s
where sr.sid = s.sid
and   rownum <= 10;

This could not help me as the connected sessions listed were not responsible for huge redo generation. The session would have logged out yesterday itself.

I thought of changing the above query to list it from the dba_hist views. Well, I can get the history of v$session from dba_hist_active_sess_history. and the v$statsname is static, we just need to get the statistics name from there. But the historical v$sessstat is not there. DBA_HIST_SESSMETRIC_HISTORY is supposed to have the history but the view is not populated(!).

I googled a lot to get some hints. Searched in the Oracle support site. Got a lot of information about redo generation, redo and archive log maintenance. I have listed some of the links in the bottom.

I took an AWR report for the problematic window, and scrolled up and down many times. My eye stuck on the section  'Segments by DB Blocks Changes' where one of the segment was having very huge DB_BLOCK_CHANGES. Which means what? I got the segment which was having huge block changes. Now I just need to find out what was the SQL statement causing this segment change a lot. That will be the SQL statement caused the huge archive generation.  Then I searched in the section 'Complete List of SQL Text' of AWR report to find out the list of SQLs which refers the mentioned segment. With God's grace I could find out the query and session generated huge archive logs yesterday in our database. 

Later on I found this blog related to the same what I need. This is really helpful, He is giving queries to find out the same I did using AWR.
http://appcrawler.com/wordpress/2009/04/15/who-is-generating-all-the-redo/

Some other useful  links:
http://www.4tm.com.ar/blog/2008/oracle-howto-collect-session-history-metrics.html
Master Note: Troubleshooting Redo Logs and Archiving[Article ID 1507157.1]
SQL: How to Find Sessions Generating Lots of Redo or Archive logs[Article ID 167492.1]
Troubleshooting High Redo Generation Issues[Article ID 782935.1]
http://4dag.cronos.be/village/dvp_forum.OpenThread?ThreadIdA=42511
http://www.orafaq.com/forum/t/180221/0/

3 comments: