Oracle Parameter Files

Parameter Files

Oracle must read either an initialization parameter file or a server
parameter file(SPFILE) to start an instance. These files contain list of initialization parameters and a value for each parameter.

In the platform-specific default location, Oracle Database locates your initialization parameter file by examining filenames in the following order:

  • spfile$ORACLE_SID.ora
  • spfile.ora
  • init$ORACLE_SID.ora

Initialization Parameters

 

Initialization parameters are divided into two groups: basic and advanced. In the majority of cases, it is necessary to set and tune only the basic parameters to get reasonable performance. In rare situations, modification to the advanced parameters may be needed for optimal performance.

Complete List of Initialization Parameters (10g)

Basic Parameters

There are about 29 basic parameters. Only a few of these parameters must be explicitly set because the default values are adequate in the majority of cases.

DB_DOMAIN NLS_LANGUAGE
DB_NAME , DB_UNIQUE_NAME NLS_TERRITORY
DB_RECOVERY_FILE_DEST OPEN_CURSORS
DB_RECOVERY_FILE_DEST_SIZE PGA_AGGREGATE_TARGET
DB_CREATE_ONLINE_LOG_DEST_n PROCESSES
COMPATIBLE REMOTE_LISTENER
CONTROL_FILES REMOTE_LOGIN_PASSWORDFILE
CLUSTER_DATABASE ROLLBACK_SEGMENTS
DB_BLOCK_SIZE SESSIONS
DB_CREATE_FILE_DEST SGA_TARGET
INSTANCE_NUMBER SHARED_SERVERS
JOB_QUEUE_PROCESSES STAR_TRANSFORMATION_ENABLED
LOG_ARCHIVE_DEST_n UNDO_MANAGEMENT
LOG_ARCHIVE_DEST_STATE_n UNDO_TABLESPACE

  

Advanced Parameters

Other than basic parameters, several parameters are used for database tuning and other advance services. Few examples of parameters

SESSION_CACHED_CURSORS
CURSOR_SHARING
DB_KEEP_CACHE_SIZE
OPEN_LINKS
AUDIT_FILE_DEST
JAVA_POOL_SIZE
LARGE_POOL_SIZE
STREAMS_POOL_SIZE
QUERY_REWRITE_ENABLED
 

Hidden/Undocumented Parameters

Hidden parameters should only be used as the last resort and preferably when advised by Oracle Support.

_abort_recovery_on_join

_alert_message_purge

_backup_io_pool_size

_cursor_plan_enabled

_log_checkpoint_recovery_check

Complete List of Hidden Parameters(10g)

 

Viewing and Changing Parameters Settings

  • SHOW PARAMETERS command from SQL*PLUS can be used to to view parameter values
    • SQL> SHOW PARAMTERS CONTROL_FILES

  • V$PARAMETER, V$PARAMETER2 views is used to display current parameter values
  • V$SPPARAMETER view is used to display contents of server parameter file.

CHANGING PARAMETER VALUES 

  PFILE can be edited with any text editor but the SPFILE is a binary file and its parameter values can be changed using “ALTER SYSTEM SET” and “ALTER SYSTEM RESET”commands.

Removing a Parameter from SPFILE: 

SQL> ALTER SYSTEM RESET timed_statistics SCOPE=SPFILE SID=‘*’;

 

Parameter Setting to SPFILE / Memory/ Both

SQL> ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE;

SQL> ALTER SYSTEM SET timed_statistics=TRUE SCOPE=BOTH SID='*';

 

The scope parameter can be set to SPFILE, MEMORY or BOTH. Memory is default if PFILE was used at STARTUP and BOTH is default if SPFILE was used at STARTUP.

  • MEMORY:  The parameter value is set for the current instance only.
  • SPFILE: The parameter value is updated in SPFILE and takes effect at next STARTUP.
  • BOTH: The parameter value is set for current instance and persists to the SPFILE


Advertisements

One response to this post.

  1. […] Oracle Parameter Files […]

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: