Initialization Parameter Files


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.

Although the values within parameter files are specific to a single instance, and a parameter file can only be used for its particular instance, more than one parameter file can be used for an instance to optimize performance for different situations.


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 PFILE

The 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:

  • White space is ignored, and all parameters are optional because the server has a default value for each parameter (some are operating system dependent according to the operating system and parameter).

  • The parameters can be in any order in the file; you should determine the manner in which you want to group the parameters in your parameter file and follow that standard for all parameter files, not because Oracle cares, but because it makes it easier for you to find parameters later regardless of what parameter file you are examining.

  • Comment lines in the parameter file are signified by lines that begin with the "#" symbol.

  • The IFILE parameter signifies that the value to which the parameter is set is an included file, called into the parameter file when the instance is started.

  • If a parameter takes on a list of values, those values are enclosed in parentheses and comma separated.

If the operating system recognizes case sensitivity, the filenames in the parameter file need to follow that sensitivity.


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:

[View full width]

#initialization parameter file: initmydb1.ora db_name = "mydb1" instance_name = mydb1 control_files = ("/mydatabases/mydb1/control01.ctl", "/mydatabases2/mydb1/control02.ctl", "/mydatabases3/mydb1/control03.ctl") open_cursors = 100 shared_pool_size = 360407040 large_pool_size = 614400 java_pool_size = 52428800 processes = 220 background_dump_dest = /mydatabases/mydb1/logs/bdump core_dump_dest = /mydatabases/mydb1/logs/cdump user_dump_dest = /mydatabases/mydb1/logs/udump db_block_size = 8192 compatible = "9.1.0" sort_area_size = 65536 sort_area_retained_size = 65536

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.

Table 4.1. Commonly Configured Parameters

Parameter Name

Description

BACKGROUND_DUMP_DEST

Specifies the directory where the trace files generated by the background processes are to be written. This is also the location of the alert log for the database.

COMPATIBLE

Provides Oracle with the understanding of what features you intend the database to have. If there is a feature in 9i that was not available in 8i and this parameter is set to 8.1.7, the feature will fail to perform.

CONTROL_FILES

The location of the control files for the database.

DB_BLOCK_SIZE

The default block size for the database.

USER_DUMP_DEST

Specifies the directory where the trace files generated by user sessions are written.

CORE_DUMP_DEST

Specifies the location where core dump files generated by Oracle are written.

DB_NAME

The name of the database and also of the SID.

INSTANCE_NAME

The name of the instance and, with the exception of a RAC environment, also the database and the SID.

OPEN_CURSORS

The maximum number of cursors that you want to have opened in your instance at any given time.

PROCESSES

The maximum number of processes permitted to be connected to the instance at any given time.


Working with SPFILE

The 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:

  • MEMORY signifies that the changes to the parameter value should be effective only in the currently running instance, but should not persist across shutdown and startup. This is often done to see what effect the changes will have, when the effect may be determined to be detrimental and you want to easily be able to back out of the change.

  • SPFILE signifies that the parameter value should change only in the SPFILE.

  • BOTH signifies that the parameter value should change not only in the currently running instance but also in the SPFILE to make it persistent across shutdown and startup. BOTH is the default, so make sure that you know what your command is doing if you don't specify the command's scope.

The form that the command takes is as follows:

 ALTER SYSTEM SET <parameter> = <value> [SCOPE=MEMROY|SPFILE|BOTH] 

Creating the SPFILE

The 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 SPFILE

The 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 OEM

To 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.

Table 4.2. ALTER SYSTEM Command Parameters for SPFILE

Parameter

Value Example

Description

COMMENT

I changed this parameter because…

Puts a comment on the parameter line that is changed.

DEFERRED

 

Changes the value for the parameter for any new sessions connecting after the statement is issued while leaving the parameter as it was previously for already connected sessions.

SCOPE

 

Specifies when the change will take effect and takes any of the following three values.

 

MEMORY

The change takes effect immediately for the running instance.

 

SPFILE

The change is made in the server parameter file (SPFILE) only, but the changes will not take place until the next startup.

 

BOTH

This is the combination of MEMORY + SPFILE. It will be available now and will still be available after next startup; this is the default.

SID

 

Specifies the SID of the instance where you want this value to apply. This is applicable to a Real Application Clusters (RAC) environment and applies to all instances; this is the default.


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' 

Comments in the PFILE are carried over into the SPFILE only if they are specified on the same line as the parameter in the PFILE. If they are not on the same line as the parameter, they are not transferred to the SPFILE.


Specifying Often-Used Initialization Parameters

Regardless 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.

Table 4.3. Often-Used Initialization Parameters

Parameter

Description

BACKGROUND_DUMP_DEST

The location where the background processes trace files are written (arch, dbw, lgwr, and so on). Also the location where the alert files are written.

This parameter should be specified.

COMPATIBLE

The version of the server with which the instance should be compatible.

This parameter should be specified.

CONTROL_FILES

Name and location of the control files.

This parameter should be specified.

DB_BLOCK_BUFFERS (deprecated for DB_CACHE_SIZE but still supported)

The number of blocks cached in the SGA.

This parameter should be specified.

DB_BLOCK_SIZE

The default block size in the database.

This parameter should be specified.

DB_NAME

The database identifier that consists of eight characters or less. This is the only parameter that is absolutely required when the database is created but can be acquired from the create database statement if you don't supply it in the initialization file.

This parameter should be specified.

IFILE

The name of additional parameter files to be embedded within the current parameter file. Up to three levels of nested files are possible.

This is a commonly modified parameter.

LOG_BUFFER

The number of bytes allocated to the redo log buffers in the SGA.

This is a commonly modified parameter.

MAX_DUMP_FILE_SIZE

The maximum size of the trace files. This is specified as the number of operating system blocks.

This is a commonly modified parameter.

PROCESSES

The maximum number of operating system processes that can simultaneously connect to an instance.

This is a commonly modified parameter.

SHARED_POOL_SIZE

The size of the shared pool in bytes.

This parameter should be specified.

SQL_TRACE

This enables or disables the SQL trace facility for every user session.

This is a commonly modified parameter.

TIMED_STATISTICS

This enables or disables the timing in trace files and in monitor screens.

This is a commonly modified parameter.

USER_DUMP_DEST

The location where the trace files go that are used for debugging user transaction failures. The trace files are created on behalf of the user processes.

This parameter should be specified.




    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net