Impact on running system spfile deletion

[Former Member]

Hi ,

Due to some mistake, We got spfile deleted in our Oracle 11g and ECC6.07 system.

After spfile deletion also our system is running fine so got below question on my mind of Oracle Task execution -

1) After spfile deletion which was used during oracle startup how oracle is determining parameter values, Is it during startup oracle loads all parameters from spfile to memory and using memory contents to find value of parameter values or it is fetching parameter values from pfile.

2) I read oracle reads spfile<sid>.ora  -> spfile.ora --> pfille for reading sequence, Does it means for all activities including startup, parameters maintain and fetch oracle uses this sequence.

3) what if we delete pfile also, Will oracle be up and running and will create issue only during next startup.

Regards,

Shivam

[fidel_vales · Accepted solution]

Hi,

If you delete the spfile with Oracle up and running you will have no problems until you stop the instance and try to start it again (if no pfile exists)

BRBACKUP create a copy of the spfile as pfile, therefore, there should be a pfile copy (also in the backups if BRBACKUP is used)

If you still have not stopped the system, you can recreate the spfile with the command

CREATE SPFILE FROM MEMORY

Regarding your second point, Oracle reads the parameters on startup, and then keeps then in memory. they are not read again. The spfile might be written again if you modify a parameter with ALTER SYSTEM specifying it to be written.

If you have no spfile and no pfile, Oracle will not start, as mentioned by Yves.

For more information, the oracle documentation is the place to go:

Managing Initialization Parameters Using a Server Parameter File

Regards

[former_member185954 · Accepted solution]

Hello Fidel,

That command to create from spfile from memory sure is a lifesaver

Regards,

Siddhesh

[Brindavan_M]

Hi Shivam,

1) After spfile deletion which was used during oracle startup how oracle is determining parameter values, Is it during startup oracle loads all parameters from spfile to memory and using memory contents to find value of parameter values or it is fetching parameter values from pfile.

when the oracle database started first it will check the spfile  to start the DB, if spfile is not avaliable then it will check for pfile. spfile is system oriented file and pfile can editable. once the database started which need all the parameters then it will go for shared memory. If you delete the spfile at current situation datasbase will avalaible for user once you stop and start then it will start with pfile if spfile not avalaible.  you can create spfile using pfile.

    create spfile from pfile ='/oracle/<SID>/112_64/dbs/init<SID>.ora';

2) I read oracle reads spfile<sid>.ora  -> spfile.ora --> pfille for reading sequence, Does it means for all activities including startup, parameters maintain and fetch oracle uses this sequence.

This is answered in first question: Oracle first check for spfile and then then pfile if spfile not avaliable. both file have same information. It recommended oracle should run with spfile.

3) what if we delete pfile also, Will oracle be up and running and will create issue only during next startup.

If you delete pfile as well while database is running then oracle run with shared memory which is already take the required inforamtion from this file. If you stop and start again if no spfile and pfile avaliable then database never start.

I hope you got the answer.

Thanks,

BM

[JamesZ]

Hi Shivam，

spfile should be used next time starting up oracle.

The start process will first locate spfile and if there is no spfile, then it will try to bring database up with
pfile. If the pfile is not found either, then you won't bring database up.

You need to bring the spfile parameters in memory to a temp folder:

create spfile='/tmp/spfileZ07.ora' from memory;

Then copy to the spfile directory manually. If you do not give full file name to spfile in above command, the oracle won't allow you to do that .

best regards,
James

[former_member207186]

Hi,

You can also refer to SAP Note 601157 for more information about parameter files .

Regards,
Bíborka

[ACE-SAP]

Hello

Your Oracle system is surely starting using the default pfile.

You can check this running these commands on sqlplus

show parameter pfile

show parameter spfile

Brspace is creating a copy of the spfile in the default pfile directory.

Spfile could also be included in Rman backups (rman> CONFIGURE CONTROLFILE AUTOBACKUP ON;).

You could easily recreate spfile from pfile with this command

create spfile from pfile='/oracle/<SID>/112_64/dbs/init<SID>.ora';

Without spfile and pfile your Oracle instance should not be able to start.

Regards

Initialization Parameter files: PFILEs vs. SPFILEs | Oracle FAQ

Oracle searches for a suitable initialization parameter file in the following order:

• Try to use the spfile${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)
• Try to use the spfile.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)
• Try to use the init${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)