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,


5 Answers 5


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

  • The size of the database is 10GB. The application data is stored in the users tablespace only, it's about 4GB. Remainder tablespaces are system, sysaux and undo and temp tablespace.
    – Sarith
    Mar 3, 2010 at 9:13

As suggested in another thread, you could use LogMiner to discover what transactions are logged:


  • This link is dead. May 26, 2016 at 9:12

UPDATE statements don't necessarily change the size of a table or row, but must be captured in redo, which is what drives the size of archive logs.

Uncommitted transactions -- those which roll back -- make no permanent changes to the database, but still generate redo.

Table DML activity which causes indexes to be updated (does the application do a lot of DELETEing and INSERTing?) also cause indexes to be updated, as do UPDATE statements which change indexed columns.

In short, measuring archive log size is a measure of change activity -- growth is a different and often unrelated metric. What you may wish to do is query ALL_TAB_MODIFICATIONS to show exactly how busy from a write perspective your tables actually are since last analyzed, as in 10g+, all tables are automatically monitored for DML activity.


Materialized view refreshes can generate a lot of redo. Just a thought.


A simple solution of this problem is

1) Setting Block Change Tracking in Oracle for speedup of Incremental backup
http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmbckba.htm # BRADV8125

2) Run Incremental Backup Level 1 of DB and archivelogs (eg 2 times a day) with the addition of the next line to the end of RMAN script:

If RMAN significantly reduces the query run speed of your database, you can add to the BACKUP DATABASE string this keywords:
Where, 1:00 - time 1 hour for which should be backed up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.