Re: Bad Tuning, need to reset spfile

lancegrieb_at_yahoo.com (TX-DBA) wrote in message news:<a95941b3.0306071223.68afec70_at_posting.google.com>...
> 9i Version 9.2.0.3
> Unix-Solaris 8
>
> I did some research before asking but really need some confirmation.
>
> 1)I dynamically increased my the following parameters on DB1 with
> scope=both except for sga, can only scope to spfile.
>
> sga_max_size -> 2G
> shared_pool_size -> 400M
> large_pool_size -> 400M
> db_cache_size -> 500M
>
> 2) Memory errors, I allocated 2Gig of the 4Gig RAM to the SGA. I guess
> I got greeedy and took too much memory. Obviously I'm a amatuer. Doh!
>
> 3) Shutdown DB1 so DB2 & DB3 can still work. Yes, three databases on
> the same domain sharing the same RAM, not my choice.
>
> 4) I want to startup DB1 and reset the params. I'm afraid I might not
> even have enough memory to finish the first couple of alter system
> stmts, plus it might cause memory errors again hurting DB2 & DB3.
>
> 5) Can I do the pre-9i way and issue a startup pfile=init.ora with the
> init.ora containing new lower param values? Then dynamically update
> the params with the old low values so they are written to the spfile
> too.
>
> Also, should I do a complete startup and OPEN the database or should I
> do a NOMOUNT?
>

Ability to dynamically set parameters in 9i DOES NOT mean that it is the only option available to set parameters. Oracle is generally very good with keeping things backward compatable. Pick one of these options,

1. Set parameters dynamically with scope=spfile
2. Edit pfile, shutdown database and recreate spfile from pfile then startup
3. Startup using spfile
4. Startup using pfile

Don't know if it is more of an art then science but one thing is certain... You must have good to excellent knowledge of Oracle RDBMS and underlying operating system before embarking on performance tuning.

Regards
/Rauf Sarwar Received on Sun Jun 08 2003 - 06:11:49 CEST