A spfile doesnt need a local copy of the pfile to start oracle from a remote machine. Use the create spfile statement to create a server parameter file from a. Some oracle functionality does not work as well if you are not using the spfile. Create spfile for information on server parameter files. In oracle9i, a new feature called spfile server parameter file was introduced. Understand what options you have for oracle instances parameter file. 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. If you end up wanting to use an spfile, you create it from the pfile. If you are using asm the spfile then the spfile will be located in shared asm file system. 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. What is difference between pfile and spfile answers. For whatever reason, despite all the other similarities, linuxunix and windows store it in different locations. You can always go back and forth with your spfile and pfile in this manner.
By default, if you do not specify pfile in your startup command, oracle will use server parameter file spfile. To demonstrate i start a database using pfile like sql shutdown immediate database closed. Some parameters appear in both the generic database parameter table. Parameters in pfile can be edited using any text editor. The oracle spfile is a binary representation of the text0based init. I also need to know the difference between these two files since i am new to oracle 10g. Use the create spfile statement to create a server parameter file from a clientside initialization parameter file. Spfile permits dynamic changes without requiring you to restart that instance. Architecture oracle install datapump rman flashback dataguard asm oracle. Managing initialization parameters using a server parameter file. Pfile is a static, clientside text file that must be updated with a standard text editor like notepad or vi.
The oracle spfile is a binary representation of the textbased init. Get help with specific problems with your technologies, process and projects. Pfile vs spfile until oracle 8i dbas have been using a text file called the pfile parameter file to store the database initialization parameters. A binary server parameter file spfile can now be created from the textbased init. When i manually went to spfile and file locations, i didnt find anyone of them. Local copy of pfile required to start database from a remote machine. If database is down and you are trying to create a pfile from spfile. Spfile is default file for database start up, that is the reason i did not give any parameter name at start up time.
Until oracle 8i, we were using a text file called the pfile parameter file for setting the database initialization parameters. A spfile doesnot need a local copy of the pfile to start oracle from a remote machine. Inside the pfile are a number of database settings called parameters. This file normally reside on the server, however, you need a local copy if you want to start oracle from a remote machine. 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. Spfile is a binary file and modications to that can only be done through alter system set command. A pfile is a static, clientside text file that must be changed with a standard text editor like notepad windows or. Tom i am going through your book effective oracle by design. Converting the spfile to a pfile, editing the pfile and reconverting the corrected pfile to the spfile made oracle happy and me too.
How to find database is using spfile or pfile in oracle 10g. What is the difference between spfile and pfile in oracle. Oracle searches for a suitable initialization parameter file in the following order. The oracle spfile is a binary representation of the text based init. As mark mentioned, spfile should be used instead of a init. Use the create pfile statement to export either a binary server parameter file or the. Since the spfile was introduced in oracle 9i, i do not use the old pfile any more. The first found file is used for the instance initialization. During instance startup, all parameter settings are logged to. I will answer the second part of your question first the difference between spfile and pfile. 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. This parameter is created when opening the oracle instance and then we can change the. By default, a new oracle9i database will be working on a pfile, so the. Database spfile and oracle net configuration file samples.
Ora file, but starting from oracle 9i and up, oracle corp. These parameters help the oracle programs know how to start. It is stored on the server, rather than on a client machine. The instance finds the new spfile in the default location and starts up with it. Use the create pfile statement to export a binary server parameter file into a text initialization parameter file. Spfile and pfile not created during installtion of oracle 11g. How to modify spfile in oracle database the geek diary. Create spfile and pfile in oracle smart way of technology.
As spfile is a server side binary file, local copy of the pfile is not required to start oracle from a remote machine. 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. 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. The selftuning sga works best when using a spfile as oracle can store some information abou the sga components in the spfile. Creating a server parameter file spfile oracle dba on linux. I have list my spfile and pfile files and i need to recreate my spfile from scratch. This chapter describes server parameter file spfile placement and configuration in. Startup command will first check the spfile location for initialization parameter if it not found then it go to pfile. So, oracle first look for a spfile and after that after a pfile. Create spfile and pfile in oracle spfile is called as 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. Hope you like the content for create spfile from pfile in oracle database. 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. Server parameter files are binary files that exist only on the server and are called. In oracle database 11g and onwards, the from memory clause creates a pfile or spfile using the current systemwide parameter settings. I want to create pfile from spfile oracle\product\11. Spfile is a binary file that contains the same information as the old pfile. Difference between pfile and spfile in oracle blogger. Server parameter files let you make persistent changes to individual parameters. Pfile and spfile initialization files in oracle oracle database maintenance 10g, 9i, 11g. Server parameter files are binary files that exist only on the server and are called from client locations to start up the database. Sometimes dba needs to find out database is running by spfile or pfile. Use the create pfile statement to export a binary server parameter file into a.
Sql show parameter pfile if spfile is used in the database,then output will be spfile location,else pfile location. This clause lets you specify a name for the server parameter file you are creating. The instance cannot be started without a spfile or pfile file because the controlfiles. 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. Beginning with oracle9i, oracle introduced the concept of the server parameter file. Parameter file is a text or binary to store the database initialization parameters. If your pfile is in different directory you can specify the file name using following format. In a rac environment, the created file contains the parameter settings from each instance. The value of this parameter is the name of the current server parameter file spfile in use. Difference between pfileand spfile in oracle 10g11g. A spfile, server parameter file, is a server managed binary. If the instance is using an spfile, show parameter spfile will show the location of the spfile.
An spfile cannot be edited directly, but one can be configured via the pfile init. Home database oracle database online documentation, 10g release 2 10. How to easily find the oracle database startup and shutdown time using sqlplus. I need to know where i should set these parameters spfile or pfile. Where is the parameter file p file stored in oracle. The tables in this section represent the database, rac, and data guard parameter file values. Once the pfile is produced you can edit it and use it to create a new or modified spfile. Oracle database how to create pfile or spfile using the. 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 it stored with the data files or stored separately.
Is there anyway to find out how the 10g database is started. I need to change some oracle parameters recommended by sap early watch alert. How to find database is using spfile or pfile in oracle 10g sometimes dba needs to find out database is running by spfile or pfile. Oracle provides two different types of parameter files that you can use, pfile and spfile. This file, known as spfile, differs from the standard init. I changed a parameter in such a bad way, that oracle did not start anymore. As we said, the parameters are stored in either a pfile or an spfile. This will behave the same way on other platforms as well. You can use the command to recreate the spfile when you have lost your spfile and pfile. In the situation where both types of parameter files exist, the databse instance will be started with the spfile. Spfile is a server parameter file in a binary format whereas pfile is text based parameter file. With a new database, you always start with a pfile. The actual file names can also be specified in the create command.
Spfile the spfile is the preferred type of parameter file. 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. When the default server parameter file is used by the server, the value of spfile is internally set by the server. The pfile is read at instance startup time to get specific instance characteristics. 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. The oracle instance reads the parameter file during startup which are then used.
612 322 760 110 1299 863 881 833 776 524 300 547 1280 1204 140 1238 1374 240 743 208 576 1358 511 1174 1444 643 966 648 1092 183 758 1090 1367 21 1139 276 554 782 1101 152 818 738