Sanae BEKKAR – My Blog –

SPFILEs represent a fundamental change in the way Oracle accesses and maintains parameter settings for the instance.

An SPFILE eliminates the two serious issues associated with legacy parameter files: • It stops the proliferation of parameter files.

An SPFILE is always stored on the database server; the SPFILE must exist on the server machine itself and can’t be located on the client machine.

This makes it practical to have a single source of “truth” with regard to parameter settings.

• It removes the need (in fact, it removes the ability) to manually maintain parameter files outside of the database using a text editor.

The ALTER SYSTEM command lets you write values directly into the SPFILE.

Administrators no longer have to find and maintain all of the parameter files by hand.

The naming convention for this file by default is :
$ORACLE_HOME/dbs/spfile$ORACLE_SID.ora    (UNIX/Linux environment variable)

%ORACLE_HOME/database/spfile%ORACLE_SID%.ora   (Windows environment variable)
I strongly recommend using the default location; doing otherwise defeats the simplicity SPFILEs represent.

When an SPFILE is in its default location, everything is more or less done for you.

Moving the SPFILE to a nondefault location means you have to tell Oracle where to find the SPFILE, leading to the original problems of legacy parameter files all over again!
Converting to SPFILEs Suppose you have a database that is using a legacy parameter file. The move to an SPFILE is quite simple—you use the CREATE SPFILE command.
Note ■ You can also use a “reverse” command to create a parameter file (PFILE) from an SPFILE. i’ll explain shortly why you might want to do that.
So, assuming you have an init.ora parameter file and that init.ora parameter file is in the default location on the server, you simply issue the CREATE SPFILE command and restart your server instance:
EODA@ORA12CR1> show parameter spfile;
NAME                                 TYPE           VALUE

———————————— —–

spfile                                 string

EODA@ORA12CR1> create spfile from pfile;

create spfile from pfile

*

ERROR at line 1:

ORA-01031: insufficient privileges

Well, that SHOW PARAMETER command shows that we did not create an SPFILE: the value is blank.

We are not privileged enough to create the SPFILE, even though I am logged in as a DBA. Creating an SPFILE is considered to be very privileged, and you can do it only if you are connected using credentials that allow you to startup and shutdown the database.

So let’s do that:
EODA@ORA12CR1> connect / as sysoper;

Connected.
PUBLIC@ORA12CR1> create spfile from pfile;

File created.
PUBLIC@ORA12CR1> startup force; ORACLE instance started.

Database mounted.

Database opened.
I used the least privileged account I can to perform that operation, an account (mine) that uses the SYSOPER administrator privilege.

SYSOPER is allowed to manage the parameter files, start and stop the database, but not much else; that’s why the output of the startup command looks different—there is no SGA report, the memory settings are not visible, in fact:
PUBLIC@ORA12CR1> show parameter spfile;

ORA-00942: table or view does not exist
While the SYSOPER privilege can start and stop the database, it can’t access V$ views and so on.

It is very limited in what it can do.

We can verify that we are using the SPFILE by connecting as an account privileged enough to do so:
EODA@ORA12CR1> show parameter spfile;
NAME                                 TYPE        VALUE

———————————— ———– —————————–spfile                               string      /home/ora12cr1/app/ora12cr1/pr                                         oduct/12.1.0/dbhome_1/dbs/spfileora12cr1.ora
To recap, we used the SHOW PARAMETER command here to show that initially we were not using an SPFILE, but after we created one and restarted the instance, we were using one and it had the default name.
Note ■ in a clustered environment, using Oracle raC, all instances share the same SPFILE, so this process of  converting over to an SPFILE from a PFILE should be done in a controlled fashion. the single SPFILE can contain all of the parameter settings, even instance-specific settings, but you’ll have to merge all of the necessary parameter files into a single PFILE using the format that follows.
In a clustered environment, in order to convert from individual PFILEs to an SPFILE shared by all, you’d merge your individual PFILEs into a single file resembling this:

*.cluster_database_instances=2

*.cluster_database=TRUE

*.cluster_interconnects=’10.10.10.0′