給客戶通過streams配置了schema級別的同步

warehouse發表於2013-01-03

記錄一個大致的過程供大家參考,穩定了執行了近2年,最近出了一些問題,今天重新配置了一遍。

[@more@]

1、在源和目標端建立流管理使用者:

SQL> create user strmadmin identified by lnmsairc001 default tablespace tbs_streams;

使用者已建立。

SQL> grant dba to strmadmin;

授權成功。

2、在源和目標端建立db link,要保證db link的名字和對方的global_name的名字一致,因為在配置前期需要把引數global_names設定為true

SQL> create database link lnmsacx connect to strmadmin identified by lnmsairc001 using 'lnmsacx';

資料庫連結已建立。

SQL> select sysdate from dual@lnmsacx;

SYSDATE
-------------------
2011-08-10 11:09:34

SQL>
--=========================================
SQL> create database link lnmsa connect to strmadmin identified by lnmsairc001 using 'lnmsa';

資料庫連結已建立。

SQL> select sysdate from dual@lnmsa;

SYSDATE
-------------------
2011-08-09 11:11:48

SQL>
3、例項化streams環境以及初始化資料:

-- connect as streams administrator to lnmsa1
PROMPT Connecting as streams administrator to site 1
CONNECT strmadmin/lnmsairc001@lnmsa1

--
-- Set up queue "STRMADMIN"."LNMSA_CAP_QUEUE"
--
BEGIN
dbms_streams_adm.set_up_queue(
queue_table => '"STRMADMIN"."LNMSA_CAP_QUEUE_TABLE"',
storage_clause => NULL,
queue_name => '"STRMADMIN"."LNMSA_CAP_QUEUE"',
queue_user => '');
END;
/
--
-- PROPAGATE changes for schema LNMSAIRC
--
DECLARE
version_num NUMBER := 0;
release_num NUMBER := 0;
pos NUMBER;
initpos NUMBER;
q2q BOOLEAN;
stmt VARCHAR2(100);
ver VARCHAR2(30);
compat VARCHAR2(30);

BEGIN
BEGIN
stmt := 'BEGIN dbms_utility.db_version@LNMSACX(:ver, :compat); END;';
EXECUTE IMMEDIATE stmt USING OUT ver, OUT compat;
-- Extract version number
initpos := 1;
pos := INSTR(compat, '.', initpos, 1);
IF pos > 0 THEN
version_num := TO_NUMBER(SUBSTR(compat, initpos, pos - initpos));
initpos := pos + 1;

-- Extract release number
pos := INSTR(compat, '.', initpos, 1);
IF pos > 0 THEN
release_num := TO_NUMBER(SUBSTR(compat, initpos,
pos - initpos));
initpos := pos + 1;
ELSE
release_num := TO_NUMBER(SUBSTR(compat, initpos));
END IF;
ELSE
version_num := TO_NUMBER(SUBSTR(compat, initpos));
END IF;

-- use q2q propagation if compatibility >= 10.2
IF version_num > 10 OR
(version_num = 10 AND release_num >=2) THEN
q2q := TRUE;
ELSE
q2q := FALSE;
END IF;

EXCEPTION WHEN OTHERS THEN
q2q := FALSE;
END;


dbms_streams_adm.add_schema_propagation_rules(
schema_name => '"LNMSAIRC"',
streams_name => '"PROP_LNMSA"',
source_queue_name => '"STRMADMIN"."LNMSA_CAP_QUEUE"',
destination_queue_name => '"STRMADMIN"."LNMSA_APP_QUEUE"@LNMSACX',
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => TRUE,
source_database => 'LNMSA',
inclusion_rule => TRUE,
and_condition => NULL,
queue_to_queue => q2q);
END;
/
--
-- PROPAGATE changes for schema X3SYS
--
DECLARE
version_num NUMBER := 0;
release_num NUMBER := 0;
pos NUMBER;
initpos NUMBER;
q2q BOOLEAN;
stmt VARCHAR2(100);
ver VARCHAR2(30);
compat VARCHAR2(30);

BEGIN
BEGIN
stmt := 'BEGIN dbms_utility.db_version@LNMSACX(:ver, :compat); END;';
EXECUTE IMMEDIATE stmt USING OUT ver, OUT compat;
-- Extract version number
initpos := 1;
pos := INSTR(compat, '.', initpos, 1);
IF pos > 0 THEN
version_num := TO_NUMBER(SUBSTR(compat, initpos, pos - initpos));
initpos := pos + 1;

-- Extract release number
pos := INSTR(compat, '.', initpos, 1);
IF pos > 0 THEN
release_num := TO_NUMBER(SUBSTR(compat, initpos,
pos - initpos));
initpos := pos + 1;
ELSE
release_num := TO_NUMBER(SUBSTR(compat, initpos));
END IF;
ELSE
version_num := TO_NUMBER(SUBSTR(compat, initpos));
END IF;

-- use q2q propagation if compatibility >= 10.2
IF version_num > 10 OR
(version_num = 10 AND release_num >=2) THEN
q2q := TRUE;
ELSE
q2q := FALSE;
END IF;

EXCEPTION WHEN OTHERS THEN
q2q := FALSE;
END;


dbms_streams_adm.add_schema_propagation_rules(
schema_name => '"X3SYS"',
streams_name => '"PROP_LNMSA"',
source_queue_name => '"STRMADMIN"."LNMSA_CAP_QUEUE"',
destination_queue_name => '"STRMADMIN"."LNMSA_APP_QUEUE"@LNMSACX',
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => TRUE,
source_database => 'LNMSA',
inclusion_rule => TRUE,
and_condition => NULL,
queue_to_queue => q2q);
END;
/
--
-- Disable propagation. Enable after destination has been setup
--
DECLARE
q2q VARCHAR2(10);
destn_q VARCHAR2(65);
BEGIN
SELECT queue_to_queue INTO q2q
FROM dba_propagation
WHERE source_queue_owner = 'STRMADMIN' AND
source_queue_name = 'LNMSA_CAP_QUEUE' AND
destination_queue_owner = 'STRMADMIN' AND
destination_queue_name = 'LNMSA_APP_QUEUE' AND
destination_dblink = 'LNMSACX';

IF q2q = 'TRUE' THEN
destn_q := '"STRMADMIN"."LNMSA_APP_QUEUE"';
ELSE
destn_q := NULL;
END IF;

dbms_aqadm.disable_propagation_schedule(
queue_name => '"STRMADMIN"."LNMSA_CAP_QUEUE"',
destination => 'LNMSACX',
destination_queue => destn_q);
EXCEPTION WHEN OTHERS THEN
IF sqlcode = -24065 THEN NULL; -- propagation already disabled
ELSE RAISE;
END IF;
END;
/
--
-- CAPTURE changes for schema LNMSAIRC
--
DECLARE
compat VARCHAR2(512);
initpos NUMBER;
pos NUMBER;
version_num NUMBER;
release_num NUMBER;
compat_func VARCHAR2(65);
get_compatible VARCHAR2(4000);
BEGIN
SELECT value INTO compat
FROM v$parameter
WHERE name = 'compatible';

-- Extract version number
initpos := 1;
pos := INSTR(compat, '.', initpos, 1);
IF pos > 0 THEN
version_num := TO_NUMBER(SUBSTR(compat, initpos, pos - initpos));
initpos := pos + 1;

-- Extract release number
pos := INSTR(compat, '.', initpos, 1);
IF pos > 0 THEN
release_num := TO_NUMBER(SUBSTR(compat, initpos, pos - initpos));
initpos := pos + 1;
ELSE
release_num := TO_NUMBER(SUBSTR(compat, initpos));
END IF;
END IF;

IF version_num < 10 THEN
compat_func := 'dbms_streams.compatible_9_2';
ELSIF version_num = 10 THEN
IF release_num < 2 THEN
compat_func := 'dbms_streams.compatible_10_1';
ELSE
compat_func := 'dbms_streams.compatible_10_2';
END IF;
ELSE
compat_func := 'dbms_streams.compatible_11_1';
END IF;

get_compatible := ':lcr.get_compatible() <= '||compat_func;


dbms_streams_adm.add_schema_rules(
schema_name => '"LNMSAIRC"',
streams_type => 'CAPTURE',
streams_name => '"CAPTURE_LNMSA"',
queue_name => '"STRMADMIN"."LNMSA_CAP_QUEUE"',
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => TRUE,
source_database => 'LNMSA',
inclusion_rule => TRUE,
and_condition => get_compatible);
END;
/
--
-- CAPTURE changes for schema X3SYS
--
DECLARE
compat VARCHAR2(512);
initpos NUMBER;
pos NUMBER;
version_num NUMBER;
release_num NUMBER;
compat_func VARCHAR2(65);
get_compatible VARCHAR2(4000);
BEGIN
SELECT value INTO compat
FROM v$parameter
WHERE name = 'compatible';

-- Extract version number
initpos := 1;
pos := INSTR(compat, '.', initpos, 1);
IF pos > 0 THEN
version_num := TO_NUMBER(SUBSTR(compat, initpos, pos - initpos));
initpos := pos + 1;

-- Extract release number
pos := INSTR(compat, '.', initpos, 1);
IF pos > 0 THEN
release_num := TO_NUMBER(SUBSTR(compat, initpos, pos - initpos));
initpos := pos + 1;
ELSE
release_num := TO_NUMBER(SUBSTR(compat, initpos));
END IF;
END IF;

IF version_num < 10 THEN
compat_func := 'dbms_streams.compatible_9_2';
ELSIF version_num = 10 THEN
IF release_num < 2 THEN
compat_func := 'dbms_streams.compatible_10_1';
ELSE
compat_func := 'dbms_streams.compatible_10_2';
END IF;
ELSE
compat_func := 'dbms_streams.compatible_11_1';
END IF;

get_compatible := ':lcr.get_compatible() <= '||compat_func;


dbms_streams_adm.add_schema_rules(
schema_name => '"X3SYS"',
streams_type => 'CAPTURE',
streams_name => '"CAPTURE_LNMSA"',
queue_name => '"STRMADMIN"."LNMSA_CAP_QUEUE"',
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => TRUE,
source_database => 'LNMSA',
inclusion_rule => TRUE,
and_condition => get_compatible);
END;
/
--
-- Start capture process CAPTURE_LNMSA
--
BEGIN
dbms_capture_adm.start_capture(
capture_name => '"CAPTURE_LNMSA"');
EXCEPTION WHEN OTHERS THEN
IF sqlcode = -26666 THEN NULL; -- CAPTURE process already running
ELSE RAISE;
END IF;
END;
/
-- connect as streams administrator to site 2
PROMPT Connecting as streams administrator to site 2
CONNECT strmadmin/lnmsairc001@lnmsacx1
--
-- Datapump SCHEMA MODE IMPORT (NETWORK)
--
DECLARE
h1 NUMBER; -- data pump job handle
schema_expr_list VARCHAR2(32767); -- for metadata_filter
cnt NUMBER; -- temp variable
object_owner dbms_utility.uncl_array; -- obj owners
job_state VARCHAR2(30); -- job state
status ku$_Status; -- data pump status
job_not_exist exception;
pragma exception_init(job_not_exist, -31626);
STRM_MASTER_SCN NUMBER(16);
BEGIN

object_owner(1) := 'LNMSAIRC';
object_owner(2) := 'X3SYS';
FOR idx IN 1..2 LOOP
-- schema does not exist locally, need instantiation
IF schema_expr_list IS NULL THEN
schema_expr_list := '(';
ELSE
schema_expr_list := schema_expr_list ||',';
END IF;
schema_expr_list := schema_expr_list||''''||object_owner(idx)||'''';
END LOOP;
IF schema_expr_list IS NOT NULL THEN
schema_expr_list := schema_expr_list || ')';
ELSE
COMMIT;
RETURN;
END IF;
--====================================================

--修改後的指令碼
--====================================================
--獲得匯入時系統改變號,EXPDP時-1
SELECT (DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() - 1) INTO STRM_MASTER_SCN FROM DUAL@LNMSA;
h1 := dbms_datapump.open(operation=>'IMPORT',job_mode=>'SCHEMA', remote_link=>'LNMSA',job_name=>NULL, version=>'COMPATIBLE');
DBMS_DATAPUMP.ADD_FILE(H1, 'streams_import_lnmsa.log', 'DIR_DEST', '', DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,NULL);
DBMS_DATAPUMP.SET_PARAMETER(H1, 'FLASHBACK_SCN', STRM_MASTER_SCN);
dbms_datapump.metadata_filter(handle=>h1,name=>'SCHEMA_EXPR',value=>'IN (''LNMSAIRC'',''X3SYS'')');
--不匯出/入統計資訊
DBMS_DATAPUMP.METADATA_FILTER(H1,'EXCLUDE_PATH_EXPR','IN (''STATISTICS'',''TRIGGER'',''JOB'')');

DBMS_DATAPUMP.SET_PARAMETER(H1, 'INCLUDE_METADATA', 4);
DBMS_DATAPUMP.SET_PARALLEL( HANDLE => H1, DEGREE => 4);

--============================================================

dbms_datapump.start_job(h1);

job_state := 'UNDEFINED';
BEGIN
WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED') LOOP
status := dbms_datapump.get_status(
handle => h1,
mask => dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,
timeout => -1);
job_state := status.job_status.state;
dbms_lock.sleep(10);
END LOOP;
EXCEPTION WHEN job_not_exist THEN
dbms_output.put_line('job finished');
END;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
--
-- Set up queue "STRMADMIN"."LNMSA_APP_QUEUE"
--
BEGIN
dbms_streams_adm.set_up_queue(
queue_table => '"STRMADMIN"."LNMSA_APP_QUEUE_TABLE"',
storage_clause => NULL,
queue_name => '"STRMADMIN"."LNMSA_APP_QUEUE"',
queue_user => '');
END;
/
--
-- APPLY changes for schema LNMSAIRC
--
DECLARE
compat VARCHAR2(512);
initpos NUMBER;
pos NUMBER;
version_num NUMBER;
release_num NUMBER;
compat_func VARCHAR2(65);
get_compatible VARCHAR2(4000);
BEGIN
SELECT value INTO compat
FROM v$parameter
WHERE name = 'compatible';

-- Extract version number
initpos := 1;
pos := INSTR(compat, '.', initpos, 1);
IF pos > 0 THEN
version_num := TO_NUMBER(SUBSTR(compat, initpos, pos - initpos));
initpos := pos + 1;

-- Extract release number
pos := INSTR(compat, '.', initpos, 1);
IF pos > 0 THEN
release_num := TO_NUMBER(SUBSTR(compat, initpos, pos - initpos));
initpos := pos + 1;
ELSE
release_num := TO_NUMBER(SUBSTR(compat, initpos));
END IF;
END IF;

IF version_num < 10 THEN
compat_func := 'dbms_streams.compatible_9_2';
ELSIF version_num = 10 THEN
IF release_num < 2 THEN
compat_func := 'dbms_streams.compatible_10_1';
ELSE
compat_func := 'dbms_streams.compatible_10_2';
END IF;
ELSE
compat_func := 'dbms_streams.compatible_11_1';
END IF;

get_compatible := ':lcr.get_compatible() <= '||compat_func;


dbms_streams_adm.add_schema_rules(
schema_name => '"LNMSAIRC"',
streams_type => 'APPLY',
streams_name => '"APPLY_LNMSA"',
queue_name => '"STRMADMIN"."LNMSA_APP_QUEUE"',
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => TRUE,
source_database => 'LNMSA',
inclusion_rule => TRUE,
and_condition => get_compatible);
END;
/
--
-- APPLY changes for schema X3SYS
--
DECLARE
compat VARCHAR2(512);
initpos NUMBER;
pos NUMBER;
version_num NUMBER;
release_num NUMBER;
compat_func VARCHAR2(65);
get_compatible VARCHAR2(4000);
BEGIN
SELECT value INTO compat
FROM v$parameter
WHERE name = 'compatible';

-- Extract version number
initpos := 1;
pos := INSTR(compat, '.', initpos, 1);
IF pos > 0 THEN
version_num := TO_NUMBER(SUBSTR(compat, initpos, pos - initpos));
initpos := pos + 1;

-- Extract release number
pos := INSTR(compat, '.', initpos, 1);
IF pos > 0 THEN
release_num := TO_NUMBER(SUBSTR(compat, initpos, pos - initpos));
initpos := pos + 1;
ELSE
release_num := TO_NUMBER(SUBSTR(compat, initpos));
END IF;
END IF;

IF version_num < 10 THEN
compat_func := 'dbms_streams.compatible_9_2';
ELSIF version_num = 10 THEN
IF release_num < 2 THEN
compat_func := 'dbms_streams.compatible_10_1';
ELSE
compat_func := 'dbms_streams.compatible_10_2';
END IF;
ELSE
compat_func := 'dbms_streams.compatible_11_1';
END IF;

get_compatible := ':lcr.get_compatible() <= '||compat_func;


dbms_streams_adm.add_schema_rules(
schema_name => '"X3SYS"',
streams_type => 'APPLY',
streams_name => '"APPLY_LNMSA"',
queue_name => '"STRMADMIN"."LNMSA_APP_QUEUE"',
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => TRUE,
source_database => 'LNMSA',
inclusion_rule => TRUE,
and_condition => get_compatible);
END;
/
--
-- Get tag value to be used for Apply
--
DECLARE
found BINARY_INTEGER := 0;
tag_num NUMBER;
BEGIN
-- Use the apply object id as the tag
SELECT o.object_id INTO tag_num
FROM dba_objects o
WHERE o.object_name= 'APPLY_LNMSA' AND
o.object_type='APPLY';
LOOP
BEGIN
found := 0;
SELECT 1 INTO found FROM dba_apply
WHERE apply_name != 'APPLY_LNMSA' AND
apply_tag = hextoraw(tag_num);
EXCEPTION WHEN no_data_found THEN
EXIT;
END;
EXIT WHEN (found = 0);
tag_num := tag_num + 1;
END LOOP;

-- alter apply
dbms_apply_adm.alter_apply(
apply_name => '"APPLY_LNMSA"',
apply_tag => hextoraw(tag_num));
END;
/
--
-- Start apply process APPLY_LNMSA
--
BEGIN
dbms_apply_adm.start_apply(
apply_name => '"APPLY_LNMSA"');
EXCEPTION WHEN OTHERS THEN
IF sqlcode = -26666 THEN NULL; -- APPLY process already running
ELSE RAISE;
END IF;
END;
/
-- connect as streams administrator to site 1
PROMPT Connecting as streams administrator to site 1
CONNECT strmadmin/lnmsairc001@lnmsa1
--
-- Enable propagation schedule for "STRMADMIN"."LNMSA_CAP_QUEUE"
-- to LNMSACX
--
DECLARE
q2q VARCHAR2(10);
destn_q VARCHAR2(65);
BEGIN
SELECT queue_to_queue INTO q2q
FROM dba_propagation
WHERE source_queue_owner = 'STRMADMIN' AND
source_queue_name = 'LNMSA_CAP_QUEUE' AND
destination_queue_owner = 'STRMADMIN' AND
destination_queue_name = 'LNMSA_APP_QUEUE' AND
destination_dblink = 'LNMSACX';

IF q2q = 'TRUE' THEN
destn_q := '"STRMADMIN"."LNMSA_APP_QUEUE"';
ELSE
destn_q := NULL;
END IF;

dbms_aqadm.enable_propagation_schedule(
queue_name => '"STRMADMIN"."LNMSA_CAP_QUEUE"',
destination => 'LNMSACX',
destination_queue => destn_q);
EXCEPTION WHEN OTHERS THEN
IF sqlcode = -24064 THEN NULL; -- propagation already enabled
ELSE RAISE;
END IF;
END;
/
4、上面的指令碼可以通過10gR2提供的包生成,正常通過執行下面指令碼就可以完成第3步的任務:

declare
schemas DBMS_UTILITY.UNCL_ARRAY;
begin
schemas(1) := 'LNMSAIRC';
schemas(2) := 'X3SYS';
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names=>schemas,
source_directory_object => 'DIR_SOURCE',
destination_directory_object => 'DIR_DEST',
source_database => 'lnmsa',
destination_database => 'lnmsacx',
capture_queue_table => 'lnmsa_cap_queue_table',
capture_queue_name => 'lnmsa_cap_queue',
apply_queue_table => 'lnmsa_app_queue_table',
capture_name => 'capture_lnmsa',
propagation_name => 'prop_lnmsa',
apply_queue_name => 'lnmsa_app_queue',
apply_name => 'apply_lnmsa',
bi_directional => false,
include_ddl => true,
instantiation => DBMS_STREAMS_ADM.instantiation_schema_network);
end;
/

由於源庫使用了大量的trigger,這樣在同步的時侯經常報錯,於是通過下面指令碼先生成了指令碼,然後在初始化資料的時侯把trigger排除出去了。於是就有了第3步的一堆指令碼,第三步的指令碼就是通過下面指令碼生成的。

declare
schemas DBMS_UTILITY.UNCL_ARRAY;
begin
schemas(1) := 'LNMSAIRC';
schemas(2) := 'X3SYS';
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names=>schemas,
source_directory_object => 'DIR_SOURCE',
destination_directory_object => 'DIR_DEST',
source_database => 'lnmsa',
destination_database => 'lnmsacx',
--==========================
perform_actions => false,
script_directory_object => 'DIR_SOURCE',
script_name => 'lnmsa.sql',
--==============================
capture_queue_table => 'lnmsa_cap_queue_table',
capture_queue_name => 'lnmsa_cap_queue',
apply_queue_table => 'lnmsa_app_queue_table',
capture_name => 'capture_lnmsa',
propagation_name => 'prop_lnmsa',
apply_queue_name => 'lnmsa_app_queue',
apply_name => 'apply_lnmsa',
bi_directional => false,
include_ddl => true,
instantiation => DBMS_STREAMS_ADM.instantiation_schema_network);
end;
/
5、配置完成之後根據需要可以調整一些內部引數以提高streams的效能:

connect strmadmin/lnmsairc001@lnmsa1


--調整capture 佇列表引數
BEGIN
DBMS_AQADM.ALTER_QUEUE_TABLE(
queue_table => 'STRMADMIN.LNMSA_CAP_QUEUE_TABLE',
primary_instance => 1,
secondary_instance => 2);
END;
/
--調整capture引數
BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
CAPTURE_NAME => 'CAPTURE_LNMSA',
PARAMETER => 'PARALLELISM',
VALUE => 2);
END;
/

--2、調整 CHECKPOINT_RETENTION_TIME 日誌檢查保留時間(預設60天,佔用SYSTEM.LOGMNR_RESTART_CKPT$大小),修改7天

BEGIN
DBMS_CAPTURE_ADM.ALTER_CAPTURE(
CAPTURE_NAME => 'CAPTURE_LNMSA',
CHECKPOINT_RETENTION_TIME => 7
);
END;
/

--3、調整 _SGA_SIZE 捕獲程式的記憶體空間大小50M

BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
CAPTURE_NAME => 'CAPTURE_LNMSA',
PARAMETER => '_SGA_SIZE',
VALUE => 50);
END;
/
--==================================
connect strmadmin/lnmsairc001@lnmsacx1

--調整apply佇列引數:
BEGIN
DBMS_AQADM.ALTER_QUEUE_TABLE(
queue_table => 'STRMADMIN.LNMSA_APP_QUEUE_TABLE',
primary_instance => 1,
secondary_instance => 2);
END;
/
--調整streams apply引數
--1、設定引數DISABLE_ON_ERROR = N

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
APPLY_NAME => ' APPLY_LNMSA',
PARAMETER => 'DISABLE_ON_ERROR',
VALUE => 'N');
END;
/

--2)、調整內在引數 _TXN_BUFFER_SIZE =10050

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
APPLY_NAME => ' APPLY_LNMSA',
PARAMETER => '_TXN_BUFFER_SIZE',
VALUE => 100);
END;
/

--3)、調整內在引數 _HASH_TABLE_SIZE = 10000000 (default 1000000)

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
APPLY_NAME => ' APPLY_LNMSA',
PARAMETER => '_HASH_TABLE_SIZE',
VALUE => 1000000);
END;
/

--4)、調整內在引數 _DYNAMIC_STMTS = Y

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
APPLY_NAME => ' APPLY_LNMSA',
PARAMETER => '_DYNAMIC_STMTS',
VALUE => 'Y');
END;
/

--5)、調整 PARALLELISM 並行數(依據應用業務繁忙情況而定)

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
APPLY_NAME => ' APPLY_LNMSA',
PARAMETER => 'PARALLELISM',
VALUE => 4);
END;
/

--6)、調整 TXN_LCR_SPILL_THRESHOLD LCR記錄寫硬碟(表)最小引數(default 10000)

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
APPLY_NAME => ' APPLY_LNMSA',
PARAMETER => 'TXN_LCR_SPILL_THRESHOLD',
VALUE => 1000000);
END;
/

--7)、調整 _CMPKEY_ONLY 引數 = Y

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
APPLY_NAME => ' APPLY_LNMSA',
PARAMETER => '_CMPKEY_ONLY',
VALUE => 'Y');
END;
/

--8)、設定引數commit_serialization = N (default full)

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
APPLY_NAME => ' APPLY_LNMSA',
PARAMETER => 'COMMIT_SERIALIZATION',
VALUE => 'NONE');
END;
/
--===========================

6、如果配置過程中出錯,可以按照下面過程清除流環境然後再重新執行第3步:

--連線到查詢庫
Connect strmadmin/lnmsairc001@lnmsacx1
--停止streams應用
begin
dbms_apply_adm.stop_apply(
apply_name => 'APPLY_LNMSA');
end;
/

--停止佇列
begin
dbms_aqadm.stop_queue('LNMSA_APP_QUEUE');
end;
/

begin
dbms_aqadm.stop_queue('AQ$_LNMSA_APP_QUEUE_TABLE_E');
end;
/

begin
dbms_aqadm.drop_queue_table('LNMSA_APP_QUEUE_TABLE',true);
end;
/

begin
dbms_apply_adm.drop_apply(
apply_name => 'APPLY_LNMSA');
end;
/

--====================================================
--連線到生產庫
Connect strmadmin/lnmsairc001@lnmsa1
--停止streams捕獲
begin
dbms_capture_adm.stop_capture(
capture_name => 'CAPTURE_LNMSA');
end;
/
--停止streams傳播
begin
dbms_propagation_adm.stop_propagation('PROP_LNMSA');
END;
/

begin
dbms_capture_adm.drop_capture(
capture_name => 'CAPTURE_LNMSA');
end;
/

begin
dbms_propagation_adm.drop_propagation(
propagation_name => 'PROP_LNMSA');
end;
/
--刪除佇列表之後佇列就不存在了
begin
dbms_aqadm.drop_queue_table('LNMSA_CAP_QUEUE_TABLE',true);
end;
/

exit

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/19602/viewspace-1060002/,如需轉載,請註明出處,否則將追究法律責任。

相關文章