How to Purge Apply Spilled Transactions in Streams Environment. [ID 472440.1]
来源:互联网 发布:python编写程序 示范 编辑:程序博客网 时间:2024/06/05 06:47
最近在搞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.2Information in this document applies to any platform.
Goal
How to Purge Apply Spilled Transactions.Solution
Apply SpillApply 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
* 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');
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:
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 ScriptNOTE:437838.1 - Streams Recommended Patches
Attachments
streams_purge_apply_spill_txn.plb (1.16 KB)
- How to Purge Apply Spilled Transactions in Streams Environment. [ID 472440.1]
- How to Purge Apply Spilled Transactions in Streams Environment. (Doc ID 472440.1)
- How to add a DELAY in applying transactions in GoldenGate?
- How To Avoid ORA-04030/ORA-12500 In 32 Bit Windows Environment [Video] [ID 373602.1]
- How To Add/Increase The Size Of Redo Log Files In Rac Environment? (文档 ID 779306.1)
- How to create an OCM Response file to apply patch in silent mode - opatch silent (Doc ID 966023.1)
- How to remove (purge) Disconnected Mailboxes
- How to write Alphablox JSR168 Portlet in WebSphere Environment
- How to build the environment of XPCOM in Windows XP
- How to Setup X500 Environment in Sun ONE Directory
- How to build the environment of MSYS in the windows?
- How to setup django, python environment in ubuntu
- How to rename ASM instances in RAC or clustered environment
- 11g How To Enable Archive Logging In RAC Environment
- Interview Series: How To Implement Singleton In Concurrent Environment?
- How to setup CI development environment in Linux
- How to check environment variable in robot framework?
- How to Apply Visa of UK in Beijing
- linux 自动备份dropbox
- Hibernate 对象 Transient 瞬时 Persist 持久 Detached 脱管 状态 图解
- IE中,ActiveX控件中控制Internet Explorer 事件
- Android适配 获取手机屏幕的分辨率
- faplayer ubuntu 编译
- How to Purge Apply Spilled Transactions in Streams Environment. [ID 472440.1]
- 信用卡的利与弊
- iphone开发 内存管理
- 在varchar2与clob之间进行类型转换
- MFC 之Tab控件使用
- 开发中巧用Enum枚举类型
- jquery ui tabs详解(中文)
- SQL Server 2008 R2 性能计数器详细列表(四)
- 农历5月16