OraFAQ Forum: Server Administration » SPFILE persistent across startups

Home » RDBMS Server » Server Administration » SPFILE persistent across startups

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SPFILE persistent across startups [message #262287]

Sun, 26 August 2007 00:51

naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore

Member

Hi,

1) Can any one please tell the main difference between PFILE and SPFILE, because

Using ALTER SYSTEM we can make changes to the parameters in PFILE but the thing is we need to manually change in the init.ora file to reflect the changes on future startups, but with SPFILE, the ALTER SYSTEM command can update the binary file as well. This allows the changes to be persistent across startups. I think this is not the main difference.

2) Why server-side initialization parameter file is considered as spfile and client-side initialization parameter file is considered as pfile?

Regards,
Naveen

Report message to a moderator

Re: SPFILE persistent across startups [message #262289 is a reply to message #262287]

Sun, 26 August 2007 00:59

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,

Using ALTER SYSTEM we can make changes to the parameters in PFILE

NO,

alter system set ... scope=both (default)
alter system set ... scope=SPFILE

Above both command make changes in SPFILE if spfile is used for instance startup.

Reason: you can't edit directly spfile that is why we use SCOPE=SPFILE and restart oracle server.

2) Why server-side initialization parameter file is considered as spfile and client-side initialization parameter file is considered as pfile?

Yes,

Spfile is BINARY FILE you can't edit directly if edit it will corrupted.

Pfile is ASCII text file you can edit directly

There is two way to edit parameter in spfile.
1. alter system set ...scope=spfile + shutdown + startup

2. shutdown + edit pfile + create new spfile from pfile + startup

Regards
Taj

Report message to a moderator

Re: SPFILE persistent across startups [message #262292 is a reply to message #262287]

Sun, 26 August 2007 01:07

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Quote:

alter system set ... scope=both (default)

This is not true. This is true only if spfile is used to system startup. If pfile is used to system startup then default is memory, i.e it is modified during entire session.

Report message to a moderator

Re: SPFILE persistent across startups [message #262293 is a reply to message #262292]

Sun, 26 August 2007 01:10

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Yes, It is correct.

Default parameter file used is SPFILE so below condition is true.

both=memory,spfile

and if oracle server used pfile then also below condition is true.

both=[b]memory[/b],spfile

Regards
Taj

Report message to a moderator

Re: SPFILE persistent across startups [message #262294 is a reply to message #262287]

Sun, 26 August 2007 01:10

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Quote:

I think this is not the main difference.

But, this is the main difference.

Report message to a moderator

Re: SPFILE persistent across startups [message #262295 is a reply to message #262294]

Sun, 26 August 2007 01:19

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Main difference
spfile ===> binary file
pfile ====> ascii text file

you can't changes directly spfile
you can changes directly pfile

default instance startup parameter file is spfile.
if spfile is not found then pfile (init.ora) is used.

prior oracle 9i init.ora pfile is used.
and for any changes we need to down database server.

but in 9i through alter system ...command and new feature spfile we can edit and change any (modifiable) parameter without down oracle server.

Regards
Taj

[Updated on: Sun, 26 August 2007 01:20]

Report message to a moderator

Re: SPFILE persistent across startups [message #262298 is a reply to message #262287]

Sun, 26 August 2007 01:28

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Yes, Taj. Thank you.

By the way Both is always Both. (Memory+spfile). It has no relation with startup.(Whether by spfile or by pfile).

Quote:

and if oracle server used pfile then also below condition is true.

both=memory,spfile

It is true but not the default one.

Mohammad Abdul Momin Arju.

Report message to a moderator

Re: SPFILE persistent across startups [message #262337 is a reply to message #262287]

Sun, 26 August 2007 04:45

naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore

Member

Hi,

Thank you for all your answers.

Regards,
Naveen

Report message to a moderator

Previous Topic:	CBO - Initial Parameters
Next Topic:	Dupilcate database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 19:44:27 CDT 2024