Instructor's Notes

(If applicable)

• Lesson 7: Initialization Parameter Files
• In this lesson, you will learn about parameter files and how to control the database by modifying parameter values. Oracle9i introduced a new type of parameter file – a server-side binary file called an “spfile” – so you will learn all about those too.
• To understand the material in this lesson, you will need to have a solid understanding of:
• Oracle basics (SQL, SQL*Plus)
• Architecture (instance, SGA, database files). (You should have mastered the material in the Architecture lessons in the SkillBuilders Oracle9i Database Administration course.)
• Administrative users and SYSDBA
• the STARTUP and SHUTDOWN command
• OK, let’s get started…

Instructor's Notes

(If applicable)

• This material was first taught in the lesson Architecture, Part I: Files.
• The database parameter file, typically called INIT.ORA (or initSID.ora, where SID is the instance name) contains parameters used (required) to start the database.
• With Oracle9i, this file can either be a “pfile” (text parameter file), on “spfile” (binary server parameter file) or a combination of both.
• The excerpt of the parameter file shown above shows that the parameter file can control:
• The name of the database to connect to (DB_NAME)
• The database block size (DB_BLOCK_SIZE)
• The size of the database buffer cache (DB_CACHE_SIZE)
• The location and name of all control files (CONTROL_FILES). Note that the control files are mirror images and should be located on separate disk drives to reduce the possibility of losing all copies of the control file.
• It is important to note that the parameters within the parameter file are read sequentially. If a parameter is specified twice, the value specified on the last one are used to start the database.
• Notes continue on next page…

Instructor's Notes

(If applicable)

• See the Starting and Stopping an Oracle Instance lesson for more information on database parameter files.
• Supplemental Notes
  • The V$PARAMETER view contain the current, in-use values for database parameters. The SQL*Plus SHOW PARAMETER command will also reveal the current, in-use parameter values.

Instructor's Notes

(If applicable)

• The text parameter file was the only type of parameter file supported prior to the release of Oracle9i.
• The STARTUP command PFILE parameter points to the text parameter file you want to start with. Note that if the PFILE parameter is not specified, Oracle9i searches first for a binary server parameter file, then for a text parameter file. (You will learn more about binary server parameter files next.)
• Text parameter files are changed with a standard OS text editor such as Notepad (Windows) or vi in UNIX or Linux. Since the parameter file is read during the NOMOUNT phase of startup, you must stop and restart the database after changing a parameter in the file to put the change into effect.
• Note that if your connected to your database from a remote (client) machine, the text parameter file must be located on your client – not on the server. (This is one of the “problems” that SPFILEs fix – though you may not exactly consider it a problem.
• Mini-Workshop
  • Most Oracle installations come with a sample text parameter file. See if you can find it and, if so, review the parameters specified within the file. The location is usually $ORACLE_HOME/dbs.

Instructor's Notes

(If applicable)

• The server parameter file (SPFILE) allows you to store and manage your initialization parameters “persistently” in a server side binary file. It is similar to the parameter file file from earlier versions of Oracle in that it is used to set the parameters for an instance. It is different in that it is a binary file and it is always stored on the server itself. (In contrast to the text parameter file which is sometimes replicated to client workstations used to startup the database.)
• By “persistent”, Oracle means that you can change a parameter with an Oracle command, and have the change recorded in the SPFILE. Thus, the next time you start the database, the change is in effect, i.e. the change is “persistent” across a shutdown/startup cycle.

Instructor's Notes

(If applicable)

• The server parameter file is created with the CREATE SPFILE command and is based on an existing text parameter file - thus it does not completely replace and obsolete the text-based INIT.ORA parameter file.

Instructor's Notes

(If applicable)

• By using a Server Parameter file, we gain persistent parameter initialization, i.e. parameter changes persist across database / instance shutdowns and startups. You will learn more on this feature later in this lesson.
• Another key benefit is eliminating the need to have a copy of the parameter file on every remote machine can be used to do STARTUPs.

Instructor's Notes

(If applicable)

• A server parameter file is created with the CREATE SPFILE command (SYSDBA or SYSOPER privilege is required). The input to the CREATE SPFILE command is a text parameter file. If the database is already using an SPFILE (this is likely if you created a 9i database with the DBCA), the database must be shutdown to recreate an SPFILE; otherwise a “already used by instance” error is returned.
• The CREATE SPFILE command in its simplest form does not require file names or paths. In the example above (CREATE SPFILE), the default location and name is used for the SPFILE and PFILE. The default is OS-dependent:
• Windows: $ORACLE_HOME\database\spfile$ORACLE_SID.ora
• Linux or UNIX: $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
• The default name and location can be overridden by including the path and file name on the command:
• CREATE SPFILE = ‘e:\oracle\admin\prod\pfile\spfileprod.ora’
• FROM PFILE = ‘e:\oracle\admin\prod\pfile\initprod.ora’;


• Notes for this slide continue on the next page...

Instructor's Notes

(If applicable)

• Notes continued from the previous page:
• It is also possible to create a text parameter file from the SPFILE with the CREATE PFILE command. I recommend doing this before you change a parameter in the SPFILE – as a backup technique. You will see an example of this later.
• Supplemental Notes
  • When creating an SPFILE, text parameter file comment-only lines are ignored. Parameter lines with comments on the same line as parameter values are retained in the SPFILE.
  • The Database Configuration Assistant also supports the creation of the SPFILE. If you are using a database created by the Oracle9i DBCA, your probably already using an SPFILE.
• Mini-Workshop
  • Check if your database is already using an SPFILE. There are two techniques; the SQL*Plus SHOW command to display the contents of the SPFILE parameter. If this is null, your instance is not using a parameter file:
  • SQL> show parameter spfile

• The second technique is helpful if you are using an SPFILE to determine how many parameters are actually set in the SPFILE. In this example, I am not using an SPFILE, thus there are 0 parameters set:
• SQL> select count(*) from v$spparameter
• 2 where value is not null
• 
  COUNT(*)
• ----------
• 0

Instructor's Notes

(If applicable)

• The CREATE SPFILE command can be run before or after instance startup, depending on the current situation.
• If the database is shutdown and a SPFILE already exists in the location specified in the command and has the same file name as the command requests, then the existing SPFILE is overwritten. A warning is not issued.
• If the database is started, CREATE SPFILE will work only if the instance was not started with an SPFILE. Otherwise an ORA-32002 error will occur. In this situation, shut the instance down, backup the current SPFILE, then execute the CREATE SPFILE command.

Instructor's Notes

(If applicable)

• The server parameter file is read at instance startup (specifically, when the instance is mounted). If the PFILE parameter is not supplied on STARTUP, Oracle goes through these steps, in the following order, to locate the initialization parameters it will use:
• Check for a file called:
• $ORACLE_HOME\DATABASE\SPFILE$ORACLE_SID.ORA
• If found, then Oracle uses this file. If not found, perform the following check:
• Check for a file called $ORACLE_HOME\DATABASE\SPFILE.ORA
• If found, then Oracle uses this file. If not found, perform the following check:
• Check for a file called:
• $ORACLE_HOME\DATABASE\PFILE\INIT$ORACLE_SID.ORA
• If found, then Oracle uses this file. If not found, Oracle generates an error.
• Note: the OS paths specified above assume a Windows server; if UNIX or Linux is used, replace ‘DATABASE’ with ‘DBS’ and the back slash with a forward slash.

Instructor's Notes

(If applicable)

• Oracle does not support a direct reference to SPFILE on the STARTUP command. So, if your SPFILE is not in the default location, use the SPFILE parameter in a text parameter file to tell Oracle where the SPFILE is.
• Note: The SPFILE initialization parameter is a STATIC parameter. To change its value, you must change the value in the parameter file and restart the database.

Instructor's Notes

(If applicable)

• When specifying the SPFILE parameter, you can still define other parameters in your parameter file. However, remember the parameter file is read sequentially. Why is this important? If you specify the same parameter in your SPFILE and in your parameter file, then the parameter value that is read last will take precedence over others. For example, given these INIT.ORA entries:
• SPFILE=c:\oracle\admin\ora91\pfile\spfileprod.ora
• SHARED_POOL_SIZE=50000000
• TIMED_STATISTICS=TRUE
• OPEN_CURSORS=300
• The SPFILE parameter is listed before the other INIT.ORA parameters. If it contains an entry for SHARED_POOL_SIZE, TIMED_STATISTICS or OPEN_CURSORS, those entries will be superceded by the values subsequently read from the INIT.ORA file.

Instructor's Notes

(If applicable)

• The first question you may have when working with Oracle9i parameter values is “is my instance using a server parameter file?”.
• The answer to this question can be found in the SPFILE parameter value. Either the SHOW PARAMETER SQL*Plus command or a query on V$PARAMETER will reveal its value. If the VALUE column returns a non-NULL value, then your instance is using a SPFILE. Otherwise it is using a PFILE.

Instructor's Notes

(If applicable)

• Initialization parameters for pfiles and spfiles can be monitored with the V$PARAMETER, V$PARAMETER2, and V$SPPARAMETER data dictionary views.
• V$PARAMETER always contains the current, in-use value. Query V$PARAMETER directly or use the SQL*Plus SHOW command to see a parameter value.
• V$SPPARAMETER contains the parameter values read from the SPFILE at database startup. As the following example illustrates, the values in V$SPPARAMETER might not be the in-use values because dynamic parameters can be changed after startup:



• Notes for this slide continue on the next page…

Instructor's Notes

(If applicable)

• Notes continued from the previous page:
• SQL> alter system set db_cache_size=24m scope=memory;
• System altered.


• SQL> show parameter db_cache_size

• There is another reason the V$SPPARAMETER value might not be the current, in-use value. Remember that if duplicate parameter entries are encountered, Oracle will use the last one found. See the example in the Parameter Overrides section of this lesson.

Instructor's Notes

(If applicable)

• V$PARAMETER2 is similar to V$PARAMETER in that it contains the current, in-use values. The difference is it contains a column called ORDINAL. When a parameter has multiple values (e.g. CONTROL_FILES), this provides a way of extracting values in the proper order.


• Supplemental Notes
  • V$SPPARAMETER, like V$PARAMETER2, contains the ORDINAL column.

Instructor's Notes

(If applicable)

• The contents of a SPFILE can be exported to a text file using the CREATE PFILE command. This can be used as an excellent, easy backup technique. I recommend using this before you change the SPFILE with the ALTER SYSTEM command (you will learn how to do this on the next page).
• You also might want a readable copy of your initialization parameters as they are defined in your SPFILE for documentation purposes. Another possibility is to use the text file as a template when for a new database.
• The CREATE PFILE command can be run as long as a SPFILE has been created. The instance does not need to have been started with the SPFILE in order for the CREATE PFILE to run successfully.

Instructor's Notes

(If applicable)

• As shown above, parameter values can be altered with the ALTER SYSTEM statement.
• The Oracle9i SPFILE provides another useful feature: persistent parameter initialization. This means that you can use the ALTER command to change a parameter – and that change will be in effect the next time you start the database! The new SCOPE clause of the ALTER SYSTEM command allows us control exactly how the change should take effect. In the example above, “BOTH” is used to tell Oracle to put the change in effect in memory (immediate) and in the SPFILE as well. So, the next time we startup, the change persists and the shared pool will still be 12M.
• Also, the COMMENT clause has been added to allow the ability to associate comments with a particular change. Comments can be displayed using the V$PARAMETER view:
• SQL> select update_comment from v$parameter
• 2 where name='shared_pool_size';
• UPDATE_COMMENT
• -------------------------------------
• changed on 4/3/04

Instructor's Notes

(If applicable)

• The SCOPE clause is used to indicate the desired persistence of the change. Valid options for SCOPE are SPFILE, MEMORY, and BOTH.
• Valid SCOPE values:
• MEMORY will make the change for the current instance only. The SPFILE will not be changed. This option can be used for dynamic parameters only. Default.
• SPFILE will make the change in the SPFILE but will not take effect until the next shutdown-startup cycle of the instance. This option can be used for both dynamic and static parameters.
• BOTH will make the change for the current instance (memory) and will update the SPFILE also. This option can be used for dynamic parameters only.
• Note: Dynamic parameters were introduced in Oracle8. See the next page for examples of determining if a parameter is dynamic.

Instructor's Notes

(If applicable)

• Each parameter documented in the Oracle9i Database Reference has a “parameter class”. The parameter class is “Static” (the value cannot be changed without restarting the database, or “Dynamic”, the parameter can be changed.
• Besides the documentation (which you should have access to – remember HTTP://TECHNET.ORACLE.COM), another way to tell whether or not an initialization parameter is dynamic or static is to query the V$PARAMETER view and look at the ISSYS_MODIFIABLE column.
• Valid values for the ISSYS_MODIFIABLE column:
• IMMEDIATE – This is a dynamic parameter, i.e. by using the ALTER SYSTEM command you can change the value for this parameter for the current instance.


• Notes for this slide continue on the next page…

Instructor's Notes

(If applicable)

• Notes continued from the previous page:
• FALSE – This is not a dynamic parameter. The parameter is static and its value cannot be changed for the current instance. Change with SCOPE=SPFILE (or change the text parameter file if used) and restart the database.
• DEFERRED – It can be altered, but any new value for the parameter as defined by the ALTER SYSTEM command will only affect new sessions within the current instance.
• To summarize:
• IMMEDIATE – Dynamic – can change for current instance, affects all sessions
• DEFERRED – Dynamic – can change for current instance, only affects new sessions
• FALSE – static – cannot be changed for current instance

Instructor's Notes

(If applicable)

• You have covered a lot of ground in this lesson. Here are the highlights:
• Parameter files contain database initialization paramet