| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
When an Oracle instance starts, it reads initialization parameters from an initialization parameter file. This file can be either a read-only text file, or a read/write binary file. The binary file is called a server parameter file, and it always resides on the server. 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 the Oracle Database server. 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 STARTUP command.
For more information on server parameter files, see "Managing Initialization Parameters Using a Server Parameter File". For more information on the STARTUP command, see "Understanding Initialization Parameter Files".
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 | init$ORACLE_SID.ora
For example, the initialization parameter file for the
|
$ORACLE_HOME/dbs
For example, the initialization parameter file for the
|
| Windows | init%ORACLE_SID%.ora |
%ORACLE_HOME%\database |
Sample Initialization Parameter File
The following is an example of a text initialization parameter file used to start a database instance on a UNIX system.
control_files = (/u0d/lcg03/control.001.dbf,
/u0d/lcg03/control.002.dbf,
/u0d/lcg03/control.003.dbf)
db_name = lcg03
db_domain = us.oracle.com
log_archive_dest_1 =
"LOCATION=/net/fstlcg03/private/yaliu/testlog/log.lcg03.fstlcg03/lcg03/arch"
log_archive_dest_state_1 = enable
db_recovery_file_dest =
/net/fstlcg03/private/yaliu/testlog/log.lcg03.fstlcg03/lcg03/arch
db_recovery_file_dest_size = 100G
db_block_size = 8192
processes = 1000
sessions = 1200
open_cursors = 1024
shared_servers = 4
remote_listener = tnsfstlcg03
compatible = 11.1.0
memory_target = 1500M
ddl_lock_timeout = 10
nls_language = AMERICAN
nls_territory = AMERICA
Oracle Database provides generally appropriate values in the sample initialization parameter file provided with your database software or created for you by DBCA. 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. For any relevant initialization parameters not specifically included in the initialization parameter file, the database supplies defaults.
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, 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, initialization parameter file changes made by the ALTER SYSTEM statement can persist across shutdown and startup. This is discussed in "Managing Initialization Parameters Using a Server Parameter File".
This section introduces you to some of the basic initialization parameters you can add or edit before you create your new database.
The following topics are contained in this section:
The COMPATIBLE Initialization Parameter and Irreversible Compatibility
See Also:
Oracle Database Reference for descriptions of all initialization parameters including their default settings
Chapter 5, "Managing Memory" for a discussion of the initialization parameters that pertain to memory management
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 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.acme.com, edit the parameters of the new parameter file as follows:
DB_NAME = test DB_DOMAIN = us.acme.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_NAME and DB_DOMAIN initialization parameters and re-creating the control file.
See Also:
Oracle Database Utilities for information about using theDBNEWID utility, which is another means of changing a database nameDB_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 not the same, the database does not start.
DB_DOMAIN is a text string that specifies the network domain where the database is created. This is typically the name of the organization that owns the database. If the database you are about to create will ever be part of a distributed database system, give special attention to this initialization parameter before database creation.
See Also:
Part V, "Distributed Database Management" for more information about distributed databasesA flash 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 Oracle-managed current database files (datafiles, control files, and online redo logs).
You specify a flash recovery area with the following initialization parameters:
DB_RECOVERY_FILE_DEST: Location of the flash recovery area. This can be a directory, file system, or Automatic Storage Management (ASM) disk group. It cannot be a raw file system.
In a RAC environment, this location must be on a cluster file system, 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 flash recovery area. This initialization parameter must be specified before DB_RECOVERY_FILE_DEST is enabled.
In a 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_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters. You must disable those parameters before setting up the flash recovery area. You can instead set values for the LOG_ARCHIVE_DEST_n parameters. If you do not set values for local LOG_ARCHIVE_DEST_n, then setting up the flash recovery area will implicitly set LOG_ARCHIVE_DEST_10 to the flash recovery area.
Oracle recommends using a flash 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 flash recovery areaThe 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 using a default operating system dependent filename or, if you have enabled Oracle-managed files, 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.
Caution:
Use extreme caution when setting this specifyingCONTROL_FILE filenames. If you inadvertently specify a file that already exists and execute the CREATE DATABASE statement, the previous contents of that file will be overwritten.Oracle strongly recommends you use at least two control files stored on separate physical disk drives for each database.
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 need to specify. Typically, DB_BLOCK_SIZE is set to either 4K or 8K. If you do not set a value for this parameter, 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, 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:
DB_BLOCK_SIZE=4096
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.
The 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, you will have additional background processes. If you are using Automatic Storage Management, then add three additional processes.
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, the DDL statement fails, though it might have succeeded if it had been executed subseconds later.
To enable DDL statements to wait for locks, specify a DDL lock timeout—the number of seconds a DDL command waits for its required locks before failing.
To specify a DDL lock timeout, use the DDL_LOCK_TIMEOUT parameter. The permissible range of values for DDL_LOCK_TIMEOUT is 0 to 100,000. The default is 0.
You can set DDL_LOCK_TIMEOUT at the system level, or at the session level with an ALTER SESSION statement.
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 14, "Managing Undo"The UNDO_MANAGEMENT initialization parameter determines whether or not 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 has no value.
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 undo management mode, then the default undo tablespace (either the system-created SYS_UNDOTS 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 has been specified during database creation or 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. You should avoid running in this mode.
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 1 (11.1) database, but specify COMPATIBLE = 10.0.0 in the initialization parameter file, then features that requires 11.1 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. If you do advance the compatibility of your database with the COMPATIBLE initialization parameter, 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 1 (11.1), the default value of theCOMPATIBLE parameter is 11.1.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.See Also:
Oracle Database Upgrade Guide for a detailed discussion of database compatibility and the COMPATIBLE initialization parameter
Oracle Database Backup and Recovery User's Guide for information about point-in-time recovery of your database
Note:
Oracle no longer offers licensing by the number of concurrent sessions. Therefore theLICENSE_MAX_SESSIONS and LICENSE_SESSIONS_WARNING initialization 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
