ORA-26744&ORA-26775

最新推荐文章于 2024-09-30 09:09:52 发布

cjanvb5410

最新推荐文章于 2024-09-30 09:09:52 发布

阅读量188

点赞数

文章标签：数据库

刚才监控报表数据库发现stream的capture进程abort掉了，具体报错如下：

Streams CAPTURE CP01 for DOWNSTREAM_CAPTURE with pid=20, OS id=13879 stopped
Errors in file /oracle/diag/rdbms/myrpt/myrpt/trace/myrpt_cp01_13879.trc:
ORA-26744: STREAMS capture process "DOWNSTREAM_CAPTURE" does not support "MYNET_APP"."USERTAGCOUNT" because of the following reason:
ORA-26775: Temporary table not supported
Wed Mar 14 16:41:50 2012
Warning: STREAMS failed to prepare instantiation for table "MYNET_APP"."USERTAGCOUNT".
Wed Mar 14 16:42:02 2012
Streams CAPTURE CP01 for DOWNSTREAM_CAPTURE started with pid=20, OS id=13937
Wed Mar 14 16:42:03 2012

具体错误信息如下：
26744, 00000, "STREAMS capture process \"%s\" does not support \"%s\".\"%s\" because of the following reason: %s"
// *Cause: STREAMS capture encountered a table with an unsupported
//         property. The most common reason is an unsupported column
//         data type.
// *Action: Revise the Capture rules to skip over the table in
//           question. One way might be to add a negative rule
//           excluding changes from the unsupported table from being
//           captured.
//
//           Also query the DBA_STREAMS_UNSUPPORTED view to determine
//           which tables are not supported by STREAMS and for what
//           reason. Consider adding negative rules for any tables
//           that may be captured, but are present in this view.
//
//           For potential workarounds to certain unsupported properties, see
//           Metalink.

到源库查询相关表发现，这张表是我们新来的dba童鞋跑存储过程用到的一张临时表，从具体报错信息也可以看出，对这类表是不支持的，具体哪些表stream不支持，可查询DBA_STREAMS_UNSUPPORTED 视图，最后建议把这类表换成一般的表作为临时表，由于是下游捕获，最后在目标库执行navigative rule忽略掉对该表的capture就可以解决了，具体操作如下：

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES (
table_name => 'MYNET_APP.USERTAGCOUNT',
streams_type => 'capture',
streams_name => 'DOWNSTREAM_CAPTURE',
queue_name => 'STRMADMIN.DOWNSTREAM_Q',
include_dml => true,
include_ddl => true,
source_database => 'UAT',
inclusion_rule => false ); --specifies the negative rule set
END;
/

然后启动capture进程就可以了，信息如下：

Wed Mar 14 16:46:21 2012
LOGMINER: session#=19, reader MS00 pid=47 OS id=13979 sid=267 started
Wed Mar 14 16:46:21 2012
LOGMINER: session#=19, builder MS01 pid=49 OS id=13981 sid=272 started
Wed Mar 14 16:46:21 2012
LOGMINER: session#=19, preparer MS02 pid=50 OS id=13983 sid=24 started
LOGMINER: Begin mining logfile for session 19 thread 1 sequence 10259, /arch/1_10259_716985918.dbf
CP01: large txn committed, xid: 0x0001.01c.00017a2c
CP01: large txn committed, xid: 0x0009.014.0001aa2b
CP01: large txn committed, xid: 0x0005.01d.0001b411
CP01: large txn committed, xid: 0x000a.023.0001ac1d
LOGMINER: End   mining logfile for session 19 thread 1 sequence 10259, /arch/1_10259_716985918.dbf
LOGMINER: Begin mining logfile for session 19 thread 1 sequence 10260, /arch/1_10260_716985918.dbf
LOGMINER: End   mining logfile for session 19 thread 1 sequence 10260, /arch/1_10260_716985918.dbf
LOGMINER: Begin mining logfile for session 19 thread 1 sequence 10261, /arch/1_10261_716985918.dbf
Wed Mar 14 16:46:31 2012
LOGMINER: End   mining logfile for session 19 thread 1 sequence 10261, /arch/1_10261_716985918.dbf
LOGMINER: Begin mining logfile for session 19 thread 1 sequence 10262, /arch/1_10262_716985918.dbf
LOGMINER: End   mining logfile for session 19 thread 1 sequence 10262, /arch/1_10262_716985918.dbf
LOGMINER: Begin mining logfile for session 19 thread 1 sequence 10263, /arch/1_10263_716985918.dbf
Wed Mar 14 16:46:33 2012
LOGMINER: End   mining logfile for session 19 thread 1 sequence 10263, /arch/1_10263_716985918.dbf
LOGMINER: Begin mining logfile for session 19 thread 1 sequence 10264, /oracle/oradata/myrpt/stdbyredo01.log
......