Monday, August 12, 2013

ORA-4031: unable to allocate 32 bytes of shared memory | Sun Solaris


Environment Details:
OS: Sun Solaris 5.10
Database Version: 11gR2
User: oracle

Issue: When we try to create a database using dbca in Sun Solaris Server we get an ORA Error 
ORA-00604: Error occurred at recursive SQL level 1 
ORA-4031: unable to allocate 32 bytes of shared memory("shared pool","select obj#,type#,ctime,mtim...,","SQLA","tmp")




This issue is related to the amount of privileged memory allocated for use to the Oracle user, if the memory required exceeds the privileged limit, oracle throws error

Solution: To resolve this use the following

Login as oracle user on the server and issue the command (id -p)

bash-3.00$ id -p
uid=555(oracle) gid=1000(oinstall) projid=100(user.oracle)

Login as root/sudo to the server and check the memory allocated to the user.oracle project

# prctl -n project.max-shm-memory -i project user.oracle
project: 100: user.oracle
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged       2.0GB      -   deny                                 -
        system          16.0EB    max   deny                                 -

Increase the memory of by issuing the below command

# prctl -n project.max-shm-memory -r -v 4G -i project user.oracle

Verify the memory changes

# prctl -n project.max-shm-memory -i project user.oracle
project: 100: user.oracle
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged       4.0GB      -   deny                                 -
        system          16.0EB    max   deny                                 -

Now try to create the database it will go through.

No comments:

Post a Comment