Last week at a customer site, we experienced an excessive redo log generation rapidly filling up the FRA (Flash/Fast Recovery Area) with archivelog files. This eventually caused the database to crash.

As usual, we started the problem analysis by scanning the alert.log file. The trouble began at 7 am when the DEFAULT_MAINTENANCE_PLAN started.

In less than 10 minutes more than 100 archivelog files of 50 Mb each were generated (cf. Below):


Setting Resource Manager plan SCHEDULER[0x3109]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Thu Jun 30 07:00:38 2011
Thread 1 advanced to log sequence 2648 (LGWR switch)
…....
…....
Thu Jun 30 07:09:44 2011
Thread 1 advanced to log sequence 2749 (LGWR switch) Errors in file X:\ORACLE\diag\rdbms\mydb_site1\mydb\trace\mydb_m000_2196.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 5368709120 bytes is 100.00% used, and has 0 remaining bytes available.


As this output did not reveal that much, we started generating the AWR reports for the period when the database crashed (see AWR below).

Here are the top SQL consumers sorted by different criteria:

Based on these reports, we observed that the SQL Module consuming all the resources was always the DBMS_SCHEDULER which calls the stored procedure dbms_space.AUTO_SPACE_ADVISOR_JOB.

What does it mean?

During the default Oracle maintenance window, the DBMS.SCHEDULER package starts the AUTO_SPACE_ADVISOR_JOB job. This job's purpose is to trigger the DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC stored procedure. This last one itself triggers the Automatic Segment Advisor which uses the Compression Advisor in order to identify all the segments which should benefit from table compression (Oracle Compression Feature).

Going deeper in our investigations about this Compression Advisor, we found out that this problem was due to bug 8896202 (Oracle Support) which should have been fixed in version 11.2.0.2 (cf. below). However, our customer was still hit by this issue.

METALINK

How Does Compression Advisor Work? [ID 1284972.1]
Compression advisor is a new advisor added in 11gR2.
It is shipped with Segment Advisor, and will be executed whenever Segment Advisor task run.
We can't disable it separately at this point of time, but an enhancement request has been submitted to separate it from Segment Advisor.
Segments that are at least 10MB in size and with at least 3 indexes will be evaluated for compressibility (but not all the tables which meet the requirements will be evaluated, an internal algorithm will decide which segment should be evaluated).
The dbms_compression package will be invoked to estimate how much space can be saved. It will create two internal tables (DBMS_TABCOMP_TEMP_UNCMP, DBMS_TABCOMP_TEMP_CMP) in the tablespace which hosts the segment.
DBMS_TABCOMP_TEMP_UNCMP is created with 99% sample blocks by default.
DBMS_TABCOMP_TEMP_CMP is created with compress option based on DBMS_TABCOMP_TEMP_UNCMP.
COMPRESSION_RATIO is generated by comparing DBMS_TABCOMP_TEMP_UNCMP and DBMS_TABCOMP_TEMP_CMP. These two tables will be dropped immediately after the estimation.
The recommendations of compressing table can be obtained from EM DB console.
Please note the following impacts:
Without applying unpublished bug 8896202, excessive redo log will be generated. (This is fixed in 11.2.0.2) however this bug still occurred on our site.
But be aware that bug 8896202 might not eliminate redo generation if object containing LOB data which is not having NOCACHE NOLOGGING option; check note  976722.1 for details.
Ora-20000 against tables DBMS_TABCOMP_TEMP_CMP and DBMS_TABCOMP_TEMP_UNCMP might be seen when performing dbms_stats
Extra disk space might be required to host DBMS_TABCOMP_TEMP_CMP and DBMS_TABCOMP_TEMP_UNCMP, it can result in space pressure in some cases.

So we again had a look at the AWR report and noticed that the top SQL statement was the TEMPORARY table creation against a table containing LOB segments:

Root cause and Workaround

"If a source table contains a LOB segment with "CACHE/LOGGING" enabled then despite the database NOLOGGING clause setting, Oracle will still generate excessive redo logs as the NOLOGGING for LOBS can only be invoked when the LOB segment is in 'NOCACHE and NOLOGGING'."

In our case we did not have cache enabled, however, our customer still was affected by the bug!

Therefore we implemented the following workaround:

• Disable the Automatic Segment Advisor.

SQL>execute dbms_scheduler.disable('sys.auto_space_advisor_job');

or

Of course we are now awaiting (as several customers) the patch set 11.2.0.3 in order to verify if the bug is fixed even if the "auto space advisor" is enabled.

Hope this helped