Re: 9i, pfiles and spfiles

Glen A Stromquist wrote:

> 9ir2 on SuSE 7.3
>
> after creating a database and importing data from an 8.1.7 instance
> succesfully, I was playing with some init params, namely adding:
>
> cursor_sharing=force
> session_cached_cursors=200
> timed_statistics=true
>
> To try and get omlet for oracle working. Seeing that there was no init.ora
> in the dbs directory I added them directly in to the spfile<sid>.ora, (ya
> I know now NOT to) shut down the database and started it up, and up it
> went ok with nary an error.
>
> After reading a bit more on spfiles, (this is when I realized that one
> DOESNT add to the spfile) I shut the db down, edited the standard
> init.ora, made sure that compatible= was commented out and did a startup
> pointing at the new init.ora, immediatly getting an:
>
> ORA-00402: database changes by release 9.2.0.0.0 cannot be used by release
> 8.1.0.0.0
> ORA-00405: compatibility type "Locally Managed SYSTEM tablespace"
>
> So thats weird, now the document I'm reading about spfiles says that you
> cant startup the db pointing to an spfile like you can an init file, but I
> read this *after* I have actually done it and it works... so, now that its
> up and running again, create an init file witf "create pfile from
> spfile='myspfile" and create the file, shut down the db again and this
> time start it up pointing at the new init file, which now gives me the :
>
> Database mounted.
> ORA-00402: database changes by release 9.2.0.0.0 cannot be used by release
> 8.1.0.0.0
> ORA-00405: compatibility type "Locally Managed SYSTEM tablespace"
>
> So I edit the init file to make compatible=9.0.2.0, issue startup and same
> thing!, point it at the spfile again just for the hell of it and away it
> goes!
>
> what am I missing here?
>
> TIA
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
2. You corrupt your spfile
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
4. You get a startup error message
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'.

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.

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.

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.

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

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------