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
Showing posts with label 10g Db-Issue. Show all posts
Showing posts with label 10g Db-Issue. Show all posts
Tuesday, May 19, 2009
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
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
Subscribe to:
Posts (Atom)