Re: 9i, pfiles and spfiles

snipped

comments embedded
>
>
> Let's see if I get the sequence of events right.
>
> 1. You create a 9iR2 database (presumably using dbca) which causes you to
> have a locally managed SYSTEM tablespace (a uniquely 9iR2 feature) and
> compatible=9.2; and an spfile

I used dbca to create the scripts, didn't run the shell script(never seems to work for me) so ran the resulting sql a statement at a time after editing further for added tbs's etc.. (it was a slow day)

but yes, dbca was used in that regard

> 2. You corrupt your spfile

yup, by adding the three params mentioned, shut the db down, issued startup, errored out saying that it could not find init file so re-issued startup with pfile=$ORACLE_HOME/dbs/myspfile and it started up fine...
>
> 3. You revert to using an init.ora, where by commenting out compatible=9.2,
> you revert to the default value of that parameter, so you effectively have
> compatible=8.1

edited the generic init.ora to fit my db, compatible was 8.1.0 so I left it commented out(default), then shut the db down again and issued startup.

> 4. You get a startup error message

yup
> 5. You then 'create a pfile from an spfile"... an spfile which you don't
> have at this point, because you corrupted it in step 2, and I don't see you
> recreating it with 'create spfile from pfile'.
couldnt start it with the pfile, so pointed at the spfile again and it started, THEN created pfile from spfile ok, shut the db down and still got an error when issuing startup with no pfile parameter.

> And then things just get very hazy because I have no idea whether "away it
> goes" means your instance is running just fine or not, or whether the error
> message has gone or not.

"away it goes" meant that it started fine when I again pointed at the spfile, which shouldnt work, right?

> In any case, the problem is obviously that you set compatible to a value
> that's *not* compatible with a feature you've chosen to use (in this case,
> the locally-managed SYSTEM tablespace which is dbca's default way of
> working). Your other problems simply stem from a misunderstanding of the
> order of preference Oracle has at startup for pfiles and spfiles. The order
> goes:
>
> 1. SID-specific spfile
> 2. Generic spfile
> 3. SID-specific init.ora
> 4. Generic init.ora
>
> ...and all of that within the ORACLE_HOME/dbs directory.
>
> Where you have an initSID.ora and an spfileSID.ora both residing in the
> OH/dbs directory, then the spfile will be used to start the instance.

yes, I get it now, the mystery to me was why did it start when I pointed it to the spfile (shouldnt have) but wouldnt start just by issuing "startup" as it would look for this same file. But then when I think about it that makes sense now as it saw the spfile as corrupted with my dickering with it. Still dont know why it started ok when I pointed to it though (going to try it again tomorrow)

> You can modify this default order of precendence by issuing an over-ride at
> startup time: startup pfile=/blah/blah/initwhatever.ora will cause the
> specified init.ora to be used, regardless of the existence of an spfile in
> the OH/dbs directory. You cannot do the same thing for an spfile, though.
> Startup spfile=/blah/blah/spfilewhatever.ora will generate an error about
> the wrong parameters being specified for the startup command.

maybe thats it, I didnt use "startup
spfile=$ORACLE_HOME/dbs/spfilesid.ora", I used "startup pfile=$ORACLE_HOME/dbs/spfilesid.ora"

I have a better understanding now though, and when I got the compatible error I de-commented out the compatible=8.1.0 and made it 9.0 which of course would still not work with r2.

> Hopefully you can use that behaviour to work out what it is that is
> happening when you issue the startup command, because from your
> description, I can't work out. I would need to see what exactly are the
> commands you are issuing, what exactly are the error messages you're
> getting, and the exact contents of ORACLE_HOME/dbs before I could do that.
>
> Regards
> HJR

I left my session open so hopefully my xterm is buffered enough I can go all the way back thru it and investigate more.

Now that I think about it, once I edited the spfile and issued just "startup", it errored saying it could not find init<sid>.ora, which is correct because at that point the spfile was corrupt and there was no init file yet. It still doesnt explain why the instance started fine though when specifying pfile=/pathto/spfile<sid>.ora.

Also I'm pretty sure when I queried v$spparameter it showed that the instance had been started with an spfile, value=0 if I recall correctly

In any event, some more experimentation tomorrow when I get back may clear things up more, but it seems that it *will* start with a corrupted spfile if the "pfile=/path_to/spfile" is used with startup...

At least I know how to fix it now with the compatible=9.0.2 param in the init.ora

thanks again! Received on Thu Oct 09 2003 - 23:33:01 CDT