OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » pfile and spfile

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

pfile and spfile [message #263138]

Wed, 29 August 2007 03:26

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.

Report message to a moderator

Re: pfile and spfile [message #263140 is a reply to message #263138]

Wed, 29 August 2007 03:35

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

Senior Member

show parameter spfile/pfile

Report message to a moderator

Re: pfile and spfile [message #263143 is a reply to message #263138]

Wed, 29 August 2007 03:51

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

Report message to a moderator

Re: pfile and spfile [message #263145 is a reply to message #263140]

Wed, 29 August 2007 03:51

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

Report message to a moderator

Re: pfile and spfile [message #263149 is a reply to message #263145]

Wed, 29 August 2007 04:02

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

Report message to a moderator

Re: pfile and spfile [message #263150 is a reply to message #263138]

Wed, 29 August 2007 04:04

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 ..

Report message to a moderator

Re: pfile and spfile [message #263154 is a reply to message #263138]

Wed, 29 August 2007 04:13

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

Report message to a moderator

Re: pfile and spfile [message #263161 is a reply to message #263149]

Wed, 29 August 2007 04:29

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You may have started with a pfile (init.ora) file that contains "spfile=?/dbs/spfile@.ora".

Regards
Michel

Report message to a moderator

Re: pfile and spfile [message #263162 is a reply to message #263154]

Wed, 29 August 2007 04:29

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!

Report message to a moderator

Re: pfile and spfile [message #263164 is a reply to message #263161]

Wed, 29 August 2007 04:32

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.

Report message to a moderator

Re: pfile and spfile [message #263166 is a reply to message #263154]

Wed, 29 August 2007 04:33

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.

Report message to a moderator

Re: pfile and spfile [message #263171 is a reply to message #263164]

Wed, 29 August 2007 04:41

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

pablolee
Messages: 2882
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

)

Report message to a moderator

Re: pfile and spfile [message #263188 is a reply to message #263138]

Wed, 29 August 2007 05:50

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?

Report message to a moderator

Re: pfile and spfile [message #263191 is a reply to message #263188]

Wed, 29 August 2007 05:55

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.

Report message to a moderator

Re: pfile and spfile [message #263192 is a reply to message #263138]

Wed, 29 August 2007 06:00

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

Senior Member

Thanks for the feedback.

Report message to a moderator

Re: pfile and spfile [message #263193 is a reply to message #263138]

Wed, 29 August 2007 06:02

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.?

Report message to a moderator

Re: pfile and spfile [message #263194 is a reply to message #263193]

Wed, 29 August 2007 06:09

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.

Report message to a moderator

Re: pfile and spfile [message #263195 is a reply to message #263138]

Wed, 29 August 2007 06:16

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.

Report message to a moderator

Re: pfile and spfile [message #263196 is a reply to message #263138]

Wed, 29 August 2007 06:26

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.

Report message to a moderator

Re: pfile and spfile [message #263198 is a reply to message #263196]

Wed, 29 August 2007 06:31

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.

Report message to a moderator

Re: pfile and spfile [message #263202 is a reply to message #263138]

Wed, 29 August 2007 06:34

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!

Report message to a moderator

Re: pfile and spfile [message #263203 is a reply to message #263202]

Wed, 29 August 2007 06:35

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Thanks for getting back to us aline

Report message to a moderator

Re: pfile and spfile [message #263300 is a reply to message #263203]

Wed, 29 August 2007 11:08

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

Report message to a moderator

Re: pfile and spfile [message #263314 is a reply to message #263300]

Wed, 29 August 2007 12:34

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

kewl information.

Report message to a moderator

Previous Topic:	controlfile error
Next Topic:	Status change of a data file without achive log file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 04 07:01:36 CST 2025