ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用10046进行诊断一例

用10046进行诊断一例

原创 Linux操作系统 作者:space6212 时间:2019-03-13 10:03:05 0 删除 编辑

今天,一直运行正常的物化视图刷新忽然执行不正常
SQL> exec dbms_refresh.refresh('WAI_REFRESH');

begin dbms_refresh.refresh('WAI_REFRESH'); end;

ORA-02019: connection description for remote database not found
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1


但是手工刷新正常(item属于WAI_REFRESH刷新组中)
SQL> exec dbms_snapshot.refresh('item');

PL/SQL procedure successfully completed

检查DB LINK情况
SQL> select distinct master_link from user_mviews;

MASTER_LINK
--------------------------------------------------------------------------------
@WAI.SOUCHANG.COM

SQL> select * from dual@wai.souchang.com;

DUMMY
-----
X

一切正常。百思不得其解,决定用10046事件试试到底发生什么。
SQL> @gettrace

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/admin/sc2test/udump/sc2test_ora_30715.trc

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered

SQL> exec dbms_refresh.refresh('WAI_REFRESH');

begin dbms_refresh.refresh('WAI_REFRESH'); end;

ORA-02019: connection description for remote database not found
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

用tkprof格式化/opt/oracle/admin/sc2test/udump/sc2test_ora_30715.trc,有如下可疑信息
The following statement encountered a error during parse:

select errcount from deferrcount@IMAGE.SOUCHANG.COM where destination = 'IMAGE.SOUCHANG.COM'

Error encountered: ORA-02019

检查user_refresh_children,发现有一个OWNER是test用户的,它在test_tag_group用户下是一个同义词

SQL> select OWNER,NAME,ROWNER from user_refresh_children;

OWNER NAME ROWNER
------------------------------ ------------------------------ ------------------------------
TEST_TAG_GROUP USERS TEST_TAG_GROUP
TEST IMAGE TEST_TAG_GROUP
TEST_TAG_GROUP AUTHORITIES TEST_TAG_GROUP
TEST_TAG_GROUP AVERREVIEWRATE TEST_TAG_GROUP
TEST_TAG_GROUP CATEGORY TEST_TAG_GROUP
TEST_TAG_GROUP CHANNEL TEST_TAG_GROUP
TEST_TAG_GROUP CLOB_CONTENT TEST_TAG_GROUP
TEST_TAG_GROUP FLEA_MARKET_INFO TEST_TAG_GROUP
TEST_TAG_GROUP ITEM TEST_TAG_GROUP
TEST_TAG_GROUP ITEM_TAG TEST_TAG_GROUP
TEST_TAG_GROUP KEYWORD TEST_TAG_GROUP
TEST_TAG_GROUP LOGS TEST_TAG_GROUP

.....................

进一步检查发现,test_tag_group用户下并没有@IMAGE.SOUCHANG.COM这个DBLINK。
知道错误原因了,问题就容易解决了。在本例中,只需在test_tag_group中建立新建一个与IMAGE.SOUCHANG.COM一样的DB LINK 即可。也可以把test用户下的DB LINK修改为public类型的。

最后关闭10046事件
SQL> alter session set events '10046 trace name context off';

Session altered

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-63720/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 简单模拟死锁
请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    188
  • 访问量
    142056