Oracle Stream忽略大事務 C001: large txn detected
源資料庫
作業系統:AIX 6.1
資料庫版本:Oracle 10.2.0.4 RAC單向schema級別複製至非RAC
目標資料庫
作業系統:Centos 5.4
資料庫版本:Oracle 11.2.0.2
現象:apply長時間沒有同步,apply
error中無報錯資訊,檢視captrue 端資料庫alert發現有下面輸入資訊
1) 檢查源庫的alert log,發現大量large txn detected
C001: large txn detected (76042142 LCRs), xid: 0x0006.023.0000b016
Thu Oct 27 04:45:03 2011
C001: large txn detected (78290526 LCRs), xid: 0x0006.023.0000b016
Thu Oct 27 04:55:03 2011
C001: large txn detected (80502433 LCRs), xid: 0x0006.023.0000b016
Thu Oct 27 05:05:03 2011
C001: large txn detected (82753961 LCRs), xid: 0x0006.023.0000b016
Thu Oct 27 05:15:04 2011
C001: large txn detected (85000771 LCRs), xid: 0x0006.023.0000b016
Thu Oct 27 05:25:03 2011
C001: large txn detected (87087911 LCRs), xid: 0x0006.023.0000b016
Thu Oct 27 05:35:04 2011
C001: large txn detected (89236244 LCRs), xid: 0x0006.023.0000b016
Thu Oct 27 05:45:04 2011
C001: large txn detected (91461549 LCRs), xid: 0x0006.023.0000b016
Thu Oct 27 05:55:04 2011
C001: large txn detected (93237056 LCRs), xid: 0x0006.023.0000b016
Thu Oct 27 06:05:05 2011
C001: large txn detected (95410577 LCRs), xid: 0x0006.023.0000b016
Thu Oct 27 06:15:05 2011
C001: large txn detected (97406466 LCRs), xid: 0x0006.023.0000b016
Thu Oct 27 06:25:05 2011
C001: large txn detected (99577844 LCRs), xid: 0x0006.023.0000b016
Thu Oct 27 06:35:05 2011
C001: large txn detected (101705806 LCRs), xid: 0x0006.023.0000b016
2) 查詢活動事務和事務號
在主庫上面執行下面操作
select xid,
xidusn,
xidslt,
xidsqn,
mining_status,
xidusn || '.' || xidslt || '.' || xidsqn transaction_id
from v$logmnr_transaction
where mining_status = 'ACTIVE';
發現有大量xid為0003000D0000945E的logmnr事務,注意這裡的transaction_id下面需要用到
3) 查詢事務的sql_id及其對應的語句
SQL>
select distinct sql_id from gv$active_session_history where xid =
'0003000D0000945E';
SQL_ID
-------------
0k91g13q8nhqc
SQL> select sql_text from
dba_hist_sqltext where sql_id in ('0k91g13q8nhqc');
分析這個語句,為建立一個超過1億條記錄的臨時表,和開發人員確認後這個表可以不進行同步,只是一箇中間表(如果需要使用當然也可以使用其它方式進行同步,如expdp,impdp)
4) 停止捕捉程式、忽略活動事務、啟動捕捉程式
-- #停止Capture程式
begin
dbms_capture_adm.stop_capture(
capture_name => 'CAPTURE_RACDB');
end;
/
-- #這裡的事務ID為了2中的transaction_id
execute dbms_capture_adm.set_parameter('CAPTURE_RACDB','_ignore_transaction','3.13.37982');
-- #啟動Capture程式
begin
dbms_capture_adm.start_capture(
capture_name => 'CAPTURE_RACDB');
end;
/
Apply端忽略大事務
當然從apply端也可以忽略大事務
1) 檢視大事務和原庫執行時間,執行記錄數
SELECT t.xidusn||'.'||t.xidslt||'.'||t.xidsqn transaction_id ,message_count,t.first_message_create_time FROM DBA_APPLY_SPILL_TXN t;
2)忽略大事務
-- #停止Apply程式
begin
dbms_apply_adm.stop_apply(
apply_name => 'APPLY_RACDB_TO_ORA72');
end;
/
SQL> exec dbms_apply_adm.set_parameter('APPLY_RACDB_TO_ORA72','ignore_transaction','6.35.45078');
PL/SQL procedure successfully completed
-- #啟動Apply程式
BEGIN
dbms_apply_adm.start_apply(
apply_name => 'APPLY_RACDB_TO_ORA72');
end;
/
再檢視執行狀態
SELECT sid,
apply_name,
server_id servid,
state,
commitscn,
total_messages_applied total_mess,
apply_time,
applied_message_number app_mess_num,
applied_message_create_time app_create_time
FROM v$streams_apply_server;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/21605631/viewspace-710294/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle Stream忽略大事務 C001: large txn detectedOracle
- oracle large poolOracle
- mysql大事務MySql
- Oracle查詢回滾大事務所需時間Oracle
- ORACLE LARGE MEMORY(zt)Oracle
- 為什麼要避免大事務以及大事務如何解決?
- Oracle Executable Binary Mismatch DetectedOracle
- ORACLE STREAM ERROROracleError
- restart oracle streamRESTOracle
- Oracle simple streamOracle
- STREAMS筆記(9) 大事務 & 長事務筆記
- oracle 在 aix上large page特性OracleAI
- The installer has not detected any Oracle 10.2.0.4.0Oracle
- WARNING: Oracle executable binary mismatch detectedOracle
- Oracle stream案例分享Oracle
- oracle stream pool sizeOracle
- Spring大事務到底如何優化?Spring優化
- Github :git push檔案過大報錯remote: error: GH001: Large files detected.處理方法GithubREMError
- Oracle Stream概述與配置Oracle
- Oracle Stream Replication 技術Oracle
- oracle stream學習(一)Oracle
- Oracle Stream 深入探討Oracle
- oracle 10 g stream tableOracle
- Oracle Stream(2)--Streams功能Oracle
- Oracle Stream(1)--Streams概述Oracle
- GoldenGate--大事務拆分成小事務定位問題Go
- 頭疼,大事務問題如何解決?
- oracle 10g table streamOracle 10g
- oracle 10g user streamOracle 10g
- oracle 10g stream weihuOracle 10g
- Oracle Global Enqueue Services Deadlock detected錯誤詳解OracleENQ
- Oracle USE_LARGE_PAGES初始化引數Oracle
- 注意Spring事務這一點,避免出現大事務Spring
- oracle中容易忽略的後臺程式Oracle
- oracle stream之schema級複製Oracle
- Oracle Stream配置詳細步驟Oracle
- oracle資料庫升級的時候oracle universal installer has detected........Oracle資料庫
- LOB(large object)Object