记录一例 ORA-27300 ORA-27301 ORA-27302

 今天在看崔华的《基于Oracle的SQL优化》一书。看到一个案例,这个案例和之前自己碰到的现象很像,可惜不是自己处理的,而且故障也没有重现过。所以具体原因无法考证。仅仅把书上的解决方法和自己碰到的现象记录下来。

以下图片内容摘自《基于Oracle的SQL优化》一书







--------- 以下内容为发生故障时候,同事收集到的信息

Wed Feb 07 04:53:17 2018
Process startup failed, error stack:
Errors in file /u01/app/oracle/diag/rdbms/sae/sae2/trace/sae2_psp0_6554062.trc:
ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3
Process J003 died, see its trace file
kkjcre1p: unable to spawn jobq slave process 
Errors in file /u01/app/oracle/diag/rdbms/sae/sae2/trace/sae2_cjq0_4587790.trc:
Process startup failed, error stack:
Errors in file /u01/app/oracle/diag/rdbms/sae/sae2/trace/sae2_psp0_6554062.trc:
ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3
Wed Feb 07 04:53:22 2018
Process J003 died, see its trace file
kkjcre1p: unable to spawn jobq slave process 
Errors in file /u01/app/oracle/diag/rdbms/sae/sae2/trace/sae2_cjq0_4587790.trc:
Wed Feb 07 04:53:27 2018
Process startup failed, error stack:
Errors in file /u01/app/oracle/diag/rdbms/sae/sae2/trace/sae2_psp0_6554062.trc:
ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3
Process J003 died, see its trace file

errpt看到的错误信息

$ errpt
IDENTIFIER TIMESTAMP  T C RESOURCE_NAME  DESCRIPTION
A6DF45AA   0207131418 I O RMCdaemon      The daemon is started.
C5C09FFA   0207131218 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
9DBCFDEE   0207131418 T O errdemon       ERROR LOGGING TURNED ON
C5C09FFA   0207073818 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207073818 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207073818 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207073818 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207072618 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207072518 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207072518 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207060118 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207060118 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207060118 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207060118 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207060018 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207060018 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207060018 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207060018 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207055318 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207055318 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207054918 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207054918 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207054918 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207054918 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207054718 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207054718 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207054018 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207054018 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207054018 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
C5C09FFA   0207054018 P S SYSVMM         SOFTWARE PROGRAM ABNORMALLY TERMINATED
BC3BE5A3   0207045218 P S SRC            SOFTWARE PROGRAM ERROR
4B436A3D   1224082617 T H fscsi1         LINK ERROR
8650BE3F   1128024817 I H ent4           ETHERCHANNEL RECOVERY
42F2355C   1128024717 T H ent0           PROBLEM RESOLVED
59224136   1128024717 P H ent4           ETHERCHANNEL FAILOVER
F1814D51   1128024717 T H ent0           ETHERNET DOWN



Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
              12845134
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 06:00:09 BEIST 2018
Sequence Number: 697
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
               7930094
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 06:00:09 BEIST 2018
Sequence Number: 696
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
              11403714
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 06:00:09 BEIST 2018
Sequence Number: 695
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
               7602464
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 05:53:33 BEIST 2018
Sequence Number: 694
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
              12583146
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 05:53:31 BEIST 2018
Sequence Number: 693
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
               9372060
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 05:49:03 BEIST 2018
Sequence Number: 692
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
               3539928
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 05:49:01 BEIST 2018
Sequence Number: 691
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
               5178002
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 05:49:01 BEIST 2018
Sequence Number: 690
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
               9699744
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 05:49:01 BEIST 2018
Sequence Number: 689
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
               7996270
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 05:47:06 BEIST 2018
Sequence Number: 688
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
               8651320
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 05:47:06 BEIST 2018
Sequence Number: 687
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
              13369476
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 05:40:39 BEIST 2018
Sequence Number: 686
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
               9634226
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 05:40:39 BEIST 2018
Sequence Number: 685
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
               6816194
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 05:40:39 BEIST 2018
Sequence Number: 684
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
               5505686
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0
---------------------------------------------------------------------------
LABEL:          PGSP_KILL
IDENTIFIER:     C5C09FFA

Date/Time:       Wed Feb  7 05:40:39 BEIST 2018
Sequence Number: 683
Machine Id:      00FA51814C00
Node Id:         exporc2
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSVMM          

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SYSTEM RUNNING OUT OF PAGING SPACE

Failure Causes
INSUFFICIENT PAGING SPACE DEFINED FOR THE SYSTEM
PROGRAM USING EXCESSIVE AMOUNT OF PAGING SPACE

        Recommended Actions
        DEFINE ADDITIONAL PAGING SPACE
        REDUCE PAGING SPACE REQUIREMENTS OF PROGRAM(S)

Detail Data
PROGRAM
oracle
USER'S PROCESS ID:
               8651666
PROGRAM'S PAGING SPACE USE IN 1KB BLOCKS
           0

--- 后记

通过查询MOS,发下的确有类似的文章,提出是AIX的一个bug,不过个人感觉bug的可能性不是很大。因为此事情过去了几个月了,再也没有重现。感觉还是应用程序方面耗光了PGA。可惜当时是别人处理的,重启后,故障消失。也没有抓取当时的AWR,可惜.........


END

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/xxzhaobb/article/details/80323574
个人分类: 11gR2 RAC
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭