Non-Default Name for Pfile in Oracle

Back to: Oracle DBA Tutorials

Non-Default Name for Pfile in Oracle

In this article, I am going to discuss the Non-Default Name for Pfile in Oracle with Examples. Please read our previous article where we discussed Search Order for a Parameter file in Oracle with Examples.

Non-Default Name for Pfile in Oracle

In this article, we will try to create another pfile with no default name for example newpfile.ora and we will try to start the instance using the new pfile. In this new pfile we can make any changes that we like and we can edit each and every parameter. We will try to learn about this in following steps.

Step1: Connect to the database as “sqlplus /as sysdba”

Step2: Creation of pfile using spfile.

In the previous topic we have already created pfile using spfile. Now we will do the same thing but we will specify a name for pfile.

Command: create pfile ‘newpfile.ora from spfile;

Step3: Existence of pfile.

Go to the below location and check for the newly created pfile.

Search for the pfile newpfile.ora.

So, the newpfile.ora is present in the path “/u01/app/oracle/product/19.0.0/dbhome_1/dbs/”.

Step4: Change the value of any parameter.

Let us try to change any parameter in the newpfile.ora and try to reflect the value in the database. Let us take the parameter max_idle_time. This parameter works as the maximum time the user can be logged in without doing anything in the database.

Command: show parameter max_idle_time.

We can see the value of max_idle_time is 0. The value 0 indicates there is no maximum time. Users can log in as long as possible doing nothing. Your session will not be terminated.

Step5: Change the value

Open the newpfile.ora and add this parameter as below.

We have added a new line as formatted “*.max_idle_time=5”. So, the idle time for any user is limited to 5 minutes. Let us save the file from here.

Step6: Restart the database using the newly edited pfile.

Shut down the database.

Now the database is down. We have to start the database.

Command: startup pfile=newpfile.ora

We are getting an error saying “could not open parameter file ‘newpfile.ora’. This type of error indicates that the instance cannot fetch the location of the pfile. This means that we have to mention the path of the pfile location. The location of the pfile is /uo1/app/oracle/19.0.0/dbhome_1/dbs/

So, we have to start the instance using the above location.

Command: startup pfile=/uo1/app/oracle/19.0.0/dbhome_1/dbs/newpfile.ora

Let us check the value of parameter spfile because we need to find out with which file the instance has started.

Command: show parameter spfile

So, we can see the instance is started using the pfile that we mentioned above i.e newpfile.ora. The oracle instance can only read a pfile Now the database is up and running. Let us check the value of the parameter max_idle_time.

Command: show parameter max_idle_time

So, any user who has logged in and did not perform any task in the database will be logged out after 5 minutes.

Step7: Start the instance using spfile.

Let us start the instance using the normal spfile.

We have just started the instance normally i.e we haven’t used any parameter file separately to start the instance. Let us check and verify the value of the parameter spfile.

Command: show parameter spfile

So, the value of parameter spfile has the location of spfile. Let us now check the max_idle_time parameter.

Command: show parameter max_idle_time

We can see the max_idle_time is 0. The value is 0 because we have started the instance using the spfile and the spfile does not have any changes to the parameter max_idle_time.

Note: We learned that if we make any changes in a parameter that would be limited to the parameter file. We made changes in the pfile and we restarted the instance using spfile, and we could see the changes that we made are not reflected in the database when we started the instance using spfile.

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