Resolving/Suppressing Memory Notification: Library Cache Object loaded into SGA

I noticed this entry in alert log of the DB 10.2.0.1 and it generally occurs when your heap size exceeds the allocated memory.

1) Generated by mman(memory manager) process for changing the shared pool size or tuning the shared pool size

Sun Jul 13 18:33:00 2008
Memory Notification: Library Cache Object loaded into SGA
Heap size 6062K exceeds notification threshold (2048K)
KGL object name :SYS.ALERT_QUE

Suppressing the above entry can be done by creating an entry in init.ora file and boucing the instance resolves this issue.

Entry to be done in init.ora
_kgl_large_heap_warning_threshold=8388608 (this value was decided according to instance SGA Size)

Since this is kernel parameter so it need a bounce as it cannot be modified while the instance is running

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608;
alter system set "_kgl_large_heap_warning_threshold"=8388608
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

Starting Instance/DB on MS-Windows command based

c:\

c:\ edit startwplains.bat

When you get into edit mode of this file write the following command in it.

oradim -startup -sid wplains -starttype srvc,inst

Once done save and exit.Note: wplains is my instance id

Same way you can create stopping the instance/database

c:\edit stopwplains.bat

oradim -shutdown -sid wplains -shuttype srvc,inst -shutmode immediate

Once done save and exit.Note: wplains is my instance id

This gives DBA a command line interface to work with oracle on windows. I 'm used to work on UNIX and feel less comfortable on windows env so i thought of working this way.

Resolving Ora-01102 Error

I was working on some of locking issues with one of my production databases and found out that most of the sessions are being blocked with Sessions from DBSNMP i.e Agent so I tried to kill them but this could not do much to me.So after agreement with Application owner I brought down the Instance down and found the following error in the alert log

Mon Jul 7 21:40:38 2008
ALTER DATABASE CLOSE NORMAL
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Mon Jul 7 21:42:18 2008

Later when I tried to brought up the instance I was not able to bring up the instance and found the following error in alert.log

Mon Jul 7 21:40:16 2008
starting up 1 shared server(s) ...
Mon Jul 7 21:40:17 2008
ALTER DATABASE MOUNT
Mon Jul 7 21:40:17 2008
sculkget: failed to lock /sfprd1/oracle/products/10.2/dbs/lkSFPRD11 exclusive
sculkget: lock held by PID: 13537
Mon Jul 7 21:40:17 2008
ORA-09968: unable to lock file
Linux Error: 11: Resource temporarily unavailable
Additional information: 13537
Mon Jul 7 21:40:17 2008
ORA-1102 signalled during: ALTER DATABASE MOUNT...


Solution
---------

This issue occurs due to the following reasons:-

- there is still an "sgadef.dbf" file in the "ORACLE_HOME/dbs" directory
- the processes for Oracle (pmon, smon, lgwr and dbwr) still exist
- shared memory segments and semaphores still exist even though the database has been shutdown
- there is a "ORACLE_HOME/dbs/lk" file 

So this time I removed the file "/sfprd1/oracle/products/10.2/dbs/lkSFPRD11" and did
--shutdown immediate
again
--startup open;

And this worked for me.