RE: Shooting yourself in the spfile

From: <Alexander.Feinstein_at_mitchell1.com>
Date: Wed, 18 Feb 2004 17:00:29 -0800
Message-ID: <27C31DE9ADF1D211A7C300E0B1041E23015115A3@discovery.mitchellrepair.com>

Jonathan,
What are you going to do when you hosed your initSID.ora file? Probably, open it with text editor, like vi, fix it and try to start again. With spfile one can use followin steps:

1. sqlplus /nolog
2. SQL> create pfile='/tmp/pfile' from spfile='?/dbs/spfileSID.ora'
3. SQL> host vi /tmp/pfile
4. SQL> create spfile='?/dbs/spfileSID.ora' from pfile='/tmp/pfile'
5. SQL> host rm /tmp/pfile
6. SQL> startup

Alex.

-----Original Message-----

From: Jonathan Gennick [mailto:jonathan_at_gennick.com] Sent: Wednesday, February 18, 2004 2:57 PM To: oracle-l_at_freelists.org
Subject: Shooting yourself in the spfile

I just went through an episode in which I changed a parameter setting using scope=spfile, 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=spfile. Arg!

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:

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.
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.

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?

Best regards,

Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit
http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body.

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

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

Received on Wed Feb 18 2004 - 19:00:29 CST