Oracle的ORA-00604錯誤案例學習

ORA-00604 error occurred at recursive SQL level string

Cause:An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).

Action:If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Customer Support.

ORA-00604: 遞迴某個SQL 層時出現錯誤

原因:在運行一條遞迴SQL語句(該語句將應用於對內部表或資料字典的操作)時，發生錯誤。

方案:如果上述描述的錯誤所在棧可以被修複，則修複並繼續運行;否則，請聯絡Oracle客服。當然，那是Oracle官方的解決辦法。我曾經記得有個高手總結了關於ORA-00604/ORA-04031問題的解決：

修改INIT.ora

添加

_db_handles_cached = 0
 

並重新啟動資料庫.

分析:ORA-00604這個資訊表明，在資料庫執行內部SQL語句時，發生了錯誤。比如，要往表中插入一行資料，但沒有可擴充的空間。ORACLE於是去查尋，哪兒可以建立下一個擴充空間，它有多大小，但沒有成功。一般在發生ORA-00604錯誤時，還伴隨著其它的錯誤，例如:ORA-1547等。

首先，應當檢查警告檔案alertSID.log，尋找有關ORA-600類的資訊。

該錯誤最常見的原因是資料庫檔案initSID.ora中的參數OPEN_CURSORS值太小。可以修改initSID.ora檔案，OPEN_CURSORS的值一般為255。修改完後，宕下ORACLE，再重新啟動。

還可以設定並啟動資料庫的事件跟蹤功能。在initSID.ora中加上一行:

event = "00604 trace name errorstack"
 

宕下並重新啟動ORACLE，使這個事件跟蹤參數起作用。這樣，當再發生ORA-604錯誤時，有關資訊就儲存在TRACE檔案中。

造成ORA-604錯誤的其它原因可能有:

- initSID.ora中，參數DC_FREE_EXTENTS或ROW_CACHE_ENQUEUES太低。可以根據作業系統和資料庫的情況，適當增加這兩個參數的值，宕下並重新啟動ORACLE。

- 運行超出空間(伴隨ORA-1547錯誤)。這時，要對錶空間添加新檔案，即增加資料表空間的大小。

- 達到了MAX_EXTENTS(伴隨ORA-1556錯誤)。如果這樣，就要修改表，允許更多的擴充。請從技術手冊中尋找MAX_EXTENTS的最大值。如果已經達到了最大值，必須用compress extents選項，把表卸出(export)，再匯入(import)資料庫中。

 

案例一:Oracle執行遞迴查詢的時候出錯

問題描述:我經常遇到ORA-00604 和ORA-01000(開啟遊標數量達到最大值)錯誤。然而，當我檢查代碼的時候，所有的結果集和語句對象都在最後的塊中關閉了(我使用的是JDBC)。我執行的查詢是一個Oracle遞迴查詢(以這個開始並通過這個串連)。您能告訴我是哪裡出現了問題，以及在什麼樣的情況下會出現上述的錯誤嗎?

解決方案:可能是init.ora 檔案中的open_cursors 參數值的設定太低了。這個參數的預設值是非常低的(50)。它應該設定為200或者更高。即使是你關閉了結果集，但是你並沒有在JAVA代碼中關閉SQL語句，就會導致這個問題。

如果設定為yes的話，那麼確保你的活動串連池啟用了(為了效能的原因)，否則設定為no。

請你的資料庫管理員監視資料庫，並看看使用V$OPEN_CURSORS 和 V$SYSSTAT資料字典視圖的條目。

 

案例二:Exp出錯的一個案例

問題描述:客戶用的Linux系統，Redhat 企業版(RHEL 3.0).資料庫,安裝的9iR2, 前一段時間升級過.現在的版本是9204.

客戶準備要做Exp匯出，以前一直系統沒有空間.先給給系統擴了一些空間。Linux下的LVM還算比較好用。雖然檔案系統用的是ext3 ,要暫時停機.

進行匯出操作,不成功,發現系統報告錯誤:

EXP-00056: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00000: Export terminated unsuccessfully
 

很多朋友可能對這個錯誤都很熟悉.

哦,對了,客戶說是升級過資料庫，首先猜測是不是升級有問題?畢竟在論壇上類似升級不成功的問題看過很多了.

執行$ORACLE_HOME/rdbms/admin/catpatch.sql 指令碼.

同時要注意調大java_pool_size 和shared_pool_size這兩個參數的大小,要不重新來就耽誤時間了，不要犯低級錯誤

SQL>shutdown immediate;
SQL>startup migrate;
SQL>@?/rdbms/admin/catpatch.sql
 

之後查看Spool 出來的日誌. 發現有編譯錯誤，重新執行了第二次. 等待......之有這個時候我才想起才抱怨CPU不夠快,記憶體不夠大

這次Log沒錯誤.不料想....................使用者串連報告錯誤:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC_EVENTS
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
 

發現connect / as sysdba 還是可以登陸進去的。

看來是 LBACSYS.LBAC_EVENTS的狀態有點問題。聯結進去，編譯一下如何? 我的如意算盤是@?/rdbms/admin/utlrp.sql執行一下就沒有問題了，不料根本沒有用，錯誤依然。當時有些頭暈，這系統還沒有備份呢，看來有些麻煩了(心裡暗地埋怨客戶，一直不讓備份，總說"等等再說",作為一個DBA說話總不被重視也挺悲哀的不是? ，雖然我自己偷著有個備份，不過還是上次升級時候的呢)，趕緊上網Metalink查查，這裡網路速度還不錯 LBACSYS.LBAC_EVENTS 作為關鍵詞，找到如下的資訊:

The reason for this problem seems to be an Upgrade for Label-Security
even if it's not installed. 　//Label security 沒有安裝，居然補丁去預設給升級?
 

解決方案:

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

技術服務人員說這是個Bug.但是普通使用者不可見. 不太放心，再找找，在Suse.com網站的Maillist也發現了一則類似的案例，看來還可以,心裡有底了。

按照上面的執行，重新檢查，OK。

總結一下

其實是一個很沒有技術含量的Case。首先以前升級的時候至少要測試一下Export是否可以(Export已經成為升級成功的一個標誌了!) 其次,準備不夠充分,早成了手忙腳亂.所幸不是關鍵系統,使用者還可以容忍.Oracle 總說微軟是個爛公司,其實他們才真的夠欄.Bug多的不可勝數.

 

案例三:使用網路應用程式的時候出現遞迴SQL錯誤

問題描述:當我使用網路應用程式的時候，遇到了下面的這個錯誤。

ORA-00604: 遞迴SQL1級的時候出現錯誤。

ORA-04031: 無法分配4200位元組的共用記憶體，"RBKS_BK_INFO", "sga_heap", "library cache"。

這些錯誤資訊是什麼意思?我該如何解決它們?它們是在應用程式裡面還是資料庫裡面?

解決方案:您應該使用的是Oracle 8.1.7.4之前版本的Oracle。第一個錯誤資訊告訴你Oracle針對你的行為執行的SQL 陳述式失敗了。ORA-4031告訴你為什麼它會失敗。ORA-4031錯誤資訊的意思是你沒有獲得足夠的空閑空間。你可以增加你的SHARED_POOL_SIZE，重新啟動資料庫再拭一次。這個bug已經在後續的補丁包中修複了。如果你使用的不是這個版本，你可以應用一下補丁包。

 

案例四:Sql_trace進行Oracle診斷案例

問題說明:很多時候，在我們進行資料庫操作時，比如drop user,drop table等，經常會遇到這樣的錯誤

ORA-00604: error occurred at recursive SQL level 1 .

這樣的提示，很多時候是沒有絲毫用處的.本案例就這一類問題提供一個思路及方法供大家參考.

1. drop user出現問題

報出以下錯誤後退出

ORA-00604: error occurred at recursive SQL level 1

ORA-00942: table or view does not exist .

關於 recursive SQL 錯誤我們有必要做個簡單說明.

我們知道，當我們發出一條簡單的命令以後

Oracle資料庫要在後台解析這條命令，並轉換為Oracle資料庫的一系列後台操作.

這些後台操作統稱為遞迴sql.

比如create table這樣一條簡單的DDL命令,Oracle資料庫在後台，實際上要把這個命令轉換為對於obj$,tab$,col$等底層表的插入操作.Oracle所作的工作可能比我們有時候想的要複雜的多.

2.跟蹤問題

我們知道Oracle提供sql_trace的功能

可以用於跟蹤Oracle資料庫的後台遞迴操作.

通過追蹤檔案，我們可以找到問題的所在

以下是格式化(tkprof)後的輸出:

The following statement encountered a error during parse:
DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = 'WAPCOMM'
Error encountered: ORA-00942
 

Oracle把錯誤資訊首先呈現出來,我們看到ORA-00942錯誤是由於SDO_GEOM_METADATA_TABLE表/視圖不存在所致,問題由此可以定位.

對於這一類的錯誤，定位問題以後解決的方法就要依據具體問題原因而定了。

3.問題定位

對於本案例，通過Metalink獲得以下解釋:

Problem Description
The Oracle Spatial Option has been installed and you are encountering
the following errors while trying to drop a user, who has no spatial tables,
connected as SYSTEM:
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7
A 942 error trace shows the failing SQL statement as:
DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = ''
Solution Description
 

(1)

Create a synonym for SDO_GEOM_METADATA_TABLE under SYSTEM which points to
MDSYS.SDO_GEOM_METADATA_TABLE.
 

對於本例，為MDSYS.SDO_GEOM_METADATA_TABLE建立一個同義字即可解決,是相對簡單的情況.

(2)

Now the user can be dropped connected as SYSTEM.
Related Documents
ORA-604 and ORA-942 Reported During DROP USER CASCA
 

4.實際處理

MDSYS.SDO_GEOM_METADATA_TABLE為Spatial對象

如果未使用Spatial選項，可以刪除

SQL> connect / as sysdbaConnected.
SQL> select * from dba_sdo_geom_metadata order by owner;
select * from dba_sdo_geom_metadata order by owner
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-04063: view "MDSYS.DBA_SDO_GEOM_METADATA" has errors
SQL> select object_name from dba_objects where object_name like '%SDO%';
OBJECT_NAME
ALL_SDO_GEOM_METADATA
ALL_SDO_INDEX_INFO
ALL_SDO_INDEX_METADATA
DBA_SDO_GEOM_METADATA
DBA_SDO_INDEX_INFO
DBA_SDO_INDEX_METADATA
....
DBA_SDO_GEOM_METADATA
DBA_SDO_INDEX_INFO
...
SDO_WITHIN_DISTANCE
USER_SDO_GEOM_METADATA
USER_SDO_INDEX_INFO
USER_SDO_INDEX_METADATA
88 rows selected.
SQL> drop user MDSYS cascade;
User dropped.
SQL> select owner,type_name from dba_types where type_name like 'SDO%';
no rows selected
SQL>
SQL> alter session set sql_trace=true;
Session altered.
SQL> drop user wapcomm;
User dropped.
SQL> alter session set sql_trace=false;
Session altered.
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
 

這時使用者得以順利drop

5.一點總結

使用sql_trace可以追蹤資料庫的很多後台操作有利於我們發現問題的所在,很多時候，我們想要研究Oracle的內部活動或後台操作,也可以通過sql_trace跟蹤,sql_trace/10046 是Oracle提供的最為有效診斷工具之一.

 

案例五:表更新時發生遞迴SQL2級失敗錯誤

問題描述:表更新的時候失敗了，並且產生了一條ORA-00604 錯誤資訊。這個錯誤發生在遞迴SQL 2級。

解決方案:不幸的是，這個錯誤並不能告訴你Oracle資料庫在錯誤發生的時候正要做什麼。當你執行一條SQL語句的時候，Oracle資料庫輝為你在幕後做很多事情。例如，考慮下面的SQL語句:

UPDATE emp SET sal = sal*1.05 WHERE empno=1001;
 

這條SQL語句給號碼為1001的僱員漲5%的工資。當你執行這條語句的時候，Oracle查詢資料目錄來確定是否有這個表或者你是否使用了同義字。一旦它找到了資料庫物件，Oracle查詢資料字典來判斷你是否擁有訪問這個對象的許可權。那麼，Oracle到底是如何與資料字典進行互動的呢?它執行一條自己的SQL 陳述式。這些Oracle為你執行的SQL語句被稱為“遞迴”SQL語句。你最初的SQL 陳述式是0級。Oracle為你執行的遞迴SQL語句是1級。有時候，一條遞迴SQL語句又會引起自己的遞迴SQL語句，就是2級。

在你的案例中，有一個2級的遞迴SQL語句正在執行，並且產生了問題。為瞭解決問題，你需要找出執行的是什麼遞迴SQL語句引起的錯誤。要做到這一點，你必須啟動會話中的追蹤。首先，執行下面的SQL 陳述式:

　　ALTER SESSION SET sql_trace=TRUE;
 

然後，執行你的更新語句。你會看到ORA-604 錯誤。接下來，執行下面的語句:

　　ALTER SESSION SET sql_trace=FALSE;
 

現在到你為資料庫定義的USER_DUMP_DEST 起始參數上的路徑去。那裡應該有一個時間戳記為目前時間的檔案。那個就是你產生的追蹤檔案。你可以開啟檔案並檢查遞迴SQL語句，其中包括引起錯誤的一條。(接近追蹤檔案的末尾)。

 

 

案例六:串連資料庫使用者的時候遇到ORA-00604錯誤

問題描述:當我試圖串連到資料庫使用者的時候，得到了如下的錯誤資訊:ORA-00604:遞迴SQL 1級的時候出現錯誤。但是如果我使用資料庫管理員的角色的時候，使用者就能夠串連。系統使用者可以串連，但是scott 就不能串連。

解決方案:Oracle為你在幕後做了很多的工作。它在自己的SQL 陳述式的全過程中進行這項工作。Oracle發布給你的任何的SQL 陳述式都是“遞迴的SQL”語句。應該有很多的SQL 陳述式會引起你遇到的問題。我建議你所做的就是在INIT.ORA檔案中設定SQL_TRACE=TRUE，然後重新啟動資料庫。然後複製ORA-604錯誤。這會在你的USER_DUMP_DEST目錄中產生所有使用者進程的大量追蹤檔案。在錯誤發生之後，立即關閉資料庫，並設定SQL_TRACE=FALSE。然後再一次啟動資料庫。現在通過追蹤檔案，你就可以USER_DUMP_DEST目錄中產生的追蹤檔案中尋找ORA-604錯誤那一條資訊。就在那裡，你就發現ORA-604錯誤是哪一個遞迴SQL語句產生的，以及實際發生的錯誤情況。你的解決方案依賴於語句和實際的錯誤。

 

案例七:有人Move了系統資料表Dependencie$表, Crash了

今天有人問我這樣之後能不能恢複, 我想基本上已經不能了. 在open時報ORA-01092號錯誤, 我查了一下event也沒有這方面的合適的event啊, 我推薦用不完全恢複, 不過好象是沒有備份, 運行在noarchivelog模式.

從trc檔案中得到的內容:

KCRA: buffers claimed = 0/0, eliminated = 0
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state
oerr ora 704
00704, 00000, "bootstrap process failure"
// *Cause: Failure in processing bootstrap data - see accompanying error.
// *Action: Contact your customer support representative.
SQL_TRACE開啟的情況下產生的Trace:
PARSING IN CURSOR #9 len=84 dep=2 uid=0 oct=3 lid=0 tim=18446744073254091198
hv=2287793623 ad='66f6c06c'
select o.name, u.name from obj$ o, user$ u where o.obj# = :1 and o.owner# = u.user#
END OF STMT
PARSE #9:c=0,e=343,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=0,tim=18446744073254091193
EXEC #9:c=0,e=186,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=18446744073254091456
FETCH #9:c=0,e=28019,p=2,cr=5,cu=0,mis=0,r=1,dep=2,og=4,tim=18446744073254119501
STAT #9 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS '
STAT #9 id=2 cnt=1 pid=1 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ#(18) '
STAT #9 id=3 cnt=1 pid=2 pos=1 obj=36 op='INDEX UNIQUE SCAN OBJ#(36) '
STAT #9 id=4 cnt=1 pid=1 pos=2 obj=22 op='TABLE ACCESS CLUSTER OBJ#(22) '
STAT #9 id=5 cnt=1 pid=4 pos=1 obj=11 op='INDEX UNIQUE SCAN OBJ#(11) '
ORA-00704: 引導程式進程失敗
ORA-00604: 遞迴 SQL 層 1 出現錯誤
ORA-01502: 索引'SYS.I_DEPENDENCY1'或這類索引的分區處於不可用狀態
EXEC #1:c=109375,e=5578667,p=44,cr=616,cu=1,mis=0,r=0,dep=0,og=4,
tim=18446744073255895570
ERROR #1:err=1092 tim=23012387
 

DBA做事一定要細心, 在運行批處理時一定要審了再審.

 

補充:

後來我用AnySQL UnLoader去恢複資料了, 和客戶一起花了24小時, 最後他們說OK了.

Eygle和Chensq對這個問題也有研究, 他們想出了更好的辦法解決此事, 不過最後原來的庫肯定是不能再用了, 必須要exp/imp到別的庫了, 我是用AUL幫客戶恢複資料的, 資料量在30G以上.

 

案例八:ORA-00604:遞迴SQL產生的錯誤

問題描述:我有一個Pro*c 的程式，有時候會給出下列的錯誤資訊:

ORA-00604:遞迴SQL 1級上產生錯誤

你能告訴為什麼會出現這個錯誤，它什麼時候出現，以及可能的解決方案是什麼嗎?

解決方案:無論你什麼時候執行查詢，系統都會在後台執行一些查詢來判斷許多事情，例如“你是否有許可權來執行這個查詢?”，“你要訪問的這個對象是否存在?”。這些系統執行的查詢被稱為“遞迴SQL”。有時候，一個遞迴SQL語句需要調用自身的遞迴SQL。那麼這些執行的遞迴SQL語句就是另一個層級的，2級。

你不會在SQL*Plus 中看到遞迴SQL語句。要查看它們的最好的方式就是開啟會話中的追蹤。啟動SQL*Plus ，執行下列命令:

ALTER SESSION SET sql_trace=TRUE;
 

然後運行你的進程，直到崩潰。繼續，並關閉SQL*Plus 。現在到USER_DUMP_DEST 目錄中。那裡會產生一個追蹤檔案給你。查看追蹤檔案中的有關ORA錯誤的資訊。這就是問題產生的根源。糾正ORA錯誤就會防止ORA-600錯誤再次出現。

大多數的ORA-600錯誤都可以通過以SYS登入，並從ORACLE_HOME/rdbms/admin 運行CATALOG 和 CATPROC 來予以糾正。