Email: service@parnassusdata.com 7 x 24 online support!
Oracle ORA-00604とORA-04024エラの解決策 ORA-00604: 再帰SQLレベルでエラーが発生しました。 ORA-04024: ピン・カーソルを相互排除しようとして自己デッドロックを検出しました
ORACLEデータベース によくあるエラ の解決策
プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com
ORA-00604: 再帰SQLレベルでエラーが発生しました。
ORA-04024: ピン・カーソルを相互排除しようとして自己デッドロックを検出しました
一般的なOracleのbootstrap index（テーブルのインディクスと一部のコアオブジェクトをガイドする）も似たような方法で対応できる。例えば以下のクエリ文のI_OBJxxxxx。
.
テスト環境 11.2.0.3データベース：
[oracle@lunarpri ~]$ ss SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 27 19:12:57 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options orcl>col OBJECT_NAME for a30 orcl>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%'; OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_ ------------------------------ ------------------------------ ------------------- ------- --------- SYS I_OBJ# INDEX VALID 08-FEB-13 SYS I_OBJ5 INDEX VALID 08-FEB-13 SYS I_OBJ3 INDEX VALID 08-FEB-13 SYS I_OBJ1 INDEX VALID 08-FEB-13 SYS I_OBJ2 INDEX VALID 08-FEB-13 SYS I_OBJ4 INDEX VALID 08-FEB-13 SYS I_OBJAUTH1 INDEX VALID 08-FEB-13 SYS I_OBJAUTH2 INDEX VALID 08-FEB-13 SYS I_OBJ#_INTCOL# INDEX VALID 08-FEB-13 SYS I_OBJTYPE INDEX VALID 08-FEB-13 10 rows selected. Elapsed: 00:00:00.06 orcl>
データベースで、bootstrap indexを操作出来ない、例えば：
あるbootstrapインディクスはupgradeモードで修正できなくなる：
orcl>alter index SYS.I_OBJ5 unusable * ERROR at line 1: ORA-00701: object necessary for warmstarting database cannot be altered Elapsed: 00:00:00.01 orcl>alter index SYS.I_OBJ3 unusable * ERROR at line 1: ORA-00701: object necessary for warmstarting database cannot be altered Elapsed: 00:00:00.02 orcl>alter index SYS.I_OBJ1 unusable * ERROR at line 1: ORA-00701: object necessary for warmstarting database cannot be altered Elapsed: 00:00:00.01 orcl>
アップグレードモードを起動できる。このモードでデータベースは自動的にsystem triggerを禁止する操作を増やす。
一部のbootstrageオブジェクトの操作を実行できる、例えば：
orcl>startup upgrade ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 184550744 bytes Database Buffers 432013312 bytes Redo Buffers 7532544 bytes Database mounted. Database opened. orcl> orcl>alter index SYS.I_OBJAUTH2 unusable; alter index SYS.I_OBJ#_INTCOL# unusable; alter index SYS.I_OBJTYPE unusable; Index altered. Elapsed: 00:00:00.04 orcl>alter index SYS.I_OBJ# unusable Index altered. Elapsed: 00:00:00.20 orcl> Index altered. Elapsed: 00:00:00.03 orcl> Index altered. Elapsed: 00:00:00.03 orcl>
アップグレードモード自動追加のバラメタは以下の通り：
Fri Mar 27 19:21:48 2015 MMNL started with pid=16, OS id=15218 ALTER SYSTEM enable restricted session; ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY; Autotune of undo retention is turned off. ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY; ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY; ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY; ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY; Resource Manager disabled during database migration: plan '' not set ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY; ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY; Resource Manager disabled during database migration replication_dependency_tracking turned off (no async multimaster replication found)
ここで、データディクショナリーが破壊されたから、それについての機能も効かなくなった：
orcl>col OBJECT_NAME for a30
orcl>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';
select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state
Elapsed: 00:00:00.20
orcl>
起動するときにORA-00604 ORA-04024エラになる:
orcl>startup ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 184550744 bytes Database Buffers 432013312 bytes Redo Buffers 7532544 bytes Database mounted. ORA-00604: error occurred at recursive SQL level 4 ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x07EF125E8 orcl>
正常にデータベースをクロスするのもできない。なら、原因は一部のコアアーカイブSQLを実行するときにトラブルがあったと意味している。shutdown abortしかできない：
orcl>shutdown immediate ORA-00604: error occurred at recursive SQL level 4 ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x07EF125E8 orcl>shutdown abort ORACLE instance shut down orcl>
アップグレードモードでデータベースを起動して、インディクスをリカバリする：.
orcl>startup upgrade ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 184550744 bytes Database Buffers 432013312 bytes Redo Buffers 7532544 bytes Database mounted. Database opened. orcl> orcl>show parameter NLS_LENGTH_SEMANTICS ORA-00604: error occurred at recursive SQL level 1 ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state orcl>
ミニアップグレードスクリプトを実行してリカバリする：
orcl>ALTER SESSION SET NLS_LENGTH_SEMANTICS = BYTE; Session altered. Elapsed: 00:00:00.00 orcl>@?/rdbms/admin/utlmmig.sql View created. Elapsed: 00:00:01.32 PL/SQL procedure successfully completed. Elapsed: 00:00:00.34 Commit complete. Elapsed: 00:00:00.01 Table dropped. Elapsed: 00:00:00.01 Table created. Elapsed: 00:00:00.53 Index created. Elapsed: 00:00:00.12 Index created. Elapsed: 00:00:00.05 Index created. Elapsed: 00:00:00.02 Index created. Elapsed: 00:00:00.03 Index created. Elapsed: 00:00:00.04 Table dropped. Elapsed: 00:00:00.02 Table created. Elapsed: 00:00:00.07 Index created. Elapsed: 00:00:00.03 Index created. Elapsed: 00:00:00.03 Table dropped. Elapsed: 00:00:00.00 Table created. Elapsed: 00:00:00.04 Table dropped. Elapsed: 00:00:00.01 Table created. Elapsed: 00:00:00.05 declare * ERROR at line 1: ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state ORA-06512: at line 13 ORA-06512: at line 137 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options [oracle@lunarpri ~]$
ここでSYS.I_OBJ#_INTCOL#インディクスはunusableと見られる。アップグレードモードでリカバリできない。
けど10gのあと、このインディクスはevent 38003で禁止できる：
orcl>create pfile='/tmp/spfile.bak' from spfile;
File created.
Elapsed: 00:00:00.01
orcl>show parameter spfile
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state
orcl>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
orcl>
[oracle@lunarpri ~]$ tail /tmp/spfile.bak
*.db_recovery_file_dest_size=10485760000
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.open_cursors=300
*.pga_aggregate_target=153092096
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=629145600
*.undo_tablespace='UNDOTBS1'
*.EVENT="38003 trace name context forever, level 10"
[oracle@lunarpri ~]$
orcl>startup pfile=/tmp/spfile.bak upgrade
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 432013312 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
orcl>col OBJECT_NAME for a30
orcl>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';
select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state
Elapsed: 00:00:00.08
orcl>alter index SYS.I_OBJ#_INTCOL# rebuild;
Index altered.
Elapsed: 00:00:00.46
orcl>
ここで、壊されたインディクスをリカバリした。
そして、再び、ミニアップグレードスクリプトを実行する：
orcl>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';
OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_
------------------------------ ------------------------------ ------------------- ------- ---------
SYS I_OBJ# INDEX VALID 08-FEB-13
SYS I_OBJ5 INDEX VALID 08-FEB-13
SYS I_OBJ3 INDEX VALID 08-FEB-13
SYS I_OBJ1 INDEX VALID 08-FEB-13
SYS I_OBJ2 INDEX VALID 08-FEB-13
SYS I_OBJ4 INDEX VALID 08-FEB-13
SYS I_OBJAUTH1 INDEX VALID 27-MAR-15
SYS I_OBJAUTH2 INDEX VALID 27-MAR-15
SYS I_OBJ#_INTCOL# INDEX VALID 27-MAR-15
SYS I_OBJTYPE INDEX VALID 27-MAR-15
SYS I_OBJ_MIG1 INDEX VALID 27-MAR-15
SYS I_OBJ_MIG2 INDEX VALID 27-MAR-15
SYS I_OBJ_MIG3 INDEX VALID 27-MAR-15
SYS I_OBJ_MIG4 INDEX VALID 27-MAR-15
SYS I_OBJ_MIG5 INDEX VALID 27-MAR-15
15 rows selected.
Elapsed: 00:00:00.49
orcl>@?/rdbms/admin/utlmmig.sql
View created.
Elapsed: 00:00:00.69
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
Commit complete.
Elapsed: 00:00:00.00
Table dropped.
Elapsed: 00:00:00.28
Table created.
Elapsed: 00:00:00.08
Index created.
Elapsed: 00:00:00.09
Index created.
Elapsed: 00:00:00.03
Index created.
Elapsed: 00:00:00.02
Index created.
Elapsed: 00:00:00.03
Index created.
Elapsed: 00:00:00.03
Table dropped.
Elapsed: 00:00:00.12
Table created.
Elapsed: 00:00:00.08
Index created.
Elapsed: 00:00:00.02
Index created.
Elapsed: 00:00:00.03
Table dropped.
Elapsed: 00:00:00.08
Table created.
Elapsed: 00:00:00.03
Table dropped.
Elapsed: 00:00:00.06
Table created.
Elapsed: 00:00:00.03
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
PL/SQL procedure successfully completed.
Elapsed: 00:00:26.30
49 rows created.
Elapsed: 00:00:00.03
60 rows created.
Elapsed: 00:00:00.00
Commit complete.
Elapsed: 00:00:00.01
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.14
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.16
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
10 rows deleted.
Elapsed: 00:00:00.03
Commit complete.
Elapsed: 00:00:00.00
10 rows created.
Elapsed: 00:00:00.01
Commit complete.
Elapsed: 00:00:00.00
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.82
COUNT(*)
----------------
60
Elapsed: 00:00:00.00
COUNT(*)
----------------
60
Elapsed: 00:00:00.00
COUNT(*)
----------------
60
Elapsed: 00:00:00.01
COUNT(*)
----------------
60
Elapsed: 00:00:00.01
COUNT(*)
----------------
60
Elapsed: 00:00:00.00
COUNT(*)
----------------
60
Elapsed: 00:00:00.00
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
Database closed.
Database dismounted.
ORACLE instance shut down.
orcl>
そして、データベースを起動する：
orcl>startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 432013312 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
orcl>
orcl>col OBJECT_NAME for a30
orcl>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';
OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_
------------------------------ ------------------------------ ------------------- ------- ---------
SYS I_OBJ# INDEX VALID 08-FEB-13
SYS I_OBJ_MIG1 INDEX VALID 08-FEB-13
SYS I_OBJ_MIG2 INDEX VALID 08-FEB-13
SYS I_OBJ_MIG3 INDEX VALID 08-FEB-13
SYS I_OBJ_MIG4 INDEX VALID 08-FEB-13
SYS I_OBJ_MIG5 INDEX VALID 08-FEB-13
SYS I_OBJAUTH1 INDEX VALID 27-MAR-15
SYS I_OBJAUTH2 INDEX VALID 27-MAR-15
SYS I_OBJ#_INTCOL# INDEX VALID 27-MAR-15
SYS I_OBJTYPE INDEX VALID 27-MAR-15
SYS I_OBJ1 INDEX VALID 27-MAR-15
SYS I_OBJ2 INDEX VALID 27-MAR-15
SYS I_OBJ3 INDEX VALID 27-MAR-15
SYS I_OBJ4 INDEX VALID 27-MAR-15
SYS I_OBJ5 INDEX VALID 27-MAR-15
15 rows selected.
Elapsed: 00:00:00.14
orcl>
初めて実行したミニアップグレードスクリプトを削除したのはエラによって、データベースに残された一時的なインディクスを削除する：
orcl>DROP INDEX SYS.I_OBJ_MIG1;
Index dropped.
Elapsed: 00:00:00.44
orcl>C/1/2
1* DROP INDEX SYS.I_OBJ_MIG2
orcl>/
Index dropped.
Elapsed: 00:00:00.06
orcl>C/2/3
1* DROP INDEX SYS.I_OBJ_MIG3
orcl>/
Index dropped.
Elapsed: 00:00:00.05
orcl>C/3/4
1* DROP INDEX SYS.I_OBJ_MIG4
orcl>/
Index dropped.
Elapsed: 00:00:00.08
orcl>C/4/5
1* DROP INDEX SYS.I_OBJ_MIG5
orcl>/
Index dropped.
Elapsed: 00:00:00.05
orcl>
orcl>col OBJECT_NAME for a30
orcl>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';
OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_
------------------------------ ------------------------------ ------------------- ------- ---------
SYS I_OBJ# INDEX VALID 08-FEB-13
SYS I_OBJAUTH1 INDEX VALID 27-MAR-15
SYS I_OBJAUTH2 INDEX VALID 27-MAR-15
SYS I_OBJ#_INTCOL# INDEX VALID 27-MAR-15
SYS I_OBJTYPE INDEX VALID 27-MAR-15
SYS I_OBJ1 INDEX VALID 27-MAR-15
SYS I_OBJ2 INDEX VALID 27-MAR-15
SYS I_OBJ3 INDEX VALID 27-MAR-15
SYS I_OBJ4 INDEX VALID 27-MAR-15
SYS I_OBJ5 INDEX VALID 27-MAR-15
10 rows selected.
Elapsed: 00:00:00.07
orcl>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
orcl>startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 432013312 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
orcl>
orcl>col OBJECT_NAME for a30
orcl>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';
OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_
------------------------------ ------------------------------ ------------------- ------- ---------
SYS I_OBJ# INDEX VALID 08-FEB-13
SYS I_OBJAUTH1 INDEX VALID 27-MAR-15
SYS I_OBJAUTH2 INDEX VALID 27-MAR-15
SYS I_OBJ#_INTCOL# INDEX VALID 27-MAR-15
SYS I_OBJTYPE INDEX VALID 27-MAR-15
SYS I_OBJ1 INDEX VALID 27-MAR-15
SYS I_OBJ2 INDEX VALID 27-MAR-15
SYS I_OBJ3 INDEX VALID 27-MAR-15
SYS I_OBJ4 INDEX VALID 27-MAR-15
SYS I_OBJ5 INDEX VALID 27-MAR-15
10 rows selected.
Elapsed: 00:00:00.13
orcl>
ここで、完璧にリカバリした！