Re: How to edit spfile ?/

  • From: "Mark Brinsmead" <pythianbrinsmead@xxxxxxxxx>
  • To: orcl@xxxxxxxxxxx
  • Date: Wed, 26 Apr 2006 18:57:32 -0600

There are lots of ways to do this -- some supported; most not.

The supported ways basically amount to one of

   - CREATE PFILE=... FROM SPFILE
   - ALTER SYSTEM SET xxxx = yyyyyyyy SCOPE = SPFILE

I would normally recommend you stick to one of these.

If you insist on editing an SPFILE, you can, (I have done so successfully on
a couple occasions) but it is a very bad idea.  The first thing to know is
this:

   - The documentation says SPFILEs are "binary" files.
   - The documentation is only half-true.

In actual fact, the SPFILE is really just a simple text file, with binary
(ASCII NUL) padding at the beginning and end.  On a UNIX(-like) system, you
can create a perfectly valid PFILE from any SPFILE with the command:

$  strings spfileMYSID.ora > initMYSID.ora

You *can* also edit it directly, providing you have a binary-capable editor
(e.g., "emacs").  Forget editors like 'vi' -- the standard 'vi' editor does
bad things to binary files, like replacing ASCII NULs with spaces.  Since
SPFILEs use ASCII NUL for padding...   (note: the Linux version of 'vi' --
actually "vim" -- probably doesn't do this.  Others may not any more,
either.  I wouldn't know; I stopped trying to use 'vi' to edit binary files
about 15 years ago.)

If you insist on editing your SPFILE, I have found that balancing my edits
by adding or removing characters from the NUL padding at the end of the file
works quite nicely.  Of course, I've only actually done it about twice.  The
fact is, there are enough safe (and supported!) alternatives that you should
never have to do this.


On 4/26/06, Bob <orcl@xxxxxxxxxxx> wrote:
>
> HI, my approach is, if its a parameter that cant be set dynamically eg
> ALTER SYSTEM...
>
> create pfile from spfile
> shutdown
> make changes to the pfile
> startup pfile=/path/to/pfile...
> check the parameters are in effect
> create spfile from pfile
> shutdown ....
> startup
> the db will now be using the spfile (by default Oracle looks for the
> spfile first, then pfile)
>
> for kicks you *can* view the spfile which  is mainly text, but you
> should not edit it and expect it to work
>
> The spfile is very useful as parameters can be set dynamically and if
> your in  a Data guard environment oracle writes and rewrites to the
> spfile, while performing switchover getting extremely verbose at
> times... pretty neat stuff
>
> advise,,, get used to using the spfile
>
> Bob
>
> --
> "Oracle error messages being what they are, do not
> highlight the correct cause of fault, but will identify
> some other error located close to where the real fault lies."
>
>
>
> Dean Paul wrote:
>
> > Without creating pfile from spfile how can we edit  spfile ?
> > thx
> >
> > _________________________________________________________________
> > Express yourself instantly with MSN Messenger! Download today - it's
> > FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>


--
Cheers,
-- Mark Brinsmead
   Staff DBA,
   The Pythian Group
   http://www.pythian.com/blogs

Other related posts: