Jan 26, 2015 understand what options you have for oracle instances parameter file. Oracle database initializationserver parameter file init. The purpose of a database is to store and retrieve related information. Oracle will look for default files, but typically you will need to specify the full pathname.
The following command determine whether the system uses. In order learn about pfile and spfile, you need to learn what is database. Jul 25, 2011 a spfile doesnot need a local copy of the pfile to start oracle from a remote machine. To demonstrate i start a database using pfile like sql shutdown immediate database closed. How to check whether oracle instance is started using pfile. Mar 02, 2011 recreating spfile on asm storage from pfile filed under.
You can check where the spfile is in the asm diskgroup by using the. I mean when u like to update few oracle parameters dynamically on run then u can update records to spfile, but when u want those changes to be applicable in next startup of db then u. Tom i am going through your book effective oracle by design. Parameters in pfile can be edited using any text editor. Stack overflow for teams is a private, secure spot for you and your coworkers to find and share information. How to determine whether oracle uses spfile or pfile while starting the database. The default configuration for the dispatcher is equivalent to the following dispatchers parameter setting in the database initialization parameter file. May 16, 2007 in sqlplus, issue show parameter spfile. The oracle spfile is a binary representation of the textbased init. Many people leave it set thinking oracle is saying it is a good idea or best practice to have it set. Jun 22, 2008 there are couple of ways of finding if the database was started with spfile or pfile. Database is a collection of data treated as a unit.
There are a couple of ways of finding if the database was started with spfile or pfile. Converting spfile to pfile and viceversa this could be done in order to have a backup in the other format or to change the initialization file for the. How to find database is using spfile or pfile in oracle 10g. Editing an spfile directly will corrupt the file and the start up of the database or instance will not be possible. Use the create pfile statement to export either a binary server parameter file or the current inmemory parameter settings into a text initialization parameter file. The best way to clean these up is to turn off the dispatchers parameter if you arent using xml db and xdb related protocols. Sometimes when you strewed up with parameters, you need to use the pfile as stepstone to undo the changes in spfile. A spfile doesnot need a local copy of the pfile to start oracle from a remote machine. In the beginning i thought rac was using a spfile but it turns out it was using a pfile which contains a single line pointing to a spfile located in the raw device and that is why it was saying ora27037. How to tell if the database was started with spfile or pfile. Server parameter files let you make persistent changes to individual parameters. A database server is the key to resolving the problem of information management. If you use this parameter, it is best to assign a file corresponding to every.
If you had both a pfile and an spfile in the default location, which one would be used. Creating a text parameter file is a convenient way to get a listing of the current parameter settings being used by the database, and it lets you edit the file easily in a text. How to recreate the spfile for rac instances where the spfile is. Asm disk wont be onlinemounted if asm instance isnt started. To create an spfile you must have a preexisting init.
Oracle pfile and spfile for parameters life after coffee. Managing initialization parameters using a server parameter file. If you are using an spfile and not a pfile, you will still need to have a file named init. Instead it can only be altered using the alter system command. How to determine if the database is running with spfile or pfile. For each modification we need to restart the profile for change replications. The answer is that the spfile would be the one used first at least for oracle versions 10g onwards that is. The spfile has a parameter set which is not allowing the database instance to start. If the value was nonnull, the indicated spfile was used. Enterprise manager displays a table listing the value of each initialization parameter specified in the server parameter file. Pfile and spfile initialization files in oracle oracle. Inside the pfile are a number of database settings called parameters.
False, enables dispatcher support for the oracle direct nfs client. Creating a text parameter file is a convenient way to get a listing of the current parameter settings being used by the database, and it lets you edit the file easily in a text editor and then convert it back into a. The syntax follows again but the simple version is just the following. Aug 29, 2010 today we are going to discuss about pfile and spfile. One can also specify a nondefault location for either or both the pfile and spfile parameters. You uniquely identify these instance components using the sid designator in the. How to check whether oracle instance is started using pfile or spfile. But we cant be sure about it, because the former dba may specify the pfile location other than the default one to startup the database. How to check whether the oracle database is using pfile or. But note the following when replacing a spfile that resides in asm. By default, a new oracle9i database will be working on a pfile, so the spfile must be created from the pfile. You can execute this statement either before or after instance startup. Learn more how to check whether oracle instance is started using pfile or spfile.
How does it happen if your spfile sits on asm storage. This morning, i was wondering how asm instance could be started if the spfile is stored inside asm disk. Also, oracle has a preference when it comes to choose spfile over pfile spfilesid. Then recreate the server parameter file using the create spfile statement. There are couple of ways of finding if the database was started with spfile or pfile. Pfile is static parameter file and we can edit by using text editor. In oracle databases through 8i, parameters controling memory, processor usage, control file locations and other key parameters are kept in a pfile short for parameter file. The database will first look for a file called spfilesid. Execute the following commands from a user with sysdba or sysoper privileges. Recreating spfile on asm storage from pfile dba sensation.
By default, the preconfigured database uses a parameter file spfile named spfilesid. So the file contains a line something like spfilef. Spfile is a binary file and modications to that can only be done through alter system set command. If a parameter is changed using the alter system set command oracle will apply this parameter change to the current spfile.
I want to create pfile from spfile oracle\product\11. This parameter is also used to determine the number of readsperfile in the redo. How to recreate the spfile for rac instances where the. With regard to older oracle version, this parameter file is known as pfile and the dba use to more often call it as init. Parameter management in real application clusters environments. Sep 20, 2019 ora600 kspsetpao2 when creating spfile from pfile doc id 434228. One can easily migrate from a pfile to spfile or vice versa. Another thing that is not usual and everytime i do, someone be surprised. Jun 25, 2019 a binary server parameter file spfile can now be created from the textbased init. Sql select decode value, null, pfile, spfile init file from sys. A pfile is a static, clientside text file that must be updated with a standard text editor like notepad or vi. Oracle startup initialization parameters morgans library. Server parameter files are binary files that exist only on the server and are called from client locations to start up the database.
It says that one of the reasons to use spfile is if i use oemoms to startstop database, the pfile needs to be on the machine where oms is installed or pfile should be accessible to the machine running oms. The first thing to check is if you have a pfile or spfile. As we said, the parameters are stored in either a pfile or an spfile. Whenever we start oracle instance, it internally refers to a parameter file which is either a pfile or a spfile. In rac with asm the spfile is placed in the asm shared location maintained by asm so all instances can access it. Today we are going to discuss about pfile and spfile. A text parameter pfile can be easily edited in a text editor. Use the create spfile statement to create a server parameter file from a clientside initialization parameter file. The spfile also contain db initialization parameters but it is binary file and no need to restart the profile if any changes done. Sometimes dba needs to find out database is running by spfile or pfile. Ora type parameter file, there is the converse command to do this. Cannot create pfile from spfile solutions experts exchange. How to determine if the database is running with spfile or.
When the db is created the spfile inside asm is as below. Spfile should only be modified through alter system set command. As spfile is a server side binary file, local copy of the pfile is not required to start oracle from a remote machine. Here you will get answers about question related to pfile and spfile which one is recommended and why location.
Create spfile on asm from pfile on filesystem grepora. If the value returned is null, a pfile was used to start the database. I just installed oracle database 12cr2 and am trying to startup a database. The parameter settings used to start the instance can be located from the alert. Query to findout, if database is running with pfile or spfile goto sql prompt and execute the following command to figure out if database is started with a pfile or spfile. Since the database uses this file during startup all parameter changes persist between shutdowns. To see whether a pfile opf spfile was used at startup time, the first place to check is the alert log, especially of the startup command contains the pfile or spfile clause. These entries can be used to created a valid spfile. I mean when u like to update few oracle parameters dynamically on run then u can update records to spfile, but when u want those changes to be applicable in next startup of db then u can update the pfile. The spfile has a parameter set which is not allowing the database instance to start using the spfile. Also, you can run this query to see if the database was started via an spfile.
A binary server parameter file spfile can now be created from the textbased init. Understand what options you have for oracle instances parameter file. Creating a server parameter file spfile oracle dba on linux. As you can tell from the title i have little use of the dispatchers parameter. A spfile, server parameter file, is a server managed binary file that oracle uses to hold persistent initialization parameters. Ora600 kspsetpao2 when creating spfile from pfile doc id 434228. This demonstration shows how to identify deprecated parameters specified in the spfile. Sep 29, 2005 oracle pfile and spfile for parameters in oracle databases through 8i, parameters controling memory, processor usage, control file locations and other key parameters are kept in a pfile short for parameter file. If you have a valid pfile then you will not need to perform step 1 of the following process. The instance and dispatchers register certain information with the listener. This is a typical production init file used to create an spfile. So the first thing i did was to confirm that the spfile is in the asm disk. One way would be tell show the check the value of parameter spfile, if it returns blank then database was started by pfile.
992 1054 493 191 394 1156 1583 242 1091 1097 571 196 1178 1453 26 609 914 667 178 1625 1575 928 572 1185 1475 139 298 376 1218 1471 1181 1118 922 148 1317 944 625 268 904 128 573 152 1076