Email: service@parnassusdata.com 7 x 24 online support!

    You are here

    • You are here:
    • Home > Blogs > PDSERVICE's blog > Oracle ORA-00604とORA-04024エラの解決策 ORA-00604: 再帰SQLレベルでエラーが発生しました。 ORA-04024: ピン・カーソルを相互排除しようとして自己デッドロックを検出しました

Oracle ORA-00604とORA-04024エラの解決策 ORA-00604: 再帰SQLレベルでエラーが発生しました。 ORA-04024: ピン・カーソルを相互排除しようとして自己デッドロックを検出しました

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>
ここで、完璧にリカバリした!