鉴于blogger在公司非常难上，所以还是JAVAEYE拉

Daily 12th July 2009

之前几天一直再弄Oracle stream

只知道有Capture Staging Apply

然后就不学走先学跑的完成以下动作



3.1 Stop Capture



connect /@THAMW;

BEGIN

DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name => 'STREAM_CAPTURE');

END;

/



3.2 Stop apply

connect /@THAMR;



BEGIN

DBMS_APPLY_ADM.STOP_APPLY(apply_name => 'STREAM_APPLY');

END;

/



3.3 Add APPLY rules in destination site

connect /@THAMR;

BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_RULES(

table_name => 'THAMWDA1.WILLIAM',

streams_type => 'APPLY',

streams_name => 'STREAM_APPLY',

queue_name => '.STREAM_Q',

include_dml => true,

include_ddl => true,

source_database => 'THAMW',

inclusion_rule => true);

END;

/



3.4 Setup DML handler for APPLY in destination site 4 types )

connect /@THAMR;

BEGIN

DBMS_APPLY_ADM.SET_DML_HANDLER(

object_name => 'THAMWDA1.WILLIAM',

object_type => 'TABLE',

operation_name => 'INSERT',

error_handler => false,

user_procedure => '.DML_HANDLER_APPEND',

apply_database_link => NULL,

apply_name => NULL );

END;

/



BEGIN

DBMS_APPLY_ADM.SET_DML_HANDLER(

object_name => 'THAMWDA1.WILLIAM',

object_type => 'TABLE',

operation_name => 'UPDATE',

error_handler => false,

user_procedure => '.DML_HANDLER_APPEND',

apply_database_link => NULL,

apply_name => NULL );

END;

/



BEGIN

DBMS_APPLY_ADM.SET_DML_HANDLER(

object_name => 'THAMWDA1.WILLIAM',

object_type => 'TABLE',

operation_name => 'DELETE',

error_handler => false,

user_procedure => '.DML_HANDLER_APPEND',

apply_database_link => NULL,

apply_name => NULL );

END;

/



3.5 Setup DML error handler for APPLY in destination site

connect /@THAMR;



BEGIN

DBMS_APPLY_ADM.SET_DML_HANDLER(

object_name => 'THAMWDA1.WILLIAM',

object_type => 'TABLE',

operation_name => 'INSERT',

error_handler => true,

user_procedure => '.errors_pkg.regions_pk_error',

apply_database_link => NULL,

apply_name => NULL);

END;

/





3.6 Add Capture rules in source site

connect /@THAMW;



BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_RULES(

table_name => 'THAMWDA1.WILLIAM',

streams_type => 'CAPTURE',

streams_name => 'STREAM_CAPTURE',

queue_name => '.STREAM_Q',

include_dml => true,

include_ddl => true,

inclusion_rule => true,

source_database => 'THAMW');

END;

/



3.7 Add propagation rules in source site

connect /@THAMW;



BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES

(

table_name => 'THAMWDA1.WILLIAM',

streams_name =>'STREAM_PROP',

source_queue_name => '.STREAM_Q',

destination_queue_name => '.STREAM_Q@THAMR',

include_dml => true,

include_ddl => true,

source_database => 'THAMW',

inclusion_rule => true

);

END;

/



3.8 Create schema and add columns in destination site (3 types )

/*导出，导入格式*/

host exp /@THAMW tables=THAMWDA1.WILLIAM ROWS=N



host imp /@THAMR fromuser=THAMWDA1 touser=THAMWDA1 ignore=y





connect /@THAMR;



ALTER TABLE THAMWDA1.WILLIAM

ADD (

TRANS_OPERATION VARCHAR2(1) DEFAULT 'I' NOT NULL,

T_STAMP VARCHAR2(26 byte) DEFAULT

SUBSTR(TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24.MI.SSxFF'),1,26) NOT NULL ,

TRANS_FLAG VARCHAR2(1) DEFAULT 'Y' NOT NULL) ;





3.9 Table instantiation

/*导出数据*/

host exp /@THAMW tables=THAMWDA1.WILLIAM object_consistent=y

file=/oracle/THAMWDA1.WILLIAM.dmp



在destination site依次�行：



connect /@THAMR



truncate table THAMWDA1.WILLIAM;

alter table THAMWDA1.WILLIAM drop supplemental log data (primary key,

unique, foreign key) columns;

/*导入数据*/

host imp /@THAMR fromuser=THAMWDA1 touser=THAMWDA1 ignore=y

streams_instantiation=y STREAMS_CONFIGURATION=y

file=/oracle/THAMWDA1.WILLIAM.dmp



connect /@THAMR



alter table THAMWDA1.WILLIAM drop supplemental log data (primary key,

unique, foreign key) columns;





3.10 �定not compare old value （capture)

BEGIN

DBMS_APPLY_ADM.COMPARE_OLD_VALUES(

object_name => 'THAMWDA1.WILLIAM',

column_list => '*',

operation => '*',

compare => false);

END;

/





3.11 Start APPLY in destination site

connect /@THAMR;



BEGIN

DBMS_APPLY_ADM.START_APPLY(apply_name => 'STREAM_APPLY');

END;

/





3.12 Start CAPTURE in source site

connect /@THAMW;



BEGIN

DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'STREAM_CAPTURE');

END;

/







注意：在原表的格式如下

CREATE TABLE WILLIAM

(

SID VARCHAR2(16 BYTE)

)

TABLESPACE MPPTUSR1

PCTUSED 0

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 1M

NEXT 1M

MINEXTENTS 1

MAXEXTENTS 2147483645

PCTINCREASE 0

BUFFER_POOL DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

结果是只能同步到Insert不能同步Update、delete

然后师傅说是没有INDEX的原因

我也不知道为什么

然后这样就可以了



CREATE TABLE WILLIAM

(

SID VARCHAR2(16 BYTE)

)

TABLESPACE MPPTUSR1

PCTUSED 0

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 1M

NEXT 1M

MINEXTENTS 1

MAXEXTENTS 2147483645

PCTINCREASE 0

BUFFER_POOL DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;





ALTER TABLE WILLIAM ADD (

PRIMARY KEY

(SID)

USING INDEX

TABLESPACE MPPTIDX1

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 512K

NEXT 512K

MINEXTENTS 1

MAXEXTENTS 2147483645

PCTINCREASE 0

));









估计是这行出现了问题：

alter table THAMWDA1.WILLIAM drop supplemental log data (primary key,

unique, foreign key) columns;



究竟为什么还是不清楚