Re: (S)PFiles in genaral [Oracle 9i] (Newbie)

On Thu, 10 Nov 2005 18:33:16 +0100, Joachim Hofmann wrote:

> Hello,
>
> I read on (S)Pfiles in a book, but I think I didnt get this topic.
> Some databases seem to have a text pfile including the parameters
> and some only have a binary spfile.
>
> (1) What is the main difference between a "normal" init file and a sp-initfile?

SPFILE

- is maintained and updated by the server;
- it must be in a server-accessible location;
- it contains a 'binary' part and therefore should not be edited;

PFILE

- can exist anywhere, including on the remote administrator's machine;
- multiple remote DBAs may have own copies - how to sync?;
- must be used for specific parameters that can not be set using "alter";
- can be edited.

>
> (2) How do I decide if the database has to work with a "normal" init file or a
> spinit-File?

IMO, always use SPFILE. That allows you to 'alter database' and 'alter system' immediately for most parameters, keep param setting across bounces, and also stage alterations to be set only at bounce.

IMO, use PFILE only as backup or when you need to set somthing not handled in SPFILE mode.

>
> I did a BACKUP of a spfile with a playground database as described in the Backup
> and Recovery Manual:
>
> SQL>CREATE PFILE = 'initmydb.ora.bak' FROM SPFILE;
>
> The format seems to be an text excerpt of the binary spfile, but it has not the
> same format like a real text pfile.

It is a 'real' pfile. You may be confused by the new 'instance specific' parameter syntax, where the param can be prefixed by an instance name or by a * to indicate all instances which allows the same [S]Pfile to be used in a RAC as well as a Single Instance environment.

> (3) What is the purpose of this backup file ? Could I restore this in case of
> emergency and how would I do this ?

Backup, manual alterations for non-dynamic parms, etc.

startup pfile=...

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** Top posting relies guarantees I won't respond. ***