Re: spfile vs pfile in 9i

Connor McDonald wrote:

> when you work at a site where you have SYSDBA access but not any access
> to the box itself, then an SPFILE is a nice thing to have.  Much better
> than having a DBA start the database from a pfile sitting on their own
> C: drive (or worse)
>

Well, here's a little counter argument.

I want to duplicate a database with RMAN.

First thing I do is copy the password file and the spfile from the production database.

Of course, the spfile for the clone has to have a couple of parameters edited to make it workable: DB_NAME and CONTROL_FILES, for example. And, of course, to edit the spfile one has to have an instance.

So I startup nomount, and alter system set db_name=XXX scope=spfile.

No problems. Then I fire up RMAN, and attempt to connect to the auxiliary and the target, only to be told that the auxiliary service name is not something my listener recognises.

Of course not! My auxiliary instance registers itself with the listener using the *original* db_name (and hence, since service_names=db_name+db_domain, the listener is aware of the OLD service name only). Because the instance I started up in order to edit the spfile only knows the original settings for db_name. I successfully edited that parameter after it had started up, of course. But until I restart the instance, it doesn't re-register itself with the listener using the correct db_name information.

It is totally logical and completely forseeable. But if I'd been doing it with an init.ora, I would have edited it and *then* started the instance, and the listener would therefore have been immediately aware of the *correct* service name.

It is nothing that an instance re-start can't fix. But I had to think about it for a couple of minutes, and I shouldn't have to.

Oracle needs to release a tool which directly edits the spfile, and not just blandly state "you edit it with an instance". Because that means the editing tool is dependent on the thing which is doing the editing, and vice versa. And therein lies most of the "problems" associated with using an spfile.

Regards
HJR Received on Sat Nov 06 2004 - 05:52:09 CST