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.2Information in this document applies to any platform. SymptomsStreams 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 CauseDBMS_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 . SolutionThe 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 clausein the ADD_TABLE_RULES command to place the rule in the negative rule set. BEGINDBMS_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 setEND;/ 解决：1、在源数据库用streams的strmadmin用户把caputer进程停止SQL> begindbms_capture_adm.stop_capture（capture_name => "capture_stream"）;end;/ 2、建立3个限制不传输表的规则SQL> BEGINDBMS_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> BEGINDBMS_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> BEGINDBMS_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> begindbms_capture_adm.start_capture（capture_name => "capture_stream"）;end;/ PL/SQL procedure successfully completed.解决上面问题在Oracle 11g Streams单向传输的基础上配置Streams双向传输测试在Oracle 11g Streams测试Streams数据传输相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）