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

9 comments:

Anonymous said...

Nice brief and this post helped me alot in my college assignement. Say thank you you for your information.

Anonymous said...

Opulently I assent to but I dream the collection should have more info then it has.

Anonymous said...

Your means of telling all in this article
is truly fastidious, all can effortlessly know it, Thanks a lot.


Here is my web page airplane game

Anonymous said...

thanx
awesome dear thi help me very much

Anonymous said...

thanks, helped me a lot!

oakleyses said...

nike air max, gucci handbags, ugg boots, louis vuitton outlet, polo ralph lauren, christian louboutin outlet, christian louboutin uk, cheap oakley sunglasses, nike free, tory burch outlet, burberry pas cher, polo outlet, jordan shoes, sac longchamp pas cher, oakley sunglasses, replica watches, louis vuitton, longchamp outlet, oakley sunglasses, nike free run, christian louboutin shoes, ray ban sunglasses, oakley sunglasses wholesale, louis vuitton outlet, nike air max, louis vuitton, louboutin pas cher, tiffany jewelry, prada handbags, nike roshe, oakley sunglasses, air max, prada outlet, ugg boots, nike outlet, tiffany and co, ray ban sunglasses, longchamp outlet, chanel handbags, michael kors pas cher, longchamp pas cher, jordan pas cher, replica watches, polo ralph lauren outlet online, uggs on sale, longchamp outlet, kate spade outlet, christian louboutin

oakleyses said...

ray ban uk, coach purses, polo lacoste, sac vanessa bruno, nike tn, hogan outlet, michael kors, michael kors outlet online, converse pas cher, nike air max uk, kate spade, michael kors outlet, hollister pas cher, uggs outlet, coach outlet, north face, michael kors outlet, north face uk, nike air max uk, ray ban pas cher, uggs outlet, nike air force, ralph lauren uk, mulberry uk, lululemon canada, nike free uk, true religion jeans, guess pas cher, replica handbags, timberland pas cher, nike blazer pas cher, coach outlet store online, michael kors outlet online, burberry handbags, abercrombie and fitch uk, michael kors, nike air max, michael kors outlet, vans pas cher, oakley pas cher, true religion outlet, hollister uk, nike roshe run uk, sac hermes, true religion outlet, new balance, michael kors outlet online, burberry outlet, true religion outlet

oakleyses said...

timberland boots, instyler, wedding dresses, mcm handbags, beats by dre, ghd hair, converse, oakley, herve leger, nike huaraches, hollister, valentino shoes, nfl jerseys, mac cosmetics, vans, abercrombie and fitch, insanity workout, mont blanc pens, hollister clothing, reebok outlet, bottega veneta, louboutin, lululemon, p90x workout, baseball bats, nike roshe run, celine handbags, vans outlet, converse outlet, north face outlet, hermes belt, new balance shoes, north face outlet, ray ban, longchamp uk, jimmy choo outlet, babyliss, ralph lauren, gucci, chi flat iron, soccer jerseys, asics running shoes, iphone cases, ferragamo shoes, hollister, nike trainers uk, soccer shoes, nike air max, giuseppe zanotti outlet, nike air max

oakleyses said...

canada goose, moncler outlet, canada goose outlet, toms shoes, montre pas cher, louis vuitton, moncler, canada goose, swarovski, juicy couture outlet, louis vuitton, replica watches, links of london, moncler, canada goose uk, moncler uk, hollister, wedding dresses, thomas sabo, karen millen uk, canada goose outlet, barbour uk, louis vuitton, ugg uk, ugg,ugg australia,ugg italia, moncler outlet, doudoune moncler, ugg,uggs,uggs canada, canada goose, supra shoes, coach outlet, canada goose outlet, ugg pas cher, ugg, pandora uk, louis vuitton, canada goose jackets, juicy couture outlet, moncler, marc jacobs, louis vuitton, pandora jewelry, lancel, swarovski crystal, pandora jewelry, pandora charms, barbour, moncler