Oracle11g 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 - Enterprise Edition - 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 "
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.
解決上面問題
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12778571/viewspace-681673/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- GetDlgItem() 出現錯誤Git
- Opencv出現detecMultiScale錯誤OpenCV
- 在Oracle11g Streams測試Streams資料傳輸Oracle
- Laravel 出現 419 錯誤Laravel
- AS打包出現app:transformClassesAndResourcesWithProguardForRelease錯誤APPORM
- 使用 sudo 命令出現錯誤
- FTP出現426錯誤FTP
- Flutter 最常出現的典型錯誤Flutter
- 配置nagios出現的錯誤iOS
- 執行Bex 出現 61704 錯誤
- Servlet訪問WebService出現錯誤ServletWeb
- 在schema模式下的Streams資料傳輸,出現某個表不能傳輸錯誤ORA-26687模式
- android 打包出現錯誤Error: ResourceNameAndroidError
- 埠占用出現的不同的錯誤:
- 網路連線出現意外錯誤
- 執行jdonMVC示例出現了錯誤MVC
- eclispe 出現超記憶體錯誤Lisp記憶體
- sloaris 出現“mount: I/O error”錯誤Error
- 經常出現 HTTP Status 500 -錯誤HTTP
- DBCA建庫出現CHMOD NOT FOUND錯誤
- JDBC 連oracle 時出現錯誤,,求助???JDBCOracle
- jboss+mysql+hibernate 出現錯誤!!!!!!!MySql
- vsftp出現500 OOPS錯誤FTPOOP
- Oracle11g R2 RAC安裝Oracle
- git push出現Permission denied (publickey)錯誤Git
- scp出現錯誤的解決辦法
- Qt5.3 列印示例時出現錯誤QT
- 編譯EJB出現錯誤的解決編譯
- 編譯實體Bean出現如下錯誤.....編譯Bean
- ontape出現段錯誤(未解決篇)
- Qt處理中文編碼出現錯誤QT
- nginx出現403錯誤的解決方法Nginx
- ORACLE11G解決ORA-00845錯誤Oracle
- sql出現結果集錯誤以及出現ora-600或者ora-7445錯誤的解決方法思路SQL
- 安裝oracle11g遇到的Oracle11g Error in invoking target類錯誤OracleError
- SqlServer NBU備份出現錯誤程式碼2SQLServer
- web前端小白經常出現“四”個錯誤Web前端
- mysql,出現同步停止的錯誤的處理MySql