How to Purge Apply Spilled Transactions in Streams Environment. (Doc ID 472440.1)

In this Document Goal   Solution   References APPLIES TO: Oracle Database - 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 is normal parameter with 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 = 'APP_TEST'; 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('APP_TEST',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. Bug 9825314  Apply does not clean up spilled messages is fixed in 12.1.0.1 and 11.2.0.3.