How to recover from lost or missing database parameter. Database administrators can start dispatchers and shared servers with the sql alter system statement without having to restart the instance to start shared. The goal of this note is to clarify for customers the use of the pfile spfile options with oradim. The maximum number of network sessions to allow for each dispatcher.
Sometimes dba needs to find out database is running by spfile or pfile. You can find this in the previous successful startup entries. 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. We can say that this file is oracle instance configuration file that all parameters are kept for startup of oracle instance. After you recreate spfile from pfile in last step the next time you restart database oracle will start with newly created spfile. Server parameter files let you make persistent changes to individual parameters. Spfile should only be modified through alter system set command.
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. Some oracle functionality does not work as well if you are not using the spfile. How to find database is using spfile or pfile in oracle 10g. Oracle dumps the startup parameters and installed patches information at the creation of a log. Both spfile, as well as pfile, is lost or corrupted and your database is down. Jan 07, 2018 45 videos play all oracle db online training tutorials point india ltd. The oracle spfile is a binary representation of the text based init. Spfile permits dynamic changes without requiring you to restart that instance. 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. I need to start nomount it to make the change, but it wont let me in. I want to create pfile from spfile oracle \product\11. How to start up the asm instance when the spfile is. Oracle database administrators guide for information about creating and using server parameter files. To enable a shared server configuration, set the dispatchers parameter.
This is useful when you want to make a change starting from the next startup and not for the current instance. Hi, in this article, i will explain the parameter files that are very important for oracle database. What is the difference between spfile and pfile in oracle. There should be a proper pfile or spfile to let the asm instance startup andor the gpnp profile should indicate to a proper spfile. The spfile is created using the create spfile statement. Ora starting oracle instance without parameter file for retrieval of spfile oracle instance started total system global area 159019008 bytes. How to recreate the spfile for rac instances where the spfile is. When opening the database, the parameters in this file are read in the nomount step and the instance is configured according to these parameters. Spfile is a binary file that contains the same information as the old pfile. Total system global area 159383552 bytes fixed size 1246820 bytes variable size 96471452 bytes database buffers 58720256 bytes redo buffers 2945024 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. But the problem is that the shell script which contains database startup procedure is pretty complex and i dont want to even touch it. One exciting new feature of oracle is the removal of the init. Sep 19, 2017 hi mike, thanks for all the great posts and tips.
Since the database uses this file during startup all parameter changes persist between shutdowns. In my case, i plan to use spfile to startup the database instance which used to use a known pfile to open. Do this by running the startup command as in the following example, where the sid variable sid in the file init sid. 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. To create an spfile you must have a preexisting init. How to check whether oracle instance is started using pfile. The database didnt start and i had to create a pfile from spfile, remove the parameter and recreate the spfile. A binary server parameter file spfile can now be created from the textbased init. 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. For an oracle asm instance, creation of the singleline traditional parameter file to start up the database is not required. Editing an spfile directly will corrupt the file and the start up of the database or instance will not be possible.
If you start a new instance while the system is suspended, then that new. This parameter is created when opening the oracle instance and then we can change the contents of these parameter files either dynamically or statically. It is read at startup for specific instance characteristics. Instead it can only be altered using the alter system command. Jun 25, 2019 a binary server parameter file spfile can now be created from the textbased init. Ask tom after repairing spfile create pfile from spfile. I just installed oracle database 12cr2 and am trying to startup a database. Id like to share with you with a bug on aix platform, that besides using the preupgrade 12. Spfile is a server parameter file in a binary format whereas pfile is text based parameter file. Oracle in our database there are 2 identical parameter files, which are named spfile and pfile.
Create spfile from pfile share improve this answer. 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. When starting an oracle instance with the simple command startup, oracle will first look for a binary spfile in the default location. Overview of initialization parameter files in oracle rac. 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. By default, if you do not specify pfile in your startup command, oracle will use server parameter file. Ora file, you must either remove the spfile or use the pfile option on the startup command. The following example shows how to create a pfile parameter file. 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. Configuring the server parameter file in oracle real. Creating a server parameter file spfile oracle dba on linux. Even if you don not specify the pfile or spfile database starts itself. 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.
I thought of creating a pfile from spfile, changing dispatchers and then creating spfile from pfile. You can execute this statement either before or after instance startup. Typically you will need to specify the full path and filename for parameter files on your operating system. By default, a new oracle9i database will be working on a pfile, so the spfile must be created from the pfile. In oracle9i, a new feature called spfile server parameter file was introduced. Specify the name of the text parameter file you want to create. In general to be able to start up the crs properly. 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. 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. Total system global area 612368384 bytes fixed size 1290892 bytes variable size 285216116 bytes database buffers 318767104 bytes redo buffers. Stack overflow for teams is a private, secure spot for you and your coworkers to find and share information. Asm diskgroups are not mounted with spfile on asm on next startup with crsctl command in 11gr2. May 30, 2003 remember also that if you want to startup the database with this init.
Id have to shutdown, since my spfile is in use by the instance. 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. Server parameter files are binary files that exist only on the server and are called from client locations to start up the database. May 09, 2010 hi, how could i start database using spfile permanently. You can also find the location of this file from the database as follows. 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. Ora file, but starting from oracle 9i and up, oracle corp. It will post a warning message in alert log everytime database start. How can i tell if my database was started with a spfile. So it appears as though oracle is exporting a pfile from its spfile that isnt valid.
Oracle rac uses a traditional pfile only if an spfile does not exist or if you specify pfile in your startup command. By using the oracle asm filename syntax, you can create the spfile in an oracle asm disk group. Oracles automatic memory management and the spfile. The storage structure of oracle is divided into physical storage structure and logical storage structure. A spfile doesnt need a local copy of the pfile to start oracle from a remote machine. If you have a valid pfile then you will not need to perform step 1 of the following process. The database was using spfile to start up the database, and at the time of database refresh database was started using pfile. Parameter files in oracle dba spfile and pfile youtube.
A spfile is created from a pfile using one of the following commands. 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. Spfile is a binary file and modification to that can only be done through alter system set command. Do this using the create pfile from spfile statement. This chapter describes how to configure dispatcher. 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.
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. Tom i am going through your book effective oracle by design. If you start the oracle instance and specify pfilexxx then oracle will use the oldfashioned. I have read the sequence of files used during startup, 1 spfile. If no spfile is associated with the instance, then the database looks for the platformspecific default server parameter file name. 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. If none of these files exist, you receive the following messages. The oracle spfile is a binary representation of the textbased init.
You cannot set it in a pfile or spfile, but you can set it with an alter system statement. Oracle startup search order for spfile and pfile burleson consulting. You may want to tune various instance parameters for memory usage, etc. Storage structure of oracle database develop paper. Moreover, if pfile is used, you have to know the location of pfile.
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. Oracle database terminates all existing connections through that dispatcher. Start the standby database and convert the pfile parameter file to an spfile server. 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. To demonstrate i start a database using pfile like sql shutdown immediate database closed. How can i fix the spfile if the database instance wont start.
The parameter settings used to start the instance can be located from the alert. If there are any changes made in the pfile, these will be applied only after the dat. The spfile has a parameter set which is not allowing the database instance to start using the spfile. Refer to your oracle operating system documentation for path information. How to recreate the spfile for rac instances where the. Appendix f for information about creating a standby database with recovery manager rman. I cleaned up a little mess with a deprecatedobsolete parameter in the spfile. Also, you can run this query to see if the database was started via an spfile.
In that case, you have remaining option to create new pfile using the nondefault parameters recorded in the alert. Since the spfile was introduced in oracle 9i, i do not use the old pfile any more. If a parameter is changed using the alter system set command oracle will apply this parameter change to the current spfile. Oracle database searches for your parameter file in a particular order depending on your platform. Oracle startup pfile issues edstevens sep 29, 2016 5. The pfile is a textbased file that can be edited on notepad or a similar editor depending on the os.
On the next startup, use startup without the pfile parameter and the new spfile will be used. Use the create spfile statement to create a server parameter file from a clientside initialization parameter file. Oracle provides two different types of mutually exclusive parameter files that you can use, pfile and spfile. The parameter settings used to start the instance can be located from the. 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. Parameters in pfile can be edited using any text editor. 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. How to startup an oracle database instance using spfile or. When the size of the xml file exceeds 10m, oracle creates a new file log.
1253 587 1264 916 517 1406 1023 141 1057 733 895 738 338 1245 877 809 1514 23 810 1277 405 1278 65 228 966 1449 499 1160 984 1044 1330 27 783 1031 822 1076 1117 719 244 944 571 1333 1056 460 1013 265 1192 114 1479