Archive for the ‘Oracle Concepts’ Category

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


Oracle Shutdown Concepts

Shut down the database to make operating system offline backups of all physical structures and to have modified static initialization parameters take effect when restarted.

SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]

SHUTDOWN NORMAL

Normal is the default shutdown mode.Normal database shutdown proceeds with the
following conditions:

• No new connections can be made.
• The Oracle server waits for all users to disconnect before completing the shutdown.
• Database and redo buffers are written to disk.
• Background processes are terminated, and the SGA is removed from memory.
• Oracle closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>



SHUTDOWN TRANSACTIONAL

A transactional shutdown prevents clients from losing work. A transactional database

shutdown proceeds with the following conditions:

• No client can start a new transaction on this particular instance.
• A client is disconnected when the client ends the transaction that is in progress.
• When all transactions have finished, a shutdown occurs immediately.
• The next startup does not require an instance recovery.

SQL> shutdown transactional

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SHUTDOWN IMMEDIATE

Immediate database shutdown proceeds with the following conditions:

• Current SQL statements being processed by Oracle are not completed.
• The Oracle server does not wait for the users, who are currently connected to the database, to disconnect.
• Oracle rolls back active transactions and disconnects all connected users.
• Oracle closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SHUTDOWN ABORT

If no shutdown option works then database instance can be shutdown using abort option. Aborting an instance proceeds with the following conditions:

• Current SQL statements being processed by the Oracle server are immediately terminated.
• Oracle does not wait for users currently connected to the database to disconnect.
• Database and redo buffers are not written to disk.
• Uncommitted transactions are not rolled back.
• The instance is terminated without closing the files.
• The database is not closed or dismounted.
• The next startup requires instance recovery, which occurs automatically.

SQL> shutdown abort

ORACLE instance shut down.

SQL>

All shutdown options except ABORT are clean shutdown where no instance recovery is required. Shutdown abort is a dirty shutdown as it requires instance recovery before getting back up.