Search Order for a Parameter file in Oracle

Back to: Oracle DBA Tutorials

Search Order for a Parameter file in Oracle

In this article, I am going to discuss Search Order for a Parameter file in Oracle with Examples. Please read our previous article where we discussed V$SYSTEM_PARAMETER vs V$SYSTEM_PARAMETER2 in Oracle with Examples.

Search Order for a Parameter file in Oracle

In this article, we will try to understand how an oracle instance searches for the parameter file while starting the instance. We have already learnt that there are two types of parameter files. One is an SP file and the other file is a P file. We cannot edit the SP file manually. We can edit the SP file using the alter command. We can edit the P file manually in the server but we need to restart the database after editing the P file.

When the oracle instance starts the instance searches for the SP file or P file. There is an order where the instance searches for the parameter file.

Search Order for Parameter File in Oracle:

When you just mentioned start-up in the database without any option. The instance search in the following steps

1. Instance will try to search for spfile (spfile<sid>.ora) like sfilejaya.ora. If this instance does not find this file, then it will go for step 2.
2. The instance will search for the spfile.ora. If the instance does not find this file as well then, the instance will go for step 3.
3. The instance will go and search for pfile. Intijaya.ora

Exercise:

Now let us practically test the search order of the parameter file.

Step1: Open your Linux machine and open the file manager and go to the below path.

Once you go to the path you can see spfile present here.

Step2: Let us try to rename the spfile. We are changing the spfile name from spfilejayadb.ora to abcspfilejayadb.ora.

Changing the spfile to abcspfilejayadb.ora

Now the spfile name is changed to spfilejayadb.ora.

Step3: Let us start the database and test.

Open the terminal and connect to the database as “sqlplus / as sysdba”. Once you are connected to the database start the database using the command “Startup”.

You can see the error above.

Error: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initjayadb.ora’

This error indicates the failure to find the parameter file. Now by looking at this error we can state that the oracle instance tried to search for spfile spfilejayadb.ora but could not find that file because we have changed the file. Afterwards, it started searching for file spfile.ora and it also failed. Then the instance started to search for initjayadb.ora and this also failed because our path does not have all three files mentioned.

Step4: Rename the spfile back to its original name.

Let us go back to the file manager and change the spfile back to its original name and try to start the instance.

So, the spfile name is back to its original name as spfilejayadb.ora Now let’s try to start the instance.

We can see the instance is started without any issues.

Step5: Create pfile from spfile.

Let us create pfile from spfile using the database. We can create the pfile from the spfile using a simple command “create pfile = initjayadb.ora from spfile;”. The format of pfile is inti<sid>.ora.

This command will create pfile from the spfile. This pfile can be edited manually. Let us check in the file manager whether the file is created or not.

We can see the pfile is created. Let’s open this initjayadb.ora pfile and see what are parameters present in the pfile.

We do not see any redlines in this pfile. We can modify this file. We can edit and add new parameters to this pfile.

Step6: DB Startup using Pfile.

Let us go ahead and rename the spfile. We are renaming the spfile so that when the instance starts the instance will start with pfile.

Now the spfile is renamed as abcspfilejayadb.ora. Let us go ahead and shut down and start the database again.

The database is started again. We don’t see any error. The background process that happened during shutdown and start-up is the instance searches for the spfile spfilejayadb.ora and the file is not found. The instance searches for the spfile.ora and this file is also not found. So, the instance searches for pfile initjayadb.ora. So, the instance is started without any error.

Note: So, we have learnt that even if the spfile is not present the instance will search for pfile and starts the database.

Question:

We have started the instance using spfile and pfile. How we can identify that with which file the instance has started?

There is a parameter that determines with which parameter the instance has started. In order to view that parameter connect to the database as “sqlplus / as sysdba”. Once you have connected to the database using the command “show parameter spfile”.

We can see the value of the parameter spfile displays nothing so this indicates that the instance has started using pfile. Now let us try to rename the spfile back to the original and start the instance again.

So, we have renamed the spfile from abcspfilejayadb.ora to spfilejayadb.ora. Now let us go ahead and start the instance again and let’s see what would be the value of the parameter spfile.

We can see the parameter for the spfile is mentioned and that is the location of the spfilejayadb.ora. So, we understood that the parameter spfile determines with which file the instance has started.

In the next article, I am going to discuss the Non-Default Name for Pfile in Oracle with Examples. Here, in this article, I try to explain Search Order for a Parameter file in Oracle with Examples and I hope you enjoy this Search Order for a Parameter file in Oracle article.