SYSAUX tablespace grows quite fast due to Apply spilling [ID 556183.1]

早上发现从库的SYSAUX表空间自动增长的很大，虽然原因并不适合11.2.0.3，但是依然记录于此。

Modified 14-MAR-2012     Type PROBLEM     Status PUBLISHED

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References

---

Applies to:

Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.2.0.3 to 11.1.0.6

Symptoms

SYSAUX tablespace is growing uncontrollably and no updates are being propagated from source to
target

We can find out the transaction ID on the TARGET side from the healthcheck section titled:

++ APPLY SPILLED TRANSACTIONS ++

No matter how many times this transaction is set to "_ignore_transaction" and purged on the
TARGET  site, the Source site will continue to send this transaction over and it will show up in this
section. The way to clear up this transaction is to issue the "_ignore_transaction" on the
source and then issue the "_ignore_transaction" and sys.purge_spill_txn on the TARGET. An example is
followed below:

OnTarget's healthcheck report, the ++ APPLY SPILLED TRANSACTIONS ++section shows the following  tranaction ID: 3.30.13393

Changes

Cause

Any extremely large transaction can cause SYSAUX tablespace to grow.
or
It could be bug 6640950 UPDATE OF LONG CAN CAUSE CAPTURE LCR ENQ LOOP / LARGE TXN / SPILL ON APPLY SIDE
An update of a LONG column from a large long (>4K) to a small long (<4K) causes Streams Capture to enter into an infinite loop and an unusually large spill transaction at the Apply side.

Even after purging Apply Spillage transaction using  Unpublished doc ID 472440.1>>  How to Purge Apply Spill the SYSAUX tablespace is not reduced.

As the note/doc id is unpublished customers will not be able to view Metalink

Solution

1) Apply one off patch for BUG 6640950 or Merge patch 6640950

2) For immediate solution follow the workaround of _ignore_transaction

 

On Source

exec dbms_capture_adm.stop_capture('CAPTURE_NETDB');
exec dbms_capture_adm.set_parameter('CAPTURE_NETDB',
                                 '_ignore_transaction','3.30.13393');
exec dbms_capture_adm.start_capture('CAPTURE_NETDB');


On Target
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Mar 1 08:50:04 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: strmadmin
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

exec dbms_apply_adm.stop_apply('APPLY_NETDB');

exec dbms_apply_adm.set_parameter('APPLY_NETDB',
                                '_ignore_transaction','3.30.13393');

exec dbms_apply_adm.start_apply('APPLY_NETDB');

SELECT * FROM DBA_APPLY_SPILL_TXN;
APPLY_NAME XIDUSN XIDSLT XIDSQN FIRST_SCN  MESSAGE_COUNT FIRST_MES SPILL_CRE
------------- --------- ---------
APPLY_NETDB3         30     13393   395069754  202800 29-FEB-08 01-MAR-08


connect as sysdba
exec dbms_apply_adm.stop_apply('APPLY_NETDB');

execute dbms_apply_adm.set_parameter('APPLY_NETDB',
                                     '_ignore_transaction','3.30.13393');
exec sys.purge_spill_txn('APPLY_NETDB','3.30.13393');

SELECT * FROM DBA_APPLY_SPILL_TXN;

exec dbms_apply_adm.set_parameter('APPLY_NETDB','_ignore_transaction',null);
exec dbms_apply_adm.start_apply('APPLY_NETDB');
SELECT * FROM DBA_APPLY_SPILL_TXN;

Note : The data would be inconsistent as  transaction is skipped.

 

Save the procedure purge_spill_txn as streams_purge_apply_spill_txn.plb and execute

create or replace procedure purge_spill_txn wrapped a000000 1 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 7 7b1 40a LjTu5SYzX5Y445YIWAJ/qtCS3Y8wgzvDTK6GfC80weQYlOby91EYwHo3MLi3eS1abuDagL6c Fqk3/PIpbCa5yJKGIJAaT2f7KWjl8vxVgUeK2FtlM39aFBX1pyR1oHVK1Iag5Nrerd051qV7 lFbmDpVE5HAlJBebhcep/ulWfz2E95tw9pbdLyroLedkHjRIdgeFNm5uNdACniipPlt+uUSD yEPToXfpQqO7JvsoaqAfwryDaLf0pYikfHAJsPKAI0IwCscwMGeoIrYtWnilloS8BePRM77K GlVaYzhonFdattdqn42FEG+bT7SjQxJkxQmyW47zgQ1EDHp6VFtHAkofdO15e8vuKfpX4TI7 g/IMkfeDIBixOImP3T2tCQ+HUq0K/QtPeagb4QSbyszgIr8Fuc/clsV39pacrMDfq8mv6mi9 qdRNEGus1Sa6pChoYv1uw7wELhyph6meSHdYcmLhsdb4wXbCKfXk4evkOjyOArJVYNzIerqL nEI3zJSz58P0k6owKKNM4vTD9gwhKErL41tzfceOM2hkfHzIVXrBY74KL7SGIF8BQzyO49vN tJG2x1sDJvZiNsrMSQlQ7/EVNL9F/WgtRL/knd3wG2ki7Cr1G3KhBpqCgoK7r4wkKR7GzNsy Yn3RNKRtxAMV9guxOb8NzFwNRuXXKM/9Jrzli8J/IACr9QnrKZ6tJZRLhzrwN+gTFdfUY/pr R0jUORicUUyJvvpBGHOgZaOM7l4IaRrrVpavAgJUINHERxe3BdghcIyAQ31C4V4lvx0xwQU8 wwNvkoLFLGsWT5e2tMfqBIcDZrW7KtcdEUgOI+4vc0bbQ8xG+7AaeMITenaSV1B6bys4+38N oeUNyRBYrDf4fiHiXdZMkvcdJtBcdUV0c1fdKDawNvz/o8vyiGJ9t6qI6lPKbKyDiG2hulTb OqAIaT48gFyxErHPCpNSyog2KylMv7irwGN5oQKricl7zBfRx5CkgLSXT+ZxTAiY/CG3NI53 LcUQ+FtXd/Ni /

References

NOTE:472440.1 - How to Purge Apply Spilled Transactions in Streams Environment.