Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

📚 preface

The Oracle pfile file is used as a parameter file.

When the Oracle database is started, the parameter file is required in the nomount state.

Spfile is also a kind of Parameter file, full name: Server Parameter Files!

☀ ️ spfile is introduced

The spfilesid. ora file is a binary spfile file.

Starting with Oracle 9I, Oracle introduced the spfile file file. With spfile, users can ALTER parameters by using ALTER SYSTEM or ALTER SESSION instead of manually changing parameters.

Using the spfile parameter file has the following benefits:

  • Changes to dynamic parameters take effect immediately, and the user can choose to apply the changes only to the current instance, to the SPfile, or to both.
  • Can completely say no to manually modify the initialization parameter file, also greatly reduce the occurrence of human error;
  • Spfile is a binary file that can be backed up using RMAN to increase database security and facilitate recovery.

Therefore, spfile is basically used to start the database by default. However, if the database cannot be started due to spfile modification parameters, only pfile modification parameters can be used to start the database.

How to check whether the current database is started with spfile?

After connecting to the database, execute the following command:

show parameter spfile
Copy the code

If the field selected in the following box is not empty, it indicates that the current database is started using SPfile; otherwise, the database is started using pfile.

⭐️ spfile Specifies the location of the parameter file

In a standalone database, the spfile parameter file is usually stored in the following directories:

  • Windows： $ORACLE_HOME/database
  • Linux: $ORACLE_HOME/DBS

In an RAC cluster, spfiles are usually stored on ASM disks. The preceding directories only store pfiles.

  • The pfile file format is as follows:initSID.ora
  • The spfile file format is as follows:spfileSID.ora

🌛 Startup priority

If there are both pfile and spfile files, what is the priority of database startup?

Official:spfileSID.ora > initorcl.ora

⭐ ️ development:

Ora spfilesid. ora > spfiles. ora > initorcl.ora

So, I did a little test:

1. Close the database first:

sqlplus / as sysdba
shutdown immediate
exit
Copy the code

SQL > create spfilesid. ora file from spfilesid. ora

cd $ORACLE_HOME/dbs
cp spfileorcl.ora spfile.ora
Copy the code

3. Restart the database

sqlplus / as sysdba
startup
show parameter spfile
Copy the code

Spfilesid.ora = spfilesid.ora = spfilesid.ora

4. Delete the spfilesid. ora file and restart the database

cd $ORACLE_HOME/dbs
rm -rf spfileorcl.ora
sqlplus / as sysdba
shutdown immediate
startup
show parameter spfile
Copy the code

ORACLE_SID = spfile.ora = spfile.ora = spfile.ora = spfile.ora = spfile.ora = spfile.ora = spfile.ora = spfile.ora = spfile.ora = spfile.ora = spfile.ora = spfile.ora = spfile.ora = spfile.ora = spfile.ora = spfile.ora = spfile.ora

5. Close the current database, set a new ORACLE_SID, and start the database

export ORACLE_SID=lucifer
sqlplus / as sysdba
startup
Copy the code

According to the above experiment, the instance name is Lucifer, and the database can also be successfully started 😂.

Ora file is used to start the database. If ORACLE_SID is set incorrectly, the spfilesid. ora file is used to start the database.

6. Delete all spfile files and restart the database

source ~/.bash_profile
rm -rf spfile*
sqlplus / as sysdba
shutdown immediate
startup
Copy the code

If no spfile file is available, only the initsid. ora file can be selected to start the database.

Therefore, the order of priority:spfileSID.ora > spfile.ora > initorcl.oraThere is no problem.

❄️ Example Explanation

① Switch from pfile to spfile to start the database

In some special cases, it is necessary to switch the parameter file. How do I switch between pfile and spfile?

1. Start the database with pfile:

sqlplus / as sysdba
startup pfile=$ORACLE_HOME/dbs/initorcl.ora
Copy the code

To test that spfile is generated, I delete it in advance:

Pfile is used to start the database environment.

sqlplus / as sysdba
create spfile from pfile;
Copy the code

You can run the preceding command to generate spfile based on pfile and save it in the default parameter file directory.

4. Restart the database. By default, spfile will be used to start the database.

sqlplus / as sysdba
shutdown immediate
startup
Copy the code

At this point, the database has been started using spfile.

② The database cannot be started due to incorrect parameter modification after spfile is used to start the database

When is the pfile parameter file required?

Problem reproduction

For example, if you set a database parameter in the database using ALTER SYSTEM, and then close the database, then reopen the database, the error cannot open the database.

In this case, the spfile is a binary file and cannot be directly opened for modification. Therefore, you need to manually generate the pfile file to start the modification.

Solving steps

1. Manually generate the pfile file

sqlplus / as sysdba
create pfile from spfile;
Copy the code

Note: The above command can be executed when the database is not started. The pfile generation path can also be specified: pfile= generation path /pfile file name.

2. Edit the pfile file and modify the parameters incorrectly set

cd $ORACLE_HOME/dbs
vi initorcl.ora
Copy the code

Once opened, change the processes parameter to the correct value ✅.

3. Start the database with the modified pfile parameter file

sqlplus / as sysdba
startup pfile=$ORACLE_HOME/dbs/initorcl.ora
Copy the code

At this point, the database is successfully opened.

4. After the database is successfully started, switch back to SPfile to restart the database. The current database is started using pfile.

sqlplus / as sysdba
create spfile from pfile;
Copy the code

After the modification is complete, restart the database to take effect:

sqlplus / as sysdba
shutdown immediate
startup
Copy the code

As shown above, the database has been successfully restored to spfile startup and the current database parameters are correct.

5. Be careful

In the RAC cluster, the SPfile file in the RAC cluster is saved on the ASM disk, so it can be used together with the pfile file.

For example, the RAC cluster example below:

Spfile file on ASM disk
ASMCMD> pwd
+data/orcl
ASMCMD> ls spfile*
spfileorcl.ora

Pfile under ORACLE_HOME/ DBS
[oracle@orcl1 dbs]$ cat initorcl1.ora 
SPFILE='+DATA/orcl/spfileorcl.ora'
Copy the code

As you can see from the example above, RAC actually uses pfile to start, but writes the spfile path to the pfile file.

❤ ️ note:

Therefore, when generating spfile, you need to manually specify the path of spfile as the path in the ASM disk!

** The file spfile[ORACLESID]. Ora ∗ cannot exist in ORACLEHOME/ DBS; otherwise, the file spfile[ORACLE_SID]. Ora file is preferred in ORACLE_HOME/ DBS during startup. Otherwise, spfile[ORACLESID]. Ora ∗ cannot exist in the ORACLEHOME/ DBS directory during startup. Otherwise, spfile under ORACLE_HOME/ DBS is selected during startup.

This is the end of sharing ~

If you think the article is helpful to you, please like it, favorites it, pay attention to it, comment on it, and support it four times with one button. Your support is the biggest motivation for my creation.

❤️ technical exchange can follow the public number: Lucifer think twice before you do ❤️