Lesson Objectives

• Start an instance with various parameter files
• Control the instance and database environment by modifying parameters
• Server Parameter Files
  • Start database with SPFILE
  • Modify
  • Backup
• You need to know
  • Basics, architecture, STARTUP and SHUTDOWN

Review: Parameter Files

• Read at startup
• Can contain over 200 parameters
• Parameters read sequentially
  • If duplicates are found, last parameter “wins”
• 9i supports two types
  • Text (PFILE) and Binary (SPFILE)

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.

Text Parameter File

• Only type supported prior to 9i
• Can specify on STARTUP command
• Modify with any OS text editor
• Stop / start database to put change in effect

Introduction to SPFILE...

• SPFILE = Server Parameter File
• Similar to text parameter file
  • Contains initialization parameter settings
• Differences from text parameter file:
  • Stored in binary format
  • SPFILE always resides on database server
    • Parameter file resides on client if remote machine performing STARTUP

...Introduction to SPFILE

• Created with CREATE SPFILE command
• Based on text INIT.ORA file
  • INIT.ORA still relevant

Benefits of SPFILE

• “Persistent parameter initialization”
  • Parameter changes persist across database shutdown / startup cycles
• Centralized location
  • Avoids having multiple copies of init.ora

Creating an SPFILE

• Created with CREATE SPFILE command
  • Input is text parameter file
  • Output is binary SPFILE

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.

CREATE SPFILE Notes

• CREATE SPFILE can be executed:
  • Instance stopped
    • Existing SPFILE overwritten if exists
    • No warning given!
    • “OR REPLACE” option not supported
  • Instance started
    • Error occurs if SPFILE exists and is being used
    • ORA-32002: cannot create SPFILE already being used by the instance

Using SPFILE...

• STARTUP command with no parameters
• Uses 1st file found in search sequence: $ORACLE_HOME\database or /dbs (unix):
  • Checks for SPFILE$ORACLE_SID.ORA
  • Checks for SPFILE.ORA
  • Checks for a text file called init$ORACLE_SID.ora
• The first file found is used
• Error if no parameter file is found

...Using SPFILE

• SPFILE cannot be directly referenced on the STARTUP command
• Use default or ‘SPFILE=‘ parameter in text parameter file
• Startup your instance with INITPROD.ORA
• “initprod.ora” entry:

Parameter Overrides

• Use caution including parameters other than SPFILE
• Assume INIT.ORA contains
• Oracle uses last parameter it finds
  • Shared pool will be 50000000, regardless of SPFILE setting

Am I Using SPFILE?

• Is my instance using SPFILE?
• SHOW PARAMETER spfile
  • Shows current value
  • Same as SELECT FROM V$PARAMETER
  • SPFILE will be NULL if SPFILE not in use

Helpful Dictionary Views

• V$PARAMETER
  • Current, in-use values
• V$SPPARAMETER
  • SPFILE values
  • Might not be the in-use values
    • Dynamic parameters can change while database open
    • Parameter overridden at startup
• V$PARAMETER2
  • Shows parameters with multiple values in order

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.

…Helpful Dictionary Views

• V$PARAMETER2
  • Same as V$PARAMETER
  • Except it contains ORDINAL column

Export & Backup

• Export SPFILE contents to a text file
  • Performed with the CREATE PFILE command
• Reasons for exporting
  • Backup and recovery purposes
    • RMAN will also backup
  • Documentation purposes
  • Use as a template for a new database

Modifying Parameters

• ALTER SYSTEM to change parameter values
• SCOPE=BOTH changes current instance and SPFILE
  • Change persists
  • Only valid for dynamic parameters
• Comment can be seen in V$PARAMETER file

SCOPE Clause

• MEMORY
  • Default
  • Change is immediate
  • SPFILE is not changed; change lost at shutdown
  • Only valid for dynamic parameters

Dynamic Parameters

• Dynamic parameters can be changed for the current instance

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.

Summary

• Parameter file is read at startup
  • Default location or use PFILE parameter
• Parameters read sequentially
• SPFILE is binary server-based parameter file
• Data dictionary views show current parameter values
• Parameters can be modified with ALTER SYSTEM
  • If using SPFILE, can make change permanent

Server Parameter Files Workshop

• Server Parameter Files

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.