Parameter files in oracle dba spfile and pfile youtube. Moreover, if pfile is used, you have to know the location of pfile. Since the database uses this file during startup all parameter changes persist between shutdowns. Ask tom after repairing spfile create pfile from spfile. Aug 29, 2010 with regard to older oracle version, this parameter file is known as pfile and the dba use to more often call it as init. One exciting new feature of oracle is the removal of the init.
The following example shows how to create a pfile parameter file. This chapter describes how to configure dispatcher. If you start a new instance while the system is suspended, then that new. Ora starting oracle instance without parameter file for retrieval of spfile oracle instance started total system global area 159019008 bytes. For example, assume you start an instance with a server parameter file containing the following. If you have a valid pfile then you will not need to perform step 1 of the following process. Spfile is a binary file that contains the same information as the old pfile.
In our case it always start database using pfile while starting startup command. A spfile doesnt need a local copy of the pfile to start oracle from a remote machine. Create spfile from pfile share improve this answer. Specify the name of the text parameter file you want to create. Oracle startup pfile issues edstevens sep 29, 2016 5. Jan 07, 2018 45 videos play all oracle db online training tutorials point india ltd. 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. How to recreate the spfile for rac instances where the spfile is. Oracle dumps the startup parameters and installed patches information at the creation of a log. By default, if you do not specify pfile in your startup command, oracle will use server parameter file. I cleaned up a little mess with a deprecatedobsolete parameter in the spfile. When starting an oracle instance with the simple command startup, oracle will first look for a binary spfile in the default location.
Id like to share with you with a bug on aix platform, that besides using the preupgrade 12. To demonstrate i start a database using pfile like sql shutdown immediate database closed. This is useful when you want to make a change starting from the next startup and not for the current instance. I need to start nomount it to make the change, but it wont let me in. Use the create spfile statement to create a server parameter file from a clientside initialization parameter file. Oracle database administrators guide for information about creating and using server parameter files. I did find what i think is the default pfile that is created when the database is installed, and i copied it and attempted to use it in the startup pfile command, but i get the same exact oracle errors.
In that case, you have remaining option to create new pfile using the nondefault parameters recorded in the alert. If no spfile is associated with the instance, then the database looks for the platformspecific default server parameter file name. I thought of creating a pfile from spfile, changing dispatchers and then creating spfile from pfile. Storage structure of oracle database develop paper. If there are any changes made in the pfile, these will be applied only after the dat. How to check whether oracle instance is started using pfile. Spfile is a server parameter file in a binary format whereas pfile is text based parameter file. The database didnt start and i had to create a pfile from spfile, remove the parameter and recreate the spfile.
The parameter settings used to start the instance can be located from the alert. You can also find the location of this file from the database as follows. Oracle rac uses a traditional pfile only if an spfile does not exist or if you specify pfile in your startup command. We can say that this file is oracle instance configuration file that all parameters are kept for startup of oracle instance. Oracle startup search order for spfile and pfile burleson consulting. Rman oracle s recovery manager will backup the spfile with the database control file if setting configure controlfile autobackup is on the default is off. It will post a warning message in alert log everytime database start. The goal of this note is to clarify for customers the use of the pfile spfile options with oradim. Oracle in our database there are 2 identical parameter files, which are named spfile and pfile. Server parameter files let you make persistent changes to individual parameters. Ora file, you must either remove the spfile or use the pfile option on the startup command.
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. Start up the instance pointing to this initialization parameter file. How to recover from lost or missing database parameter. For an oracle asm instance, creation of the singleline traditional parameter file to start up the database is not required. If you want to make a change to a parameter in the spfile without affecting the current instance, you can do so using the scope spfile option of the alter system statement. Total system global area 159383552 bytes fixed size 1246820 bytes variable size 96471452 bytes database buffers 58720256 bytes redo buffers 2945024 bytes database mounted. Startup pfiletestparm nomount to shutdown a particular database, immediately restart and open it, allow access only to users with the restricted session privilege, and use the parameter file myinit. How to start up the asm instance when the spfile is. One of the use of get rid of a parameter from spfile is sometime after upgrade you might have obsolete parameter left over in spfile. If you look in your alert log from the last time your started up, it normally will list all of the important parameters that were used to start the instance, so this gives a good starting point. In general to be able to start up the crs properly. Asm diskgroups are not mounted with spfile on asm on next startup with crsctl command in 11gr2. If you do not specify such a path prefix, then the database adds the path prefix for the default storage location, which is platform dependent.
A binary server parameter file spfile can now be created from the textbased init. How to recreate the spfile for rac instances where the. Id have to shutdown, since my spfile is in use by the instance. There should be a proper pfile or spfile to let the asm instance startup andor the gpnp profile should indicate to a proper spfile. You can find this in the previous successful startup entries. May 30, 2003 remember also that if you want to startup the database with this init. On the next startup, use startup without the pfile parameter and the new spfile will be used. What is the difference between spfile and pfile in oracle. Instead it can only be altered using the alter system command. The maximum number of network sessions to allow for each dispatcher. Ora file, but starting from oracle 9i and up, oracle corp. The spfile has a parameter set which is not allowing the database instance to start using the spfile.
When opening the database, the parameters in this file are read in the nomount step and the instance is configured according to these parameters. Oracle database exadata cloud machine version na and later oracle cloud infrastructure database service version na and later oracle database backup service version na and later information in this document applies to any platform. It says that one of the reasons to use spfile is if i use oemoms to start stop database, the pfile needs to be on the machine where oms is installed or pfile should be accessible to the machine running oms. Total system global area 612368384 bytes fixed size 1290892 bytes variable size 285216116 bytes database buffers 318767104 bytes redo buffers. Some oracle functionality does not work as well if you are not using the spfile. The shared server architecture enables a database server to allow many user processes to share very few server processes, so the number of users that can be supported is increased. How can i tell if my database was started with a spfile. But the problem is that the shell script which contains database startup procedure is pretty complex and i dont want to even touch it. I have read the sequence of files used during startup, 1 spfile. How to use it of note and caution, the spfile you just created is a binary file. As spfile is a server side binary file, local copy of the pfile is not required to start oracle from a remote machine. If you start the oracle instance and specify pfilexxx then oracle will use the oldfashioned. How to find database is using spfile or pfile in oracle 10g. For oracle rac databases, you can easily determine the location of the parameter file by using the srvctl config database command on linux and unix platforms, the search order is as follows.
Parameters in pfile can be edited using any text editor. Database administrators can start dispatchers and shared servers with the sql alter system statement without having to restart the instance to start shared. A spfile is created from a pfile using one of the following commands. Overview of initialization parameter files in oracle rac. Hi, in this article, i will explain the parameter files that are very important for oracle database. 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. Oracle recommends that you use the spfile by using the pfile as a pointer to the spfile. Do this by running the startup command as in the following example, where the sid variable sid in the file init sid. May 09, 2010 hi, how could i start database using spfile permanently. Configuring the server parameter file in oracle real.
Stack overflow for teams is a private, secure spot for you and your coworkers to find and share information. In my case, i plan to use spfile to startup the database instance which used to use a known pfile to open. Sometimes dba needs to find out database is running by spfile or pfile. Do this using the create pfile from spfile statement. This parameter is created when opening the oracle instance and then we can change the contents of these parameter files either dynamically or statically. By default, a new oracle9i database will be working on a pfile, so the spfile must be created from the pfile. Also, you can run this query to see if the database was started via an spfile. Typically you will need to specify the full path and filename for parameter files on your operating system. Oracle database searches for your parameter file in a particular order depending on your platform. Refer to your oracle operating system documentation for path information. Oracles automatic memory management and the spfile.
Spfile permits dynamic changes without requiring you to restart that instance. In oracle9i, a new feature called spfile server parameter file was introduced. Crs and the asm instance can not start up because the spfile is misconstrued or lost. The storage structure of oracle is divided into physical storage structure and logical storage structure. You can execute this statement either before or after instance startup. So it appears as though oracle is exporting a pfile from its spfile that isnt valid. How to startup an oracle database instance using spfile or. The database was using spfile to start up the database, and at the time of database refresh database was started using pfile. Spfile should only be modified through alter system set command. The parameter settings used to start the instance can be located from the. Both spfile, as well as pfile, is lost or corrupted and your database is down. The spfile is created using the create spfile statement. Even if you don not specify the pfile or spfile database starts itself. The oracle spfile is a binary representation of the textbased init.
I want to create pfile from spfile oracle \product\11. If we were trying to change parameters in an unfamiliar database that was handed over from other dba, we have to figure it out whether the database started with spfile or pfile. And now when i tried to check the parameter file using show parameter pfile and show parameter spfile both the values are displayed as null. When the size of the xml file exceeds 10m, oracle creates a new file log. By using the oracle asm filename syntax, you can create the spfile in an oracle asm disk group. The oracle spfile is a binary representation of the text based init. Sep 19, 2017 hi mike, thanks for all the great posts and tips. After you recreate spfile from pfile in last step the next time you restart database oracle will start with newly created spfile. I just installed oracle database 12cr2 and am trying to startup a database. How can i fix the spfile if the database instance wont start. You may want to tune various instance parameters for memory usage, etc.
Oracle data guard broker and the enterprise manager online help system for information about using the graphical user interface to automatically create a physical standby database. Tom i am going through your book effective oracle by design. You cannot set it in a pfile or spfile, but you can set it with an alter system statement. If none of these files exist, you receive the following messages. Total system global area 612368384 bytes fixed size 1290892 bytes variable size 285216116 bytes database buffers 318767104 bytes redo buffers 7094272 bytes database mounted.
Oracle recommends that you use an spfile to simplify administration, to maintain parameter setting consistency, and to guarantee parameter setting persistence across database shutdown and startup events. 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 pfile is a textbased file that can be edited on notepad or a similar editor depending on the os. It is read at startup for specific instance characteristics. Oracle database terminates all existing connections through that dispatcher. I am trying to create a pfile from spfile using following query. Learn more how to check whether oracle instance is started using pfile or spfile. If a parameter is changed using the alter system set command oracle will apply this parameter change to the current spfile. Server parameter files are binary files that exist only on the server and are called from client locations to start up the database.
To enable a shared server configuration, set the dispatchers parameter. Since the spfile was introduced in oracle 9i, i do not use the old pfile any more. Creating a server parameter file spfile oracle dba on linux. Spfile is a binary file and modification to that can only be done through alter system set command. The selftuning sga works best when using a spfile as oracle can store some information abou the sga components in the spfile. Appendix f for information about creating a standby database with recovery manager rman.
Editing an spfile directly will corrupt the file and the start up of the database or instance will not be possible. These entries can be used to created a valid spfile. Start the standby database and convert the pfile parameter file to an spfile server. To create an spfile you must have a preexisting init.