流capture报错ORA-26854: STREAMS 没有队列的入队或出队权限。

dennis

DB版本
1    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
2    PL/SQL Release 11.2.0.3.0 - Production
3    CORE    11.2.0.3.0    Production
4    TNS for Linux: Version 11.2.0.3.0 - Production
5    NLSRTL Version 11.2.0.3.0 - Production

正常steams配置,以前都正常,这次出现一个新的错误
select capture_name,queue_name,rule_set_name,negative_rule_set_name,status,error_number,error_message from dba_capture;

1    CAPTURE_QQ    QUEUE_QQ    RULESET$_56        ABORTED    26854    ORA-26854: STREAMS  没有队列的入队或出队权限。
ORA-26854: STREAMS propagation receiver 没有队列的入队或出队权限。
ORA-02063: 紧接着 line (起自 PETDOG)


还请帮忙看看.谢谢
dennis

Maclean Liu(刘相兵

action plan:


分别在源和目标端 运行  以下地址中的脚本
http://www.askmaclean.com/archiv ... -10g-release-2.html

并将生成的HTML文件上传为附件

dennis

单向的基于table的streams复制,手工配置的.附件是脚本的运行结果,真长啊,谢谢分析.

Maclean Liu(刘相兵

[oracle@nas ~]$ oerr ora 26854
26854, 00000, "STREAMS %s has no enqueue or dequeue privileges to the queue."
// *Cause:  The GoldenGate, XStream or Streams user did not have enqueue or dequeue
//          privilege to the queue.
// *Action: Grant GoldenGate, XStream or Streams user enqueue or dequeue privileges to the queue.


你是如何授予 streams admin用户权限的？

dennis

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin;
GRANT EXECUTE ON DBMS_AQ TO strmadmin;
GRANT EXECUTE ON DBMS_AQADM            TO strmadmin;
GRANT EXECUTE ON DBMS_CAPTURE_ADM      TO strmadmin;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM  TO strmadmin;
GRANT EXECUTE ON DBMS_STREAMS_ADM      TO strmadmin;
GRANT EXECUTE ON DBMS_APPLY_ADM        TO strmadmin;
GRANT EXECUTE ON DBMS_FLASHBACK        TO strmadmin;
BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
    grantee      => 'strmadmin',
    grant_option => FALSE);
END;
/
BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,
    grantee      => 'strmadmin',
    grant_option => FALSE);
END;
/

begin dbms_streams_auth.grant_admin_privilege(grantee => 'strmadmin',grant_privileges => true); end;

Maclean Liu(刘相兵

是否在 2端都配置了以上权限

尝试：
GRANT DBA TO strmadmin;