How to Purge Apply Spilled Transactions in Streams Environment. [ID 472440.1]

最近在搞stream做全库复制，出现了一些问题，查阅了mos的文档 记录于此

Modified 11-APR-2012     Type HOWTO     Status PUBLISHED

In this Document
  Goal
  Solution
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.3 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Goal

How to Purge Apply Spilled Transactions.

Solution

Apply Spill

Apply Spillover, introduced in 10.2, allows Streams to handle large transactions and long running transactions. A large transaction is a transaction with more than 10000 Logical Change Records (LCRs). The size of a large transaction can be set by the user via the apply parameter TXN_LCR_SPILL_THRESHOLD.  LCRs will start to spill to the apply spill table when the number of LCRs in the transaction reaches the value of TXN_LCR_SPILL_THRESHOLD.  The apply spill table is located in SYSAUX and uses a partitioned table to store the LCRs.   Each transaction is a separate partition of the table (the key is apply_name and transaction id). When a transaction has been applied and it is time to remove the LCRs for that transaction, a drop partition is performed for the transactionid. This is a very quick operation and the space in SYSAUX is returned. If an error should occur while applying the transaction, the transaction will remain in the apply spill table and be marked as an error.

Purging Apply Spill

 

* Note this procedure should only be used under the guidance of Support.  Purging a transaction from the apply spill table can result in data inconsistencies in the user tables (ORA-1403 or other apply errors).  This should not be done unless the customer completely understands the impact of this action. When the fix for  unpublished Bug 5736709 is implemented _ignore_transaction will be sufficient to ignore any kind of transaction whether it is spilled or not.


* The following procedure is written to purge just one transaction only, if there are a large number of spilled transactions and you have reached the decision of deleting them all,  you can automate the steps like the following :

Stop all apply processes. For this example, we have one apply process and the apply name is APP_TEST
exec dbms_apply_adm.stop_apply('APP_TEST');

- get count(transactions) from the apply_spill_table ;
- using a for loop from 1 to number of spilled txns
- perform the steps inside the loop :

from step 3 to step 7

To purge a transaction from the apply spill table, use the following process

1. Download the PLB file: streams_purge_apply_spill_txn.plb

2. Load this procedure into the SYS schema for the target database using sqlplus. This creates the procedure purge_spill_txn in the SYS schema.

connect / as sysdba
@streams_purge_apply_spill_txn.plb

 

The procedure signature is

purge_spill_txn( apply_name , ignore_txn_id , delete_only )

where the parameters have the following meaning

apply_name:- is the name of the apply process for the spilled transaction to be ignored.

ignore_txn_id :- is the transaction id of the spilled transaction.This transaction id must also be listed
in the _ignore_transaction parameter of the apply process.

delete_only :- is a boolean value. The default is FALSE which will drop the partition of the spilled transaction. Set to TRUE if you just want to remove the spilled transaction.

3. Check the DBA_APPLY_SPILL_TXN view to identify the transaction id to remove  

select apply_name, xidusn||'.'||xidslt||'.'||xidsqn txn_id, first_scn, first_message_create_time, message_count, spill_creation_time from dba_apply_SPILL_TXN;

4. Stop all apply processes. For this example, we have one apply process and the apply name is APP_TEST

exec dbms_apply_adm.stop_apply('APP_TEST');

5. Set the apply parameter _IGNORE_TRANSACTION with the relevant transaction id from step 3.
For this example, the transaction id is 10.22.333.

exec dbms_apply_adm.set_parameter('APP_TEST','_ignore_transaction','10.22.333');

IGNORE_TRANSACTION made as normal parameter in 11.2
Reference: Document 1355121.1 >

6. Run the purge_spill_txn procedure supplying the apply name and the transaction id.

exec sys.purge_spill_txn('APP_TEST','10.22.333');

7. Check the DBA_APPLY_SPILL_TXN view to confirm that the transaction has been removed.

select apply_name, xidusn||'.'||xidslt||'.'||xidsqn txn_id, first_scn, first_message_create_time, message_count, spill_creation_time from dba_apply_SPILL_TXN;


8. Restart all the apply processes

exec dbms_apply_adm.start_apply('APP_TEST');


9. Clear the transaction id from the apply parameters

exec dbms_apply_adm.set_parameter('APP_TEST','_ignore_transaction',null);

Note:

Sometimes, apply-spilled transactions are not cleaned up and get stuck in the spill table forever even if the transaction has definitely been applied ,this is very likely to be the unpublished Bug 9825314.

If the transaction has definitely been applied there is no need to use the _ignore_transaction parameter so we can more easily remove a large number of spilled transactions when the Apply process has been shut down.

--This will only removed spilled transactions which have DEFINITELY been applied.
--This is the reason that we do not need to use the _ignore_transaction parameter because the commit_scn in streams$_apply_milestone is equivalent to APPLIED_MESSAGE_NUMBER which is the point to which we have DEFINITELY applied.

ex:
spool <directory>/spillpurge.lst
set serveroutput on size 10000000

declare
txid varchar2(50);
maxscn number;
counter number := 0;
cursor c is
select xidusn, xidslt, xidsqn from streams$_apply_spill_txn
where commit_scn < maxscn
and applyname = 'CDC$A_TARGETMI_DIMENSION_S';
begin
select commit_scn into maxscn from streams$_apply_milestone;
dbms_output.put_line ('Starting the purge process');
for r in c loop
txid := r.xidusn||'.'||r.xidslt||'.'||r.xidsqn;
sys.purge_spill_txn('CDC$A_TARGETMI_DIMENSION_S',txid);
dbms_output.put_line ('Purging transaction...'||txid);
commit;
counter := counter + 1;
end loop;
dbms_output.put_line ('Transactions purged = '||counter);
dbms_output.put_line ('Purge process finished');
end;
/

To permanently resolve this issue apply the fix for unpublished Bug 9825314.

References

NOTE:273674.1 - Streams Configuration Report and Health Check Script
NOTE:437838.1 - Streams Recommended Patches

Attachments streams_purge_apply_spill_txn.plb (1.16 KB)