Oracle 11g database archive log got increased suddenly in production server

archive-logoracleoracle-11g-r2performancerman

My production db archivelog got increased suddenly one night. Around 200 MB of archivelog were generating in FRA within a minute. It got into normal condition after 15 hours from the start of this issue. We haven't done any unusual transactions during this time period.

There was no messages in alert or trace of db regarding this enormous increase of archivelog.

What could be the reason for this unusual behaviour of Oracle database?

Is this an issue with our database or Oracle ADF application or related to our servers?

Please let me if you require more inputs on this.

Any help will be appreciated.

Best Answer

If what you say is true (no unusal transactions), then I say this is typically caused by Segment(Compression) Advisor going rogue.

Compression Advisor still Generates Redo Log on 11.2.0.2 (Doc ID 1324598.1)

Huge Redo Generation by Compression Advisor (Doc ID 1969765.1)

Sometimes it copies your tables to check if you could reclaim space by reorganizing them, in the maintenance window (nights, weekends). This may generate a high amount of redo and archivelogs depending on the size of the table.

This is the reason I always disable the maintenance task for it (also because I find it useless, and I can run my own diagnosis whenever I need to):

begin
  dbms_auto_task_admin.disable
  (
    client_name => 'auto space advisor', 
    operation => null, 
    window_name => null
  );
end;
/

You can create an AWR/Statspack report of the problematic time period, it is quite easy to recognize if it was the culprit (CREATE TABLE statements with weird/specific names that was not executed by the DBA/application/regular jobs).

Related Question