Re: How to edit spfile ?/

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_at_comcast.net> 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/
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

--
http://www.freelists.org/webpage/oracle-l