Parameter File in Oracle DBA with Examples
In this article, I am going to discuss Parameter File in Oracle DBA with Examples. Please read our previous article where we discussed Initialization Parameters and Parameter Files in Oracle.
Search for Parameter File in Oracle:
We have already learned that there are two types of parameter files. One is an SP file and the other file is the 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
- 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.
- The instance will search for the spfile.ora. If the instance doesn’t find this file as well then, the instance will go for step 3.
- The instance will go and search for pfile. Intijaya.ora
Note: We can create pfile from the sp file but we should name it as init<sid>.ora in order for the instance can use the pfile if the spfile is not available. This indicates that we can create pfile from the spfile but in order to create pfile we have to mention init<sid>.ora. We can mention another name as well but if we mention another name, we need to mention this file name during the start-up of the database.
Example: startup pfile=pfilename;
We can start the instance directly with the P file but we need to mention the p file name during the start-up of the instance. We will understand this in further practice sessions. These files will be more helpful while starting or stopping the database. We will learn more about these files and the parameters present in the pfile or spfile in further articles.
Uses of Initialization Parameter files in Oracle:
Until now we have learned about the pfile and spfile and what are their roles in the database. Let us have quick look at the uses of these files in the database.
- Initialization parameters optimize the performance by adjusting the memory structures.
- Set the database-wide default, such as the amount of space initially allocated for a context area when it is created (Context area: an area for processing the SQL statements). These parameters will have some values and these parameters will be adjusted automatically in order to provide good performance for the database.
- Set database limits such as the maximum number of database users.
- Specify names of files or directories required by the database. When the oracle instance starts, DBA needs to find out the location of the control file which contains metadata about the database, and redo log files.
Note: All initialization parameters are optional. Oracle has a default value for each parameter. So, the database admin will change these parameters if they need something. We have more than 400 parameters available in the database. The DBA needs only a few parameters to understand it and change the parameters. Most of the other parameters we never use in our daily activities.
In the next article, I am going to discuss Types of Initialization Parameters in Oracle with Examples. Here, in this article, I try to explain Parameter File in Oracle DBA with Examples and I hope you enjoy the Parameter File in Oracle article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.