Oracle Initialization Parameters

Initialization Parameters: Oracle designed its database products to be flexible and configurable on a variety of hardware platforms and they succeeded. At present Oracle software can run on more than 70 platforms, from mainframes to home PC networks to PDAs. The secret of its success lies in the software initilization parameters. These parameters can be set and tune for top performance in countless environments. On the other side improper setting and can slow down and even halt the system.
Oracle has now divided the initialization parameters into two groups basic and advanced. In majority of cases, it is necessary to tune only the basic parameters of which there are 20 to 30 to get reasonable performance from the database. In rare cases modification of advanced parameters may required.
Below are the Basic Parameters in alphabetical order with their default value underlined and their breif description.
1. CLUSTER_DATABASE (TRUE/FALSE) boolean: It is a real application cluster RAC parameter that specifies whether or not RAC is enabled.
2. COMPATIBLE(9.2.0 to Default release) integer: It allows you to use a new release at the same time guaranteeing backward compatibility with an earlier release.
3. CONTROL_FILES(OS dependent , 1 to 8 filenames) string: Every database has a controlfile which contains entries that describe the structure of the database. We can specify one or more filenames seperated with a comma.
4. DB_BLOCK_SIZE(8192, 2048 to 32768 depending on OS) integer: It determines the size of the blocks at the time of creation of a database. The value of this parameter must be a multiple of the phyisical block size at device level.
5. DB_CREATE_FILE_DEST(no default value, directory) string: Specifies the default location for Oracle Managed datafiles and controlfiles.
6. DB_CREATE_ONLINE_LOG_DEST_n( no default value, directory) string: Specified the default location of oracle manged controlfiles and redo log files, where n=1, 2, 3, ....5.
7. DB_DOMAIN(no default value, any legal string of name components) string: In a distributed database system, this parameter specifies the logical location of the database within the network structure.
8. DB_NAME(no default value, any legal string) string:It specifies the database identifier of up to 8 characters. It must be specified and must correspond to the name specified in the create database statement.
9. DB_RECOVERY_FILE_DEST(no default value, directory) string: It specifies the default locatiton for flash recovery area(FRA). FRA contains multiplexed copies of contro files, redo logs, archived logs, flashback logs and RMAN backup.
10. DB_RECOVERY_FILE_DEST_SIZE(no default value, bytes)Big integer: It specifies the hard limit on the total space to be used by target database recover files created in flash recovery area.
11. DB_UNIQUE_NAME(db_name/+ASM, any legal string) string: It specifies a globally unique name for the database. Databases with the same db_name within the same db_domain must have a unique db_unique_name.
12. INSTANCE_NUMBER(lowest available no./0, 1-max instances specified during db creation stmt) Integer: It is a RAC parameter that can be specified in parallel mode or exclusive mode.
13. JOB_QUEUE_PROCESSES(0, 0 to 1000) Integer: It specifies the maximum number of processes that can be created for the execution of jobs.
14. LOG_ARCHIVE_DEST_n(no default value, any legal string) string: It is the attribute to specify where to archive redo data, where (n=1, 2, 3...10) destinations.
15. LOG_ARCHIVE_DEST_STATE_n(enable, alternate, reset, defer, enable) string: It specifies the availability state of the corresponding destination, where (n=1, 2, 3...10) for the LOG_ARCHIVE_DEST_n destination parameters.
16. NLS_LANGUAGE(OS dependent, language) string: It specifies the default language of the database.
17. NLS_TERRITORY(OS dependent, territory) string: It specified the name of the territory whose conventions are to be followed for day and week numbering.
18. OPEN_CURSORS(50, 0 to 65535) integer: It specifies the maximum number of open cursors(handles to private SQL areas) a session can have at once.
19. PGA_AGGREGATE_TARGET(10M or 20% of the size of SGA, whichever is greater, 10m-4096gb-1) big integer: It specifies the target aggregate PGA memory available to all server processes attached to the instance.
20. PROCESSES(40 to OS dependent, 6 to OS dependent) integer: It specifies the maximum number of OS user processes that can simultaneously connect to Oracle, including backgroun processes.
21. REMOTE_LISTENER(no default value, any legal string) string: It specifies a network name that resolves to an address or address list of Oracle Net remote listeners(i.e. listeners that are not running on the same machine as this instance).
22. REMOTE_LOGIN_PASSWORD_FILE(shared, none/shared/exclusive) string: It specifies whether oracle checks for a password file.
23. ROLLBACK_SEGMENTS(if not specified, instance uses public rbs, unless undo_management is set to auto) string: Allocates one or more rollback segments by name to this instance.
24. SESSIONS({derived :1.1*PROCESSES)+5} ) It specifies max no. of sessions that can be created in the system. The value should be your estimate of the max no. of concurrent users, plus no. of the backgroun processes, plus approximately 10% recursive sessions.
25. SGA_TARGET(0, OS dependent) big integer: It specifies the total size of all SGA components. If it is specified then following memory parameters are automatically sized.
a. Buffer Cache(DB_CACHE_SIZE)
b Shared Pool( SHARED_POOL_SIZE)
c Large Pool(LARGE_POOL_SIZE)
d Java Pool (JAVE_POOL_SIZE)
e Streams Pool(STREAMS_POOL_SIZE)
If these automatically tuned memory pools are set to non-zero then that value is used as min leves by ASMM.
26. SHARED_SERVERS(0, value should be less than max_shared_servers) integer: It specifies the number of server processes that you want to create when an instance is started.
27. UNDO_MANAGEMENT(manual, manual/auto) string: It specifies which undo space management mode the system should use.
28. UNDO_TABLESPACE(the first available undo tbs in the db, undo tbs name) string: It specifies the undo tablespace to be used when an instance starts up.