Home » RDBMS Server » Server Administration » pfile and spfile
pfile and spfile [message #263138] Wed, 29 August 2007 03:26 Go to next message
aline
Messages: 92
Registered: February 2002
Member
hi,

I'm searching a query to know is the instance is running with a pfile or a spfile and of course the name of this file.



Re: pfile and spfile [message #263140 is a reply to message #263138] Wed, 29 August 2007 03:35 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

show parameter spfile/pfile
Re: pfile and spfile [message #263143 is a reply to message #263138] Wed, 29 August 2007 03:51 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
hello,

I think this query does'nt work.

My test database hasn't any spfile and for both query, the result is ?/dbs/spfile@.ora
Re: pfile and spfile [message #263145 is a reply to message #263140] Wed, 29 August 2007 03:51 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
If you are running on pfile, you will not get the name. It displays null
Re: pfile and spfile [message #263149 is a reply to message #263145] Wed, 29 August 2007 04:02 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
NandKumar wrote on Wed, 29 August 2007 03:51
If you are running on pfile, you will not get the name. It displays null


no,

I'm running with a pfile and the result of the query is

Quote:

SQL> show parameter pfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string ?/dbs/spfile@.ora
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string ?/dbs/spfile@.ora


Re: pfile and spfile [message #263150 is a reply to message #263138] Wed, 29 August 2007 04:04 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I don't believe you.


You shutdown your instance and start your database by

startup pfile=/oracle/app/oracle/product/10.2.0/db_1/dbs/initsource.ora

and then use ..
Re: pfile and spfile [message #263154 is a reply to message #263138] Wed, 29 August 2007 04:13 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
show parameter pfile;
show parameter spfile;


You missed this point.

both are identical. You use anyone of them , not two. Whether you give anyone on these two it will show spfile..

No pfile parameter exist only spfile. So take care of it.

Quote:
SQL> select name from v$parameter where
name in ('pfile','spfile');

NAME
------------------------------
spfile
Re: pfile and spfile [message #263161 is a reply to message #263149] Wed, 29 August 2007 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You may have started with a pfile (init.ora) file that contains "spfile=?/dbs/spfile@.ora".

Regards
Michel
Re: pfile and spfile [message #263162 is a reply to message #263154] Wed, 29 August 2007 04:29 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
I'm sorry, but in my test database I'm using a pfile
and the result of your query is:

Quote:

SQL> select name from v$parameter where name in ('pfile','spfile');

NAME
----------------------------------------------------------------
spfile



In this case, I would like to find pfile and where it is!
Re: pfile and spfile [message #263164 is a reply to message #263161] Wed, 29 August 2007 04:32 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
Michel Cadot wrote on Wed, 29 August 2007 04:29
You may have started with a pfile (init.ora) file that contains "spfile=?/dbs/spfile@.ora".

Regards
Michel




Hi Michel,

No, this parameter is the default parameter.
Re: pfile and spfile [message #263166 is a reply to message #263154] Wed, 29 August 2007 04:33 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Arju are you sure...may be you are running on spfile

Check this out

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      D:\ORACLE\PRODUCT\10.2.0\DB_2\
                                                 DATABASE\SPFILEALPHA.ORA
SQL> create pfile from spfile;

File created.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile
SP2-0714: invalid combination of STARTUP options
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1247876 bytes
Variable Size              83887484 bytes
Database Buffers           75497472 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL>


It shows null.
Re: pfile and spfile [message #263171 is a reply to message #263164] Wed, 29 August 2007 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's the query for 2 databases (9.2.0.6 and 10.2.0.2):
SQL> select value from v$parameter where name='spfile';
VALUE
------------------------------------------------------------


1 row selected.

SQL> select value from v$parameter where name='spfile';
VALUE
-----------------------------------------------------------------

1 row selected.

Both don't use spfile but a pfile where there is no "spfile" parameter.
The parameter value is empty.

Regards
Michel

[Updated on: Wed, 29 August 2007 04:42]

Report message to a moderator

Re: pfile and spfile [message #263172 is a reply to message #263138] Wed, 29 August 2007 04:43 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I could not able to catch you.

[Updated on: Wed, 29 August 2007 04:46]

Report message to a moderator

Re: pfile and spfile [message #263186 is a reply to message #263172] Wed, 29 August 2007 05:43 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Hopefully this will clarify things for those not sure:
SQL> select value from v$parameter where name='spfile';

VALUE
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEREMOTEDB.ORA

SQL> CREATE PFILE FROM SPFILE;

File created.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP PFILE='C:\oracle\product\10.2.0\db_1\database\initremoteDB.ora'
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             167775108 bytes
Database Buffers          436207616 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name='spfile';

VALUE
--------------------------------------------------------------------------------


SQL>

i.e. If the db is using an spfile, then you will see its name and location in select value from v$parameter where name='spfile';
If you are using a pfile, then the aforementioned query will result in a NULL. As to how to find the name and location of the pfile? I don't believe you can from sql. You'd need to search the OS (I could be wrong of course, it's not a rare thing to happen Smile )
Re: pfile and spfile [message #263188 is a reply to message #263138] Wed, 29 August 2007 05:50 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Pablolee and Michel, yes , it is always ok.But my query is about the post of NandKumar. He started his database with spfile but when he invoked "show parameter spfile " nothing is shown..


How possible?
Re: pfile and spfile [message #263191 is a reply to message #263188] Wed, 29 August 2007 05:55 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Arju....i forgot to mention...after i created pfile from spfile, shut the database, i renamed the <dbname>spfile.ora to some other name. When i started the database....it searched for the <database>spfile.ora...since its not present..it took the pfile( just created before shutting down) from the default location. So the database is running with pfile. Thats why it didnt display any value in the show parameter.
Re: pfile and spfile [message #263192 is a reply to message #263138] Wed, 29 August 2007 06:00 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Thanks for the feedback.
Re: pfile and spfile [message #263193 is a reply to message #263138] Wed, 29 August 2007 06:02 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Anyway, I figure out your point . Specially this one,
Quote:

Arju are you sure...may be you are running on spfile


For which you are point me? Did I do any wrong.?
Re: pfile and spfile [message #263194 is a reply to message #263193] Wed, 29 August 2007 06:09 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
See...if you are on spfile...it shows the file name...if you are on pfile it doesnt...since you mentioned you are on pfile...it prompted me to ask that.
Re: pfile and spfile [message #263195 is a reply to message #263138] Wed, 29 August 2007 06:16 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

By the way , you missed my point and took it in different manner.

I asked the questioner to see the result if you use startup pfile=... then observe the result.

Re: pfile and spfile [message #263196 is a reply to message #263138] Wed, 29 August 2007 06:26 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I really wonder about the questioner. Where he went and in his profile nothing is mentioned. From where he is and etc.
Re: pfile and spfile [message #263198 is a reply to message #263196] Wed, 29 August 2007 06:31 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Arju..Iam sure he will come back again...as soon as he faces another problem.
Re: pfile and spfile [message #263202 is a reply to message #263138] Wed, 29 August 2007 06:34 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
Hello all,

I'm sorry, I made a mistake and was running with an spfile! Embarassed
Quote:

SQL> select value from v$parameter where name='spfile';

VALUE
--------------------------------------------------------------------------------
?/dbs/spfile@.ora

SQL> CREATE PFILE FROM SPFILE;

File created.
SQL> SHUTDOWN IMMEDIATE
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup pfile='?/dbs/init.ora';
ORACLE instance started.

Total System Global Area 424641568 bytes
Fixed Size 742432 bytes
Variable Size 234881024 bytes
Database Buffers 167772160 bytes
Redo Buffers 21245952 bytes
Database mounted.
Database opened.

SQL> select value from v$parameter where name='spfile';

VALUE
--------------------------------------------------------------------------------

SQL>



So all of your queries where good!
Re: pfile and spfile [message #263203 is a reply to message #263202] Wed, 29 August 2007 06:35 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Thanks for getting back to us aline
Re: pfile and spfile [message #263300 is a reply to message #263203] Wed, 29 August 2007 11:08 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hello All,


Always confusion between spfile or pfile now it is fix in 11g.

whenever instance startup through spfile or pfile ...entry recorded in ALERT<SID>.LOG file.

so we can check which parameter file is used currently.


Regards
Taj

Re: pfile and spfile [message #263314 is a reply to message #263300] Wed, 29 August 2007 12:34 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Thumbs Up kewl information.
Previous Topic: controlfile error
Next Topic: Status change of a data file without achive log file
Goto Forum:
  


Current Time: Mon Dec 05 12:47:06 CST 2016

Total time taken to generate the page: 0.12442 seconds