9206下配置雙向stream
一個客戶要在9206上用雙向stream，個人不推薦這麼做（每天800G的日誌有點恐怖），今天下午給搭建了測試環境，配置過程記錄下。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
5 rows selected.
/***************************************************
--需要在2個資料庫分別執行：
conn /as sysdba
show parameter JOB_QUEUE_PROCESSES
show parameter AQ_TM_PROCESSES
show parameter LOG_PARALLELISM
show parameter global_name
create tablespace pmid datafile '/oracle/app/oracle/oradata/crmtest/pmid.dbf' size 512m;
create user strmadmin identified by strmadmin default tablespace pmid;
grant dba, CONNECT, RESOURCE, SELECT_CATALOG_ROLE to strmadmin;
GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_AQADM TO strmadmin;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin;
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'strmadmin',
grant_option => FALSE);
END;
/
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'strmadmin',
grant_option => FALSE);
END;
/
create user strmtest identified by strmtest default tablespace pmid;
grant dba to strmtest;
--create tablespace PMID(修改名字) for logmnr --建立logmnr表空間
EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('PMID');
alter system set global_names=true scope=both;
/***************************************************
--只在source資料庫執行；
SQL> conn strmtest/strmtest
create table test1(id number not null primary key,mc varchar2(200));
create table test2(id number,mc varchar2(200));
/***************************************************
在source資料庫上執行
conn /as sysdba
ALTER TABLE strmtest.test1 ADD (time TIMESTAMP WITH TIME ZONE);
ALTER TABLE strmtest.test2 ADD (time TIMESTAMP WITH TIME ZONE);
CREATE OR REPLACE TRIGGER strmtest.insert_time_test1
BEFORE
INSERT OR UPDATE ON strmtest.test1 FOR EACH ROW
BEGIN
IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN
:NEW.TIME := SYSTIMESTAMP;
ELSE
:NEW.TIME := :OLD.TIME + 1 / 86400;
END IF;
END;
/
CREATE OR REPLACE TRIGGER strmtest.insert_time_test2
BEFORE
INSERT OR UPDATE ON strmtest.test2 FOR EACH ROW
BEGIN
IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN
:NEW.TIME := SYSTIMESTAMP;
ELSE
:NEW.TIME := :OLD.TIME + 1 / 86400;
END IF;
END;
/
ALTER TABLE strmtest.test1 ADD SUPPLEMENTAL LOG GROUP log_group_test1(id, mc, time) ALWAYS;
ALTER TABLE strmtest.test2 ADD SUPPLEMENTAL LOG GROUP log_group_test2(id, mc, time) ALWAYS;
GRANT ALL ON strmtest.test1 TO strmadmin;
GRANT ALL ON strmtest.test2 TO strmadmin;
/***************************************************
--在source資料庫執行：
conn strmadmin/strmadmin
CREATE DATABASE LINK test CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'test';
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_name=>'capture_queue');
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_name=>'apply_queue');
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'strmtest.test1',
streams_name => 'stream_propagation',
source_queue_name => 'strmadmin.capture_queue',
destination_queue_name => 'strmadmin.apply_queue@test',
include_dml => true,
include_ddl => true,
source_database => 'crmtest');
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'strmtest.test2',
streams_name => 'stream_propagation',
source_queue_name => 'strmadmin.capture_queue',
destination_queue_name => 'strmadmin.apply_queue@test',
include_dml => true,
include_ddl => true,
source_database => 'crmtest');
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmtest.test1',
streams_type => 'capture',
streams_name => 'stream_capture',
queue_name => 'strmadmin.capture_queue',
include_dml => true,
include_ddl => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmtest.test2',
streams_type => 'capture',
streams_name => 'stream_capture',
queue_name => 'strmadmin.capture_queue',
include_dml => true,
include_ddl => true);
END;
/
DECLARE
cols DBMS_UTILITY.NAME_ARRAY;
BEGIN
cols(1) := 'id';
cols(2) := 'mc';
cols(3) := 'time';
DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
object_name => 'strmtest.test1',
method_name => 'MAXIMUM',
resolution_column => 'time',
column_list => cols);
END;
/
DECLARE
cols DBMS_UTILITY.NAME_ARRAY;
BEGIN
cols(1) := 'id';
cols(2) := 'mc';
cols(3) := 'time';
DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
object_name => 'strmtest.test2',
method_name => 'MAXIMUM',
resolution_column => 'time',
column_list => cols);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmtest.test1',
streams_type => 'apply',
streams_name => 'stream_apply',
queue_name => 'strmadmin.apply_queue',
include_dml => true,
include_ddl => true,
source_database => 'test');
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmtest.test2',
streams_type => 'apply',
streams_name => 'stream_apply',
queue_name => 'strmadmin.apply_queue',
include_dml => true,
include_ddl => true,
source_database => 'test');
END;
/
exp userid=strmtest/strmtest@crmtest FILE=streams.dmp TABLES=strmtest.test1,strmtest.test2 OBJECT_CONSISTENT=y
imp userid=strmtest/strmtest@test full=y FILE=streams.dmp COMMIT=y LOG=import.log STREAMS_INSTANTIATION=y
/***************************************************
--在target資料庫執行：
CONN /AS SYSDBA
GRANT ALL ON strmtest.test1 TO strmadmin;
GRANT ALL ON strmtest.test2 TO strmadmin;
--確認SUPPLEMENTAL LOG和trigge和許可權都正常
CONNECT strmadmin/strmadmin@test
CREATE DATABASE LINK crmtest CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'crmtest';
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_name=>'capture_queue');
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_name=>'apply_queue');
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'strmtest.test1',
streams_name => 'stream_propagation',
source_queue_name => 'strmadmin.capture_queue',
destination_queue_name => 'strmadmin.apply_queue@crmtest',
include_dml => true,
include_ddl => true,
source_database => 'test');
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'strmtest.test2',
streams_name => 'stream_propagation',
source_queue_name => 'strmadmin.capture_queue',
destination_queue_name => 'strmadmin.apply_queue@crmtest',
include_dml => true,
include_ddl => true,
source_database => 'test');
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmtest.test1',
streams_type => 'capture',
streams_name => 'stream_capture',
queue_name => 'strmadmin.capture_queue',
include_dml => true,
include_ddl => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmtest.test2',
streams_type => 'capture',
streams_name => 'stream_capture',
queue_name => 'strmadmin.capture_queue',
include_dml => true,
include_ddl => true);
END;
/
DECLARE
cols DBMS_UTILITY.NAME_ARRAY;
BEGIN
cols(1) := 'id';
cols(2) := 'mc';
cols(3) := 'time';
DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
object_name => 'strmtest.test1',
method_name => 'MAXIMUM',
resolution_column => 'time',
column_list => cols);
END;
/
DECLARE
cols DBMS_UTILITY.NAME_ARRAY;
BEGIN
cols(1) := 'id';
cols(2) := 'mc';
cols(3) := 'time';
DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
object_name => 'strmtest.test2',
method_name => 'MAXIMUM',
resolution_column => 'time',
column_list => cols);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmtest.test1',
streams_type => 'apply',
streams_name => 'stream_apply',
queue_name => 'strmadmin.apply_queue',
include_dml => true,
include_ddl => true,
source_database => 'crmtest');
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmtest.test2',
streams_type => 'apply',
streams_name => 'stream_apply',
queue_name => 'strmadmin.apply_queue',
include_dml => true,
include_ddl => true,
source_database => 'crmtest');
END;
/
DECLARE
v_scn NUMBER;
BEGIN
v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@crmtest(
source_object_name => 'strmtest.test1',
source_database_name => 'test',
instantiation_scn => v_scn);
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@crmtest(
source_object_name => 'strmtest.test2',
source_database_name => 'test',
instantiation_scn => v_scn);
END;
/
exec dbms_apply_adm.start_apply('stream_apply');
/***************************************************
--在source資料庫執行：
exec dbms_capture_adm.start_capture('stream_capture');
exec dbms_apply_adm.start_apply('stream_apply');
/***************************************************
--在target資料庫執行：
exec dbms_capture_adm.start_capture('stream_capture');
測試：
在source資料庫insert資料：
SQL> insert into strmtest.test1(id,mc) values(1,'111111');
1 row created.
SQL> commit;
Commit complete.
在target資料庫查詢：
SQL> select * from strmtest.test1;
ID MC TIME
---------- ---------- --------------------------------------------------
1 111111 29-MAY-08 10.07.05.549004 PM +08:00
在target資料庫insert資料：
SQL> insert into strmtest.test1(id,mc) values(2,'222222');
1 row created.
SQL> commit;
Commit complete.
在source資料庫查詢：
SQL> select * from strmtest.test1;
ID MC TIME
---------- ---------- --------------------------------------------------
1 111111 29-MAY-08 10.07.05.549004 PM +08:00
2 222222 29-MAY-08 10.08.10.132493 PM +08:00
在source庫insert資料：
SQL> insert into strmtest.test1(id,mc) values(3,'333333');
1 row created.
SQL> commit;
Commit complete.
在target庫查詢資料：
SQL> select * from strmtest.test1;
ID MC TIME
---------- ---------- --------------------------------------------------
1 111111 29-MAY-08 10.07.05.549004 PM +08:00
2 222222 29-MAY-08 10.08.10.132493 PM +08:00
3 333333 29-MAY-08 10.14.22.920806 PM +08:00
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-324236/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Keytool配置 Tomcat的HTTPS雙向認證TomcatHTTP
- Https雙向認證Android客戶端配置HTTPAndroid客戶端
- 007.iSCSI伺服器CHAP雙向認證配置伺服器
- nginx 代理伺服器配置雙向證書驗證Nginx伺服器
- Oracle Stream概述與配置Oracle
- vue 雙向繫結(v-model 雙向繫結、.sync 雙向繫結、.sync 傳物件)Vue物件
- 雙向lstm原理
- grpc雙向流RPC
- nginx配置ssl加密（單/雙向認證、部分https） – HTTPS SSL 教程Nginx加密HTTP
- 雙向連結串列
- https雙向認證HTTP
- UDP雙向通訊UDP
- 雙向迭代介面：ListIterator
- java 雙向證書請求 雙向驗證機制 轉載Java
- MySQL雙主雙從配置MySql
- HTTPS連線建立過程(單向&雙向)HTTP
- 從單向到雙向資料繫結
- PostgreSQL雙向複製教程SQL
- BRI 融資能力：“雙向”
- 雙向通訊之websocketWeb
- 雙向通訊之SSE
- Vue雙向繫結初探Vue
- vue雙向繫結原理Vue
- Linux 雙網路卡雙IP配置Linux
- mysql雙主雙從 搭建配置MySql
- Laravel 配置雙模板Laravel
- JTO：心肺風險，“雙向奔赴”！
- BFD雙向轉發檢測
- Docker網路雙向連線Docker
- 多點雙向重發布
- 實現雙向連結串列
- google guava的BiMap：雙向MapGoGuava
- vue雙向繫結盲區Vue
- JS雙向資料繫結JS
- 揭密 Vue 的雙向繫結Vue
- Vue、MVVM、MVC、雙向繫結VueMVVMMVC
- Vue雙向繫結實現Vue
- Databinding 雙向繫結詳解