The pfile is read at instance startup time to get specific instance characteristics. Startup command will first check the spfile location for initialization parameter if it not found then it go to pfile. 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 server parameter file using the create. Is it stored with the data files or stored separately. Get help with specific problems with your technologies, process and projects. Architecture oracle install datapump rman flashback dataguard asm oracle.
Ora file spfile server parameter file, on the other hand, is a persistent serverside binary file. Create spfile and pfile in oracle smart way of technology. The tables in this section represent the database, rac, and data guard parameter file values. Database spfile and oracle net configuration file samples. With a new database, you always start with a pfile. Creating a server parameter file spfile oracle dba on linux. How to easily find the oracle database startup and shutdown time using sqlplus. By default, a new oracle9i database will be working on a pfile, so the.
Oracle provides two different types of parameter files that you can use, pfile and spfile. Difference between pfile and spfile in oracle blogger. If the instance is using an spfile, show parameter spfile will show the location of the spfile. When the default server parameter file is used by the server, the value of spfile is internally set by the server. During instance startup, all parameter settings are logged to. Inside the pfile are a number of database settings called parameters.
It has defined the initialization parameters which need to run the oracle database. Oracle database how to create pfile or spfile using the. If your pfile is in different directory you can specify the file name using following format. You can use the command to recreate the spfile when you have lost your spfile and pfile. As we said, the parameters are stored in either a pfile or an spfile. I changed a parameter in such a bad way, that oracle did not start anymore.
Oracle database 10g real application clusters documentation for more. Tom i am going through your book effective oracle by design. Sql show parameter pfile if spfile is used in the database,then output will be spfile location,else pfile location. A spfile, server parameter file, is a server managed binary. To demonstrate i start a database using pfile like sql shutdown immediate database closed. Use the create pfile statement to export a binary server parameter file into a text initialization parameter file. The oracle instance reads the parameter file during startup which are then used.
Spfile is a server parameter file in a binary format whereas pfile is text based parameter file. This will behave the same way on other platforms as well. Parameter file is a text or binary to store the database initialization parameters. Difference between pfileand spfile in oracle 10g11g. Converting the spfile to a pfile, editing the pfile and reconverting the corrected pfile to the spfile made oracle happy and me too. Pfile vs spfile until oracle 8i dbas have been using a text file called the pfile parameter file to store the database initialization parameters. Server parameter files let you make persistent changes to individual parameters. In oracle database 11g and onwards, the from memory clause creates a pfile or spfile using the current systemwide parameter settings. Oracle searches for a suitable initialization parameter file in the following order. In order to use same spfile at startup each rac instance uses its own pfile and that points to one shared spfile on shared storage. Is there anyway to find out how the 10g database is started. An spfile cannot be edited directly, but one can be configured via the pfile init.
I need to know where i should set these parameters spfile or pfile. Spfile is default file for database start up, that is the reason i did not give any parameter name at start up time. I want to create pfile from spfile oracle\product\11. The oracle spfile is a binary representation of the text based init. A spfile doesnot need a local copy of the pfile to start oracle from a remote machine. Some parameters appear in both the generic database parameter table. Hope you like the content for create spfile from pfile in oracle database. Use the create spfile statement to create a server parameter file from a. If you are using asm the spfile then the spfile will be located in shared asm file system. Create spfile for information on server parameter files. It is stored on the server, rather than on a client machine. In oracle9i, a new feature called spfile server parameter file was introduced.
In the situation where both types of parameter files exist, the databse instance will be started with the spfile. Instead it can only be altered using the alter system command. Ora file, but starting from oracle 9i and up, oracle corp. I need to change some oracle parameters recommended by sap early watch alert. To make them permanent, you must update them manually in the initialization parameter file, or they will be lost over the next shutdown and startup of the database. Some oracle functionality does not work as well if you are not using the spfile. 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. You can always go back and forth with your spfile and pfile in this manner. Once the pfile is produced you can edit it and use it to create a new or modified spfile. Create spfile and pfile in oracle spfile is called as server parameter file. If the instance is using a pfile, the spfile parameter will be null, and theres no way to tell what pfile was used to start the instance.
When you create a database using database configuration assistant, a server parameter file spfile is created from the initialization parameter file, and. Spfile permits dynamic changes without requiring you to restart that instance. Spfile and pfile not created during installtion of oracle 11g. This file normally reside on the server, however, you need a local copy if you want to start oracle from a remote machine.
Its good to have a text file backup of your spfile, since you cant directly edit the spfile you can only change it when the database is mounted. If you want to change the spfile location, create a pfile in the default location and in pfile specify the spfile parameter location to nondefault location. How to find database is using spfile or pfile in oracle 10g. A binary server parameter file spfile can now be created from the textbased init. Beginning with oracle9i, oracle introduced the concept of the server parameter file. Pfile is a text file, which can be modified and spfile is a binary file which cannot be modified using a text editor. Pfile is a static, clientside text file that must be updated with a standard text editor like notepad or vi. When i manually went to spfile and file locations, i didnt find anyone of them. The oracle spfile is a binary representation of the text0based init.
This parameter can be defined in a client side pfile to indicate the. If you end up wanting to use an spfile, you create it from the pfile. Until oracle 8i, we were using a text file called the pfile parameter file for setting the database initialization parameters. By default, a new oracle database will be working on a pfile, so the spfile must be created from the pfile at the sql prompt. What is the difference between spfile and pfile in oracle. As spfile is a server side binary file, local copy of the pfile is not required to start oracle from a remote machine. Managing initialization parameters using a server parameter file. As mark mentioned, spfile should be used instead of a init. Sometimes dba needs to find out database is running by spfile or pfile. How to configure an instance with pfile and spfiles in. Spfile is a binary file that contains the same information as the old pfile. Since the spfile was introduced in oracle 9i, i do not use the old pfile any more.
Server parameter files are binary files that exist only on the server and are called. Configuring the server parameter file in oracle real. The oracle spfile is a binary representation of the textbased init. Parameters in pfile can be edited using any text editor. Where is the parameter file p file stored in oracle. I have list my spfile and pfile files and i need to recreate my spfile from scratch. This file, known as spfile, differs from the standard init. Spfile the spfile is the preferred type of parameter file.
The selftuning sga works best when using a spfile as oracle can store some information abou the sga components in the spfile. The first found file is used for the instance initialization. If database is down and you are trying to create a pfile from spfile. Pfile and spfile initialization files in oracle oracle database maintenance 10g, 9i, 11g. So, oracle first look for a spfile and after that after a pfile. This parameter is created when opening the oracle instance and then we can change the. The instance cannot be started without a spfile or pfile file because the controlfiles. How to modify spfile in oracle database the geek diary. This chapter describes server parameter file spfile placement and configuration in. This clause lets you specify a name for the server parameter file you are creating. A pfile is a static, clientside text file that must be changed with a standard text editor like notepad windows or. By default, if you do not specify pfile in your startup command, oracle will use server parameter file spfile. In a rac environment, the created file contains the parameter settings from each instance.
The instance finds the new spfile in the default location and starts up with it. If you have spfile created from a pfile, the database will use spfile for automatic startup of the database unless you specifically mention pfile in the startup command manually. A spfile doesnt need a local copy of the pfile to start oracle from a remote machine. Home database oracle database online documentation, 10g release 2 10.
Server parameter files are binary files that exist only on the server and are called from client locations to start up the database. With regard to older oracle version, this parameter file is known as pfile and the dba use to more often call it as init. Local copy of pfile required to start database from a remote machine. By default, a new oracle9i database will be working on a pfile, so the spfile must be created from the pfile at the sql prompt. Use the create pfile statement to export either a binary server parameter file or the. Spfile is a binary file and modications to that can only be done through alter system set command. These parameters help the oracle programs know how to start. Use the create spfile statement to create a server parameter file from a clientside initialization parameter file. 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. I will answer the second part of your question first the difference between spfile and pfile.
1128 1390 813 1082 1623 76 1333 1649 952 1345 1192 783 63 841 1158 1430 1414 254 22 194 839 984 660 922 974 1081 790 789 937 720 738 8 260 728