OGG-集成模式抽取與資料庫參數streams_pool_size關係

一、學習目標

   Oracle資料庫,使用OGG集成模式抽取進程啟動時,如果沒有配置合理的streams_pool_size參數可能會過一段時間就報錯abend!

    那麼我們如何配置這個參數的大小?如何計算?

 

二、參考文檔

    從OGG官網檢索這個DB參數,沒有看到合理的建議資訊!

 2.1 MOS

OGG-02045: Database does not have streams_pool_size initialization parameter configured.
The database initialization parameter streams_pool_size is not set correctly to support integrated capture.
Set database initialization parameter streams_pool_size. For sizing recommendations, see the Oracle GoldenGate reference
documentation. Integrated Extract
/ Replicat and STREAMS_POOL_SIZE (Doc ID 2078459.1) Oracle GoldenGate - Version 11.2.1.0.0 and later GOAL What is the recommended STREAMS_POOL_SIZE for Integrated Extract (IE) and Integrated Replicat (IR) SOLUTION The ogg reference guide recommends to have 1GB STREAMS_POOL_SIZE per integrated extract + additional 25 percent Snippet from ogg oracle install guide http://docs.oracle.com/goldengate/1212/gg-winux/GIORA.pdf ------------------------------------------------------ By default, one integrated capture Extract requests the logmining server to run with MAX_SGA_SIZE of 1GB and a
PARALLELISM of 2. Thus, if you are running three Extracts in integrated capture mode in the same database instance,
you need at least 3 GB of memory allocated to the Streams pool. As best practice, keep 25 percent of the Streams pool available.
For example, if there are three Extracts in integrated capture mode, set STREAMS_POOL_SIZE to the following: 3 GB + (3 GB * 0.25) = 3.75 GB --------------------------------------- The Integrated replicat also needs to have 1GB STREAMS_POOL_SIZE per process and additional 25 percent though it
is not been explicitly mentioned in the guides. 推薦集成捕獲進程數量+ 集成捕獲數量*0.25, 默認1個進程1G!

 

2.2 相關文檔

//www.oracle-scn.com/memory-requirement-for-oracle-goldengate-integrated-extract/

     

1. OGG集成模式與資料庫記憶體區域streams_pool_size之間的關係
集成模式的情況下,是從streams_pool中申請記憶體區域,Streams_pool是Oracle SGA記憶體的一個組件,streams_pool中,
申請的記憶體大小取決於有多少個integrated extract的進程數量,以及考慮其它的進程!
2.抽取進程參數 TRANLOGOPTIONS INTEGRATEDPARAMS(max_sga_size 100, parallelism 1) 上述參數中,max_sga_size 代表的是streams pool size 的大小! 非db 參數sga_max_size 3.DB參數streams_pool_size 調整多大? 1> streams_pool_size >1g,則ogg 佔用的max_sga_size=1g; 如果streams_pool_size <=1g,則ogg 佔用的max_sga_size = streams_pool_size*75%; 2> 默認沒設置streams_pool_size 參數大小!則max_sga_size(streams size)佔用shared_pool_size 10%,最大1G, 因此!需要考慮shared pool大小! 3> 使用動態SGA,則可以通過查詢視圖v$sga_dynamic_components獲取streams_pool的使用情況!

 

2.3 相關報錯

OGG-02050 Not enough database memory to honor requested MAX_SGA_SIZE
OGG-02077: Extract encountered a read error in the asynchronous reader thread and is abending: {0}

 

三、小結

建議使用集成模式抽取的情況下,設置資料庫參數

STREAMS_POOL_SIZE ,參數大小= 抽取進程的數量*1G +抽取進程數量*1G*0.25 
(1G代表分配給每個抽取進程的記憶體,25%是給每個抽取進程streams size 考慮的空閑空間)
另外就是文檔中的各種舉例都是寫的是集成抽取,集成複製方面並沒明確說明! 但是MOS文檔的包含範圍加上了集成複製!
因此站在安全的角度也是考慮同樣的分配! 當然如果系統不是特別重要,很多細節可以忽略,報錯在處理也是一樣的。