今天遇到的有關ORA-04020: deadlock detected while trying to lock object LBACSYS.L

orchidllh發表於2005-02-24
事情的起因是我要將當前一個線上的資料庫imp到一個測試庫進行測試。
exp很順利,沒有問題
在測試庫上drop user * cascade;
然後重新建使用者,然後把剛才的備份都匯入,都很順利。

然後我要用pl/sql developer連線,察看匯入的表的時候,提示ORA-00942:表或試圖不存在。
我有一點心不在焉的,隨便百度了一把,看到有人說應該執行:
@$ORACLE_HOME/rdbms/admin/catproc.sql
我就執行了,提示很多錯誤,以下是第一個:
create or replace package dbms_standard is
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
SYS.CDC_CREATE_CTABLE_AFTER
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "LBACSYS.LBAC_EVENTS" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2

。。。。。。

我都不是很上心,想著沒有關係,這些包未見得有用:
然後我可能有點兒暈了,以為還沒有imp,就繼續imp:
但是這時候開始,imp提示錯誤了:
Import: Release 9.2.0.1.0 - Production on Thu Feb 24 13:49:37 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


IMP-00058: ORACLE error 4045 encountered
ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC$LOGON
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2Username: pointcard
Password:

我想那就重新編譯唄,就這麼三板斧,呵呵:
@$ORACLE_HOME/rdbms/admin/utlrp.sql
提示錯誤:
PL/SQL procedure successfully completed.

CREATE TABLE utl_recomp_invalid(obj# number)
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
SYS.CDC_CREATE_CTABLE_BEFORE
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
.......

這時候發現客戶端已經連不上了,還是提示LBACSYS.LBAC_EVENTS的錯誤。我有點兒傻了,雖然是測試庫,但是還是有正在測試的應用在用的,而正因為是測試庫,所以沒有每天備份,好在最後一次備份是大前天,也就是還不是很麻煩,暗自慶幸當時隨手備了一個。

再往下我決定重啟試試,我甚至都做好了啟不了重建一個資料庫的準備了,有備份在手,就是比較有信心,呵呵。

shutdown immediate 沒有反應

kill掉 showdown abort
再startup再shutdown immediate 都沒有問題
察看alert.log,都是剛才執行.sql的錯誤:
Thu Feb 24 14:21:08 2005
Errors in file /home/oracle/admin/dwtest/udump/dwtest_ora_23467.trc:
ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC$LOGON
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2

再去百度,這次比較細心了,看到Fenng在itpub的文章提到類似的問題:http://www.itpub.net/238819.html
他是在exp提示9204的時候執行了$ORACLE_HOME/rdbms/admin/catpatch.sql
並且執行了兩遍,然後發現客戶端連不上了。跟我的過程有一點像,再往下看到了解決方案:
The reason for this problem seems to be an Upgrade for Label-Security
even if it's not installed.  //Label security 沒有安裝,居然補丁去預設給升級? 

Solution:

shutdown immediate;
startup migrate;
alter view lbacsys.lbac$all_table_policies compile;
alter package lbacsys.lbac_events compile body;
shutdown immediate;
startup;

按照上面步驟的執行,就沒有問題。

非常感謝Fenng給我的這個幫助,儘管他應該不會看到這裡啦,哈哈哈。

最後發現pl/sql developer提示9204是需要:
GRANT SELECT ANY TABLE TO *;
GRANT SELECT ANY SEQUENCE TO *;
GRANT SELECT ANY DICTIONARY TO *;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/51862/viewspace-180602/,如需轉載,請註明出處,否則將追究法律責任。

相關文章