配备oracle stream(step by step setup schema level Streams Replication)
配置oracle stream(step by step setup schema level Streams Replication)
hrdbprim =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.239)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hrdbprim)
)
)
hrdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.233)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hrdb)
)
)
STRMADMIN = stream管理用户
WJ = 需要复制同步的用户
一、环境准备
1,删除原有的stream配置
execute DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
2,配置数据库参数,一般需要设置global_names, _job_queue_interval, sga_target, streams_pool_size四个参数
3,创建管理用户STRMADMIN
二、开始stream同步
1,@source DB 建立用户传播的队列和db link
以下均使用STRMADMIN用户
b, @target hrdb
c, @target DB 建立对单用户同步规则
e, @source DB 创建传播job
备注:如果导入数据的target db中已有数据库,就比较麻烦可以使用如下两种方法
G, @target db 开启apply
H,@target DB 错误处理机制
I,@source DB 打开capture
参考文档
![配备oracle stream(step by step setup schema level Streams Replication) 配备oracle stream(step by step setup schema level Streams Replication)](/default/index/img?u=aHR0cDovL3d3dy5teWV4Y2VwdGlvbnMubmV0L2ltZy8yMDEyLzA4LzE5LzIxNTMzNTc5MS5naWY=)
过程和database level基本一样,本篇只简单介绍下过程
说明:
source 为hrdbprim,target db为hrdb,此处的数据库名称必须和global_name一致hrdbprim =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.239)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hrdbprim)
)
)
hrdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.233)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hrdb)
)
)
STRMADMIN = stream管理用户
WJ = 需要复制同步的用户
一、环境准备
1,删除原有的stream配置
execute DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
2,配置数据库参数,一般需要设置global_names, _job_queue_interval, sga_target, streams_pool_size四个参数
3,创建管理用户STRMADMIN
create user STRMADMIN identified by STRM#123; ALTER USER STRMADMIN DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS; GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN; execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');
二、开始stream同步
1,@source DB 建立用户传播的队列和db link
connect STRMADMIN/STRM#123@hrdbprim BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'STREAMS_QUEUE_TABLE', queue_name => 'STREAMS_QUEUE', queue_user => 'STRMADMIN'); END; / --CREATE DATABASE LINK AT SOURCE as SYS @BOTH DB conn sys/df as sysdba create public database link HRDB using 'HRDB'; --CREATE DATABASE LINK AT SOURCE as STRMADMIN conn strmadmin/STRM#123 create database link HRDB connect to strmadmin identified by STRM#123;
以下均使用STRMADMIN用户
b, @target hrdb
/* Step 2 - Connect as the Streams Administrator in the target site TARGET and create the streams queue */ conn strmadmin/STRM#123 BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_name => 'STREAMS_QUEUE', queue_table =>'STREAMS_QUEUE_TABLE', queue_user => 'STRMADMIN'); END; /
c, @target DB 建立对单用户同步规则
/* STEP 4.- Add apply rules for the Schema at the destination database */ conn strmadmin/STRM#123 BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => 'WJ', streams_type => 'APPLY ', streams_name => 'STREAM_APPLY', queue_name => 'STRMADMIN.STREAMS_QUEUE', include_dml => true, include_ddl => true, source_database => 'hrdbprim'); END; /d, @source DB
conn strmadmin/STRM#123 BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => 'WJ', streams_type => 'CAPTURE', streams_name => 'STREAM_CAPTURE', queue_name => 'STRMADMIN.STREAMS_QUEUE', include_dml => true, include_ddl => true, source_database => 'hrdbprim'); END; /
e, @source DB 创建传播job
conn strmadmin/STRM#123 BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name => 'WJ', streams_name => 'STREAM_PROPAGATE', source_queue_name => 'STRMADMIN.STREAMS_QUEUE', destination_queue_name => 'STRMADMIN.STREAMS_QUEUE@HRDB', include_dml => true, include_ddl => true, source_database => 'HRDBPRIM'); END; /F,导入导出用户、注意参数
exp USERID=SYSTEM/123@hrdbprim OWNER=WJ FILE=wj.dmp LOG=wj.log OBJECT_CONSISTENT=Y STATISTICS = NONE imp USERID=SYSTEM/123@hrdb CONSTRAINTS=Y FULL=Y FILE=wj.dmp IGNORE=Y COMMIT=Y LOG=wj_imp.log STREAMS_INSTANTIATION=Y
备注:如果导入数据的target db中已有数据库,就比较麻烦可以使用如下两种方法
1. By means of Metadata-only export/import : Specify ROWS=N during Export Specify IGNORE=Y during Import along with above import parameters. 2. By Manaually instantiating the objects Get the Instantiation SCN at the source database: connect STRMADMIN/STRMADMIN@STRM1.NET set serveroutput on DECLARE iscn NUMBER; -- Variable to hold instantiation SCN value BEGIN iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn); END; / Instantiate the objects at the destination database with this SCN value. The SET_TABLE_INSTANTIATION_SCN procedure controls which LCRs for a table are to be applied by the apply process. If the commit SCN of an LCR from the source database is less than or equal to this instantiation SCN, then the apply process discards the LCR. Else, the apply process applies the LCR. connect STRMADMIN/STRMADMIN@STRM2.NET BEGIN DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN( SOURCE_SCHEMA_NAME => 'HR', SOURCE_DATABASE_NAME => 'STRM1.NET', RECURSIVE => TRUE, INSTANTIATION_SCN => &iscn ); END; Enter value for iscn: <Provide the value of SCN that you got from the source database above> In 10g recursive=true parameter of DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN is used for instantiation. If using the parameter recursive true with SET_SCHEMA_INSTANTIATION_SCN then you need a dblink on the destination database to the source database with the same name as the source database. Refer to the following documentation Oracle?ˉ???? Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) B14258-01 page 15-46 - SET_SCHEMA_INSTANTIATION_SCN Procedure otherwise apply aborts with following error example - ORA-26687: no instantiation SCN provided for "HR"."DEPARTMENTS" in source database "STRM1.NET" */
G, @target db 开启apply
BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'STREAM_APPLY', apply_user => 'Wj'); END; /
H,@target DB 错误处理机制
BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'STREAM_APPLY', parameter => 'disable_on_error', value => 'n'); END; / DECLARE v_started number; BEGIN SELECT decode(status, 'ENABLED', 1, 0) INTO v_started FROM DBA_APPLY WHERE APPLY_NAME = 'STREAM_APPLY'; if (v_started = 0) then DBMS_APPLY_ADM.START_APPLY(apply_name => 'STREAM_APPLY'); end if; END; /
I,@source DB 打开capture
BEGIN DBMS_CAPTURE_ADM.ALTER_CAPTURE( capture_name => 'STREAM_CAPTURE', checkpoint_retention_time => 7); END; / begin DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'STREAM_CAPTURE'); end; /结果测试:
@source db SQL> select count(*) from wj.www; COUNT(*) ---------- 1211 SQL> truncate table wj.www; Table truncated. @target db strmadmin@HRDB1(dtydb3)>select count(*) from wj.www; COUNT(*) ---------- 1211 strmadmin@HRDB1(dtydb3)>select count(*) from wj.www; COUNT(*) ---------- 0
参考文档