Oracle’s password file

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate this DBA. That is if (s)he is allowed to do so. Obviously, his password can not be stored in the database, because Oracle can not access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database.

There are two distinct mechanisms to authenticate the DBA: using the password file or through the operating system.

The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used.

Default location and file name
The default location for the password file is: $ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix and %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.

Deleting a password file
If password file authentication is no longer needed, the password file can be deleted and the init parameter remote_login_passwordfile set to none.

Password file state
If a password file is shared or exclusive is also stored in the password file. After its creation, the state is shared. The state can be changed by setting remote_login_passwordfile and starting the database. That is, the database overwrites the state in the password file when it is started up.
A password file whose state is shared can only contain SYS.

Creating a password file
Password files are created with the orapwd tool.

Difference between a database and an instance

The terms instance and database are closely related, but don’t refer to the same thing.

The database is the set of files where application data (the reason for a database) and meta data is stored.

An instance is the software (and memory) that Oracle uses to manipulate the data in the database. In order for the instance to be able to manipulate that data, the instance must open the database. A database can be opened (or mounted) by more than one instance, however, an instance can open at most one database.

Redo Log

Each Oracle database has a redo log. This redo log records all changes made in datafiles.
Purpose
The redo log makes it possible to replay SQL statements.Before Oracle changes data in a datafile it writes these changes to the redo log. If something happens to one of the datafiles, a backed up datafile can be restored and the redo, that was written since, replied, which brings the datafile to the state it had before it became unavailable.
The same technique is also used in a data guard environment (standby databases): One database (the primary database) records all changes and sends them to the standby databases. These standby databases in turn apply (reply) the arrived redo which keeps the synchronized with the primary database.

Archive Log vs Noarchive Log
As Oracle rotates through its redo log groups, it will eventually overwrite a group which it has already written to. Data that is being overwriten would of course be useless for a recovery scenario. In order to prevent that, a database can (and for production databases should) be run in archive log mode. Simply stated, in archive log mode, Oracle makes sure that online redo log files are not overwritten unless they have been savely archived somewhere.
A database can only be recoverd from media failure if it runs under archive log.

LGWR writes the redo log buffers to disk.
The background process in charge for archiving redo logs is ARCn (if automatic archiving is enabled.)
In order to find out in which mode the instance runs, one can use archive log list from within sql plus.
Log Buffer
All changes that are covered by redo is first written into the log buffer. The idea to first store it in the memory is to reduce disk IO. Of course, when a transaction commits, the redo log buffer must be flushed to disk, because otherwise the recovery for that commit could not be guaranteed. It is LGWR (Log Writer) that does that flushing.

Determining amount of generated redo log

select
n.name, t.value
from
v$mystat t join
v$statname n
on
t.statistic# = n.statistic#
where
n.name = ‘redo size’;

RBA (Redo Byte Address)
The RBA consists of three parts and is ten bytes long:

* Log sequence number
* Block number within this sequence
* Offset within this block

The location of each redo log entry is identified throuhg an RBA. The RBAs are important for dirty db blocks in the buffer cache.

Log sequence number
Whenever Oracle (or more precisely, the log writer process) writes to another online redo log file group (also referred to as log switch), the log sequence number increments by one.
If the database is opened with reset logs, the log sequence number is reset to 1.

Determining optimal redo log size
The optimal size of redo log files can be queried with

select optimal_logfile_size from v$instance_recovery;

Minimizing generation of redo
For a few operations, it is possible to minimize the generation of redo if the nologging option is set.

background processes

Here’s a list of background processes:

* SMON
The System Monitor carries out a crash recovery when a crashed insance is started up again. It also cleans temporary segments.
* PMON
The Process Monitor checks if a user process fails and if so, does all cleaning up of resources that the user process has aquired.
* DBWR
The Database Writer writes dirty blocks from the database buffer to the datafiles. How many DBWn Processes are started is determined by the initialization parameter DB_WRITER_PROCESSES. DBWR also writes the actual SCN with the Block.
* LGWR
The Log Writer writes the redo log buffer from the SGA to the online redo log file.
* MMAN
The memory manager
* MRP
Managed recovery process: the process that applies archived redo log to the standby database.
* RFS
The remote file server process on the standby database receives archived redo logs from the primary database.
* RECO
The Distributed Transaction Recovery Process finds pending (distributed) transaction and resolves them.
* CKPT
The Checkpoint Process reqularly initiates a checkpoint which uses DBWR to write all dirty blocks back to the datafiles, thus synchronizing the database. Since a Checkpoint records the current SCN, in a recovery only redo records with a SCN higher than that of the last checkpoint need to be applied.
* ARCH
The Archiver Process archives redo log files if ARCHIVELOG is enabled.
* Dnnn
The Dispatcher Process is used in a shared server environment.
* Snnn
The Shared Server Process is used in a shared server environment.
* LMON
The lock manager.
* LMD0
* QMNn
AQ Time Manager
* TRWR
Trace writer
* WMON
The wakeup monitor process.
* LCKnnn
Inter-instance locking process.
* SNPnnn
The snapshot process.
* MMON
New background process in Oracle 10g.
* DMON
The Data Guard Broker process.
* SNP
The snapshot process.

Background processes have v$session.type = ‘BACKGROUND’

SGA related init params

Auto tuned SGA parameters

* db_cache_size
* shared_pool_size
* large_pool_size
* java_pool_size

These parameters are called auto tuned because automatic shared memory managment can dynamically change the sizes of these pools if it is enabled.

DB_CACHE_SIZE
The value of this parameter affects the size of the SGA: It sets the size of the default buffer pool.With Oracle 10g, one can turn on Automatic Shared Memory Management which distributes shared memory as required. See also sga_target.
JAVA_POOL_SIZE
The value of this parameter affects the size of the SGA.With Oracle 10g, one can turn on Automatic Shared Memory Management which distributes shared memory as required. See also sga_target.
SHARED_POOL_SIZE
The value of this parameter affects the size of the SGA or more appropriately the size of the shared pool within the SGA.
Apparently, when installing JServer, this parameter must at least be set to 24M.
LARGE_POOL_SIZE
With Oracle 10g, one can turn on Automatic Shared Memory Management which distributes shared memory as required. See also sga_target.

Manual SGA parameters

* db_keep_cache_size
* db_recycle_cache_size
* db_NNk_cache_size
NN being one of 2, 4, 8, 16, 32
* log_buffer
* streams_pool_size

DB_KEEP_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DB_RECYCLE_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
LOG_BUFFER
Up to 9i
The value of this parameter defines the size of the redo log buffer. As the redo log buffer is part of the SGA, it affects the size of the SGA as well.
Setting this value to anything greater than 3M is useless as the log buffer is flushed anyway when it is filled up to 1M or when it is reaches one third of its capacity, whichever comes first. (Thanks to William White who notified me of an error here).

10g
From 10g onwards, there is a lot more to do about the initialization parameter log_buffer. Also, Metalink note 351857.1 states that the size of the log buffer cannot be changed with this paramter from 10gR2, instead, the size will be set by Oracle.
STREAMS_POOL_SIZE
DB_BLOCK_BUFFERS
The value of this parameter affects the size of the SGA, or more precisely, the size of the buffer cache. This parameter is deprecated since 9i, db_cache_size should be used instead.
With Oracle 10g, one can turn on Automatic Shared Memory Management which distributes shared memory (of which the buffer cache is part) as required. See also sga_target.
DB_BLOCK_CHECKSUM
See checking block integrity before writing to disk.
DB_BLOCK_SIZE
Determines the size of a database blocks.
SGA_MAX_SIZE
sga_max_size sets the maximum value for sga_target
If sga_max_size is less than the sum of db_cache_size + log_buffer + shared_pool_size + large_pool_size at initialization time, then the value of sga_max_size is ignored.

SGA_TARGET
This parameter is new with Oracle 10g. It specifies the total amount of SGA memory available to an instance. Setting this parameter makes Oracle distribute the available memory among various components – such as shared pool (for SQL and PL/SQL), Java pool, large_pool and buffer cache – as required.
This new feature is called Automatic Shared Memory Management. With ASMM, the parameters java_pool_size, shared_pool_size, large_pool_size and db_cache_size need not be specified explicitely anymore.
sga_target cannot be higher than sga_max_size.

Initialization Parameters

The initialization parameters can be set in the init.ora file.
There are two common ways to find out to what value an initialization parameter is set:

* show parameter in sql*plus, or
* select value from v$parameter where name = lower(‘param_name’)

The parameters can be changed for the currently connected session with a alter session set … command.
If a parameter should be set in another session, dbms_system.set_bool_param_in_session or dbms_system.set_int_param_in_session can be used.

Audit related parameters
* audit_file_dest
* audit_syslog_level
* audit_sys_operations
* audit_trail
* transaction_auditing

NLS related parameters
* nls_calendar
* nls_comp
* nls_characterset
* nls_currency
* nls_date_format
* nls_date_language
* nls_dual_currency
* nls_iso_currency
* nls_language
* nls_length_semantics
* nls_nchar_conv_excp
* nls_numeric_characters
* nls_sort
* nls_territory
* nls_time_format
* nls_time_tz_format
* nls_timestamp_format
* nls_timestamp_tz_format

Optimizer related parameters
* optimizer_dynamic_sampling
* optimizer_features_enable
* optimizer_index_caching
* optimizer_index_cost_adj
* optimizer_max_permutations
* optimizer_mode
* optimizer_percent_parallel
* optimizer_secure_view_merging

Parameters affecting SGA
The following initialization parameters affect the size of the SGA:

* db_block_buffers,
* db_block_size,
* db_cache_size,
* db_keep_cache_size,
* db_recycle_cache_size,
* java_pool_size.
* large_pool_size,
* log_buffer,
* shared_pool_size,
* streams_pool_size,

Other parameters
ALLOW_FREELIST_GROUPS
This parameter was obsoleted after Oracle V6 and default to true since. At that time, it needed to be set in order to specify the freelist groups parameter in the storage clause.
ALWAYS_ANTI_JOIN
This parameter became obsolete in 9i.
BACKGROUND_DUMP_DEST
background_dump_dest specifies the directory (folder) where trace files of background processes are being written. It also specifies the location for the alert.log file.
It is also used for ORA-00600 errors.
See also max_dump_file_size.
BITMAP_MERGE_AREA_SIZE
BUFFER_POOL_KEEP

BUFFER_POOL_RECYCLE
Deprecated in favour of db_recycle_cache_size
COMMIT_WRITE

COMMIT_WORK

COMPATIBLE
The value of this parameter specifies the version that the database must adhere to.
With Oracle 10g, the value of this parameter must be set at least to 9.2; and once it was set to 10 it cannot be lowered afterwards.
The value of this parameter can be determined with dbms_utility.db_version.
CONTROL_FILES
Every database must have at least one control file that describes important characteristics of the database. This parameter specifies their location.

CONTROL_FILE_RECORD_KEEP_TIME
This parameter controls the minimum number of days that a reusable record is kept in the control file.
Its range is 0 .. 365 (=1 year)
control_file_record_keep_time also governs the size of controlfiles.
CORE_DUMP_DEST
See also max_dump_file_size.
CURSOR_SHARING
This parameter influences hard parses and soft parses and is, according to metalink note 223299.1, one of the top parameters affecting performance.The parameter can be set to either exact, similar or force.
DB_BLOCK_CHECKSUM
Specifies if integrity checking is enabled as block level.
See also log_block_checksum
DB_BLOCK_LRU_LATCHES
This parameter became obsolete in 9i.
DB_BLOCK_MAX_DIRTY_TARGET
This parameter became obsolete in 9i.
DB_CACHE_ADVICE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DB_CREATE_FILE_DEST
DB_CREATE_FILE_DEST sets the default location for Oracle-managed datafiles. This location is also used as the default for Oracle-managed control files and online redo logs if DB_CREATE_ONLINE_LOG_DEST_n is not specified.
You can specify a file system directory as the default location for the creation of datafiles, control files, and online redo logs. However, the directory must already exist; Oracle does not create it. The directory must have appropriate permissions that allow Oracle to create files in it. Oracle generates unique names for the files, and a file thus created is an Oracle-managed file.
This parameter can be useful while creating a database.
DB_CREATE_ONLINE_LOG_DEST_n
DB_CREATE_ONLINE_LOG_DEST_ n(where n= 1, 2, 3, … 5) sets the default location for Oracle-managed control files and online redo logs. You should specify at least two parameters: DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2. This provides greater fault tolerance for the logs if one of the destinations should fail.
If more than one directory is specified, then the control file or online redo log is multiplexed across the directories. One member of each online redo log is created in each directory, and one control file is created in each directory.
The directory must already exist; Oracle does not create it. The directory must have appropriate permissions that allow Oracle to create files in it. Oracle generates unique names for the files, and a file thus created is an Oracle-managed file.
This parameter can be useful while creating a database
DB_DOMAIN
DB_FILE_DIRECT_IO_COUNT
This parameter became obsolete in 9i.
DB_FILE_MULTIBLOCK_READ_COUNT
This parameter specifies how many blocks will be read at once when Oracle performs a full table scan or an index range scan. It doesn’t affect reads on blocks that are indexed (in which case only one block is read).
The value for this parameter should be chosen carefully. The OS on which Oracle is running should be capable of reading db_file_multiblock_read_count*db_block_size in one I/O request. If it is set too high, the optimizer will think that full table scan are cheap and will prefer them to the usage of indexes. On the other hand, setting it to low makes the optimizer choose indexes more often than necessary. By the way, the preference of indexes or full table scans is also influenced by optimizer_index_cost_adj.
DB_FILE_NAME_CONVERT
This parameter is needed if a standby database does not have the same layout on the disk for its files as the primary database.
See also log_file_name_convert
DB_FILES
The maximum number of database files that can be opened for a database.
DB_FLASHBACK_RETENTION_TARGET
This is one of the relevant parameters for Flashback DB.
DB_NAME
This parameter must have the same value as the database name.
DB_RECOVERY_FILE_DEST
This is one of the relevant parameters for Flashback DB.
DB_RECOVERY_FILE_DEST_SIZE
This is one of the relevant parameters for Flashback DB.
DB_WRITER_IO_SLAVES
db_writer_io_slaves simulates asynchronous IO, but they do not perform asynchronous IO, and thus, they’re only meaningful if the OS does not support asynchronous IO.
If the OS supports asynchronous, multible dbwr processes should be used and disk_asynch_io be set to true.
DB_16K_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DB_2K_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DB_32K_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DB_4K_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DB_8K_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DISK_ASYNCH_IO
See also db_writer_io_slaves.

GC_DEFER_TIME
This parameter became obsolete in 9i.
GC_RELEASABLE_LOCKS
This parameter became obsolete in 9i.
GC_ROLLBACK_LOCKS
This parameter became obsolete in 9i.
GLOBAL_NAMES
HASH_AREA_SIZE
The default is 64K, which is far too small for most cases. A range of 512KB to 1MB should be considered.
The memory for a hash join (up to the value specified with hash_area_size) is allocated from the cursor work heap (in the uga.)
See also sort_area_size.
HASH_MULTIBLOCK_IO_COUNT
This parameter became obsolete in 9i.
INSTANCE_NAME
INSTANCE_NUMBER
INSTANCE_NODESET
This parameter became obsolete in 9i.
JOB_QUEUE_INTERVAL
This parameter became obsolete in 9i.
JOB_QUEUE_PROCESSES
Controls how many jobs can run; see also dbms_job.
LM_LOCK
This parameter became obsolete in 9i.
LM_RESS
This parameter became obsolete in 9i.
LOCK_NAME_SPACE
LOCK_SGA
On platform that support it, this parameter can be set to true which will lock the entire SGA into physical memory.
LOG_ARCHIVE_DEST
Deprectated in Enterprise Edition in favour of log_archive_dest_n.
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_n (as well as log_archive_dest) can only be used if the database is running in archive log mode.
A common misstake when moving from the (deprecated) log_archive_dest to log_archive_dest_n is to forget one of the attributes such as SERVICE= or LOCATION= which causes a ORA-16179: incremental changes to “log_archive_dest_1” not allowed with SPFILE when it altered with the alter system command.
Attributes:

* SERVICE
A standby destination
See archiving to standby.
Use the lgwr option to specify LGWR transmission or the arch option to specify ARCH transmission.
* LOCATION
A local file system path, must be defined at least once.
* DELAY=minutes
delays applying of the redo log at the standby site.

v$archive_dest_status allows to query the status (and possibly the errors) for each of the defined archive destinations.
LOG_ARCHIVE_DEST_STATE_n
log_archive_dest_state_N specifies the state for log_archive_dest_N.
LOG_ARCHIVE_FORMAT
The following expandables can be used:

* %s: log sequence number
* %S: log sequence number, zero filled
* %t: thread number
* %T: thread number, zero filled
* %d: DBID

LOG_ARCHIVE_START
This parameter is deprecated in Oracle 10g
This parameter determines if the background process ARCH is started. It can be set to either true or false.
Of course, it makes no sense, if this parameter is set to true if the database is running in noarchive log mode. If ARCH is started with the database being in noarchive log mode, messages like media recovery disabled will be written into the alert.log file.
LOG_BLOCK_CHECKSUM
See also DB_BLOCK_CHECKSUM
LOG_CHECKPOINT_INTERVAL
See Events that trigger a checkpoint
The unit of this parameter is measured in physical operating system blocks, not DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle.
LOG_CHECKPOINT_TIMEOUT
See Events that trigger a checkpoint
LOG_FILE_NAME_CONVERT
This parameter is needed if a standby database does not have the same layout on the disk for its files as the primary database.

FIXED_DATE
Fixed date can be set to a date in the following format:
YYYY-MM-DD HH24-MI-SS
If set, sysdate returns this date instead of the current date.

alter session set nls_date_format = ‘dd.mon.yyyy hh24:mi:ss’;
alter system set fixed_date=’2004-03-02 22:23:24′;
select sysdate from dual;

SYSDATE
——————–
02.mar.2004 22:23:24

MAX_DUMP_FILE_SIZE
This parameter specifies the maximum size for dump files such as trace files.
The unit of this parameter is measured in physical operating system blocks unless it has a suffix M or K, in which case the unit is Megabyte and Kilobyte, respectively. Note, the size of physical operating system blocks is not equal to the size of DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle.
MAX_IDLE_TIME
O7_DICTIONARY_ACCESSIBILITY
Default was true until 8i, and is false since 9i.
false: only privileged users can access the data dictionary.
true: any user who has been granted select any table can select from tables owned by sys. Alternatively, select_catalog_role can be granted.
The parameter should (probably) be set to false. Users that need access to sys owned table should then be granted the select any dictionary privilege.
The setting of this parameter influences grant … ANY .. to … statements.
OS_AUTHENT_PREFIX
See OS authentication
OPEN_CURSORS
This parameter defines how many cursors a session (not the cumulative sum of all sessions) can open at most.
PGA_AGGREGATE_TARGET
According to metalink note 223299.1, this is one of the top parameters affecting performance.
PROCESSES
The value of processes affects the value that the kernel parameter SEMMSL (Maximum number of semaphores in a semaphore set): it should be equal to the value of processes + 10.
If there are more than on instance on a box, the value of the instance with the greatest processes must be taken.
It affects also the optimal setting for SEMMNS (Number of semaphores in the system): 2*highets process value + 1*other process values + 10 * count of instances.
QUERY_REWRITE_ENABLED
See Create Materialized View
This parameter must be set to true to make use of function based indexes. Additionally query_rewrite_integrity must be set to trusted.
QUERY_REWRITE_INTEGRITY
This parameter can be set to either

* enforced
* trusted
* stale_tolerated

This parameter must be set to trusted to make use of function based indexes. Additionally query_rewrite_enabled must be set to true.
See also create materialized view
REMOTE_ARCHIVE_ENABLE
REMOTE_LISTENER
REMOTE_LOGIN_PASSWORDFILE
remote_login_passwordfile specifies if Oracle checks for a password file and if this password file is shared among databases.
The following values are possible:

* none
Oracle ignores the password file if it exists.
* exclusive
Password file is exclusively used by one database. Any user can be added to the password file.
* internal
Used for Oracle Parallel Server
* shared
The password file is shared among databases. However, the only users that can be authenticated are sys (and obsoletly: internal).
If the password file is shared, only SYS can be added to the password file.

RESOURCE_LIMIT
This parameter must be set to true to enforce resource limits assigned to a user through profiles
See also On profile.
RESOURCE_MANAGER_PLAN
Setting this parameter activates the resource manager.
If the paramter is set with a prepending FORCE:, the plan can only be changed by the database administrator.
ROLLBACK_SEGMENTS
Defines the rollback segments that the instance will aquire at startup
On startup, Oracle devides transactions by transactions_per_rollback_segment. If the result is greater than the number of rollback segments actually brought online by the rollback_segments init param, additional rollback segments will be brought online.
SESSIONS
SESSION_CACHED_CURSORS
See Cached cursors.
SHARED_SERVERS
SORT_AREA_SIZE
The default is 64K, which is far too small for most cases. A range of 512KB to 1MB should be considered.
The memory for a sort (up to the value specified with sort_area_size) is allocated from the cursor work heap (in the uga).
See also hash_area_size.
SORT_AREA_RETAINED_SIZE
SORT_MULTIBLOCK_READ_COUNT
This parameter became obsolete in 9i.
SPFILE
Specifies the spfile to be used.
STANDBY_ARCHIVE_DEST
This parameter specifies the location of archived redo logs that come from a primary database.
The value of this parameter is displayed in the v$archive_dest view.
STAR_TRANSFORMATION_ENABLED
STANDBY_FILE_MANAGEMENT
If not set to auto, newly created tablespaces in a standby environment must be recreated manually on the standby servers as well. Similarly, newly added datafiles must be copied to the standby servers as well.
STATISTICS_LEVEL
According to metalink note 223299.1, this is one of the top parameters affecting performance.
It can be set to one of

* ALL
* TYPICAL
* BASIC

SQL_TRACE
Setting sql_trace=true is a prerequisite for using tkprof. It can also be set for a single session with alter session set sql_trace.
After setting sql_trace to true, a trace file will be written.
There is also dbms_support that should allow to trace sessions with more information.
See also diagnostic event 10046.
sql_trace seems to be deprecated since 10.2, but not removed. It still behaves as in earlier versions of Oracle.
TEXT_ENABLE
This parameter became obsolete in 9i.
TIMED_STATISTICS
This parameter must be true in order to gather timing information in v$system_event
It is also useful when using tk prof.
TRANSACTIONS
TRANSACTIONS_PER_ROLLBACK_SEGMENT
UNDO_MANAGEMENT
Set to AUTO to use Oracle 9i’s new automatic undo management.
See Undo Tablespaces.
UNDO_RETENTION
Specifies for how many seconds undo information is kept.
See Undo Tablespaces and dbms_flashback.
UNDO_SUPPRESS_ERRORS
This parameter is important if

* An Oracle database is upgraded to version 9i,
* the upgraded database uses Undo Tablespaces,
* There are still applications that use SET TRANSACTION USE ROLLBACK SEGMENT

If in such a case the parameter is set to TRUE (default is FALSE), there won’t be any errors; although it gets written into the alert log.
See undo tablespaces.
UNDO_TABLESPACE
Specifies the undo tablespaces when using automatic undo management.
USER_DUMP_DEST
The value of user_dump_dest specifies the destination (path to a operating system directory) where user processes will write trace files.
It is also used for ORA-00600 errors.
See also max_dump_file_size.
USE_POST_WAIT_DRIVER
Setting this value to true makes Oracle use post-wait drivers instead of semaphores.
UTL_FILE_DIR
This parameter specifies one more more locations to where files can be written and from where files can be read using utl_file.
Specifying multiple directories in the spfile:

alter system set utl_file_dir=’/foo/bar/dir1′,’/foo/baz/dir2′,’/tmp’ scope=spfile

Thanks to Timothy Trauernicht who notified me of an error here.
WORKAREA_SIZE_POLICY
According to metalink note 223299.1, this is one of the top parameters affecting performance.
Hidden parameters
Parameters whose name starts with an underscore are hidden. Usually, they should not be touched! Oracle won’t probably support the database if one of these parameters were changed.
_ALLOW_RESETLOGS_CORRUPTION
Allows resetlogs even if it will cause corruption.
_COLUMN_TRACKING_LEVEL
If set to 1 (the default), will cause SMON to update sys.col_usage$ with information regarding access patterns on table columns.
_DB_AGING_COOL_COUNT
Touch count set when buffer cooled.
_DB_AGING_FREEZE_CR
Make CR buffers always be too cold to keep in cache.
_DB_AGING_HOT_CRITERIA
Touch count which sends a buffer to head of replacement list.
_DB_AGING_STAY_COUNT
Touch count set when buffer moved to head of replacement list.
_DB_AGING_TOUCH_TIME
This parameter specifies a time period in which the touch count of a buffer within the buffer cache can at most be increased once.
_DB_PERCENT_HOT_DEFAULT
Percent of default buffer pool considered hot.
_DB_PERCENT_HOT_KEEP
Percent keep buffer pool considered hot.
_DB_PERCENT_HOT_RECYCLE
Percent recycle buffer pool considered hot.
_INIT_SQL_FILE
This parameter points to the file that is executed upon creation of the database (create database). As of 9i, the value is ?/rdbms/admin/sql.bsq.
_KGHDSIDX_COUNT
Controls the number of shared area subpools.
_LOG_IO_SIZE
The unit of this parameter is measured in physical operating system blocks, not DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle.
_REALFREE_HEAP_PAGESIZE_HINT
_RECYCLEBIN
This hidden parameter is available in 10g. If set to false, then tables are purged immediately at a drop table.
_SMALL_TABLE_THRESHOLD
_SYSTEM_TRIG_ENABLED
Defaults to true and Oracle recommends setting it to false only during database upgrade.
If this parameter is set to false, then system triggers won’t be executed.
_TRACE_FILES_PUBLIC
Trace files (such as those created by a block dump are only readable by oracle, unless this parameter is set to true.
Setting this parameter to true causes a security risk as sensitive data might be written into the trace files!
_USE_ISM
If a system features ISM (intimate shared memory), Oracle uses it by default. This can be disabled by setting _use_ism to false.
As far as I can see, solaris is the only OS that has ISM.
_USE_ISM_FOR_PGA
_WAIT_FOR_SYNC
If set to false, a transaction that commits does not wait until the redo is flushed. However, a database can then not be restored if it crashes.

Control file – Contents

The control files of a database store the status of the physical structure of the database. The control file is absolutely crucial to database operation. It contains (but is not limited to) the following types of information:

* Database information (RESETLOGS SCN and their time stamp)
* Archive log history
* Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not)
* Redo threads (current online redo log)
* Database’s creation date
* database name
* current archive log mode
* Log records (sequence numbers, SCN range in each log)
* RMAN catalog
* Database block corruption information
* Database ID, which is unique to each DB
The location of the control files is specified through the control_files init param.

Backing up Controlfiles
When a controlfile (multiplexed or not) becomes unavailable (because for example of a media failure, the database cannot continue to operate.Because control files keep track of datafiles the control file should be backed up whenever a change is made to the database (alter database).
The command to back the control file up is: alter database backup controlfile to ‘filename’.

Size of control files
The size of the control files is governed by the following parameters:

* maxlogfiles
* maxlogmembers
* maxloghistory
* maxinstances
* control_file_record_keep_time

Sections
The controlfile contains the following sections:

* ARCHIVED LOG (reusable)
* BACKUP CORRUPTION (reusable)
* BACKUP DATAFILE (reusable)
* BACKUP PIECE (reusable)
* BACKUP REDOLOG (reusable)
* BACKUP SET (reusable)
* BACKUP SPFILE
* CKPT PROGRESS
* COPY CORRUPTION (reusable)
* DATABASE
* DATAFILE
* DATAFILE COPY (reusable)
* DATAFILE HISTORY
* DATABASE INCARNATION
* DELETED OBJECT (reusable)
* FILENAME
* FLASHBACK LOG
* INSTANCE SPACE RESERVATION
* LOG HISTORY (reusable)
* MTTR
* OFFLINE RANGE (reusable)
* RECOVERY DESTINATION
* REMOVABLE RECOVERY FILES
* RMAN STATUS
* RMAN CONFIGURATION
* REDO THREAD
* REDO LOG
* TABLESPACE
* TEMPORARY FILENAME
* THREAD INSTANCE NAME MAPPING
* PROXY COPY

The minimum number of days that a reusable record is kept in the controlfile is controlled by the control_file_record_keep_time parameter.These sections consist of records. The size, total number and number of used record are exposed through v$controlfile_record_section.