Home » RDBMS Server » Server Administration » SPFILE persistent across startups
SPFILE persistent across startups [message #262287] Sun, 26 August 2007 00:51 Go to next message
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
Re: SPFILE persistent across startups [message #262289 is a reply to message #262287] Sun, 26 August 2007 00:59 Go to previous messageGo to next message
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
Re: SPFILE persistent across startups [message #262292 is a reply to message #262287] Sun, 26 August 2007 01:07 Go to previous messageGo to next message
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.
Re: SPFILE persistent across startups [message #262293 is a reply to message #262292] Sun, 26 August 2007 01:10 Go to previous messageGo to next message
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

Re: SPFILE persistent across startups [message #262294 is a reply to message #262287] Sun, 26 August 2007 01:10 Go to previous messageGo to next message
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.
Re: SPFILE persistent across startups [message #262295 is a reply to message #262294] Sun, 26 August 2007 01:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: SPFILE persistent across startups [message #262337 is a reply to message #262287] Sun, 26 August 2007 04:45 Go to previous message
naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore
Member

Hi,

Thank you for all your answers.

Regards,
Naveen
Previous Topic: CBO - Initial Parameters
Next Topic: Dupilcate database
Goto Forum:
  


Current Time: Sat Dec 03 04:19:56 CST 2016

Total time taken to generate the page: 0.18832 seconds