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

Question

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

Answer 1

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

Answer 2

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

http://www.oracle.com/technology/oramag/oracle/05-jul/o45dba.html

Answer 3

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.

Answer 4

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

Answer 5

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:

DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE -2';

If RMAN significantly reduces the query run speed of your database, you can add to the BACKUP DATABASE string this keywords:

DURATION 1:00 PARTIAL MINIMIZE LOAD

Where, 1:00 - time 1 hour for which should be backed up.