|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
|PDF · Mobi · ePub|
When an Oracle instance starts, it reads initialization parameters from an initialization parameter file. This file must at a minimum specify the
DB_NAME parameter. All other parameters have default values.
The initialization parameter file can be either a read-only text file, a
PFILE, or a read/write binary file.
The binary file is called a server parameter file. A server parameter file enables you to change initialization parameters with
ALTER SYSTEM commands and to persist the changes across a shutdown and startup. It also provides a basis for self-tuning by Oracle Database. For these reasons, it is recommended that you use a server parameter file. You can create one manually from your edited text initialization file, or automatically by using Database Configuration Assistant (DBCA) to create your database.
Before you manually create a server parameter file, you can start an instance with a text initialization parameter file. Upon startup, the Oracle instance first searches for a server parameter file in a default location, and if it does not find one, searches for a text initialization parameter file. You can also override an existing server parameter file by naming a text initialization parameter file as an argument of the
Default file names and locations for the text initialization parameter file are shown in the following table:
|Platform||Default Name||Default Location|
|UNIX and Linux||
For example, the initialization parameter file for the
If you are creating an Oracle database for the first time, Oracle suggests that you minimize the number of parameter values that you alter. As you become more familiar with your database and environment, you can dynamically tune many initialization parameters using the
ALTER SYSTEM statement. If you are using a text initialization parameter file, then your changes are effective only for the current instance. To make them permanent, you must update them manually in the initialization parameter file, or they will be lost over the next shutdown and startup of the database. If you are using a server parameter file, then initialization parameter file changes made by the
ALTER SYSTEM statement can persist across shutdown and startup.
"Determining the Global Database Name" for information about the
The text initialization parameter file (PFILE) must contain name/value pairs in one of the following forms:
For parameters that accept only a single value:
For parameters that accept one or more values (such as the
Parameter values of type string must be enclosed in single quotes ('). Case (upper or lower) in filenames is significant only if case is significant on the host operating system.
For parameters that accept multiple values, to enable you to easily copy and paste name/value pairs from the alert log, you can repeat a parameter on multiple lines, where each line contains a different value.
control_files='/u01/app/oracle/oradata/orcl/control01.ctl' control_files='/u01/app/oracle/oradata/orcl/control02.ctl' control_files='/u01/app/oracle/oradata/orcl/control03.ctl'
If you repeat a parameter that does not accept multiple values, then only the last value specified takes effect.
Oracle Database provides generally appropriate values in a sample text initialization parameter file. You can edit these Oracle-supplied initialization parameters and add others, depending upon your configuration and options and how you plan to tune the database.
The following is the content of the sample file:
############################################################################## # Example INIT.ORA file # # This file is provided by Oracle Corporation to help you start by providing # a starting point to customize your RDBMS installation for your site. # # NOTE: The values that are used in this file are only intended to be used # as a starting point. You may want to adjust/tune those values to your # specific hardware and needs. You may also consider using Database # Configuration Assistant tool (DBCA) to create INIT file and to size your # initial set of tablespaces based on the user input. ############################################################################### # Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at # install time) db_name='ORCL' memory_target=1G processes = 150 audit_file_dest='<ORACLE_BASE>/admin/orcl/adump' audit_trail ='db' db_block_size=8192 db_domain='' db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area' db_recovery_file_dest_size=2G diagnostic_dest='<ORACLE_BASE>' dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' open_cursors=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS1' # You may want to ensure that control files are created on separate physical # devices control_files = (ora_control1, ora_control2) compatible ='11.2.0'
The global database name consists of the user-specified local database name and the location of the database within a network structure. The
DB_NAME initialization parameter determines the local name component of the database name, and the
DB_DOMAIN parameter, which is optional, indicates the domain (logical location) within a network structure. The combination of the settings for these two parameters must form a database name that is unique within a network.
For example, to create a database with a global database name of
test.us.example.com, edit the parameters of the new parameter file as follows:
DB_NAME = test DB_DOMAIN = us.example.com
You can rename the
GLOBAL_NAME of your database using the
ALTER DATABASE RENAME GLOBAL_NAME statement. However, you must also shut down and restart the database after first changing the
DB_DOMAIN initialization parameters and re-creating the control files. Re-creating the control files is easily accomplished with the command
TRACE. See Oracle Database Backup and Recovery User's Guide for more information.
See Also:Oracle Database Utilities for information about using the
DBNEWIDutility, which is another means of changing a database name
DB_NAME must be set to a text string of no more than eight characters. During database creation, the name provided for
DB_NAME is recorded in the datafiles, redo log files, and control file of the database. If during database instance startup the value of the
DB_NAME parameter (in the parameter file) and the database name in the control file are different, the database does not start.
DB_DOMAIN is a text string that specifies the network domain where the database is created. If the database you are about to create will ever be part of a distributed database system, then give special attention to this initialization parameter before database creation. This parameter is optional.
See Also:Part V, "Distributed Database Management" for more information about distributed databases
The Fast Recovery Area is a location in which Oracle Database can store and manage files related to backup and recovery. It is distinct from the database area, which is a location for the current database files (datafiles, control files, and online redo logs).
You specify the Fast Recovery Area with the following initialization parameters:
DB_RECOVERY_FILE_DEST: Location of the Fast Recovery Area. This can be a directory, file system, or Automatic Storage Management (Oracle ASM) disk group. It cannot be a raw file system.
In an Oracle Real Application Clusters (Oracle RAC) environment, this location must be on a cluster file system, Oracle ASM disk group, or a shared directory configured through NFS.
DB_RECOVERY_FILE_DEST_SIZE: Specifies the maximum total bytes to be used by the Fast Recovery Area. This initialization parameter must be specified before
DB_RECOVERY_FILE_DEST is enabled.
In an Oracle RAC environment, the settings for these two parameters must be the same on all instances.
You cannot enable these parameters if you have set values for the
LOG_ARCHIVE_DUPLEX_DEST parameters. You must disable those parameters before setting up the Fast Recovery Area. You can instead set values for the
n parameters. The
LOG_ARCHIVE_DEST_1 parameter is implicitly set to point to the Fast Recovery Area if a local archiving location has not been configured and
LOG_ARCHIVE_DEST_1 value has not been set.
Oracle recommends using a Fast Recovery Area, because it can simplify backup and recovery operations for your database.
See Also:Oracle Database Backup and Recovery User's Guide to learn how to create and use a Fast Recovery Area
CONTROL_FILES initialization parameter specifies one or more control filenames for the database. When you execute the
CREATE DATABASE statement, the control files listed in the
CONTROL_FILES parameter are created.
If you do not include
CONTROL_FILES in the initialization parameter file, then Oracle Database creates a control file in the same directory as the initialization parameter file, using a default operating system–dependent filename. If you have enabled Oracle Managed Files, the database creates Oracle managed control files.
If you want the database to create new operating system files when creating database control files, the filenames listed in the
CONTROL_FILES parameter must not match any filenames that currently exist on your system. If you want the database to reuse or overwrite existing files when creating database control files, ensure that the filenames listed in the
CONTROL_FILES parameter match the filenames that are to be reused, and include a
REUSE clause in the
DB_BLOCK_SIZE initialization parameter specifies the standard block size for the database. This block size is used for the
SYSTEM tablespace and by default in other tablespaces. Oracle Database can support up to four additional nonstandard block sizes.
The most commonly used block size should be picked as the standard block size. In many cases, this is the only block size that you must specify. Typically,
DB_BLOCK_SIZE is set to either 4K or 8K. If you do not set a value for this parameter, then the default data block size is operating system specific, which is generally adequate.
You cannot change the block size after database creation except by re-creating the database. If the database block size is different from the operating system block size, then ensure that the database block size is a multiple of the operating system block size. For example, if your operating system block size is 2K (2048 bytes), the following setting for the
DB_BLOCK_SIZE initialization parameter is valid:
A larger data block size provides greater efficiency in disk and memory I/O (access and storage of data). Therefore, consider specifying a block size larger than your operating system block size if the following conditions exist:
Oracle Database is on a large computer system with a large amount of memory and fast disk drives. For example, databases controlled by mainframe computers with vast hardware resources typically use a data block size of 4K or greater.
The operating system that runs Oracle Database uses a small operating system block size. For example, if the operating system block size is 1K and the default data block size matches this, the database may be performing an excessive amount of disk I/O during normal operation. For best performance in this case, a database block should consist of multiple operating system blocks.
See Also:Your operating system specific Oracle documentation for details about the default block size.
Tablespaces of nonstandard block sizes can be created using the
CREATE TABLESPACE statement and specifying the
BLOCKSIZE clause. These nonstandard block sizes can have any of the following power-of-two values: 2K, 4K, 8K, 16K or 32K. Platform-specific restrictions regarding the maximum block size apply, so some of these sizes may not be allowed on some platforms.
To use nonstandard block sizes, you must configure subcaches within the buffer cache area of the SGA memory for all of the nonstandard block sizes that you intend to use. The initialization parameters used for configuring these subcaches are described in "Using Automatic Shared Memory Management".
The ability to specify multiple block sizes for your database is especially useful if you are transporting tablespaces between databases. You can, for example, transport a tablespace that uses a 4K block size from an OLTP environment to a data warehouse environment that uses a standard block size of 8K.
Note:A 32K block size is valid only on 64-bit platforms.
Caution:Oracle recommends against specifying a 2K block size when 4K sector size disks are in use, because performance degradation can occur. For an explanation, see "Planning the Block Size of Redo Log Files".
PROCESSES initialization parameter determines the maximum number of operating system processes that can be connected to Oracle Database concurrently. The value of this parameter must be a minimum of one for each background process plus one for each user process. The number of background processes will vary according the database features that you are using. For example, if you are using Advanced Queuing or the file mapping feature, then you will have additional background processes. If you are using Automatic Storage Management, then add three additional processes for the database instance.
If you plan on running 50 user processes, a good estimate would be to set the
PROCESSES initialization parameter to 70.
Data definition language (DDL) statements require exclusive locks on internal structures. If these locks are unavailable when a DDL statement runs, then the DDL statement fails, though it might have succeeded if it had been executed subseconds later.
To specify a DDL lock timeout, use the
DDL_LOCK_TIMEOUT parameter. The permissible range of values for
DDL_LOCK_TIMEOUT is 0 to 1,000,000. The default is 0.
You can set
DDL_LOCK_TIMEOUT at the system level, or at the session level with an
Every Oracle Database must have a method of maintaining information that is used to undo changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. Collectively these records are called undo data. This section provides instructions for setting up an environment for automatic undo management using an undo tablespace.
See Also:Chapter 16, "Managing Undo"
UNDO_MANAGEMENT initialization parameter determines whether an instance starts in automatic undo management mode, which stores undo in an undo tablespace. Set this parameter to
AUTO to enable automatic undo management mode. Beginning with Release 11g,
AUTO is the default if the parameter is omitted or is null.
When an instance starts up in automatic undo management mode, it attempts to select an undo tablespace for storage of undo data. If the database was created in automatic undo management mode, then the default undo tablespace (either the system-created
SYS_UNDOTBS tablespace or the user-specified undo tablespace) is the undo tablespace used at instance startup. You can override this default for the instance by specifying a value for the
UNDO_TABLESPACE initialization parameter. This parameter is especially useful for assigning a particular undo tablespace to an instance in an Oracle Real Application Clusters environment.
If no undo tablespace is specified by the
UNDO_TABLESPACE initialization parameter, then the first available undo tablespace in the database is chosen. If no undo tablespace is available, then the instance starts without an undo tablespace, and undo data is written to the
SYSTEM tablespace. You should avoid running in this mode.
Note:When using the
DATABASEstatement to create a database, do not include an
UNDO_TABLESPACEparameter in the initialization parameter file. Instead, include an
TABLESPACEclause in the
COMPATIBLE initialization parameter enables or disables the use of features in the database that affect file format on disk. For example, if you create an Oracle Database 11g Release 2 (11.2) database, but specify
COMPATIBLE=10.0.0 in the initialization parameter file, then features that require 11.2 compatibility generate an error if you try to use them. Such a database is said to be at the 10.0.0 compatibility level.
You can advance the compatibility level of your database by changing the
COMPATIBLE initialization parameter. If you do, there is no way to start the database using a lower compatibility level setting, except by doing a point-in-time recovery to a time before the compatibility was advanced.
The default value for the
COMPATIBLE parameter is the release number of the most recent major release.
Note:For Oracle Database 11g Release 2 (11.2), the default value of the
COMPATIBLEparameter is 126.96.36.199.0. The minimum value is 10.0.0. If you create an Oracle Database using the default value, you can immediately use all the new features in this release, and you can never downgrade the database.
Note:Oracle no longer offers licensing by the number of concurrent sessions. Therefore the
LICENSE_SESSIONS_WARNINGinitialization parameters are no longer needed and have been deprecated.
If you use named user licensing, Oracle Database can help you enforce this form of licensing. You can set a limit on the number of users created in the database. Once this limit is reached, you cannot create more users.
Note:This mechanism assumes that each person accessing the database has a unique user name and that no people share a user name. Therefore, so that named user licensing can help you ensure compliance with your Oracle license agreement, do not allow multiple users to log in using the same user name.
To limit the number of users created in a database, set the
LICENSE_MAX_USERS initialization parameter in the database initialization parameter file, as shown in the following example:
LICENSE_MAX_USERS = 200