Why Oracle database generates lots of archived log per day when there is a small changes to the database

oracle

I'm currently using Oracle database 11g on RHEL5. My database generates a lot of archived logs per day even there is a small amount of changes to the database (about 500 transactions a day). I also check the size of my datafile (users tablespace); its size increases about 20MB a day but archived log generates between 5GB to 10GB a day.

Can anyone tells me what causes this issue?
Is there any solutions to check why it generates more archived?
How can I reduce it?

Best Regards,

Sarith

Best Answer

May be worth looking at the timestamps on the files and see if it is evenly spread or if you get a bunch generated at one time (perhaps corresponding with some batch activity).

A query like the following will show you current sessions generating redo.

select s.sid, n.name, s.value, sn.username, sn.program, sn.type, sn.module
from v$sesstat s 
  join v$statname n on n.statistic# = s.statistic#
  join v$session sn on sn.sid = s.sid
where name like '%redo entries%'
order by value desc;

If you are licensed for AWR you can look at the history tables too.

What is the total size of the database ? I'd suspect some large table(s) being truncated and reloaded with very similar data on a daily basis

Related Topic