設定Oracle Streams 步驟 (step by step setup database level Streams Replication)

Ref:
http://blog.csdn.net/hijk139/article/details/7871927

https://forums.oracle.com/forums/thread.jspa?threadID=716724



以下為兩端 instance_name 相同時 , 需手動修改global_name
@source DB
update global_name set global_name='ORCLP';
commit;
select * from global_name;


@target DB
update global_name set global_name='ORCLS';
commit;
select * from global_name;
--
http://blog.csdn.net/hijk139/article/details/7871927

修改的這參數 預設值為5 :

  SELECT   ksppinm, ksppstvl, ksppdesc
    FROM   x$ksppi x, x$ksppcv y
    WHERE   x.indx = y.indx AND  ksppinm = '_job_queue_interval';

_job_queue_interval
5
Wakeup interval in seconds for job queue co-ordinator

---
實際測試:
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');


配置tnsnames.ora

PRIMDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )
STDBYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.131)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )


5,开始配置stream
步骤a ,source数据库建立stream队列 ,j建 db link @source


conn strmadmin/STRM#123

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name => 'STREAMS_QUEUE',
queue_table =>'STREAMS_QUEUE_TABLE',
queue_user => 'STRMADMIN');
END;
/

conn sys/oracle as sysdba

CREATE PUBLIC DATABASE LINK "ORCLS"  CONNECT TO "STRMADMIN"
    IDENTIFIED BY "STRM#123"
    USING 'STDBYDB' ;

or
CREATE PUBLIC DATABASE LINK "STREAMLINK"  CONNECT TO "STRMADMIN" IDENTIFIED BY "STRM#123"
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.88.131)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=ORCL)))';

 select sysdate from dual@ORCLS;
 select sysdate from dual@STREAMLINK;

conn strmadmin/STRM#123

CREATE DATABASE LINK "ORCLS"  CONNECT TO "STRMADMIN"
    IDENTIFIED BY "STRM#123"
    USING 'STDBYDB';

create database link streamlink connect to strmadmin identified by STRM#123;


@target db hrdb
conn sys/oracle as sysdba
CREATE PUBLIC DATABASE LINK "ORCLP"  CONNECT TO "STRMADMIN"
    IDENTIFIED BY "STRM#123"
    USING 'PRIMDB' ;

conn strmadmin/STRM#123

CREATE DATABASE LINK "ORCLP"  CONNECT TO "STRMADMIN"
    IDENTIFIED BY "STRM#123"
    USING 'PRIMDB' ;

  select sysdate from dual@ORCLP;

b, @target hrdb 建stream队列

/* 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, @source db 建捕获(capture)和传播PROPAGATION 规则

/*Step 3 -Connected to source db, create CAPTURE and PROPAGATION rules */


conn strmadmin/STRM#123@PRIMDB
BEGIN
DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES(
streams_name => 'STRMADMIN_PROP',
source_queue_name => 'STRMADMIN.STREAMS_QUEUE',
destination_queue_name => '"STRMADMIN"."STREAMS_QUEUE"@ORCLS',
include_dml => true,
include_ddl => true,
source_database => 'ORCL');
END;
/


BEGIN
DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
streams_type => 'CAPTURE',
streams_name => 'STRMADMIN_CAPTURE',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'ORCL');
END;
/

d, @target HRDB 建apply规则

/*Step 4 - Connected as STRMADMIN at TARGET, create APPLY rules */
conn STRMADMIN/STRM#123@STDBYDB
BEGIN
DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
streams_type => 'APPLY',
streams_name => 'STRMADMIN_APPLY',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'orcl');
END;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'STRMADMIN_APPLY',
parameter => 'disable_on_error',
value => 'n');
END;
/

E：导入导出数据，注意相关参数

/*Step 7 - Take an export of the DB at SOURCE */
exp USERID=SYSTEM/oracle@PRIMDB FULL=Y FILE=stream.dmp LOG=stream.log OBJECT_CONSISTENT=Y STATISTICS=NONE

exp USERID=SYSTEM/oracle@PRIMDB OWNER="TEST" FILE=schemas.dmp GRANTS=Y ROWS=N LOG=exportSchemas.log  OBJECT_CONSISTENT=Y



/*Step 8 - Transfer the export dump file to TARGET and import */
imp USERID=SYSTEM/oracle@STDBYDB CONSTRAINTS=Y FULL=Y FILE=stream.dmp IGNORE=Y COMMIT=Y LOG=stream_imp.log STREAMS_INSTANTIATION=Y

imp USERID=SYSTEM/oracle@STDBYDB FULL=Y CONSTRAINTS=Y FILE=schemas.dmp IGNORE=Y GRANTS=Y ROWS=N COMMIT=Y LOG=importSchemas.log STREAMS_CONFIGURATION=N STREAMS_INSTANTIATION=Y





F：启动capture和apply

/*Step 9 - Start capture and Apply  */

conn strmadmin/STRM#123@PRIMDB
BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name => 'STRMADMIN_CAPTURE');
END;
/

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'STRMADMIN_CAPTURE');
END;
/


conn strmadmin/STRM#123@STDBYDB
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'STRMADMIN_APPLY');
END;
/

Streams performance tuning

 http://www.oracle11ggotchas.com/articles/MakingOracleStreamsFly.htm