Oracle 11g R2 Streams出現ORA-26744: ORA-26767:錯誤

問題：

在來源資料庫發現Streams異常，如：

查：select * from dba_capture看到異常錯誤

ORA-26744: STREAMS capture process "CAPTURE_STREAM" does not support "BHOMSWAS"."DBMS_TABCOMP_TEMP_UNCMP" because of the following reason:

ORA-26767: No enough redo log information for LogMiner

 

Oracle metalink的解釋：

Streams Capture Aborting With ORA-26767 Due To Temp Tables Created By DBMS_COMPRESSION [ID 1082323.1]

--------------------------------------------------------------------------------

修改時間22-NOV-2010    類型PROBLEM    狀態PUBLISHED 

 

In this Document

 Symptoms

 Changes

 Cause

 Solution

--------------------------------------------------------------------------------

Applies to:

Oracle Server -EnterpriseEdition - Version:11.2.0.0. to 11.2.0.0 - Release: 11.2 to 11.2

Information in this document applies to any platform.

 

Symptoms

Streams Capture is aborting with ORA-26767 . The following error message is logged in Capture trace

 

ORA-26744: STREAMS capture process "<capture name>" does not support "<schema>"."DBMS_TABCOMP_TEMP_UNCMP" because of the following reason:

ORA-26767: No enough redo log information for LogMiner

 

 

Sometime the table mentioned in above error message is either DBMS_TABCOMP_TEMP_UNCMP OR DBMS_TABCOMP_TEMP_CMP. These tables do not exist on source database.

 

-Streams Apply can fail with ORA-26714 and ORA-00942

 

-Streams slows down mining archive logs generated during maintenance window

 

Additional Explanation:

It appears that the "Automated Maintenance Window" jobs Segment Advisor calls dbms_compression which creates two tables called DBMS_TABCOMP_TEMP_UNCMP and DBMS_TABCOMP_TEMP_CMP in users schema

 

Changes

 

Cause

DBMS_COMPRESSION is a new utility introduce in 11GR2 which is used for Compression Advisory.

 

DBMS_COMPRESSION creates two temporary tables (namely,

DBMS_TABCOMP_TEMP_UNCMP &

DBMS_TABCOMP_TEMP_CMP)

 

while doing the analyze of the table in the table owner schema. These tables are compared to see what compression level can be achieved.

 

By default ddl for above mentioned tables has nologging option enabled.

 

Now if CAPTURE has schema level rule defined, then DDL/DML for these tables will be captured as well.Since ddl for above mentioned table has nologging option enabled., enough redo information for the capture process was not available, and hence CAPTURE failed with ORA-26767 .

 

Solution

The workaround would be to specify a negative rule for the tables DBMS_TABCOMP_TEMP_UNCMP, and DBMS_TABCOMP_TEMP_CMP.

This will skip the replication of these table.

 

Please see the below example and make the appropriate changes with respect your environment.

 

The negative rule condition here eliminates table named 'unwantedtable' in the SCOTT schema.

Use ADD_TABLE_RULES to specify the table. Specify the inclusion_rule => FALSE clause

in the ADD_TABLE_RULES command to place the rule in the negative rule set.

 

 

BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_RULES (

table_name => 'scott.unwantedtable',

streams_type => 'capture',

streams_name => 'strm01_capture',

queue_name => 'strmadmin.streams_queue',

include_dml => true,

include_ddl => true,

source_database => 'SOURCE.ORACLE.COM',

inclusion_rule => false ); --specifies the negative rule set

END;

/

 

解決：

1、在來源資料庫用streams的strmadmin使用者把caputer進程停止

SQL> begin

dbms_capture_adm.stop_capture(

capture_name => 'capture_stream');

end;

/

 

2、建立3個限制不傳輸表的規則

SQL> BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_RULES (

table_name =>'bhomswas.DBMS_TABCOMP_TEMP_UNCMP',

streams_type=>'capture',

streams_name=>'capture_stream',

queue_name=>'strmadmin.SOURCE_QUEUE',

include_dml=>true,

include_ddl=>true,

source_database=>'CBOMS',

inclusion_rule=>false);

END;

/

 

PL/SQL procedure successfully completed.

 

SQL> BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_RULES (

table_name =>'bhomswas.DBMS_COMPRESSION',

streams_type=>'capture',

streams_name=>'capture_stream',

queue_name=>'strmadmin.SOURCE_QUEUE',

include_dml=>true,

include_ddl=>true,

source_database=>'CBOMS',

inclusion_rule=>false);

END;

/

 

PL/SQL procedure successfully completed.

 

SQL> BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_RULES (

table_name =>'bhomswas.DBMS_TABCOMP_TEMP_CMP',

streams_type=>'capture',

streams_name=>'capture_stream',

queue_name=>'strmadmin.SOURCE_QUEUE',

include_dml=>true,

include_ddl=>true,

source_database=>'CBOMS',

inclusion_rule=>false);

END;

/

PL/SQL procedure successfully completed.

3、啟動caputer進程

SQL> begin

dbms_capture_adm.start_capture(

capture_name => 'capture_stream');

end;

/

PL/SQL procedure successfully completed.

解決上面問題