eygle.com   eygle.com
eygle.com  
 

« 陌上花开，可缓缓归矣 | Blog首页 | DB2新手上路:DB2命令行连接 »

Oracle Diagnostics:又见ORA-04031

作者：eygle |【转载时请以超链接形式标明文章和作者信息及本声明】
链接：
站内相关文章|Related Articles
  • 如何在自动SGA管理模式下调节参数设置
  • Granule 与 Redo Log Buffer (log_buffer) 的关系
  • 关于《深入浅出Oracle》中granule的补充
  • Oracle HowTo:如何在Linux上扩展SGA超过1.7G
  • Linux上shmmax参数的设置及含义

    • 今天,一个朋友的数据库出现问题,连接上去一看,原来又是ORA-04031:

    [oracle@statdata bdump]$ sqlplus "/ as sysdba"

    SQL*Plus: Release 8.1.7.0.0 - Production on 星期五 6月 23 11:04:31 2006

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    ERROR:
    ORA-00604: error occurred at recursive SQL level 2
    ORA-04031: unable to allocate 4200 bytes of shared memory ("shared
    pool","TRIGGER$","sga heap","state objects")

    sql*plus无法连接,想了一下才记起,还有svrmgrl可以用:

    [oracle@statdata dbs]$ svrmgrl

    Oracle Server Manager Release 3.1.7.0.0 - Production

    Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

    Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - Production

    SVRMGR> connect internal
    Connected.
    SVRMGR> shutdown immediate;
    ORA-00604: error occurred at recursive SQL level 1
    ORA-04031: unable to allocate 4200 bytes of shared memory ("shared

    pool","DATABASE","sga heap","state objects")

    在Oracle8.1.7.0.0中,ORA-04031的问题是由来已久的,使用svrmgrl也不能执行shutdown immediate了.只能通过shutdown abort关闭数据库后重起.

    SVRMGR> connect internal
    Connected.
    SVRMGR> shutdown abort;
    ORACLE instance shut down.

    进一步检查发现这个数据库处于初始态运行,共享池设置的只有30M,过小的共享池设置也是导致ORA-04031的原因之一:

    shared_pool_size = 31457280
    db_block_buffers = 2048

    对这两个参数进行了放大调整,主机毕竟有4G内存,调整后,ORA-04031错误应该会少很多了.

    数据库关闭后,共享内存并未及时释放:

    SVRMGR> connect internal
    Connected.
    SVRMGR> shutdown abort;
    ORACLE instance shut down.
    SVRMGR> exit
    Server Manager complete.
    [oracle@statdata dbs]$ ipcs -sa

    ------ Shared Memory Segments --------
    key shmid owner perms bytes nattch status
    0x00000000 2293760 oracle 640 77824 1 dest
    0x00000000 2326529 oracle 640 17825792 1 dest
    0x00000000 2359298 oracle 640 17825792 1 dest
    0x00000000 2392067 oracle 640 20971520 1 dest
    0x00000000 2424836 oracle 640 16961536 1 dest

    ------ Semaphore Arrays --------
    key semid owner perms nsems

    ------ Message Queues --------
    key msqid owner perms used-bytes messages

    杀掉残余的Oracle进程后,共享内存释放:

    [oracle@statdata dbs]$ ps -ef|grep ora
    oracle 4159 1 0 May11 ? 00:17:20

    /export/home/oracle/product/8.1.7/bin/tnslsnr LISTENER -inherit
    oracle 7663 7651 0 10:47 ? 00:00:00 [sshd]
    oracle 7664 7663 0 10:47 pts/1 00:00:00 -bash
    oracle 7730 7664 0 10:48 pts/1 00:00:00 svrmgrl
    oracle 7731 7730 0 10:48 ? 00:00:00 oracleora8 (DESCRIPTION=(LOCAL=YES)

    (ADDRESS=(PROTOCOL=beq)))
    oracle 8344 8342 0 11:03 ? 00:00:00 [sshd]
    oracle 8345 8344 0 11:03 pts/2 00:00:00 -bash
    oracle 9094 1 0 11:19 ? 00:00:00 oracleora8 (LOCAL=NO)
    oracle 9101 8345 0 11:19 pts/2 00:00:00 ps -ef
    oracle 9102 8345 0 11:19 pts/2 00:00:00 grep ora
    [oracle@statdata dbs]$ kill -9 9094
    [oracle@statdata dbs]$ ps -ef|grep ora
    oracle 4159 1 0 May11 ? 00:17:20

    /export/home/oracle/product/8.1.7/bin/tnslsnr LISTENER -inherit
    oracle 7663 7651 0 10:47 ? 00:00:00 [sshd]
    oracle 7664 7663 0 10:47 pts/1 00:00:00 -bash
    oracle 7730 7664 0 10:48 pts/1 00:00:00 svrmgrl
    oracle 8344 8342 0 11:03 ? 00:00:00 [sshd]
    oracle 8345 8344 0 11:03 pts/2 00:00:00 -bash
    oracle 9113 8345 0 11:19 pts/2 00:00:00 ps -ef
    oracle 9114 8345 0 11:19 pts/2 00:00:00 grep ora
    [oracle@statdata dbs]$ ipcs -sa

    ------ Shared Memory Segments --------
    key shmid owner perms bytes nattch status

    ------ Semaphore Arrays --------
    key semid owner perms nsems

    ------ Message Queues --------
    key msqid owner perms used-bytes messages

    [oracle@statdata dbs]$ svrmgrl

    Oracle Server Manager Release 3.1.7.0.0 - Production

    Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

    Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - Production

    SVRMGR> connect internal
    Connected.
    SVRMGR> startup
    ORACLE instance started.
    Total System Global Area 767996064 bytes
    Fixed Size 73888 bytes
    Variable Size 243462144 bytes
    Database Buffers 524288000 bytes
    Redo Buffers 172032 bytes
    Database mounted.
    Database opened.

    此时数据库可以成功启动.

     


    历史上的今天...
          >> 2008-06-23文章:
                 RAC基本概念：PCM、IDLM、DLM等
    ------
    这篇 【Oracle Diagnostics:又见ORA-04031】来自 eygle.com | CSDN网摘| del.icio.us|Google订阅 | 鲜果订阅 | 抓虾订阅

    By eygle on 2006-06-23 15:53 | Comments (0) | Posted to Case | Edit |

    相关文章 随机文章
  • 如何在自动SGA管理模式下调节参数设置
  • Granule 与 Redo Log Buffer (log_buffer) 的关系
  • 关于《深入浅出Oracle》中granule的补充
  • Oracle HowTo:如何在Linux上扩展SGA超过1.7G
  • Linux上shmmax参数的设置及含义
    		•  ORA-600 kcbgtcr_13 未解决之问题记录
    启用Block Change Tracking-10g新特性
    增加MT MTOnThisDay插件 - 显示历史记录
    MT read counter-增加Blog文章浏览计数器
    人逢喜事
    搜索本站:

    留言 (0)

    发表留言:



    Remember Me?
    (输入验证码后方可评论,谢谢支持)


    CopyRight © 2004~2010 eygle.com, All rights reserved.