i have some problem with my oracle database 8.1.7 running on sun solaris 8, after 2 days or more i cannot login into database and needed to restart the database, below shown the error :
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")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4200 bytes of shared memory ("shared
pool","unknown object","sga heap","state objects")
Does the server have oracle start stop scripts ?
You can use them to restart your databases.
But looking at your errors its looks like some kernal tunables need to be set.
I can't remember which ones thay are of the top of my head, But I know there are some shared memory tuneables that need to be set for oracle to function corectly.
looks like you are hitting shared memory problems,
either somebody changed added or removed something in the init.ora.
or this is a new DB just recently installed then check your /etc/system
Normally if you encounter oracle memory errors it's to do with your shmmax or semaphore & shared memory entries under /etc/system file or the settings in the oracle init.ora file
I beg to differ. Normally, a wrong entry in the semaphore in the /etc/system file will cause the DB to be unable to start up.
If the error you see happens a couple of days after restarting the DB, most likely it is due to fragmentation shared pool.
These are the things you can try in order:
1) Check for SQL that are not using binding variable, get your developers to change them to binding variable. This will reduce the number of distinct SQLs required to be cached in the shared_pool.
2) Increase the shared_pool and reduce the shared_pool_reserved size. This will allow more distinct SQLs to be cached in shared_pool and at the same time, reduce the usage of shared_pool_reserved. (This will help if the fragmented shared pool you are facing is at the shared_pool_reserved)
3) Check Oracle metalink for bugs related to this oracle error. I do know that Oracle 9i has a bug related to this, not too sure on Oracle 8i.
