In this Document
Symptoms Cause Solution References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.3 to 11.2.0.3 [Release 10.2 to 11.2]
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 transaction ID: 3.30.13393
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.
SOLUTION
1) Apply one off patch for BUG 6640950 or Merge patch 6640950
Bug 6640950 fix is included in 10.2.0.5.7 PSU and later
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_NETDB 3 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
BUG:6640950 - UPDATE OF LONG CAN CAUSE CAPTURE LCR ENQ LOOP / LARGE TXN / SPILL ON APPLY SIDE
