給客戶通過streams配置了schema級別的同步
記錄一個大致的過程供大家參考，穩定了執行了近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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- java通過schema校驗xmlJavaXML
- java web 通過request獲取客戶端IPJavaWeb客戶端
- 客戶端到伺服器端的通訊過程客戶端伺服器
- 給客戶端新增cookie客戶端Cookie
- WPF 通過程式實現異常隔離的客戶端客戶端
- 使Snowflake的客戶能夠通過Snowflake平臺
- 客戶端到伺服器端的通訊過程及原理客戶端伺服器
- oracle客戶端升級Oracle客戶端
- rsync 客戶端同步的時候報錯客戶端
- 郵件客戶端的配置使用客戶端
- 通過整合中國大陸、香港及海外網際網路網路資源，給客戶提供真正的鑽石級網際網路服務。
- 初探Thrift客戶端非同步模式客戶端非同步模式
- 如何配置WSUS客戶端客戶端
- 《samba搭建win客戶端和linux客戶端的區別》Samba客戶端Linux
- nginx 通過反向代理在多個平臺接入上游的客戶資訊Nginx
- 通過NETWORK_LINK在客戶端執行EXPDP客戶端
- Swoole 協程 MySQL 客戶端與非同步回撥 MySQL 客戶端的對比MySql客戶端非同步
- Java synchronized物件級別與類級別的同步鎖Javasynchronized物件
- 【DATAGUARD】物理dg配置客戶端無縫切換 (八.3)--客戶端TAF 配置客戶端
- oracle 10g-->11g schema級別下游實時捕獲stream配置Oracle 10g
- graylog 客戶端的安裝配置客戶端
- oracle RAC的客戶端HA配置薦Oracle客戶端
- 物理DataGuard客戶端無縫切換--客戶端TAF 配置客戶端
- 終於將SAP系統完全配置通過了
- 利用abap寫程式寄email給客戶AI
- 分散式配置中心客戶端分散式客戶端
- OutlookAnywhere客戶端配置詳解客戶端
- 客戶端負載均衡配置客戶端負載
- GoldenGate schema級複製 實施過程Go
- windows客戶端通過指令碼檔案新增信任站點薦Windows客戶端指令碼
- JAX-WS - 通過xjc命令把Schema生成JavaJava
- asyncio非同步IO——Streams詳解非同步
- STREAMS筆記(10) 同步捕獲筆記
- 不用破解版的 Navicat 了，幾款免費且好用的 SQL 客戶端送給你SQL客戶端
- Jaeger的客戶端取樣配置(Java版)客戶端Java
- 如何再造通訊連鎖的客戶之路薦
- 客戶端通過SCAN TNS無法連線ORA-12545客戶端
- FJ集團企業級郵件伺服器—iRedMail客戶端配置伺服器AI客戶端