Increasing shared_pool_size and java_pool_size

This is assuming that sikyala is using 10g.  He/she didn't mention the version being used.  If it's 9i, then the only choice to change the max SGA setting is that alter system command from slightwv and a restart.  
However, if you know that perhaps your streams pool and buffer cache, and large pool aren't being used to their max, your quick emergency fix without shutting down the db would be to take some memory away from them and then re-allocate it to your shared pool and java pool.  

Lets say your Large pool is at 2 g, but your nightly batches are done and you aren't using all of it.......
ALTER SYSTEM SET LARGE_POOL_SIZE= 512m;  --you don't need a scope argument because 'BOTH' is the default.   If that's too much, Oracle will tell you that some of that is being used and tell you.  
Assuming that works, you now have 1.5gb that you can re-allocate to your java and shared pools dynamically without having to shut down the database.  

Then you can deal with proper allocations after users are off the systems with minimal interference.  

Of course, if you aren't that fortunate, then the restart has to happen.  

In 10g, when you set SGA_TARGET to a non-zero value, in R2 the SHARED_POOL, BUFFER_CACHE, JAVA_POOL, STREAMS_POOL, AND LARGE_POOL will be automatically managed.  There are three additional processes that are addded to the mix here, MMON (Managibility Monitor), MMAN (memory manager) , and MMNL (manageability monitor light).  Every second, MMNL sticks its toe in the sga and looks at wait events, writing them to a 2m buffer (for performance) to be flushed to disk eventually.  When it's noticed that an auto-managed component is running out of memory, MMAN doesn't just do the knee jerk reaction and quickly move memory.  It behaves like the optimizer, looking at historical statistics in the sga running what if scenarios looking at the short term AND long term effects of the change and only then does it re-allocate.  

By the way...if you are going to do the manual method, you need to make sure you reserve 20% of the physical memory for the operating system.  You play with that fire a bit too much and the operating system will degrade in performance....taking your database with it.  Remember, Oracle's processes don't actually write to the disk or even allocate space or memory without the o/s' cooperation....as the O/S goes, so will your DB.  

Oh, to add to the SGA_TARGET discussion.....not even automatic memory management will save you if the overall SGA is undersized.....you may STILL have to up the SGA_Target which will likely mean a change in the SGA_MAX_SIZE.  

I needed to increase the shared_pool_size because I upgraded from 9.2.0.1 to 9.2.0.6 and it is a post-installation requirement that the shared_pool_size be at least 150M. I was aware of the command alter system. I was having problems determining what to alter the size to because it is shown in bytes I thought there may be an easier way other than doing the math.

You can specify kb or mb, when you alter the size.  Need to do the math, really.  

correction....NO need to do the math, really.  Oracle will figure out how many bytes it would amount to, so it can confuse us later with sizes.....lol

Just to add to dotten's post for clairification:

You can add K, M and more recently G to size parameters.

example:
alter system set sga_max_size=1G scope=spfile;
alter system set shared_pool_size=150M;

slightwv.....I don't think 9i likes G for size parameters.  I'll admit, that I've never bothered to try it, but I don't think 9i supports it.....alas, I don't have my 9i environment handy to test on....please correct my if I'm mistaken......I've just never bothered to use G in sizing datafiles in 9i.....

10g does support gb file sizing because of the bigfile tablespace feature.....a monster....it can only consist of one datafile, but can be anywhere from 8 to 128Tb in size.....depending on the DB_BLOCK_SIZE.....YIKES!!!!  Don't think I'll be running that off a floppy anytime soon!

I'm thinking from your post snippet 'and more recently G to size parameters'  this is what you mean....

Schwertner,
Are you sure you didn't intend the above post for another question?  The poster was not asking a migration question, only how for advice on changing initialization parameters specific to MAX_SGA_SIZE and JAVA_POOL SIZE.  Additionally, they are running on 9i.  I'm not aware of a starup migrate option in 9i, though I could be mistaken, I've just never used it.  10g, requires the STARTUP UPGRADE to be used when performing an upgrade.  Regardless, unless I missed somethiing, this poster isn't looking to migrate or upgrade.  

Dotten,

The asker asks for the typycal scenario in the second part
of the upgrade process - the catalog upgrade.
And fail in the typical trap - MAX_SGA_SIZE low value.

There is no reasons to change SPFILE parameters. Because if there were reasons to do this it had be done, but if not - there is no reason.

Changing SPFILE parameters is risky - there are syntactical traps that will lead to an invalid SPFILE.

So I always do these upgrades via PFILE miror copies of the SPFILE wit minor changes of 3 parameters.

The asker said: I needed to increase the shared_pool_size because I upgraded from 9.2.0.1 to 9.2.0.6 and it is a post-installation requirement that the shared_pool_size be at least 150M.

Also he uses the right syntax 150M.

Finaly I will said that beginning from 10.2.0.3 the upgrade scenario is more complex - you can not open the DB in normal mode without upgrading the catalog. I fail in this trap - have 5 10.2.0.3 instances running ......

So I have the feeling that my posting is adequate, but please get my appologize if this is not so ...

You know, nobody is perfect, especially old professors like me that began their programmer career with punched tapes and now are trying to be Oracle profis!

Dotten, do not worry, I have very good feelings to you!:
Please pay attention to this my sentence:
-------> So I always do these upgrades via PFILE miror copies of the SPFILE wit minor changes of 3 parameters.

May be I had to write instead PFILE the words "Parameter File" and instead SPFILE "Server Parameter File".

You are absolutely right that it is suicide to edit the SPFILE directly.
Even more - also indirectly via GUI and commands.


Why I do not use ALTER SYSTEM?

I tried this. And I run in troubles. The problem is very curious.
It seems that ALTER SYSTEM and also the OEM GUI
do not checks the entered values. It simply put them AS IS in the SPFILE.
After that trying to open the DB you got:
.... INVALID PARAMETER IN SPFILE.
It is hard task to escape.
You can not open the DB to alter the parameter.
May be this is possible in MOUNT mode, but doubt ...
So the only thing we can do is to find a PFILE and startup the DB
SQL>open pfile=/u03/init_main.ora

Another thought.
Why have you to alter the SPFILE?
If the both parameters are smaller then 150M this means
(if the DBA is a serious guy!!!!) that his will was to use
small values for them. So after the migration he has to put them back
taking the risk I explain.


If you are curious about
SQL>startup migrate pfile=/u03/init_main.ora

It is really so. On Sunday I installed on a University laptop Oracle 9i.

Excerpt (README.HTML of the upgrade kit):


Oracle9i Database Server
Patch Set Notes
Release 2 Patch Set 4 Version 9.2.0.5.0 for Linux x86
March 2004


Post-Installation Tasks

Start the database listener using a command such as lsnrctl start.

Log in as sysdba using SQL*Plus.

Run the following commands and scripts in order from ORACLE_HOME in migrate mode on all updated databases.

Run the startup migrate command.
Run the spool patch.log command.

Run the @?/rdbms/admin/catpatch.sql script.

Run the spool off command.

Review the patch.log file for errors, and rerun the catpatch.sql script after correcting any problems.

Run the shutdown command.

Run the startup command.

Run the @?/rdbms/admin/utlrp.sql script. This step is optional, and will recompile all invalid PL/SQL packages now instead of when the packages are accessed the first time. The utlrcmp.sql script can be used to parallelize this in multiprocessor machines.




 

I did the following:

ALTER SYSTEM SET SGA_MAX_SIZE=350M SCOPE=SPFILE;
ALTER SYSTEM SET SHARED_POOL_SIZE='150M' scope=spfile;

Dotten you made a comment:

I'm not aware of a starup migrate option in 9i, though I could be mistaken, I've just never used it.  10g, requires the STARTUP UPGRADE to be used when performing an upgrade.  Regardless, unless I missed somethiing, this poster isn't looking to migrate or upgrade.  


I actually needed to change the the shared_pool_size and java_pool_size because I was upgraded from 9.2.0.1 to 9.2.0.6. Instructions fro installing that patchset requires the following:

1. check system tablespace size and increase if necessary
2. check shared_pool_size and increase if necessary
3. check the java_pool_size and increase if necessary
4. shutdown database
5. startup database with command: startup migrate
6. run catpatch.sql script and check for errors
7. shutdown and startup the database
7. run utlrp.sql script

Sikyala,
You're right, I was wrong as I mentioned I might be.  I was speaking in the wrong context.  Schwertner had mentioned 10g, and for some reason I thought he meant you were upgrading to that and had totally missed (acutually, forgotten) your mention of the upgrade.  I've been having some brain cramps lately.....

Great instructions, huh?  Isn't it soooo helpful they tell you to change a pool size 'if necessary' but not give you just a hint on how to determine that?  UGH!  It's the kind of stuff that keeps us employed, since we have to figure it out as we go, but frustrating nonetheless.    

Schwertner:
I'm ecstatic to hear I'm on your  good side!  I'll sleep well now!!!!

Additionally, sir, I suddenly remembered something.....or I accessed a block of my brain that had been corrupted, or something:
I DO REMEMBER using starup migrate.....actually did it a bunch of times.  I nearly slapped myself.  

Just as a reference to all...they simply changed that startup mode to 'upgrade' now in 10g.  Oracle has not redefined the terminology.  Upgrade is now upgrading an Oracle installation.  Migrate is now defined as migrating from a non-Oracle product.