|
To start the instance, Oracle must read from an initialization parameter file to determine how to configure the SGA and in general how to run the instance and its database. Entries in the initialization files are specific to the instance that is going to be accessed. There are two kinds of parameters, explicit and implicit. Explicit parameters have entries in the parameter file. Implicit parameters are implied by taking on the Oracle default values but have no entry within the parameter file.
Changes to entries in the file take effect based on the type of parameter file used. Entries in the PFILE are static (to have a static parameter's changes take effect, the instance needs to be restarted). Those parameters in an SPFILE are persistent. (Persistent parameters can be changed in the active instance, take effect in the active instance, and persist across restarts of the instance.) It is important to understand that you cannot have the database started with both the PFILE and the SPFILE at the same time. Only one can be in use in any given instance at any one time. The parameter files contain a list of instance parameters; the name of the database and instance to which it is associated; memory structure allocation for the SGA; names, locations, and reusability of the redo log files; names and locations of the control files; and information pertaining to the undo segments for the instance. Working with PFILEThe PFILE, commonly referred to as the initSID.ora file, is known as a static parameter file. The PFILE is a simple flat text file that can be modified manually in any operating system text editor. The changes made to the initSID.ora file don't take effect until the next time the database is started (in fact, the file is in a read-only state during instance startup), and its default location is $ORACLE_HOME/dbs on Unix or %ORACLE_HOME\database on the Windows platform. A sample init.ora file is placed in the default location by the Oracle Installer during the initial installation of the database binaries, and this file can be used (copied) to provide customized, instance-specific settings for your environment. The keyword value specification in the PFILE parameters takes the following form: Keyword=value There are several points to remember about the parameter file:
One important reason to group parameters in a consistent manner is that, if you accidentally list a parameter more than once, and the values for the parameter are not identical in both cases, the last value in the parameter file will take precedence and overwrite any other value that you have set. This can have unanticipated consequences. An example of an initSID.ora file follows:
Table 4.1 lists commonly used initiation parameters and provides a brief description of their use. Remember that these parameters are version 9.2.0.1 settings and may be slightly different in other versions. A complete, version-specific list can be found in the Reference Guide of the Oracle documentation set for the version that you are using.
Working with SPFILEThe SPFILE, known more commonly as the spfileSID.ora, can be thought of as a persistent parameter file. The default location for the SPFILE is $ORACLE_HOME/dbs in Unix or $ORACLE_HOME\database directory in Windows. It is a binary file (although it is easily viewable in Unixdon't edit it) that has the capability to store and make available changes that are both dynamic and also persistent across shutdown of the instance and startup again. The SPFILE is maintained by the Oracle Server (and it is important that you not manually make changes to this file) with parameter value changes made with the ALTER SYSTEM command. ALTER SYSTEM SET UNDO TABLESPACE = 'UNDOTBSP2'; Values for the parameters can be deleted or reset to allow the instance to revert, for those parameters, back to the default values. You can specify when you issue the ALTER SYSTEM command whether the changes should be made temporarily or should persist across shutdown and startup. The following settings specify what scope the changes should take:
The form that the command takes is as follows: ALTER SYSTEM SET <parameter> = <value> [SCOPE=MEMROY|SPFILE|BOTH] Creating the SPFILEThe SPFILE can be created from the PFILE using the CREATE SPFILE command. This can be issued at any time before or after the instance has started and is run from the SQL prompt when logged in as someone with SYSDBA privileges. CREATE SPFILE FROM PFILE; or CREATE SPFILE=<SPFILE NAME> FROM PFILE=<PFILE NAME>; SPFILE NAME is the name that you want specified for the SPFILE if you don't want the default name of spfileSID.ora. PFILE NAME is the name you have given the parameter file, initSID.ora, which is assumed. You can create an editable version of the values in the SPFILE by creating the PFILE from the SPFILE. You can then edit the resulting PFILE and then re-create the SPFILE from that. The command to create the PFILE from the SPFILE follows: CREATE PFILE FROM SPFILE; An interesting thing about re-creating the PFILE in this manner is that the parameters in the resulting file show up in alphabetic order. Modifying the SPFILEThe SPFILE can be modified; however, it has to be modified indirectly, either through the OEM interface or from the SQLPLUS command line. Modifying the SPFILE Using OEMTo modify the SPFILE using the GUI OEM interface, you launch the console either in standalone mode or by connecting to an existing repository. Expand the database that you are working on from the Database folder, expand the Instance folder, and click on Configuration. The interface can be launched both by issuing the command oemapp console at the command line and by launching through the Windows Start menu. Within the General tab, click the All Initialization Parameters button to see the parameters that you can work with. They are viewed in alphabetical order. If you have logged in as SYSDBA, you can modify both the working instance parameters as well as the SPFILE values, both the dynamic parameters as well as the static ones. If you don't log in as someone with SYSDBA authority, you can alter only dynamic parameters. Modify the parameter's value column and click OK. To alter the contents of the SPFILE from the SQL prompt command line, you could enter the following command (this alters the running session and makes the change in the SPFILE): Alter system set open_cursors=4000 scope=both; Table 4.2 shows the parameters that the ALTER SYSTEM command can take when you are using an SPFILE.
The following is an excerpt from what you might see in your SPFILE. Notice that the parameters are in alphabetic order. This is helpful when trying to find parameters and their values. ^@^@ *.db_block_size=8192 *.db_domain='WORLD' *.db_file_multiblock_read_count=16 *.db_files=1022 *.db_name='mydb1'
Specifying Often-Used Initialization ParametersRegardless of whether you are using the PFILE or the SPFILE, no parameters are, strictly speaking, required. However, several should be specified in your environment and customized for your individual databases, if necessary. There are others that are most frequently altered in most organizations. Table 4.3 shows these parameters along with their description.
|
|