Oracle DBA FAQ

Oracle RAC interview questions, Oracle Dataguard, ASM, CRS, Oracle wait events, Performance Tuning


    Database terminated by error ORA-04031 and ORA-00604

    Share

    oradba

    Posts : 3
    Join date : 2010-02-08

    Database terminated by error ORA-04031 and ORA-00604

    Post  oradba on Tue Feb 09, 2010 7:30 pm

    My production database terminated by error ORA-04031 and ORA-00604.

    Restarted database and is running fine. Below is the alert from alert,log file. Anyone faced this? I am trying to find the root cause of this and how to prevent this happening again

    ORA-00604: error occurred at recursive SQL level 1
    ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select f.file#, f.block#, f....","sql area","tmp")

    Errors in file /u01/oradata/prod1/bdump/prod1_cjq0_24261.trc:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select count(*) from sys.job...","sql area","tmp")

    Errors in file /u01/oradata/prod1/bdump/prod1_reco_12416.trc:
    ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select host,userid,password,...","sql area","tmp")

    RECO: terminating instance due to error 4031

    Admin
    Admin

    Posts : 17
    Join date : 2010-02-08

    Re: Database terminated by error ORA-04031 and ORA-00604

    Post  Admin on Tue Feb 09, 2010 8:47 pm

    Can you please post the relevant messages from prod1_cjq0_24261.trc

    oradba

    Posts : 3
    Join date : 2010-02-08

    Re: Database terminated by error ORA-04031 and ORA-00604

    Post  oradba on Tue Feb 09, 2010 9:29 pm

    last wait for 'SGA: allocation forcing component growth' blocking sess=0x0 seq=31337 wait_time=17954 seconds since wait started=1

    Call stack:

    ksm_4031_dump <- ksmasg <- kghnospc <- kghalf <- kksLoadChild <- kxsGetRuntimeLock <- kksfbc <- opiexe <- opiodr <- rpidrus <- skgmstack <- rpidru <- rpiswu2 <- rpidrv <- rpiexe <- kkjcjpop <- kkjcjexe <- kkjssrh <- ksbcti <- ksbabs <- ksbrdp <- opirip <- opidrv <- sou2o <- opimai_real <- main <- start


    ==============================
    Memory Utilization of Subpool 1
    ================================
    Allocation Name Size
    _________________________ __________
    "free memory " 245765152
    "KGH: NO ACCESS " 868951744

    Admin
    Admin

    Posts : 17
    Join date : 2010-02-08

    Re: Database terminated by error ORA-04031 and ORA-00604

    Post  Admin on Wed Feb 10, 2010 7:51 am

    'KGH: NO ACCESS' refers to granules that are in transit with ASMM i.e memory being reassigned from the Shared Pool to the Database Buffer Cache and vice-versa. If you have not set minimum value for shared_pool_size and db_buffer_cache set a min value for it.

    Also Check below metalink note.


    451960.1 - How To Prevent The Growth Of The Component 'KGH: NO ACCESS' In The Shared Pool When ASMM Is Enabled
    801787.1 - Common Cause for ORA-4031 in 10gR2, Excess "KGH: NO ACCESS" Memory Allocation

    Sponsored content

    Re: Database terminated by error ORA-04031 and ORA-00604

    Post  Sponsored content


      Current date/time is Fri Jul 21, 2017 4:36 am