pfile vs spfile

[jack999]

I want to confirm my understanding.

Once spfile is activated, you cannot go back to pfile.

[sysdba]

why not:

shutdown
startup pfile=your_sid.ora

will start up the database using pfile.

[bhallar]

shutdown
create spfile from pfile;
startup

[stecal]

Originally posted by bhallar
shutdown
create spfile from pfile;
startup

Even if you could perform the steps you provided on a database that is shut down, what does it have to do with what was asked?

[bhallar]

------------------------------------------------------------------
Even if you could perform the steps you provided on a database that is shut down...........
-------------------------------------------------------------------

YES you can create a spfile even the database is shutdown.

I was just trying to say pfile will be only effective after shutdown and restart the database either by creating again spfile with pfile contents or by startup pfile=init.ora as mentioned by sysdba.

[stecal]

You need to try doing what you recommend. When you get the ORA-01034 error about Oracle not being available, please enlighten the rest of us as to how your version of Oracle manages to process the create spfile statement. Thanks.

[bhallar]

I tried several times that why I had mentioned :

bash-2.05$ pwd
/oracle/export/home/OraHome1/dbs
bash-2.05$ ls -ltr
total 64
-rw-r--r-- 1 oracle dba 3410 Nov 8 19:24 initclient.ora
-rw-rw---- 1 oracle dba 24 Nov 8 19:03 lkCLIENT
-rwSr----- 1 oracle dba 1536 Sep 4 15:58 orapwclient
bash-2.05$ export ORACLE_SID=client
bash-2.05$
bash-2.05$ sqlplus '/as sysdba'

SQL*Plus: Release 9.2.0.3.0 - Production on Sat Nov 8 19:25:29 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> exit
Disconnected
bash-2.05$ ls -ltr
total 64
-rwSr----- 1 oracle dba 1536 Sep 4 15:58 orapwclient
-rw-rw---- 1 oracle dba 24 Nov 8 19:03 lkCLIENT
-rw-r--r-- 1 oracle dba 3410 Nov 8 19:24 initclient.ora
-rw-r----- 1 oracle dba 3584 Nov 8 19:25 spfileclient.ora
bash-2.05$
bash-2.05$ sqlplus '/as sysdba'

SQL*Plus: Release 9.2.0.3.0 - Production on Sat Nov 8 19:27:52 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 554767392 bytes
Fixed Size 451616 bytes
Variable Size 117440512 bytes
Database Buffers 436207616 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>

[stecal]

So when the instance is open/running, and you do a shutdown, then do the create spfile statement, you don't get the Oracle not available error? I get it on my UNIX box at work, but here at home using XP 9iR2 --

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile;

File created.

SQL>

[bhallar]

Thats wierd ,
as per Oracle you can create spfile either before or after instance startup.
Pls check Note:166601.1 at metalink.
------------------------------------
1.Creation of the Spfile from a Pfile First of all you have to create a server parameter file (spfile). The spfile must be created from a traditional text initialization parameter file (pfile). You must have the SYSDBA or the SYSOPER system privilege to create a spfile. The command can be executed either before or after instance startup.