Home » RDBMS Server » Server Administration » spfile and pfile (11g)
spfile and pfile [message #656052] Wed, 21 September 2016 08:09 Go to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
Hi experts,

I am bit confused about spfile and pfile , as i understand the spfile is a server parameter file which can be used to set any parameters which are of dynamic in nature , meaning which can be done when database is online. For any changes to reflect after changing in pfile we need to restart the database.My doubt is if i change in spile , will those changes be done automatically in pfile.

Changes in spile will reflect in pfile automatically.

Regards

Arif
Re: spfile and pfile [message #656053 is a reply to message #656052] Wed, 21 September 2016 08:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>My doubt is if i change in spile , will those changes be done automatically in pfile.
NO

SQL> CREATE PFILE FROM SPFILE;

above is how to "refresh" the pfile to contain same parameters as spfile.
Re: spfile and pfile [message #656059 is a reply to message #656052] Wed, 21 September 2016 09:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Database Administrator's Guide,
Chapter Creating and Configuring an Oracle Database,
Section Specifying Initialization Parameters,
Paragraph About Initialization Parameters and Initialization Parameter Files

[Updated on: Wed, 21 September 2016 09:37]

Report message to a moderator

Re: spfile and pfile [message #656060 is a reply to message #656052] Wed, 21 September 2016 09:41 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
You use the PFILEor the SPFILE to start the database. Not both. Preferably the SPFILE.

If you want top see your SPFILE as text, like BlackSwan said:
SQL> CREATE PFILE FROM SPFILE;
What are you trying to achieve?
Re: spfile and pfile [message #656061 is a reply to message #656060] Wed, 21 September 2016 10:08 Go to previous messageGo to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
Thanks blackswan,Michael and gazzag, well what i am trying to understand is.

1) I make some dynamic change in spfile such as ALTER SYSTEM SET open_cursors=300 SCOPE=BOTH;
2) spfile will get updated with open_cursors=300
3) do i need to update the same in init.ora that is pfile by using
CREATE PFILE FROM SPFILE or will it automatically update the pfile.

What will happen if change some static settings in init.ora or pfile directly will that changes reflect in spfile automatically or i need to re-create the following to make changes in SPFILE from pfile.

CREATE SPFILE FROM PFILE.

Regards





Re: spfile and pfile [message #656062 is a reply to message #656061] Wed, 21 September 2016 10:13 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
The only way to reflect any changes in SPFILE is to manually create a PFILE from SPFILE. Incidentally, your SPFILE should be included in your backup regime.
Re: spfile and pfile [message #656063 is a reply to message #656061] Wed, 21 September 2016 10:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
spfile & pfile are independent objects which do NOT interact with each other without explicit & manual intervention.

if both spfile & pfile exist in "proper" directory, the spfile will be used when STARTUP statement is issued.
if spfile is used to start the DB, it must remain available while the database remains online.
spfile contents can be changed when certain "dynamic" parameter values are changed while the DB is online.
if/when pfile is used to start the DB, its content or existence is not relevant after the STARTUP command has complete.

You should experiment on your test DB to see for yourself what works & what throws errors.
Re: spfile and pfile [message #656065 is a reply to message #656061] Wed, 21 September 2016 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why do you ask questions that are answered in the link I gave you?

Re: spfile and pfile [message #656079 is a reply to message #656065] Wed, 21 September 2016 23:21 Go to previous messageGo to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
thanks experts, i got my answer . i went through the documentation and found out too.Basically we have to manually re-create the PFILE again after changing any parameters in SPFILE.

Quote:

Exporting the Server Parameter File
You can use the CREATE PFILE statement to export a server parameter file (SPFILE) to a text initialization parameter file. Doing so might be necessary for several reasons:

For diagnostic purposes, listing all of the parameter values currently used by an instance. This is analogous to the SQL*Plus SHOW PARAMETERS command or selecting from the V$PARAMETER or V$PARAMETER2 views.

To modify the &spfile;server parameter file by first exporting it, editing the resulting text file, and then re-creating it using the CREATE SPFILE statement

The exported file can also be used to start up an instance using the PFILE clause.

You must have the SYSDBA or the SYSOPER system privilege to execute the CREATE PFILE statement. The exported file is created on the database server system. It contains any comments associated with the parameter in the same line as the parameter setting.

The following example creates a text initialization parameter file from the SPFILE:

CREATE PFILE FROM SPFILE;

Re: spfile and pfile [message #656080 is a reply to message #656079] Thu, 22 September 2016 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Basically you don't need a pfile... or one with only one line "spfile=...".

Re: spfile and pfile [message #656123 is a reply to message #656079] Fri, 23 September 2016 08:55 Go to previous messageGo to next message
bpeasland
Messages: 51
Registered: February 2015
Location: United States
Member

arifmd1705 wrote on Wed, 21 September 2016 23:21
i went through the documentation and found out too.Basically we have to manually re-create the PFILE again after changing any parameters in SPFILE.
Not really. I'll give you the history....

A long time ago, there was only the PFILE. The venerable old init.ora as it was lovably known. DBA's put there parameters in this file. Life was easy back then. That is until you needed to change a parameter. Some parameters were modifiable with the system up and running. You could change the parameter value with a simple ALTER SYSTEM command. What was not so simple was that the DBA needed to remember to change the parameter value in the PFILE too otherwise the change would be lost the next time the instance was started. The DBA needed to change things twice.

Oracle decided there was a better way and that was to give us the SPFILE. Now, when you use the ALTER SYSTEM command to change a parameter's value, you can change it in MEMORY, in the SPFILE, or BOTH with the SCOPE clause.

Your database instance only uses one parameter file. One...that's it. Start it up, and it will read one file and then know how to define the parameters. You can explicitly say which parameter file to use by either of the following:

STARTUP PFILE=....
STARTUP SPFILE=...

If you just issue STARTUP, then Oracle will look in $ORACLE_HOME/dbs for the parameter file. It follows this order of precendence:

spfileSID.ora
spfile.ora
initSID.ora
init.ora

Once it finds a file, it quits looking.

If you are making changes to your SPFILE, and you just issue STARTUP, Oracle will never use the PFILE. So you updating the PFILE does nothing but cause extra work for you.

One of the problems with the SPFILE is that it is a binary file and it not readable. So you can't easily see its contents. When I need to see what is set in the SPFILE, I issue the following:

CREATE PFILE='/home/oracle/pfile.txt' FROM SPFILE;

Note that I do not place the PFILE in $ORACLE_HOME/dbs. I purposely avoid putting it there. The only parameter file in my $O_H/dbs is the SPFILE. This helps avoid any confusion down the road.

Another problem with the SPFILE is that you cannot modify it directly which means you need the instance up and running. If you start the instance and the SPFILE has a bad parameter value, then you will need to create a PFILE version, modify that with a text editor, and then create the SPFILE from the PFILE and then you can start the instance.

Oracle prefers us to use the SPFILE and to be honest, every DBA I know with an opinion on the subject will say the same. Use the SPFILE.

You only need one. So remove that PFILE from $ORACLE_HOME/dbs. Its only causing confusion with zero benefit.

Cheers,
Brian
Re: spfile and pfile [message #656127 is a reply to message #656123] Fri, 23 September 2016 11:40 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not a DBA. Thank you for explaining it in such a simple manner, I enjoyed reading it.

So, the only drawback (from my point of view) of the whole SPFILE story is that the file is binary. Wouldn't it be simpler if it was a pure txt file, so that you could modify it in case "it has a bad parameter value"? It appears that you still need a PFILE, modify it, (according to best practices) create SPFILE from PFILE, discard PFILE. Hm?
Re: spfile and pfile [message #656129 is a reply to message #656127] Fri, 23 September 2016 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The main advantage of spfile is that Oracle can store the dynamic and optimal values of SGA areas it found so the next time the instance is started it has not to learn from the workload to lead to these optimal values but can directly pick them from the spfile.
Also the spfile is centralized on the database sever when the pfile could be anywhere. It was one problem before when you found that an instance was running with bad parameters to know where it has pick them up and often take days before you discovered that a DBA has started it with a pfile on his workstation.

Re: spfile and pfile [message #656135 is a reply to message #656123] Fri, 23 September 2016 13:49 Go to previous messageGo to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
thanks brian , for the clear explanation.
Re: spfile and pfile [message #656142 is a reply to message #656123] Sat, 24 September 2016 08:43 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
[quote title=bpeasland wrote on Fri, 23 September 2016 08:55]arifmd1705 wrote on Wed, 21 September 2016 23:21

One of the problems with the SPFILE is that it is a binary file and it not readable. So you can't easily see its contents.
Well, not exactly. The actual parameters and their settings are clear text, and the file is quite readable. But there is danger in opening it in a text editor due to the possibility of inadvertently (or stupidly, as I once witnessed and eventually had to let the other guy discover for himself) modifying it in said text editor.

Quote:

When I need to see what is set in the SPFILE, I issue the following:

CREATE PFILE='/home/oracle/pfile.txt' FROM SPFILE;

That works, too. If all I need to do is see the contents, I'll usually use a 'strings' command (linux) or 'type' (Windblows).


Re: spfile and pfile [message #656143 is a reply to message #656127] Sat, 24 September 2016 08:49 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Littlefoot wrote on Fri, 23 September 2016 11:40
I'm not a DBA. Thank you for explaining it in such a simple manner, I enjoyed reading it.

So, the only drawback (from my point of view) of the whole SPFILE story is that the file is binary. Wouldn't it be simpler if it was a pure txt file, so that you could modify it in case "it has a bad parameter value"? It appears that you still need a PFILE, modify it, (according to best practices) create SPFILE from PFILE, discard PFILE. Hm?
It's pretty rare to get a bad parameter value, such that you would need to create a pfile, fix it, then recreate the spfile. I'm not saying it doesn't happen, but unless one is a complete klutz, it should be pretty rare. There are very few parms for which a bad value would prevent you from at least doing STARTUP NOMOUNT, which is as far as you need to get to ALTER SYSTEM SET ... SCOPE=SPFILE;
Re: spfile and pfile [message #656147 is a reply to message #656143] Sat, 24 September 2016 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Well, I'd say I see a complete klutz every year... and sometimes it's me... wrongly counting the number of 0 when modifying sga or target max size. /forum/fa/1606/0/

Re: spfile and pfile [message #656149 is a reply to message #656147] Sat, 24 September 2016 16:13 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Michel Cadot wrote on Sat, 24 September 2016 12:34

Well, I'd say I see a complete klutz every year... and sometimes it's me... wrongly counting the number of 0 when modifying sga or target max size. /forum/fa/1606/0/

Embarassed
Previous Topic: Cloning in oracle
Next Topic: Granting Create Access
Goto Forum:
  


Current Time: Thu Mar 28 03:51:41 CDT 2024