[试验] 验证BUG：现有streams表级单向环境下，增加新的表级单向时CREATE TABLE出错...

oracle说这是个bug，从9i开始所有平台都会涉及，验证一下
环境是上一篇的
http://space.itpub.net/?uid-317003-action-viewspace-itemid-596528
-- stop all capture/propagation/apply process
-- source
begin
dbms_capture_adm.stop_capture(
capture_name => 'capture_simp');
end;
/
select capture_name,status from dba_capture;
begin
dbms_propagation_adm.stop_propagation(
propagation_name => 'str1_to_str2');
end;
/
select propagation_name,status from dba_propagation;
-- target
begin
dbms_apply_adm.stop_apply(
apply_name => 'apply_simp');
end;
/
select apply_name,status from dba_apply;
-- target
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'scott.test',
    streams_type    => 'apply',
    streams_name    => 'apply_simp',
    queue_name      => 'strmadmin.streams_queue',
    include_dml     => true,
    include_ddl     => true,
    source_database => 'strm1.com',
    inclusion_rule  => true);
END;
/

-- source
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name     => 'scott.test',  
    streams_type   => 'capture',
    streams_name   => 'capture_simp',
    queue_name     => 'strmadmin.streams_queue',
    include_dml    => true,
    include_ddl    => true,
    inclusion_rule => true);
END;
/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'scott.test',
    streams_name            => 'str1_to_str2',
    source_queue_name       => 'strmadmin.streams_queue',
    destination_queue_name  => 'strmadmin.streams_queue@strm2.com',
    include_dml             => true,
    include_ddl             => true,
    source_database         => 'strm1.com',
    inclusion_rule          => true,
    queue_to_queue          => true);
END;
/

-- target
DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@strm2.com(
    source_object_name    => 'dbausr.test_nolog',
    source_database_name  => 'strm1.com',
    instantiation_scn     => iscn);
END;
/
 
-- s
BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name  => 'capture_simp');
END;
/

begin
dbms_propagation_adm.start_propagation(
propagation_name => 'str1_to_str2');
end;
/

-- t
BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_simp');
END;
/

-- s
16:36:16 strmadmin@STRM1> create table scott.test(id number(3));

Table created.
-- t
16:37:56 strmadmin@STRM2> select * from dba_apply_error;

APPLY_NAME                     QUEUE_NAME                     QUEUE_OWNER                    LOCAL_TRANSACTION_ID
------------------------------ ------------------------------ ------------------------------ ----------------------
SOURCE_DATABASE
-------------------------------------------------------------------------------------------------------------------
SOURCE_TRANSACTION_ID  SOURCE_COMMIT_SCN MESSAGE_NUMBER ERROR_NUMBER
---------------------- ----------------- -------------- ------------
ERROR_MESSAGE
-------------------------------------------------------------------------------------------------------------------
RECIPIENT_ID RECIPIENT_NAME                 MESSAGE_COUNT ERROR_CREATION_TIME
------------ ------------------------------ ------------- -------------------
APPLY_SIMP                     STREAMS_QUEUE                  STRMADMIN                      7.18.278
STRM1.COM
2.23.546                          928456              1          942
ORA-00942: table or view does not exist
          44 STRMADMIN                                  2 2009/05/13 16:40:32

APPLY_SIMP                     STREAMS_QUEUE                  STRMADMIN                      10.8.289
STRM1.COM
1.20.569                          931673              2        26687
ORA-26687: no instantiation SCN provided for "SCOTT"."" in source database "STRM1.COM"
          44 STRMADMIN                                  2 2009/05/13 16:40:46

-- Oracle internet Support Center(No:94159) said: when adding table rule,
-- DO NOT stop capture process, only stop propagation or apply process.
-- Then it will be fine when execute CREATE TABLE.
-- drop current rules
-- s
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_OWNER||'.'||RULE_NAME
from dba_streams_table_rules order by 1,4;
STREAMS_NAME                   STREAMS_TYP RULE_TY RULE_OWNER||'.'||RULE_NAME
------------------------------ ----------- ------- --------------------------
CAPTURE_SIMP                   CAPTURE     DML     STRMADMIN.TEST50
CAPTURE_SIMP                   CAPTURE     DDL     STRMADMIN.TEST51
CAPTURE_SIMP                   CAPTURE     DML     STRMADMIN.TEST_NOLOG35
CAPTURE_SIMP                   CAPTURE     DDL     STRMADMIN.TEST_NOLOG36
STR1_TO_STR2                   PROPAGATION DML     STRMADMIN.TEST52
STR1_TO_STR2                   PROPAGATION DDL     STRMADMIN.TEST53
STR1_TO_STR2                   PROPAGATION DML     STRMADMIN.TEST_NOLOG32
STR1_TO_STR2                   PROPAGATION DDL     STRMADMIN.TEST_NOLOG33
-- t
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_OWNER||'.'||RULE_NAME
from dba_streams_table_rules order by 1,4;
STREAMS_NAME                   STREAMS_TYP RULE_TY RULE_OWNER||'.'||RULE_NAME
------------------------------ ----------- ------- --------------------------
APPLY_SIMP                     APPLY       DML     STRMADMIN.TEST30
APPLY_SIMP                     APPLY       DDL     STRMADMIN.TEST31
APPLY_SIMP                     APPLY       DML     STRMADMIN.TEST_NOLOG22
APPLY_SIMP                     APPLY       DDL     STRMADMIN.TEST_NOLOG23
APPLY_SIMP                     APPLY       DML     STRMADMIN.TEST_NOLOG25
APPLY_SIMP                     APPLY       DDL     STRMADMIN.TEST_NOLOG26
-- s
BEGIN
DBMS_STREAMS_ADM.REMOVE_RULE(
rule_name => 'STRMADMIN.TEST50',
streams_type => 'capture',
streams_name => 'capture_simp');
END;
/
BEGIN
DBMS_STREAMS_ADM.REMOVE_RULE(
rule_name => 'STRMADMIN.TEST51',
streams_type => 'capture',
streams_name => 'capture_simp');
END;
/

BEGIN
DBMS_STREAMS_ADM.REMOVE_RULE(
rule_name => 'STRMADMIN.TEST52',
streams_type => 'propagation',
streams_name => 'str1_to_str2');
END;
/
BEGIN
DBMS_STREAMS_ADM.REMOVE_RULE(
rule_name => 'STRMADMIN.TEST53',
streams_type => 'propagation',
streams_name => 'str1_to_str2');
END;
/

select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_OWNER||'.'||RULE_NAME
from dba_streams_table_rules order by 1,4;
STREAMS_NAME                   STREAMS_TYP RULE_TY RULE_OWNER||'.'||RULE_NAME
------------------------------ ----------- ------- --------------------------
CAPTURE_SIMP                   CAPTURE     DML     STRMADMIN.TEST_NOLOG35
CAPTURE_SIMP                   CAPTURE     DDL     STRMADMIN.TEST_NOLOG36
STR1_TO_STR2                   PROPAGATION DML     STRMADMIN.TEST_NOLOG32
STR1_TO_STR2                   PROPAGATION DDL     STRMADMIN.TEST_NOLOG33

-- t
BEGIN
DBMS_STREAMS_ADM.REMOVE_RULE(
rule_name => 'STRMADMIN.TEST30',
streams_type => 'apply',
streams_name => 'apply_simp');
END;
/
BEGIN
DBMS_STREAMS_ADM.REMOVE_RULE(
rule_name => 'STRMADMIN.TEST31',
streams_type => 'apply',
streams_name => 'apply_simp');
END;
/

select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_OWNER||'.'||RULE_NAME
from dba_streams_table_rules order by 1,4;
STREAMS_NAME                   STREAMS_TYP RULE_TY RULE_OWNER||'.'||RULE_NAME
------------------------------ ----------- ------- --------------------------
APPLY_SIMP                     APPLY       DML     STRMADMIN.TEST_NOLOG22
APPLY_SIMP                     APPLY       DDL     STRMADMIN.TEST_NOLOG23
APPLY_SIMP                     APPLY       DML     STRMADMIN.TEST_NOLOG25
APPLY_SIMP                     APPLY       DDL     STRMADMIN.TEST_NOLOG26
-- t
begin
dbms_apply_adm.stop_apply(
apply_name => 'apply_simp');
end;
/
select apply_name,status from dba_apply;
APPLY_NAME                     STATUS
------------------------------ --------
APPLY_SIMP                     DISABLED
STRM2_APPLY                    DISABLED
-- s
select capture_name,status from dba_capture;
CAPTURE_NAME                   STATUS
------------------------------ --------
CAPTURE_SIMP                   ENABLED
STRM1_CAPTURE                  DISABLED

select propagation_name,status from dba_propagation;
PROPAGATION_NAME               STATUS
------------------------------ --------
STR1_TO_STR2                   ENABLED
STRM1_TO_STRM2                 DISABLED

-- target
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'scott.test',
    streams_type    => 'apply',
    streams_name    => 'apply_simp',
    queue_name      => 'strmadmin.streams_queue',
    include_dml     => true,
    include_ddl     => true,
    source_database => 'strm1.com',
    inclusion_rule  => true);
END;
/

-- source
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name     => 'scott.test',  
    streams_type   => 'capture',
    streams_name   => 'capture_simp',
    queue_name     => 'strmadmin.streams_queue',
    include_dml    => true,
    include_ddl    => true,
    inclusion_rule => true);
END;
/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'scott.test',
    streams_name            => 'str1_to_str2',
    source_queue_name       => 'strmadmin.streams_queue',
    destination_queue_name  => 'strmadmin.streams_queue@strm2.com',
    include_dml             => true,
    include_ddl             => true,
    source_database         => 'strm1.com',
    inclusion_rule          => true,
    queue_to_queue          => true);
END;
/

-- target
DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@strm2.com(
    source_object_name    => 'dbausr.test_nolog',
    source_database_name  => 'strm1.com',
    instantiation_scn     => iscn);
END;
/
 
BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_simp');
END;
/

-- test CREATE TABLE
--s
17:40:46 strmadmin@STRM1> create table scott.test(id number(3));
Table created.
--t
17:41:56 strmadmin@STRM2> select * from dba_apply_error;
APPLY_NAME                     QUEUE_NAME                     QUEUE_OWNER                    LOCAL_TRANSACTION_ID
------------------------------ ------------------------------ ------------------------------ ----------------------
SOURCE_DATABASE
-------------------------------------------------------------------------------------------------------------------
SOURCE_TRANSACTION_ID  SOURCE_COMMIT_SCN MESSAGE_NUMBER ERROR_NUMBER
---------------------- ----------------- -------------- ------------
ERROR_MESSAGE
-------------------------------------------------------------------------------------------------------------------
RECIPIENT_ID RECIPIENT_NAME                 MESSAGE_COUNT ERROR_CREATION_TIME
------------ ------------------------------ ------------- -------------------
APPLY_SIMP                     STREAMS_QUEUE                  STRMADMIN                      6.45.299
STRM1.COM
8.28.577                          936551              2        26687
ORA-26687: no instantiation SCN provided for "SCOTT"."" in source database "STRM1.COM"
          44 STRMADMIN                                  2 2009/05/13 17:44:42

-- test other DDL
--t
17:48:40 strmadmin@STRM2> create table scott.test as select * from scott.test@strm1.com;
--s
17:50:31 strmadmin@STRM1> alter table scott.test add(name varchar(20));
Table altered.
--t
17:50:43 strmadmin@STRM2> desc scott.test;
Name                Null?    Type
------------------- -------- ------------
ID                           NUMBER(3)
NAME                         VARCHAR2(20)
--s
17:50:39 strmadmin@STRM1> drop table scott.test purge;
Table dropped.
--t
17:50:58 strmadmin@STRM2> desc scott.test;
ERROR:
ORA-04043: object scott.test does not exist

-- Conclusion: it proves that solution offered by Oracle cannot handle this error.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/317003/viewspace-596529/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/317003/viewspace-596529/