Showing posts with label 10g Db-Issue. Show all posts
Showing posts with label 10g Db-Issue. Show all posts

Tuesday, May 19, 2009

ORA-00821: Specified value of sga_target 280M is too small, needs to be at least 896M

Recently I have installed Oracle10g on Windows for Trident Leave management system implementation.
The base install of Oracle10g Release 2 (10.2.0.1) went fine. As part of the install, I let the install create the starter database. after completion of the installation, I had increased the SHARED_POOL_SIZE and JAVA_POOL_SIZE are 400MB each using scope=pfile.


then I shutdown the database & restarting the database with startup command. The following error appeared
ORA-00821: Specified value of sga_target 280M is too small, needs to be at least 896M
Then I find out , that issue with SGA_TARGET size.


If we are using Automatic Shared Memory Management, ensure that the value of the SGA_TARGET initialization parameter size is at least 50 MB greater than the sum of the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters.


This is important! Most likely, you are using Automatic Shared Memory Management.
Solution is increase the value of the SGA_TARGET parameter to the value specified in the error.
Since Oracle said this could happen and that all I need to do was change the SGA_TARGET to the stated value, I did so.
SQL> alter system set sga_target=900m scope=spfile;

alter system set sga_target=900scope=spfile
*
ERROR at line 1:ORA-01034: ORACLE not available
so I 'll do a startup nomount and then change the SGA_TARGET.
SQL> startup nomount

ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0
That didn't work, so I tried it again

Connected to an idle instance.
SQL> startup nomount
ORA-00821: Specified value of sga_target 280M is too small, needs to be at 896
SQL> alter system set sga_target=900m scope=spfile;
alter system set sga_target=900m scope=spfile
*
ERROR at line 1:ORA-01034: ORACLE not available

I thought I would create a pfile from the spfile, change the SGA_TARGET in the pfile, since it is editable, and then restart the database using the pfile.
SQL> create pfile from spfile;

create pfile from spfile
*
ERROR at line 1:
ORA-27041: unable to open file
OSD-04002: unable to open fileO/S-Error:
(OS 2) The system cannot find the file specified.

I checked for pfile in ORACLE_HOME/database, there was no spfile, just a pfile with one line in it, pointing to the spfile in the directory ORACLE_HOME/dbs. Since the database was still down, I renamed the pfile in the database directory and copied the spfile from the dbs directory to the database directory, and tried the same command again.

SQL> create pfile from spfile;

File created.
I edited the pfile, setting the SGA_TARGET to 900M, and tried the startup command in new session.


SQL> startup pfile= xxx/xxx/xxx/inittrilms.ora
ORACLE instance started.
then I created the Spfile using pfile
Now the issue resolved

Tuesday, September 23, 2008

WARNING: "Memory Notification: Library Cache Object Loaded Into SGA"

In one of our Database(10g R2)10.2.0.1, we have found that the WARNING message "Memory Notification: Library Cache Object Loaded Into SGA" is being written continuously in alert log file.

To prevent or avoid these messages being generated, we need to adjust the size of a hidden initialization parameter "_kgl_large_heap_warning_threshold" to a high value or zero.

Note: In 10.2.0.2, the threshold is increased to 50MB.
so this should be a reasonable and recommended value.

To find out a hidden parameter value in the database.

SQL> show parameter _kgl_large_heap_warning_threshold

To change the hidden parameter value.

If you are using spfile, login as sysdba

$sqlplus /nolog
SQL> connect /as sysdba
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

System altered.

Or if you are using init prameter file

Then, edit the initialization parameter file and add the below line.

_kgl_large_heap_warning_threshold=8388608

Shut down and re-start the database after changes

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

SQL> show parameter _kgl_large_heap_warning_threshold

Srinivas Ramineni
Oracle Apps DBA