Joel, June 26, 2001 - 1:02 pm UTC
init.ora in 9i database
Umesh S. Kasturi, June 27, 2001 - 7:46 am UTC
a good concept..but I will be happy to get more details..
Nikhil Saxena, October 31, 2001 - 6:52 am UTC
I was not knowing this before
Oracle9i, init.ora isn't working
Hamilton Budayao, March 12, 2003 - 5:16 am UTC
thanks for the information. this site is really helpful.
i had the same problem, i can't change my audit_trail parameter to true in 9i but can easily change it in 8.1.7. version using init.ora file.
however, i've tried changing the parameter using the alter system command,
ALTER SYSTEM SET AUDIT_TRAIL = DB SCOPE = BOTH
what did i miss if i have encountered the error "specified initialization parameter cannot be modified"? can you please answer my question or just give a detailed procedure on how to change a parameter value in 9i enviromentment
March 12, 2003 - 7:59 am UTC
use scope = spfile
and then restart. that error says "sorry, that is one you cannot change online, hence scope=both won't work"
A reader, May 08, 2003 - 6:15 pm UTC
Sorry, I am still having problems:
SQL> create spfile='D:\oracle9i\admin\SHAN\pfile\SPFILESHAN.ORA' from
2 pfile='D:\oracle9i\admin\SHAN\scripts\init.ora';
File created.
SQL> sho parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> sho parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
Why is the spfile still blank after a restart? Do we have to mention the full spfile name everytime we startup like this:
SQL> startup pfile='D:\oracle9i\admin\SHAN\pfile\SPFILESHAN.ORA'
SP2-0714: invalid combination of STARTUP options
SQL> startup pfile='D:\oracle9i\admin\SHAN\pfile\SPFILESHAN.ORA'
LRM-00123: invalid character 0 found in the input file
ORA-01078: failure in processing system parameters
Please clarify with an example. Thank you verymuch.
Regards,
Shan.
May 09, 2003 - 1:05 pm UTC
unless you set it -- it'll look for it in the default location.
just go:
create spfile from pfile=.....
and it'll put it in the dbs directory named correctly for the default.
you obviously cannot use an spfile as a pfile -- spfiles are binary, pfiles are ascii text.
A reader, May 09, 2003 - 2:03 pm UTC
Got it! Thank you.
One more question, if we create the spfile at a non-default location, how do we specify that during the startup?
May 09, 2003 - 3:14 pm UTC
you can use a pfile with spfile= for example -- but i see no possible logical reasonable reason to not use the default location.
Why binary format...
Kamal Kishore, May 09, 2003 - 7:15 pm UTC
Hi Tom,
I always wondered the reasoning behind making the SPFILE a binary file and not a regular text file (like the init.ora file was).
Was there a reason (security ...), to make the format binary?
Also, If you change a parameter in your SPFILE using ALTER SYSTEM command and later restart the database, only to find that something went wrong and database will not start. In this situation, how to revert back the changed setting (can not edit the file, since it is in binary format).
Thanks a lot for the explanation on this concept.
Very useful.
Sreeviswa Peesapati, May 31, 2003 - 10:31 am UTC
I am very much impressed by the way Tom answers the doubts. Thank you Tom, it is Very helpful.
-Viswa
A reader, July 30, 2003 - 11:24 am UTC
insufficient priviledge
jack, November 11, 2003 - 1:09 am UTC
I've tried to execute "create pfile='D:\oracle9i\admin\SHAN\pfile\SPFILESHAN.ORA' from pfile='D:\oracle9i\admin\SHAN\scripts\init.ora'" in sql plus, but it returns "insufficient privileges".
my login is system/manager. What privilege do I need? system is supposed to be a dba?
November 11, 2003 - 7:13 am UTC
you need to be as SYSDBA
just being a 'dba' isn't good enough.
Oracle9i not using Spfile??
Rahul, June 09, 2004 - 9:19 am UTC
Hi Tom
I am using oracle9.0.1.1.1 on windows XP professional with p4. I have database started with spfile by default and I made some changes in memory related parameters then i just restarted the machine. Oracle got up automatically but it is not using spfile, because when i tried below command it gave me error..
SQL> alter system set sort_area_size=524288 scope=spfile;
alter system set sort_area_size=524288 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
I checked that my spfile is lying in the default location in DATABASE directory. Then how is it possible that oracle started up with using pfile?
one more question how can one know that oracle is running with which file spfile or pfile ? is there any sql to check this?
Regards
Rahul.
It's Done.
Rahul, June 10, 2004 - 12:08 am UTC
Yes Tom you were right. When I restarted the machine, db got up using services and used pfile but when manually i shut it down and started it up then it took spfile itself.
Thanks.
How can i find the correct PFILE ?
Sujit, December 24, 2004 - 5:42 am UTC
Hi,
I cannot startup oracle after power failure.
when i try startup
ora - 01078 and
lrm - 00109 : could not open parameter file '/opt/oracle/product/9.2.0/dbs/inittest_crm.ora'
occures.
at the dbs directory there is not a file named as inittest_crm.ora..
There are pfiles and spfiles but I dont know which is used for startup..
How can i find the correct PFILE i was using? The persons starting up the database is no longer in contact with me.
December 24, 2004 - 9:19 am UTC
you'll need to find one. it could have been on that persons laptop, it could have been on the OMS server machine (if you are using enterprise manager). it could be anywhere in your network.
you can rebuild one from your alert logs -- before each startup, it'll have printed out the settings you used last.
Re: How can i find the correct PFILE ?
Sujit, December 27, 2004 - 5:55 am UTC
Thanx Tom, It was very useful to me.
Location of a PFILE
Aníbal Gattás, September 07, 2009 - 12:57 pm UTC
Tom, Is there any view or table that register the location from a pfile if you start up the database with it? I understand the pfile could be anywhere, not only on the server, but is there a way to identify where is the file located? (don't know, may be if it's located on the server Oracle keep the path somewhere). Thanks in advance for your response.
September 07, 2009 - 8:30 pm UTC
Location of a PFILE
Aníbal Gattás, September 08, 2009 - 11:45 am UTC
Tom, thanks for your answer. Show parameter spfile doesn't show a path if the database has been started with a pfile (or at least not with one linked to a spfile). Is there any other command or place (table, view, etc.) to look for the path of a PFile if you started the database with one of those? A colleague told me he support a client's database that, from time to time is started with differents pfiles (don't know the reason) and sometimes he needs to identify which pfile they used to start the database, but he's unable to do so without asking the client's DBAs (and sometimes even though cause the guys don't remember). Thanks for your help.
September 08, 2009 - 9:39 pm UTC
... Show parameter spfile doesn't show a path if the
database has been started with a pfile ...
yeah, well, you asked about the SPFILE.
the pfile - might not even be on the server, the pfile is on the client that started the database - it could be on any machine in your environment.
select name || '=' || value from v$parameter where isdefault = 'FALSE'
will produce your pfile contents for you.
Location of a PFILE
Aníbal Gattás, September 09, 2009 - 9:51 am UTC
Tom, thanks for your answer. If you read carefully you'll see I asked for a PFILE from the begining. Anyway, many thanks for the select statement to generate the file, can be useful to re create it and compare it with the other files. Cheers.
September 14, 2009 - 10:06 am UTC
this was a question about SPFILES. Sorry, I read the header and answered...