RE: Shooting yourself in the spfile

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 18 Feb 2004 16:56:32 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F60262119FBD6@irvmbxw02>

It seems to me that Oracle is moving away from the text-based init.ora = file. And since you can change the init parameters with alter system, = why edit an init.ora file with vi? At least the 'alter system' command = has some syntax checking. If I say
alter system set optimizer_index_cost_adK =3D 25 scope =3D spfile ; then I get an error message, but if I type in optimizer_index_cost_adK =3D 25
in my init.ora file and try to start up the database, I get an error at = startup time and have to go edit the init.ora file a second time.

I would use as a standard practice, backup the spfile along with the = database (of course) and then before each change save a copy of the = spfile. For example create a script called "backup_spfile.sql" = containing

host cp $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora = $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora.`date +"$Y%m%d_%H%M%S`

and run backup_spfile.sql before any "alter system set ..." command.

Why the spfile is not text-based - could it be that Oracle is moving to = making it totally "encrypted"?

> -----Original Message-----
> Jonathan Gennick

>=20

> I just went through an episode in which I changed a
> parameter setting using scope=3Dspfile, attempted to bounce my
> instance, and found that I was hosed. Fortunately, I had an
> up-to-date text version of my parameter file (i.e. init.ora)
> that I was able to fall back on, and I easily used that to
> restart my instance. Now I have to recreate my server
> parameter file, because, having started my instance using
> init.ora, it won't let me change a parameter using
> scope=3Dspfile. Arg!

>=20

> All this has underscored the importance of backing myself up
> when it comes to parameter changes, and I'm wondering about
> best-practices. What do you all do to protect yourself here?
> I can think of two alternatives:

>=20

> * Follow each successful spfile change by creation of a new,
> text-based (init.ora) parameter file, to use in case you
> muck up your spfile in the future.

>=20

> * Make a copy of your binary spfile after each successful
> change, or before making a change, so that you can fall back
> by copying the last known-good spfile over the one you
> screwed up.

>=20

> Are there any alternatives that I've missed here? What
> approach is best, and why? Right now, I lean towards keeping
> a current, text-based parameter file, because that gives you
> the flexibility to go in with vi and tweak a parameter. In
> fact, I wonder why the server parameter file can't be
> text-based. If I have to constantly make text-based backups
> to protect myself, why not just make the spfile text-based
> to begin with?

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Feb 18 2004 - 18:56:32 CST