Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

5 Answers

up vote 2 down vote accepted

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

share|improve this answer
    
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 '10 at 9:13
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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