用10046进行诊断一例

今天，一直运行正常的物化视图刷新忽然执行不正常
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/231499/viewspace-63720/